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 (AT) postgresql (DOT) org)

Hunter Hillegas wrote:
You could import the file into PostgreSQL and add a primary key column
later.

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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




---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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/s...ATATYPE-SERIAL

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (FreeBSD)

iD8DBQBBjT5221dVnhLsBV0RAlGvAJ0aK0iF/6oH53xdMiI8YKqhCQsH6ACeKH5F
SK0L3yjPNatg65thWZyOIck=
=xlVQ
-----END PGP SIGNATURE-----

On Saturday November 6 2004 2:13, Franco Bruno Borghesi wrote:
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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (FreeBSD)

iD8DBQBBjUw/21dVnhLsBV0RAgIsAJ0XYXium3Qs+1SPflb9gRs73Ap6SwCgle O0
+djocSv8cNc9eL/VIIiwQnA=
=NJ/7
-----END PGP SIGNATURE-----

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



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.


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