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.

First I reviewed some of the equivalent commands.

MySQL to PostgreSQL Resources

This post will cover:

  • Working with users in PostgreSQL
  • Working with CSV files
  • Running queries from the command line

In this post, I’ll share what I learned!


Users in PostgreSQL are called “roles”. To “describe” all the “users” type \du (describe users) in psql. These are “meta-commands,” and they start with a backslash (it tilts backwards) not a forward slash. 😊

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 “database-name” 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 customers 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.

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

The file has no header row and a couple of data rows for demonstration purposes.

cat customers.csv,Bob Johnson,Jane Doe

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

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

If the rows loaded successfully, 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)
  '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

We can follow the basics shown here with the COPY command to efficiently load and dump data.

Running Queries

Running a query from the command line allows us to script operations.

Here is quick search of the “customers” table content for text that matches “email”:

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

This is a quick way to check whether the table has a column named “email.”

Running ad hoc queries and commands is useful for scripting operations.

That’s it for now!