Обсуждение: COPY from CSV, passing in default value?
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.
Le 2012-05-14 à 13:31, adebarros a écrit :
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?
Import to a temp table, fill in the default value, then copy to the final table, something like this:
CREATE TEMPORARY TABLE salaries_import(LIKE (salaries) );
COPY salaries_import(town, supervisor, salary)
FROM '...',
WITH (format csv);
INSERT salaries(town, country, supervisor, salary)
SELECT town, 'County Name', supervisor, salary
FROM salaries_import;
Hope that helps!
François Beausoleil
CREATE TEMPORARY TABLE salaries_import(LIKE (salaries) );
COPY salaries_import(town, supervisor, salary)
FROM '...',
WITH (format csv);
INSERT salaries(town, country, supervisor, salary)
SELECT town, 'County Name', supervisor, salary
FROM salaries_import;
Hope that helps!
François Beausoleil
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
Hi, Ben, Thanks for the suggestion. I do realize I could create the default value for the column; however, I probably should have specified that in this scenario I would want to supply several different values for the county (or any other missing field) during import. i.e., if I first imported a CSV with data for Cumberland County and then later another CSV for Putnam County. In any event, the solution from Francois does the trick. Thanks again! -- View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-from-CSV-passing-in-default-value-tp5708672p5708990.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
A more elegant way is to include the create table and copy into a function and pass the default values to this function. Am 17/05/2012 03:35, schrieb adebarros: > Hi, Ben, > > Thanks for the suggestion. I do realize I could create the default value for > the column; however, I probably should have specified that in this scenario > I would want to supply several different values for the county (or any other > missing field) during import. i.e., if I first imported a CSV with data for > Cumberland County and then later another CSV for Putnam County. > > In any event, the solution from Francois does the trick. > > Thanks again! > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/COPY-from-CSV-passing-in-default-value-tp5708672p5708990.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. >