Mass Import/Generate PKs

[Hunter Hillegas]

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 YourEmailAddres sHere" to majordomo@postg resql.org)

[Peter Eisentraut]

Re: Mass Import/Generate PKs

Hunter Hillegas wrote:[color=blue]
> 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).[/color]

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

[Hunter Hillegas]

Re: Mass Import/Generate PKs

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

[color=blue]
> From: Peter Eisentraut <peter_e@gmx.ne t>
> Date: Sat, 6 Nov 2004 21:21:25 +0100
> To: Hunter Hillegas <lists@lastonep icked.com>, PostgreSQL
> <pgsql-general@postgre sql.org>
> Subject: Re: [GENERAL] Mass Import/Generate PKs
>
> Hunter Hillegas wrote:[color=green]
>> 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).[/color]
>
> You could import the file into PostgreSQL and add a primary key column
> later.
>
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
>[/color]



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

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

[Franco Bruno Borghesi]

Re: Mass Import/Generate PKs

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:
[color=blue]
> 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
>
> [color=green]
> > From: Peter Eisentraut <peter_e@gmx.ne t>
> > Date: Sat, 6 Nov 2004 21:21:25 +0100
> > To: Hunter Hillegas <lists@lastonep icked.com>, PostgreSQL
> > <pgsql-general@postgre sql.org>
> > Subject: Re: [GENERAL] Mass Import/Generate PKs
> >
> > Hunter Hillegas wrote:[color=darkred]
> >> 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).[/color]
> >
> > You could import the file into PostgreSQL and add a primary key column
> > later.
> >
> > --
> > Peter Eisentraut
> > http://developer.postgresql.org/~petere/
> > [/color]
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
> [/color]

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

iD8DBQBBjT5221d VnhLsBV0RAlGvAJ 0aK0iF/6oH53xdMiI8YKqh CQsH6ACeKH5F
SK0L3yjPNatg65t hWZyOIck=
=xlVQ
-----END PGP SIGNATURE-----

[Ed L.]

Re: Mass Import/Generate PKs

On Saturday November 6 2004 2:13, Franco Bruno Borghesi wrote:[color=blue]
> 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);[/color]

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@postg resql.org so that your
message can get through to the mailing list cleanly

[Franco Bruno Borghesi]

Re: Mass Import/Generate PKs

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

iD8DBQBBjUw/21dVnhLsBV0RAgI sAJ0XYXium3Qs+1 SPflb9gRs73Ap6S wCgleO0
+djocSv8cNc9eL/VIIiwQnA=
=NJ/7
-----END PGP SIGNATURE-----