For a new project I will be using PostgreSQL. I have more experience with MySQL so I wanted to quickly learn about PostgreSQL and port over some of the skills I have.

Roles

Permissions are managed with “roles”. To see all roles, type \du. To see the privileges for all tables, run \l. Here is a list of privileges.

Working with Rails

For a Rails application, create a rails role and make it the owner:

createdb -O rails app_name_development

From a psql prompt, type \list to see all databases, and \c database to connect to a database by name. \dt will show all the tables.

If the user did not have privileges to create a database there will be an error running rake. To add the createdb permission for the rails use:

alter role rails createdb

To verify this role is added run the following query. A role full list is here.

select rolcreatedb from pg_roles where rolname = 'rails';
 rolcreatedb
-------------
 t

Working with CSV

Like MySQL PostgreSQL supports working with data from CSV files. The following example uses a company_stuff database with a customers table. First we need to create the database, connect to it and create the table.

create database company_stuff;

\c company_stuff;
create table customers 
    (id serial not null primary key, 
    email varchar(100), 
    full_name varchar(100));

Type \d customers to verify the table is set up correctly.

Assuming we have the same CSV file from the previous article when I covered how to work with CSV files using MySQL, we can load it into PostgreSQL using the copy command.

This example specifies the column names. The primary key ID column is set automatically.

% cat customers.txt
bob@example.com,Bob Johnson
jane@example.com,Jane Doe

copy customers(email, full_name) 
from '/tmp/customers.txt' 
delimiter ',' CSV;

Verify the contents of the customers table.

select * from customers;
 id |      email       |  full_name
----+------------------+--------------
  1 | bob@example.com  |  Bob Johnson
  2 | jane@example.com |  Jane Doe

Now we can insert a new record, then dump all the records out again as a new CSV file.

copy customers(email, full_name) 
to '/tmp/more_customers.csv' 
with delimiter ',';

Verify the output of the CSV file:

~ $ cat /tmp/more_customers.csv
bob@example.com, Bob Johnson
jane@example.com, Jane Doe
andy@example.com,andy

Running Queries

Running a query from the command line and combining with grep is very useful.

Here is quick search in the “customers” database for columns named like “email”:

~ $ psql -U andy -d company_stuff -c "\d customers" | grep email
     email     | character varying(100) |

That’s it for now!

More PostgreSQL Posts

Some of my blog posts on PostgreSQL