Postgres for developers

Common commands to help developers when dealing with databases, specifically postgresql.

Export CSV

An example of extracting data from the database, this sort of thing is useful if you are manually testing or checking what is in the database.

copy (select id from document) To '/tmp/docids.csv' with CSV DELIMITER ',' HEADER;

Create JSON from CSV

The bash command column (see man column) can be used to parse a csv file into JSON. For example if a file test.csv has the following contents:

1,one,uno
2,two,dos
3,three,tres

… is run through column with the follow arguments…

column -J -s',' test.csv --table-columns=num,en,es

it will output:

{
   "table": [
      {
         "num": "1",
         "en": "one",
         "es": "uno"
      },{
         "num": "2",
         "en": "two",
         "es": "dos"
      },{
         "num": "3",
         "en": "three",
         "es": "tres"
      }
   ]
}

Restore a dump into a local database

Restoring from a sql dump file, after running make start

Ensure any application is disconnected from the database - if using docker-compose then you can use docker-compose stop
Ensure your current environment has the following variables setup (you can use command line args for psql but this starts to get clumsy). Note, we start by connecting to the postgres database so that we can drop and create the database in question $MYDB.
```
export PGPASSWORD=password
export PGDATABASE=postgres
export MYDB=navigator
export PGHOST=localhost
export PGUSER=navigator
export PGPASSWORD=localpassword
```
Drop any existing database
```
psql -c "drop database $MYDB" 
```
Recreate your database
```
psql -c "create database $MYDB" 
```
The database can now be simply restored, remember to now use $MYDB when connecting/restoring:
```
export PGDATABASE=$MYDB
psql -f mydump.sql
```
Now you can restart any applications.

If you are doing this frequently you may wish to create your own script.