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.
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
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));
\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
This example specifies the column names. The primary key ID column is set automatically.
% cat customers.txt firstname.lastname@example.org,Bob Johnson email@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 | firstname.lastname@example.org | Bob Johnson 2 | email@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 firstname.lastname@example.org, Bob Johnson email@example.com, Jane Doe firstname.lastname@example.org,andy
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