在postgres COPY批处理插入期间如何增加主键? [英] How to increment primary key during postgres COPY batch insert?

问题描述

我有一个具有 serial id约束的表:

I have a table with serial id constraint:

id serial NOT NULL,
CONSTRAINT pricing_cache_pkey PRIMARY KEY (id)

现在，我想使用 postgres COPY命令将csv数据批量插入表中.

Now I want to use the postgres COPY command to batch insert csv data into the table.

COPY tablename FROM STDIN WITH CSV;

问题:csv文件(如果当然没有ID列).如何批量插入行并自动增加数据库中的ID?

Problem: the csv file if course does not have an ID column. How can I batch insert the rows and automatically increment the IDs in the database?

推荐答案

copy 命令允许您指定要填充的列.如果省略 id 列，则将使用以下序列中的值填充该列:

The copy command allows you to specify which columns to populate. If you omit the id column, it will be populated with the values from the sequence:

copy pricing_cache (column_1, column_2) from stdin with csv
col_1_value,col_2_value
col_1_value,col_2_value
\.

您尚未向我们显示您完整的表格定义.上面的语句假设您的表中(除了 id 列之外，还有两个名为 column_1 和 column_2 的列)包含用逗号分隔的这两列的值(由于 with csv )

You haven't shown us your complete table definition. The above statements assumes that there are two columns named column_1 and column_2 in your table (in addition to the id column) and that the input data contains values for those two columns separated by a comma (because of the with csv)

这篇关于在postgres COPY批处理插入期间如何增加主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！