Mass Import/Generate PKs

Hunter Hillegas wrote:

I have a CSV file with 400,000 lines of email mailing list
information that I need to migrate to a new PostgreSQL database.

Each line has all the info I need except a PK (I usually use an int4
column for a PK).

You could import the file into PostgreSQL and add a primary key column
later.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

That sounds nice and easy...

So, I would do something like 'ALTER TABLE' to generate the PK column? What
would be the best way to populate it? Is there an area of doco I should be
looking at?

Thanks,
Hunter

the simplest way to do it seems to be adding a SERIAL column to your
table, and then adding a primary key constraint:

1)insert data into table
2)ALTER TABLE <table> ADD id SERIAL;
3)ALTER TABLE <table> ADD CONSTRAINT <table>_pk PRIMARY KEY (id);

you can check the docs for the SERIAL type:
http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL

On Sat, 2004-11-06 at 17:29, Hunter Hillegas wrote:

On Saturday November 6 2004 2:13, Franco Bruno Borghesi wrote:

the simplest way to do it seems to be adding a SERIAL column to your
table, and then adding a primary key constraint:

1)insert data into table
2)ALTER TABLE <table> ADD id SERIAL;
3)ALTER TABLE <table> ADD CONSTRAINT <table>_pk PRIMARY KEY (id);

You may also need to populate the id column with unique values in between
these two steps with something like "

update table set id = nextval('table_id_seq'::text) where id isnull"

I don't think SERIAL does that for you.

Ed

I've tested it, and the SERIAL type populates the column when you add it
;)

On Sat, 2004-11-06 at 18:56, Ed L. wrote:

Yes you can use the copy command. Check for

copy TABLE NAME from 'c:\\bcpdata\\Files\\FILENAME.txt' with delimiter
as '\t' NULL as '';

When creating a table, use an incremental column (data type is serial).
Hope the above helps.

- Goutam

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Hunter Hillegas
Sent: Saturday, November 06, 2004 3:01 PM
To: PostgreSQL
Subject: [GENERAL] Mass Import/Generate PKs

I have a CSV file with 400,000 lines of email mailing list
information that I need to migrate to a new PostgreSQL database.

Each line has all the info I need except a PK (I usually use
an int4 column for a PK).

If the file were smaller I would probably just use Excel to
pop in a PK and then just load into the table...

Since Excel chokes on files greater than 65k lines, this won't work.

Is there a way to get this done inside psql for instance? Or
another route?

Thanks,
Hunter

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)

Confidentiality Notice
The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited.