PostgreSQL Tricks

Bash Alias to Login

I create a bash alias for servers I need to connect to via psql. This is a building block for other tricks in this post. Such an alias follows the following pattern:

alias codeghardb='psql --host --dbname cgdb --username cgdbuser -W'

In the above statement, -W prompts for a password. Now whenever I need to use psql for a cgdb database, I just run codeghardb.

Load Data from file using copy command

First off, please understand that there is a difference between copy and \copy command. From what I understand, you should use \copy because it allows a regular user to insert data from file while copy only allows superuser to insert data from file. Also, copy is a server command and \copy is a client command.

You login to database via psql as below (we created an alias in the first step so we don’t have to enter these details every time)


and get psql prompt as below


You can run the following command on the psql prompt:

cgdb=> \copy mytable (columnone, columntwo) from insert.txt with delimiter as '!'

We expect to have two columns in insert.txt, each delimited (or separated) by !, for the above command.

Run script from file in psql

Sometimes you create a SQL script with multiple statements and commands which need to be run through psql. We use the alias we created before, put the copy command we create previously in a file called script.sql. We also need to do some post-loading processing. So our script.sql may look like this:

--First insert the file
\copy mytable (columnone, columntwo) from insert.txt with delimiter as '!'
--Now update so that all empty string columns are made NULL
update mytable set columntwo = NULL where columntwo = ''
--We are done

All statements in the SQL script need to be on their own line. Run the script.sql file as below:

codeghar@codeghar:~$ codeghardb < script.sql

I learned this trick from Run external script in psql.

Hat Tips

Many thanks to COPY – PostgreSQL Wiki; Adding Data with INSERT and COPY; Using PostgreSQL’s COPY function effectively; Populating a Database;


Comments are closed.

%d bloggers like this: