Thread: copy from
I need additional help involving the copy from command I have a database with several fields: id, name, address, zip id is the primary key and I use nextval to get a value. The file I want to upload is in comma separated value, that does not have a primry key (or an id) field. How would I use copy from and assign the correct id number? Adam Lang Systems Engineer Rutgers Casualty Insurance Company
I get it. I upload to the temp table, then for the select/insert I just do the nextval(seq_id) for the primary key field, correct? Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Adam Lang" <aalang@rutgersinsurance.com> Cc: <pgsql-sql@postgresql.org> Sent: Monday, August 14, 2000 5:17 PM Subject: Re: [SQL] copy from > > One way would be to copy the data into a temporary table that doesn't > have the id field, and then > insert into table (fields) select * from temptable; > > Stephan Szabo > sszabo@bigpanda.com > > On Mon, 14 Aug 2000, Adam Lang wrote: > > > I need additional help involving the copy from command > > > > I have a database with several fields: id, name, address, zip > > > > id is the primary key and I use nextval to get a value. > > > > The file I want to upload is in comma separated value, that does not have a > > primry key (or an id) field. > > > > How would I use copy from and assign the correct id number? > > > > Adam Lang > > Systems Engineer > > Rutgers Casualty Insurance Company > >
Hmmm... well, I don't think I have an "explicit" nextval. I created the table and then I did a create sequence broker_id; Are you implying that I can set the field to automatically create a nextval? Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Adam Lang" <aalang@rutgersinsurance.com> Cc: <pgsql-sql@postgresql.org> Sent: Tuesday, August 15, 2000 12:34 PM Subject: Re: [SQL] copy from > Well, if you define your main table to have a serial, or an explicit > default nextval(seqid), you won't even need to do that. Just leave > the column off the insert list and the default should fill it for you. > > Stephan Szabo > sszabo@bigpanda.com > > On Tue, 15 Aug 2000, Adam Lang wrote: > > > I get it. I upload to the temp table, then for the select/insert I just do > > the nextval(seq_id) for the primary key field, correct?
Gotcha. Now, if I do an insert and just don't specify that field at all, it
will, use the default value, correct? As long as I don't "touch" the field
with anything it uses the default.
Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Adam Lang" <aalang@rutgersinsurance.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, August 15, 2000 2:00 PM
Subject: Re: [SQL] copy from
>
> Sort of. You can give the field a default value of
> nextval(<sequence>) which means that if you do not specify
> the column in an insert, it automatically gets the default
> value which should be the next value in the sequence.
> Note, that not putting the column is different from inserting a
> NULL into the field.
>
> (Example:
> sszabo=# create sequence xyzseq;
> CREATE
> sszabo=# create table xyzseqtest ( a int default nextval('xyzseq'), b
> int);
> CREATE
> sszabo=# insert into xyzseqtest (b) values (2);
> INSERT 172188 1
> sszabo=# insert into xyzseqtest (b) values (3);
> INSERT 172189 1
> sszabo=# select * from xyzseqtest;
> a | b
> ---+---
> 1 | 2
> 2 | 3
> (2 rows)
> )
>
> There are issues about this dealing with rules and triggers where another
> row may be inserted or the default may be evaluated a second time where
> you want to get the value you just inserted back, but in general it works.
>
> On Tue, 15 Aug 2000, Adam Lang wrote:
>
> > Hmmm... well, I don't think I have an "explicit" nextval. I created the
> > table and then I did a create sequence broker_id;
> >
> > Are you implying that I can set the field to automatically create a
nextval?
Well, if you define your main table to have a serial, or an explicit default nextval(seqid), you won't even need to do that. Just leave the column off the insert list and the default should fill it for you. Stephan Szabo sszabo@bigpanda.com On Tue, 15 Aug 2000, Adam Lang wrote: > I get it. I upload to the temp table, then for the select/insert I just do > the nextval(seq_id) for the primary key field, correct?
Sort of. You can give the field a default value of
nextval(<sequence>) which means that if you do not specify
the column in an insert, it automatically gets the default
value which should be the next value in the sequence.
Note, that not putting the column is different from inserting a
NULL into the field.
(Example:
sszabo=# create sequence xyzseq;
CREATE
sszabo=# create table xyzseqtest ( a int default nextval('xyzseq'), b
int);
CREATE
sszabo=# insert into xyzseqtest (b) values (2);
INSERT 172188 1
sszabo=# insert into xyzseqtest (b) values (3);
INSERT 172189 1
sszabo=# select * from xyzseqtest;a | b
---+---1 | 22 | 3
(2 rows)
)
There are issues about this dealing with rules and triggers where another
row may be inserted or the default may be evaluated a second time where
you want to get the value you just inserted back, but in general it works.
On Tue, 15 Aug 2000, Adam Lang wrote:
> Hmmm... well, I don't think I have an "explicit" nextval. I created the
> table and then I did a create sequence broker_id;
>
> Are you implying that I can set the field to automatically create a nextval?
On Tue, 15 Aug 2000, Adam Lang wrote: > Gotcha. Now, if I do an insert and just don't specify that field at all, it > will, use the default value, correct? As long as I don't "touch" the field > with anything it uses the default. Right, as long as you don't specify the field in the column list. There's a special case I should mention. If you want to insert only default values into all columns, the correct way is: "insert into table default values" -- It usually doesn't come up, but could if you had a table that was only a sequence value;