DB Developers' Intro to PostgreSQL

Published:
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.

Gabrielle Roth's Introduction to DB's with PostgreSQL gives a fair introduction. This is just the slide show that would accompany the lecture.
The PostgreSQL FAQ's are rich with information ... including the product name's pronunciation!
Ross Laird provides an example project using PostgreSQL that I found helpful for deciphering some login issues.

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

Open in new window

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

Open in new window

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

Open in new window

Then at the postgres prompt,
 \password postgres

Open in new window

and enter your new password twice as prompted.
You can still get an error saying
 FATAL: Peer authentication failed for user "postgres"

Open in new window

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;

Open in new window

. Mine was at /etc/postgresql/9/1/main/pg_hba.conf. I changed the "local" line's method from "peer" to "md5", restarted the server with
sudo /etc/init.d/postgresql restart 

Open in new window

and was able to log in with the password I created.

Quitting the command-line interface

\q 

Open in new window


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,
 \c

Open in new window

automates that.

Execute a Script File from psql

\i FileName

Open in new window


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

Open in new window


Default to Current Date

CREATE TABLE MyTable (ID SERIAL, View_Date timestamp NOT NULL Default CURRENT_TIMESTAMP ); 

Open in new window


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).
   
JSON field type, beginning in version 9.2. Meaning ... why would I look at Mongo? See Szymon Guz' article PostgreSQL as NoSQL with Data Validation



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.
0
3,010 Views

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.