Обсуждение: 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
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 > From: Peter Eisentraut <peter_e@gmx.net> > Date: Sat, 6 Nov 2004 21:21:25 +0100 > To: Hunter Hillegas <lists@lastonepicked.com>, PostgreSQL > <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] 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/ >
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:
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:
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 > From: Peter Eisentraut <peter_e@gmx.net> > Date: Sat, 6 Nov 2004 21:21:25 +0100 > To: Hunter Hillegas <lists@lastonepicked.com>, PostgreSQL > <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] 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/ > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Вложения
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 ![;)](/media/maillist_attaches/pgsql-general/2004/11/06/1099779136.5308.2.camel@taz.oficina/smiley-4.png)
On Sat, 2004-11-06 at 18:56, Ed L. wrote:
![;)](/media/maillist_attaches/pgsql-general/2004/11/06/1099779136.5308.2.camel@taz.oficina/smiley-4.png)
On Sat, 2004-11-06 at 18:56, Ed L. 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
Вложения
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 listedin 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 intendedrecipient, is strictly prohibited.