serial, sequence, and COPY FROM

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