Black Locust Software

Technology Solutions for Business

Database Developers' Quick-Reference to PostgreSQL

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.



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 ... maybe 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, \password postgres 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 SHOW hba_file;. Mine was at /etc/postgresql/9/1/main/pg_hba.conf in one installation and /etc/postgresql/9.3/main/pg_hba.conf in another. 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

\q

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.

Creating a database:

Execute a Script File from psql \i FileName

Remember the Semicolon!

Every command terminates with a semicolon. Coming from MS Sql Server, that's a little hard to get used to.

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 );

Add table only if it does not exist

This one's better in both PostgreSQL and MySQL than in MS SQL Server also
CREATE TABLE IF NOT EXISTS tablename ( ... );

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

Resetting the Forgotten Password

You can make use of the peer authentication option for this. Go back to your pg_hba.conf file and change the md5 authentication back to peer. Do a sudo /etc/init.d/postgresql restart and you can reconnect using peer authentication: sudo -u postgres psql postgres to reset your password. Naturally, you'll want to change back to md5 authentication thereafter and restart again.

What Databases Are on My Server?

From psql, that's \l. If you're in pgAdminIII, you can refresh and expand the left-hand pane.

What Tables Are in My Database?

From psql, that's \dt. Or ... the direct query of the schema tables:
SELECT * FROM information_schema.tables WHERE table_catalog = 'mydbname' and table_schema not in ('pg_catalog', 'information_schema');

Does Table X Exist in my PostgreSQL DB?

Almost identical to MySQL's query. In PostgreSQL it's
SELECT COUNT( * ) FROM information_schema.tables WHERE table_catalog = 'mydbname' AND table_name = 'mytablename';

Does Table X Have Column Y in my PostgreSQL DB?

Again, very similar to MySQL ...
select * from information_schema.columns WHERE table_catalog = 'mydbname' and table_name = 'mytablename';

Cool Things in PostgreSQL

Weird Things in PostgreSQL