Обсуждение: serial, sequence, and COPY FROM
All, I have a pipe delimited text file I'm trying to copy to a table. The file has 17 fields per line. The table has 18, with that last field (record) a serial with sequence. I have done: select setval('sequence_name_seq', 555, 'TRUE') but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter '|' the copy stops at the first row, insisting that it's missing data for the field record. Well, yeah... I can make this work with inserts but not with COPY FROM. What I've been doing is dumping it into a mysql table with an auto_increment field and then dumping that into a text file and using that for the COPY FROM; certainly clumsy. How might this be done? r
Serial fields have a default value of nextval, so if you add an 18th field to your text file with DEFAULT in every record it should work as intended. -- Brandon Aiken CS/IT Systems Engineer -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of rloefgren@forethought.net Sent: Tuesday, September 12, 2006 12:28 PM To: pgsql-general@postgresql.org Subject: [GENERAL] serial, sequence, and COPY FROM All, I have a pipe delimited text file I'm trying to copy to a table. The file has 17 fields per line. The table has 18, with that last field (record) a serial with sequence. I have done: select setval('sequence_name_seq', 555, 'TRUE') but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter '|' the copy stops at the first row, insisting that it's missing data for the field record. Well, yeah... I can make this work with inserts but not with COPY FROM. What I've been doing is dumping it into a mysql table with an auto_increment field and then dumping that into a text file and using that for the COPY FROM; certainly clumsy. How might this be done? r ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
On Tue, Sep 12, 2006 at 09:27:55AM -0700, rloefgren@forethought.net wrote: > I have a pipe delimited text file I'm trying to copy to a table. The > file has 17 fields per line. The table has 18, with that last field > (record) a serial with sequence. I have done: > select setval('sequence_name_seq', 555, 'TRUE') > but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter > '|' > the copy stops at the first row, insisting that it's missing data for > the field record. Well, yeah... > I can make this work with inserts but not with COPY FROM. What I've > been doing is dumping it into a mysql table with an auto_increment > field and then dumping that into a text file and using that for the > COPY FROM; certainly clumsy. How might this be done? You could provide a column list: COPY tablename (col1name, col2name, ..., col17name) FROM ... Or, easier than loading/dumping through another database, run the file through a filter that adds the numbering: perl -lne 'print "$_|$."' file.txt > file_numbered.txt If the file were large and you didn't want to make a copy of it then you could use a script like this: #!/usr/bin/perl -ln BEGIN {print "copy tablename from stdin delimiter '|';";} print "$_|$."; END {print "\\."} # should also work without this line Run the script and pipe the output into psql: script_name file.txt | psql database_name -- Michael Fuhr
rloefgren@forethought.net wrote: > All, > > I have a pipe delimited text file I'm trying to copy to a table. The > file has 17 fields per line. The table has 18, with that last field > (record) a serial with sequence. I have done: > select setval('sequence_name_seq', 555, 'TRUE') > but when I do: COPY tablename FROM '/path/to/file/file.txt' delimiter > '|' > the copy stops at the first row, insisting that it's missing data for > the field record. Well, yeah... > I can make this work with inserts but not with COPY FROM. What I've > been doing is dumping it into a mysql table with an auto_increment > field and then dumping that into a text file and using that for the > COPY FROM; certainly clumsy. How might this be done? Use a column list in the COPY command, something like COPY tablename (col2, col3, col4) FROM '/foo/bar.txt'; where the col1 (which you leave out) contains the SERIAL stuff. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tue, 2006-09-12 at 13:48, Brandon Aiken wrote: > Serial fields have a default value of nextval, so if you add an 18th > field to your text file with DEFAULT in every record it should work as > intended. Or you can use the copy table (field1,field2,field3,...field18) from stdin; syntax, and leave out the serial field from the list of columns.
On Tue, Sep 12, 2006 at 02:48:30PM -0400, Brandon Aiken wrote: > Serial fields have a default value of nextval, so if you add an 18th > field to your text file with DEFAULT in every record it should work as > intended. DEFAULT works with INSERT but not with COPY: test=> CREATE TABLE foo (col1 integer, col2 integer, col3 serial); NOTICE: CREATE TABLE will create implicit sequence "foo_col3_seq" for serial column "foo.col3" CREATE TABLE test=> COPY foo FROM stdin DELIMITER '|'; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1|2|DEFAULT >> \. ERROR: invalid input syntax for integer: "DEFAULT" CONTEXT: COPY foo, line 1, column col3: "DEFAULT" -- Michael Fuhr
Thanks for all the help, it works and I've learned some more about COPY, which is treated thinly in Douglas's first edition of "PostgreSQL" (which was my source.) r