X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 54EF33A2BE6 for ; Wed, 19 Jan 2005 01:59:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00398-07 for ; Wed, 19 Jan 2005 01:59:40 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id A721D3A2B2D for ; Wed, 19 Jan 2005 01:59:42 +0000 (GMT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j0J1xclk004384 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Tue, 18 Jan 2005 18:59:40 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j0J1xblu055737; Tue, 18 Jan 2005 18:59:37 -0700 (MST) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j0J1xb49055736; Tue, 18 Jan 2005 18:59:37 -0700 (MST) (envelope-from mfuhr) Date: Tue, 18 Jan 2005 18:59:37 -0700 From: Michael Fuhr To: Gautam Saha Cc: pgsql-admin@postgresql.org Subject: Re: Using COPY for bulk upload in a table with sequence field Message-ID: <20050119015937.GA55639@winnie.fuhr.org> References: <41ED9F37.6020403@imsa.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <41ED9F37.6020403@imsa.edu> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.003 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/197 X-Sequence-Number: 16056 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/