Skip site navigation (1) Skip section navigation (2)

Re: numbering rows on import from file

From: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
To: "Alexy Khrabrov" <deliverable(at)gmail(dot)com>
Cc: "sql pgsql" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: numbering rows on import from file
Date: 2008-05-02 23:00:33
Message-ID: dcc563d10805021600y503d7f1ap5258d22e28c4dcc9@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Fri, May 2, 2008 at 3:26 PM, Alexy Khrabrov <deliverable(at)gmail(dot)com> wrote:
> Greetings -- I have a huge table of the form
> (integer,integer,smallint,date).  Its origin is an ASCII file which I load
> with \copy.  Now I want to number the rows, adding an id column as an
> autoincrement from a sequence.  How should I do the import now for the
> sequence to work -- should I add the id column last, so it will not be
> filled by copy and presumably autoincrement?

Add the sequence to the column.  something like this:

create table mytable (id serial primary key, int1 integer, int2
integer, smallint1 smallint, date1 date);
copy mytable (int1,int2, smallint1, date1) from STDIN;
120,2304,4,'2007-01-01'
204,3204,2,'2007-01-02'
(and so on)
\.

with a lot of other dbs, and a lot of languages, you're taught to
perform discrete steps when operating on your data.  Generally
speaking, PostgreSQL is much better at doing the most NOW, not later.
If you've got derived data you want to put into the table, put all the
data into a loading table, and transfer it to the real table with ONE
insert into select from query.

>  Or, once the table is already in, can I add a column and force it to be
> filled with consecutive numbers, effectively numbering the rows?

Bad idea.  As mentioned before, every update or insert, whether it
succeeds or not will create a dead row in the table.  If you update
(or attempt to update) all rows in a 10,000,000 row table three times,
you now have 30,000,000 dead rows in your table.

In response to

pgsql-sql by date

Next:From: Steve CrawfordDate: 2008-05-03 00:18:27
Subject: Re: numbering rows on import from file
Previous:From: chester c youngDate: 2008-05-02 22:33:39
Subject: Re: numbering rows on import from file

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group