PostgreSQL/Table/File to Table

Copies data from a system file into the publishers table

CREATE TABLE employee (
    ID         int,
    name       varchar(10),
    salary     real,
    start_date date,
    city       varchar(10),
    region     char(1)
);
insert into employee (ID, name,    salary, start_date, city,       region)
              values (1,  "Jason", 40420,  "02/01/94", "New York", "W");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (2,  "Robert",14420,  "01/02/95", "Vancouver","N");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (4,  "Linda", 40620,  "11/04/97", "New York", "N");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (5,  "David", 80026,  "10/05/98", "Vancouver","W");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (6,  "James", 70060,  "09/06/99", "Toronto",  "N");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (7,  "Alison",90620,  "08/07/00", "New York", "W");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N");
insert into employee (ID, name,    salary, start_date, city,       region)
              values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W");
select * from employee;
-- Copy the employees table to the emp_table file, using a pipe (|) as the field delimiter
COPY employee TO "c:\\employee_data" USING DELIMITERS "|";
-- Copies data from a system file into the publishers table:
COPY employee FROM "c:\\employee_data";

drop table employee;

Copying an ASCII file to data table

CREATE TABLE employee (
    ID         int,
    name       varchar(10)
);
select * from employee;
/*
file: subjects.sql
1,Joe
2,Alison
3,Jess
*/
-- Copying an ASCII file
COPY subjects FROM "c:\\subjects.sql"
               USING DELIMITERS "," WITH NULL AS "\null";
drop table employee;