Simple COPY examples

README.md

Simple COPY examples

Two simple examples of quickly loading data into PostgreSQL with the COPY command.

• First, loading data to a database on your own computer in localhost.sh
• Second, loading data to a database hosted on the Internet in cloud.sh

Much more sophisticated stuff is possible. Refer to the official docs and consider adopting a wrapper like django-postgres-copy for more complicated tasks.

cloud.sh

#!/bin/sh

echo "Creating table"
psql -h <your ip> -U postgres -d copy_test -f `pwd`/create.sql;

echo "Moving the CSV to the tmp directory so permissions aren't an issue"
cp `pwd`/data.csv /tmp/data.csv

echo "COPYing data"
psql -h <your ip> -U postgres -d copy_test -f `pwd`/copy.sql

echo "Verifying data loaded"
psql -h <your ip> -U postgres -d copy_test -f `pwd`/count.sql;

echo "Dropping table"
psql -h <your ip> -U postgres -d copy_test -f `pwd`/drop.sql;

copy.sql

\copy names FROM '/tmp/data.csv' HEADER CSV;

count.sql

SELECT COUNT(*)
FROM names;

create.sql

CREATE TABLE names (
    name         varchar(500),
    number       integer,
    date         date
);

data.csv

drop.sql

DROP TABLE IF EXISTS names;

localhost.sh

#!/bin/sh

echo "Creating database"
createdb copy_test;

echo "Creating table"
psql -d copy_test -f `pwd`/create.sql;

echo "Moving the CSV to the tmp directory so permissions aren't an issue"
cp `pwd`/data.csv /tmp/data.csv

echo "COPYing data"
psql -d copy_test -f `pwd`/copy.sql

echo "Verifying data loaded"
psql -d copy_test -f `pwd`/count.sql;

echo "Dropping database"
dropdb copy_test;