pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Using COPY for bulk upload in a table with sequence field
2+ messages / 2 participants
[nested] [flat]

* Using COPY for bulk upload in a table with sequence field
@ 2005-01-18 23:43 Gautam Saha <gsaha@imsa.edu>
  2005-01-19 01:59 ` Re: Using COPY for bulk upload in a table with sequence field Michael Fuhr <mike@fuhr.org>
  0 siblings, 1 reply; 2+ messages in thread

From: Gautam Saha @ 2005-01-18 23:43 UTC (permalink / raw)
  To: pgsql-admin

Hi:

Can I use COPY command to upload a flat text file into a PG table where
the PK  column (item_id) is Serial type.
The table is an existing table with data and I want to
insert new rows from the flat file. The sequence already defined for 
this column.

If I do not have the item_id data in my flat file, is it going to get the
id from nextval from the sequence?

Any help is appreciated.

Gautam





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Using COPY for bulk upload in a table with sequence field
  2005-01-18 23:43 Using COPY for bulk upload in a table with sequence field Gautam Saha <gsaha@imsa.edu>
@ 2005-01-19 01:59 ` Michael Fuhr <mike@fuhr.org>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Fuhr @ 2005-01-19 01:59 UTC (permalink / raw)
  To: Gautam Saha <gsaha@imsa.edu>; +Cc: pgsql-admin

On Tue, Jan 18, 2005 at 05:43:51PM -0600, Gautam Saha wrote:

> Can I use COPY command to upload a flat text file into a PG table where
> the PK  column (item_id) is Serial type.

What does the documentation say?  What happened when you tried it?

It can be educational to practice on test data so mistakes don't
matter.  And when changing "real" data, consider using a transaction
so you can verify that the changes are correct: issue a BEGIN
statement, then make the changes, then issue enough SELECT statements
to satisfy you that the changes are correct, then issue a COMMIT.
If the changes don't look right then do a ROLLBACK.

Here's an excerpt from the COPY page in the Reference part of the
documentation:

  If a list of columns is specified, COPY will only copy the data in
  the specified columns to or from the file.  If there are any columns
  in the table that are not in the column list, COPY FROM will insert
  the default values for those columns.

Here's an experiment:

CREATE TABLE foo (id serial PRIMARY KEY, name text);
INSERT INTO foo (name) values ('John');
INSERT INTO foo (name) values ('David');

SELECT * FROM foo ORDER BY id;
 id | name  
----+-------
  1 | John
  2 | David
(2 rows)

COPY foo (name) FROM stdin;
James
Robert
\.

SELECT * FROM foo ORDER BY id;
 id |  name  
----+--------
  1 | John
  2 | David
  3 | James
  4 | Robert
(4 rows)

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-01-19 01:59 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-18 23:43 Using COPY for bulk upload in a table with sequence field Gautam Saha <gsaha@imsa.edu>
2005-01-19 01:59 ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox