From: Michael Fuhr <mike@fuhr.org>
To: Gautam Saha <gsaha@imsa.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: Using COPY for bulk upload in a table with sequence field
Date: Tue, 18 Jan 2005 18:59:37 -0700
Message-ID: <20050119015937.GA55639@winnie.fuhr.org> (raw)
In-Reply-To: <41ED9F37.6020403@imsa.edu>
References: <41ED9F37.6020403@imsa.edu>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mike@fuhr.org, gsaha@imsa.edu
Subject: Re: Using COPY for bulk upload in a table with sequence field
In-Reply-To: <20050119015937.GA55639@winnie.fuhr.org>
References: <41ED9F37.6020403@imsa.edu> <20050119015937.GA55639@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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