Skip to content

Instantly share code, notes, and snippets.

@palewire
Last active August 6, 2018 12:58
Show Gist options
  • Save palewire/91539314f475815f30dba978a88f4370 to your computer and use it in GitHub Desktop.
Save palewire/91539314f475815f30dba978a88f4370 to your computer and use it in GitHub Desktop.
Simple COPY examples

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.

#!/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 names FROM '/tmp/data.csv' HEADER CSV;
SELECT COUNT(*)
FROM names;
CREATE TABLE names (
name varchar(500),
number integer,
date date
);
NAME NUMBER DATE
ben 1 2012-01-01
joe 2 2012-01-02
jane 3 2012-01-03
DROP TABLE IF EXISTS names;
#!/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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment