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
asyour 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general