Getting started with PostgreSQL - a Database Primer

A quick guide to getting started with the PostgreSQL database server:

Download and install PostgreSQL. If you're using some kind of BSD or Linux, it's probably already in the ports / packages repository. You may need to use initdb to create a new database cluster, and edit pg_hba.conf to grant your users / machines connect privileges to the server. If you're in a UNIX-like OS, you'll need to "su - postgres". After that:

Connect as postgres:

psql -U postgres

Create a role:

CREATE ROLE testuser LOGIN PASSWORD 'test123';

Create a database:

CREATE DATABASE testdb;

Note that the database is created in the cluster ENCODING. If you want to specify a different encoding (not recommended) you can use something like:

CREATE DATABASE testdb OWNER = testuser ENCODING = 'UTF8';

List databases, groups, users, quit:

\l+ \dg+ \du+ \q

Connect as the new user to the new database:

psql -U testuser testdb

Create a table:

CREATE TABLE test (ID serial PRIMARY KEY, name varchar(25) NOT NULL UNIQUE);

List table, relations, schemas, describe table:

\d

\dt

\dn

\d test

Insert some data:

INSER INTO test VALUES (default, 'quux');

Do a query:

SELECT * FROM test;

\q

Delete a database:

psql -U postgres

DROP DATABASE testdb;

Once you've got the hang of things, you can use \? and \h for help:
\?
\h
\h CREATE DATABASE

You can now delete the testuser role:

psql -U postgres
DROP ROLE testuser;

Create your new username and database, and connect to it. Create a simple database schema.

Here's a fun PostgreSQL feature: HTML output. \H turns it on, \H again turns it of:

We have a very simple table with 3 entries:

goods=> \d countries;
Table "public.countries"
Column | Type | Modifiers
---------+-----------------------+-----------
country | character varying(50) | not null
Indexes:
"countries_pkey" PRIMARY KEY, btree (country)

goods=> SELECT * FROM countries;
country
---------
A
B
C
(3 rows)

\H
goods=> \H
Output format is html.
goods=> SELECT * FROM countries;

We can use \o to redirect the output to a file:

goods=> \o countries.html
goods=> SELECT * FROM countries;

\q and view the file in your web browser :-).



Now that you've got the feel of psql, you can use PgAdmin III (or PHPPgAdmin or whatever interface you like, like OpenOffice.Org Base or Microsoft Office Access). PgAdmin III also shows the SQL commands it's going to run, so it's a good way to learn SQL.




Use this for a tutorial and this for reference.