Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities.
I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL
that did not begin with the assumption I couldn't tell a database from a linked list. Now, as I'm getting my feet wet with PostgreSQL, I wanted something similar. Here are some notes and links I have found helpful. Hopefully they will help you as well.
The PostgreSQL FAQ's
are rich with information ... including the product name's pronunciation!
I'm actually looking for something for experienced DB developers ... a quick list to get going.
Log Into PostgreSQL from the OS Prompt
psql -U UserName
Notice that's a CAPITAL U -- unlike we use in MySql.
But what's the default user name?
No, it's not root. Or ... probably not. It's the name of the OS user under which PostgreSQL was originally configured to run. Usually that's "postgres".
If you attempt to log in using a username that has not been created, you get the error:
psql: FATAL: role "UserName" does not exist
It does say "role" rather than "user". Technically, PostgreSQL has roles
rather than users or groups.
You will need to log in as that user in order to change a password or create a new role.
sudo -u postgres psql postgres
Then at the postgres prompt,
and enter your new password twice as prompted.
You can still get an error saying
FATAL: Peer authentication failed for user "postgres"
Study of PostgreSQL authentication methods
reveals that peer authentication is not the recommended method ... and it only works on the local machine anyway ... so I really don't know why it seems to be the default.
Changing the authentication method gets you to the pg_hba.conf file. Where is it? While logged into psql, type
. Mine was at /etc/postgresql/9/1/main/p
. I changed the "local" line's method from "peer" to "md5", restarted the server with
sudo /etc/init.d/postgresql restart
and was able to log in with the password I created.
Quitting the command-line interface
What about a GUI?
PG Admin III seems to be a nice GUI. I don't see as many options as MySQL Workbench, but if you prefer a GUI to the command-line, it looks pretty good. The "SQL Pane" that shows up by default is for the DDL of the selected object, not a place for you to enter a query of your own. To get that window, click the desired database in the left-hand pane, then click the "SQL" icon in the menu bar. The other thing about it that's not as nice as MySQL Workbench or MS SQL Server Management Studio is that it displays only 1 result set when you execute multiple queries.
Creating a database
From the shell command prompt createdb -U UserName DBNAME
From the psql command prompt or a SQL query in pgAdmin III create database DBNAME;
Changing to a different database ... don't!
There is no USE DBName in PostgreSQL. One must disconnect and reconnect. In psql,
Execute a Script File from psql
Remember the Semicolon!
This is like MySQL and UNLIKE MS SQL Server. The semicolon is mandatory.
Autonumber / Identity / Auto_Increment -- SERIAL
The keyword in PostgreSQL is SERIAL:
CREATE TABLE MyTable (ID SERIAL);
Default to Current Date
CREATE TABLE MyTable (ID SERIAL, View_Date timestamp NOT NULL Default CURRENT_TIMESTAMP );
LIMIT - controlling the number of Rows
In MS SQL we use the TOP (n) qualifier at the start of the select statement.
In PostgreSQL, like MySQL, we use the LIMIT n qualifier at the end of the select statement.
Cool Things in PostgreSQL
Check constraints are enforced. MySQL's omission of these is disappointing.
Ability to create a table LIKE another -- copying its structure with that single keyword.
Ability to create a table that INHERITS another's structure. It's very similar to an OOP concept of inheritance. All scans of the parent table also check the child table(s).
As I learn more, I'll consider adding more articles. I'm taking some notes on DDL items and am about to try my first PL/Scheme function to treat hierarchical relationships as a graph to traverse. In the meantime, feel free to visit updated notes at my own website's intro to PostgreSQL.
Please add any comments below with examples or insights that you think will help.