Обсуждение: 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 > > Michael Glaesemann > grzm 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 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 Looks like WITH NULL AS '' might work > > >> >> 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 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 > > Looks 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 > > > >> >> >>> >>> 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.