从csv复制到具有ID序列列自动递增PSQL的表 [英] Copy from csv into table with id serial column auto-incrementing PSQL

问题描述

正在寻找一种解析解决方案，该解决方案将从CSV复制到我的数据库中，并生成最终的序列ID。要么在后端，要么在复制。照原样，我的ID在复制后为空。

Looking for a parse solution that copies from CSV into my database with the end result of serial ID's generated. Either on the backend, or upon copying. As is, my ID's are null after the copy.

echo CREATE TABLE IF NOT EXISTS qs_facts_template (id SERIAL NOT NULL, symbol varchar(8), timestamp date, close real null, open real null, high real null, low real null, volume real null, CONSTRAINT qs_facts_template_key PRIMARY KEY (id)) WITH (OIDS=TRUE) TABLESPACE pg_default; ALTER TABLE qs_facts_template OWNER to postgres; | psql -U postgres -h %host% readyloop

echo CREATE TABLE IF NOT EXISTS qs_facts AS select * from qs_facts_template;| psql -U postgres -h %host% readyloop

echo \copy qs_facts (symbol, timestamp, close, open, high, low, volume) from PROGRAM 'cat C:\Users\user\Documents\quantshare\quotes.csv' DELIMITER ';' CSV HEADER| psql -U postgres -h %host% %dbName%

此帖子： https://unix.stackexchange.com/questions/277080/copy-csv-data-同时填充序列列并不能解决我的问题。我使用了Guido提到的命令并使副本起作用，但是id都为空。

this post: https://unix.stackexchange.com/questions/277080/copy-csv-data-while-simultaneously-filling-serial-column did not solve my issue. I used the command mentioned by Guido and got the copy to work, but the id's are all null.

我在想也许可以在副本之后设置值。 / p>

I was thinking maybe I could set the values AFTER the copy.

推荐答案

您可以编写之前触发器：

CREATE FUNCTION id_trigger() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.id = nextval(pg_get_serial_sequence(TG_ARGV[0], 'id'));
   RETURN NEW;
END;$$;

CREATE TRIGGER id_trigger BEFORE INSERT ON qs_facts_template
   FOR EACH ROW EXECUTE PROCEDURE id_trigger('qs_facts_template');

这篇关于从csv复制到具有ID序列列自动递增PSQL的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！