复制列顺序

20 浏览
0 Comments

复制列顺序

我正在尝试使用带有HEADER选项的COPY命令，但是我的文件中的标题行的顺序与数据库中指定的列顺序不同。

文件中的列名顺序是否必要？

我的代码如下：

COPY table_name (
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'schema_name'
  AND table_name   = 'table_name'
            )
       FROM 'file.csv'  
       WITH DELIMITER ',' CSV HEADER;

我的数据库表与file.csv的顺序不同，我想选择表的顺序并将数据从csv复制到表中。

0

3 答案

0 Comments

COPY命令的列顺序问题及解决方法

在使用COPY命令进行数据导入和导出时，有时会遇到列顺序不符合预期的问题。下面是一种解决方法：

首先，我们可以使用以下设置来确保即使对于复杂的JSON列，也能成功地执行COPY TO/FROM操作：

COPY "your_schema_name.yor_table_name" (
    SELECT string_agg(
        quote_ident(column_name), 
        ','
    ) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'yuour_table_name' 
    AND TABLE_SCHEMA = 'your_schema_name'
) FROM STDIN WITH CSV DELIMITER E'\t' QUOTE '\b' ESCAPE '\';
--此处为行数据
\.

这个方法的关键点有三个：

  • 明确地从information_schema.columns中筛选，并使用table_schema过滤。否则，当一个表名在多个模式中出现时，可能会得到意外的列。
  • 使用quote_ident函数，确保命令不会因为表列使用了Postgres注册关键字（如user或unique）而出错。通过quote_ident函数，这些列将被包裹在双引号中，从而保证了安全导入。
  • 对于处理复杂/嵌套的JSON列，我还发现以下设置也很有效：

    • QUOTE '\b' - 使用退格符进行引用
    • DELIMITER E'\t' - 使用制表符作为分隔符
    • ESCAPE '\' - 使用反斜杠进行转义

有人可能会问，使用SELECT来获取列名不就是复制了默认行为吗？

确实，在没有使用括号和列名列表时，SELECT获取的列名会复制默认行为。但是，在这种情况下，我们使用了其他设置和函数，以确保能够解决列顺序问题，特别是对于复杂的JSON列。因此，即使在默认情况下可能会出现问题的情况下，我们也可以通过这种方法来保证列顺序的正确性。

0
0 Comments

COPY column order问题的原因是需要将一个表的结果复制到另一个表中，且需要保持列的顺序一致。解决方法是使用Bash脚本模拟一个子查询，通过psql命令将原表的结果复制到标准输出，然后将结果传递给目标表的COPY命令。

具体的解决方法如下：

1. 首先，使用psql命令将原表的结果复制到标准输出中：

psql schema_origin -c 'COPY table_origin TO stdout'

2. 然后，使用psql命令查询原表的列名，并将列名拼接成一个以逗号分隔的字符串：

psql schema_origin -t -c "select string_agg(column_name, ',') from information_schema.columns where table_name = 'table_origin'"

3. 接下来，将步骤2中查询到的列名作为参数传递给目标表的COPY命令，并通过管道将步骤1中的结果传递给目标表的COPY命令：

psql schema_destination -c "COPY table_destination ($(步骤2的结果)) FROM stdin"

通过以上步骤，就可以实现将原表的结果按照列的顺序复制到目标表中。

此外，以下是一些相关的链接提供了关于COPY命令、获取列名和获取结果的元组的一些建议：

- [StackOverflow关于COPY命令的回答](https://stackoverflow.com/a/36496423)

- [StackExchange关于获取列名的回答](https://dba.stackexchange.com/a/22368)

- [StackOverflow关于获取结果的元组的回答](https://stackoverflow.com/a/9934300)

0
0 Comments

COPY列顺序问题的出现原因是因为在COPY命令中无法执行SQL查询，只能列出列名。如果CSV文件的列顺序是，则需要在COPY命令中按照这个顺序列出列名。同时，还有两个相关的问题：是否可以忽略某些列以及如果列的顺序与存储顺序不同是否会有性能损失。

解决方法如下：

1. 忽略某些列：在COPY命令中只列出需要导入的列名，忽略不需要的列名。例如，如果要忽略列c，可以这样写COPY命令：

copy target_table (b, a)
from file.csv
with (delimiter ',', format csv, header)

2. 列顺序与存储顺序不同的性能损失：如果列的顺序与存储顺序不同，会导致额外的性能开销。因为数据库存储数据时通常是按照列的顺序进行存储，如果查询的列顺序与存储顺序不同，数据库会额外进行列的重新排序操作，从而降低查询性能。因此，在设计数据库时，尽量将列的顺序与存储顺序保持一致，可以提高查询性能。

总之，解决COPY列顺序问题的方法是在COPY命令中按照CSV文件的列顺序列出列名，并且可以通过忽略某些列来灵活导入数据。另外，为了避免性能损失，应尽量将列的顺序与存储顺序保持一致。

0