Re: COPY from CSV, passing in default value?

Does creating a table with a default not work?

CREATE TABLE salaries (
Town varchar(30),
County varchar(30) NOT NULL DEFAULT 'Australia',
Supervisor varchar(30),
StartDate date,
Salary int,
Benefits int
);

You might also want an auto-incrementing primary key, especially if you are importing data so you can delete any duplicates…

CREATE TABLE salaries (
id serial unique PRIMARY KEY,
Town varchar(30),
County varchar(30) NOT NULL DEFAULT 'Australia',
Supervisor varchar(30),
StartDate date,
Salary int,
Benefits int
);

An alternative that becomes simpler for importing repeatedly is to create a temporary table with the same column names as your csv file, but all the data types varchar. Import the csv (which is now easy even if there are '' in the salary field, which are not int) and then insert (with appropriate casting) the results from the temp table into the real table.

cheers

Ben

On 15/05/2012, at 1:31 AM, adebarros wrote:

> Assuming I have a table structured like so:
>
> CREATE TABLE salaries (
> Town varchar(30),
> County varchar(30),
> Supervisor varchar(30),
> StartDate date,
> Salary int,
> Benefits int
> );
>
> If I have a CSV with only three of those fields, I can import like this:
>
> COPY salaries (Town, Supervisor, Salary)
> FROM 'C:\salaries.csv'
> WITH (FORMAT CSV);
>
> However, what if I wanted to assign a default value during import to
> populate the County field? In my dreams it would be something like this
> (which does not work):
>
> COPY salaries (Town, 'County Name', Supervisor, Salary)
> FROM 'C:\salaries.csv'
> WITH (FORMAT CSV);
>
> Any ideas?
>
> Thanks.
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-from-CSV-passing-in-default-value-tp5708672.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general