Обсуждение: 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