Re: create a script which imports csv data

>
>
> From: pgsql-general-owner(at)postgresql(dot)org On Behalf Of Robert Buckley
> Sent: Donnerstag, 28. Juni 2012 13:53
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] create a script which imports csv data
>
> Hi,
>
> I have to create a script which imports csv data into postgresql
...and have a few questions about the best way to do it.
>
> The csv data is automatically created from an external database so I
have no influence over which columns etc are downloaded.
>
> The csv comes without an fid field and has therefore no unique
identifier.
>
> How can I best create a table for the import?
>
> Would I first create a table without an fid and then after the import
create a sequence and add the sequence to the table, then somehow update
the fid field?
>
> could anyone show me the best way to do this?
>
>
> at the moment I am doing this...which makes postgresql throw an error
because obviously the 'fid' field is missing from the csv data!
>
> CREATE SEQUENCE fid_seq INCREMENT 1 START 1;
>
> CREATE TABLE Anlagenregister_Aktuell_2011 (
> fid INTEGER NOT NULL DEFAULT nextval('fid_seq'),
> Firma TEXT,
> Anlagenschluessel TEXT,
> Anschrift TEXT,
> PLZ TEXT,
> Ort TEXT,
> Bundesland TEXT,
> Energietraeger TEXT,
> Inbetriebnahmedatum DATE,
> Netzzugangsdatum DATE,
> Netzabgangsdatum DATE,
> Ausserbetriebnahmedatum DATE,
> Installierte_Leistung_kW numeric(11,4),
> Lastgangmessung TEXT,
> Einspeisemanagement TEXT,
> Spannungsebene TEXT,
> Zaehlpunktbezeichnung TEXT,
> Anlagentyp TEXT,
> Geographische_Koordinaten TEXT,
> Schalloptimierung TEXT,
> Biomasse_KWK_Bonus TEXT,
> Biomasse_Technologie_Bonus TEXT,
> PRIMARY KEY (fid)
> );
>
> copy Anlagenregister_Aktuell_2011 FROM
'/var/www/Anlagenregister_Aktuell_2011.csv' DELIMITERS ';' CSV;

Hello,

1st:

you don't need to create the sequence.
Just use

CREATE TABLE Anlagenregister_Aktuell_2011 (
fid SERIAL NOT NULL ,
...

with serial as data type, a sequence will be built for you in the
backgroud.

2nd:

in COPY you can list the the columns of the csv content:

COPY Anlagenregister_Aktuell_2011 (firma, anlagenschluessel, ...) FROM

As fid is not listed here, it will be filled by its default value
(sequence).

best regards,

Marc Mamin