[GENERAL] COPY FROM and sequences

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

[GENERAL] COPY FROM and sequences

Matthew Terenzio
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?


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] COPY FROM and sequences

Matthew Terenzio

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
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] COPY FROM and sequences

Matthew Terenzio

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 [hidden email] so that your
>       message can get through to the mailing list cleanly
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] COPY FROM and sequences

Matthew Terenzio

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 [hidden email] so that
>> your
>>       message can get through to the mailing list cleanly
>>
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [GENERAL] COPY FROM and sequences

Bruno Wolff III
In reply to this post by Matthew Terenzio
On Mon, Jan 08, 2007 at 20:20:42 -0500,
  Matthew Terenzio <[hidden email]> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend