Using COPY command in PostgreSQL

Postgres has a very useful ‘COPY’ command that can be used to transfer data between text files and database tables. This is somewhat similar to Oracle’s SQLLDR utility. I will discuss some of the basic commands to get data from a text file and write them into database tables.

A lot of times, the text files may come in the form of a CSV (Comma-Separated Values) document. It is also easy to create a CSV file from popular spreadsheets like MS Excel. In order to copy data from a CSV file into a database table, you can run the following SQL command:

COPY myschema.mytable
FROM 'c:/dir1/file1.csv'
DELIMITERS ',' CSV;


Note that although the input file is on a Windows machine, we still used the ‘/’ path separator character instead of ‘\’.

One problem with running the COPY command as an SQL command is that you can only run it as superuser (i.e. ‘postgres’ on most systems) or your account needs to have the superuser privilege. If you run this command as a regular user, you will get an error message like this:

ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.


One way to overcome this is to run a variant of the command on the ‘psql’ prompt. ‘psql’ is the Postgres equivalent of Oracle’s ‘sqlplus’.

psql=> \COPY myschema.mytable FROM c:/dir2/file2.txt delimiter '|'

Note that ‘COPY’ command uses the word ‘DELIMITERS’ whereas the ‘\COPY’ command uses ‘DELIMITER’. It took me some time to figure it out. This time I used a pipe-delimited file instead of a comma-delimited file. Both forms are pretty common in the ETL (Extract Transform Load) world.

Now, imagine that you have a bunch of input files and you need to copy the data to a number of destination tables. Instead of typing the commands on psql every time, you could have a .sql file where you write all the copy commands and run that file from psql using psql’s ‘\i’ command. Assuming you have all your copy commands in a file called input.sql:

\COPY myschema.mytable1 FROM c:/dir1/file1.txt delimiter '|'
\COPY myschema.mytable2 FROM c:/dir2/file2.txt delimiter '|'
....


You can now do the following from psql:

psql=> \i input.sql


If you want to go one step further and run the whole thing using a shell script, you can write a script like this:

#!/usr/bin/ksh.exe

if [[ $# != 3 ]]; then
    echo "Usage: $0   "
    exit 1
fi

/your_directory/PostgreSQL/8.4/bin/psql -q -h $1 -d $2 -U $3 -f input.sql

Let’s call this script myscript.ksh. So, at this point, all you will need to do is the call the script like this:

./myscript.ksh  <db_host>  <db_name>  <db_user>

and you should get all the data copy done.

In case you are wondering why I used ksh.exe at the top of the shell script, I wrote the script on Windows under the Cygwin environment. I have kept the article simple and small. For more information on PostgresSQL’s COPY command, you can always google it!