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 Mysql-to-PostgreSQL Links
- Useful guide on equivalent commands in postgres from mysql
- PostgreSQL quick start for people who know MySQL
- PostgreSQL for MySQL users
- How To Use Roles and Manage Grant Permissions in PostgreSQL on a VPS
More PostgreSQL Posts
Some of my blog posts on PostgreSQL
Comments