COPY FROM and sequences
Is it true that you can't use COPY FROM to fill a table with a SERIAL
type column?
Or rather, how does one approach that situation most effectively?
On Jan 8, 2007, at 8:55 PM, Michael Glaesemann wrote:
On Jan 8, 2007, at 19:20 , Matthew Terenzio wrote:
Is it true that you can't use COPY FROM to fill a table with a SERIAL
type column?Or rather, how does one approach that situation most effectively?
Could you give an example of what you're trying to do? What errors are
you getting?
Just trying to fill a large number of rows in a table that uses a
serial for the primary key, from a comma delimited file
invalid input syntax for integer: ""
I'm not quoting anything, just leaving the the delimited field empty
and expected a seuqence would fill the column as it would in an insert
Show quoted text
Michael Glaesemann
grzm seespotcode net
Import Notes
Reply to msg id not found: 78DC7828-9D98-455F-AD90-0BE6A3203950@seespotcode.net
On Jan 8, 2007, at 9:13 PM, Matthew Terenzio wrote:
On Jan 8, 2007, at 8:55 PM, Michael Glaesemann wrote:
On Jan 8, 2007, at 19:20 , Matthew Terenzio wrote:
Is it true that you can't use COPY FROM to fill a table with a
SERIAL type column?Or rather, how does one approach that situation most effectively?
Could you give an example of what you're trying to do? What errors
are you getting?Just trying to fill a large number of rows in a table that uses a
serial for the primary key, from a comma delimited fileinvalid input syntax for integer: ""
I'm not quoting anything, just leaving the the delimited field empty
and expected a seuqence would fill the column as it would in an insert
Looks like WITH NULL AS '' might work
Show quoted text
Michael Glaesemann
grzm seespotcode net---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Jan 8, 2007, at 9:20 PM, Matthew Terenzio wrote:
On Jan 8, 2007, at 9:13 PM, Matthew Terenzio wrote:
On Jan 8, 2007, at 8:55 PM, Michael Glaesemann wrote:
On Jan 8, 2007, at 19:20 , Matthew Terenzio wrote:
Is it true that you can't use COPY FROM to fill a table with a
SERIAL type column?Or rather, how does one approach that situation most effectively?
Could you give an example of what you're trying to do? What errors
are you getting?Just trying to fill a large number of rows in a table that uses a
serial for the primary key, from a comma delimited fileinvalid input syntax for integer: ""
I'm not quoting anything, just leaving the the delimited field empty
and expected a seuqence would fill the column as it would in an
insertLooks like WITH NULL AS '' might work
I see! you've just got to specify the columns in the COPY FROM
statement and it will use the defaults for those columns not listed
Show quoted text
Michael Glaesemann
grzm seespotcode net---------------------------(end of
broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that
your
message can get through to the mailing list cleanly---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
On Mon, Jan 08, 2007 at 20:20:42 -0500,
Matthew Terenzio <matt@jobsforge.com> wrote:
Is it true that you can't use COPY FROM to fill a table with a SERIAL
type column?Or rather, how does one approach that situation most effectively?
In older versions of postgres you couldn't, in recent versions, you can
provide an explicit column list to COPY and the other columns will get
default values. You can't do this row by row though; there is no equivalent
to DEFAULT as used in INSERT statements for COPY.