09 04
2009

PostgreSQL essentials

...or how to get started. The main purpose of this page is to serve my own needs whenever I come back to PostgreSQL.

New user

There are two ways. Either use the shell command createuser or do it in the psql-prompt. Here we are going to use a combination of both. All shell commands are run as the postgres system user.

$ createuser
Enter name of role to add: foo
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
$ psql
Welcome to psql 8.3.6, the PostgreSQL interactive terminal.
...
postgres=# ALTER ROLE foo WITH ENCRYPTED PASSWORD 'bar';
ALTER ROLE
postgres=# \q

New database

We want to have our newly created user as the owner of the new database.

$ createdb -O foo baz

Returning the newly created row's id

INSERT INTO table ... RETURNING id

Other resources