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

In this post I’ll be sharing what I learned! Statements should be run using the psql command line client.


Users in PostgreSQL are called “roles”. To “describe” all the “users” type \du (described users) in psql. These are “meta-commands.”

Roles have Privileges, which give them the ability to perform various operations.

Type \list (or \l) to see all databases and \c <database-name> to connect to a database (replacing with your database name).

The \dt meta-command describes all tables, and \d with a table name argument, describes a specific table.

Working with CSV

Like MySQL, PostgreSQL supports working with data from CSV files.

Create a company_stuff database and connect to it. Once connected, create a customers table. Run the following commands from psql.

CREATE DATABASE company_stuff;

\c company_stuff

CREATE TABLE customers (
    email TEXT,
    full_name TEXT);

Type \d customers to “describe” the table you just created.

Using the same CSV file from an earlier article (or create a couple sample rows like below), load it into PostgreSQL using the COPY command.

The file should look like this. Create it in your editor (vim /tmp/customers.txt) using an absolute path, if you don’t already have the file in this location.

It only needs a couple of rows for demonstration purposes.

% cat customers.txt,Bob Johnson,Jane Doe

With the file in place, load it using the Copy command into the table you’ve just created.

COPY customers(email, full_name)
FROM '/tmp/customers.txt'

If it was successful, you’ll see COPY 2 as output.

View the rows in the customers table.

SELECT * FROM customers;
 id |      email       |  full_name
  1 |  |  Bob Johnson
  2 | |  Jane Doe

Insert another record and then dump all the records out again as a new CSV file.

INSERT INTO customers (email, full_name) VALUES ('', 'Andrew Atkinson');

COPY customers(email, full_name)
TO '/tmp/more_customers.csv'

Verify the output of the CSV file.

~ $ cat /tmp/more_customers.csv,Bob Johnson,Jane Doe,Andrew Atkinson

The Copy command can be used for loading and dumping data.

Running Queries

Running a query from the command line and combining the output with grep is powerful.

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

psql -U andy -d company_stuff -c "\d customers" | grep email

This can be used to quickly check a particular database, whether a table has a particular column.

That’s it for now!

Mysql to PostgreSQL Resources