To create a Postgres user sam and a database samdb that is owned by the sam user:

$ su - postgres
$ createuser -P -A -D sam
$ createdb -O sam samdb

(Change the -A, and -D options to affect the new user's abilities.)

Edit the user's password with:

template1=# alter user username_here with password 'password_here';

If you're going to use a boolean condition in your where clause, you're going to want to make sure you use an operator so that the index is used (if present).

For example, this will not use the index:

db=> SELECT id FROM users WHERE active;

This will use the index:

db=> SELECT id FROM users WHERE active = true;

Create a custom type which describes the structure of the rows returned:

CREATE TYPE func_return_type AS (column_name column_type, ...);

Then create the function:

CREATE OR REPLACE FUNCTION some_func (param_type, ...)
RETURNS SETOF func_return_type AS '
 SELECT columns FROM table WHERE condition;
' LANGUAGE 'SQL';

Use the form ::type to ensure that a value is cast to the proper type:

SELECT * FROM table WHERE column = 5::bigint;

In this case, column is indexed and is a bigint. The index will not be used if the query planner doesn't encounter a value of the index type. Note: this only applies in PostgreSQL 7.x.

If you need to convert a TEXT column to a BYTEA column, you can use a cast expression to do the work:

ALTER TABLE mytable ALTER COLUMN col TYPE bytea USING
  decode(replace(col, '\\', '\\\\'), 'escape');

Note that altering the column type of a column is only supported beginning with PostgreSQL version 8.0. For more information, see the manual.

If you want to create a table that has the same structure as the result set of a query, run:

mydb> CREATE TABLE foobar AS SELECT ...;

If instead of a blank space you'd prefer something that tells you a particular column in a row is NULL, you can set it:

\pset null '(null)'

If you want to run a query or a function and ditch the output, you can do something like this:

xinu=# select my_void_function() \g /dev/null

If you want to see how a query like \du is being built:

\set ECHO_HIDDEN

If you have a query like this:

SELECT max(some_column) FROM mytable WHERE ...;

and you want to eliminate the sequence scan that kills the query's performance, an alternative is:

SELECT some_column FROM mytable WHERE ... ORDER BY some_column DESC LIMIT 1;

MIN and MAX aggregates can be eliminated this way.

If you've munged your template1 database, you can rebuild it from the template0 database.

First, you must set the datistemplate attribute of template1 to false so we can drop it:

xinu=# UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
UPDATE 1
xinu=# DROP DATABASE template1;
DROP DATABASE

Next, re-create template1 using template0 as a template:

xinu=# CREATE DATABASE template1 WITH TEMPLATE = template0;
CREATE DATABASE

Then restore the datistemplate attribute of template1:

xinu=# UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';
UPDATE 1

If you see the following when trying to load a newly transplanted database, it may be due to a difference in architecture (32-bit vs. 64-bit):

FATAL:  incorrect checksum in control file.

The moral is: never do a direct copy of the data directory. It isn't considered good practice.

If you have a table 'mytable' whose columns all have default values defined and you want to insert a record using all default values, run this query:

INSERT INTO mytable DEFAULT VALUES;

Use the psycopg.Binary() function to escape the binary data:

>>> db = psycopg.connect("dbname=%s user=%s" % (database, user))
>>> db.autocommit(True)
>>> cursor = db.cursor()
>>> file = "/path/to/binary_file.jpg"
>>> fd = open(file, "r")
>>> contents = fd.read(os.stat(file)[6])
>>> fd.close()
>>> cursor.execute("INSERT INTO pr0n (image) VALUES (%s)", (psycopg.Binary(contents)))

In your ~/.psqlrc, add this to emulate MySQL-style borders in query results:

\pset border 2

It is possible to order by an array element as follows:

SELECT * FROM (SELECT ARRAY[1, 2, 3] AS foo) sub ORDER BY foo[1];

If you intend to use passwords for local database authentication, you'll need to make an adjustment to the pg_hba.conf file:

# TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK        METHOD

#local   all         all                                          ident sameuser
local   all         all                                          password

If you just need the client and not the binary, you can modify the behavior of the port installation like so:

# cd /usr/ports/postgresql7
# make install clean WITHOUT_SERVER=yes

To see the other tasty build options:

# make build

In 7.4 (and possibly 8.1), the routine to dump and restore the entire database cluster follows:

# su - postgres
$ pg_dumpall > postgresql.dmp

Transfer the postgresql.dmp file to the target system, and then do the import:

# su - postgres
$ psql template1 < postgresql.dmp

You can use this query to find the table information:

SELECT
  a.attnum,
  a.attname AS field,
  t.typname AS type,
  a.attlen AS length,
  a.atttypmod AS lengthvar,
  a.attnotnull AS notnull
FROM
  pg_class c,
  pg_attribute a,
  pg_type t
WHERE
  c.relname = 'your_table_name'
  AND a.attnum > 0
  AND a.attrelid = c.oid
  AND a.atttypid = t.oid
  ORDER BY a.attnum

If you're using the psycopg Python module to connect to Postgres, you may find that you have old scripts that use version 1 of psycopg but you have version 2 installed and don't want to modify your scripts. At the time of this writing (and according to http://initd.org/tracker/psycopg/wiki/Migration), the version 2 module provides a very easy "compatibility mode". Just import the psycopg1 submodule and alias it, or fall back to the version 1 module if version 2 is not available:

try:
    # Try importing the compatibility submodule, which will only
    # work if psycopg version 2 is available.
    import psycopg.psycopg1 as psycopg
except Exception, e:
    # Fall back to version 1.
    import psycopg

Use explain:

EXPLAIN SELECT * FROM some_table WHERE condition;

If you want to see how long a query is taking to run, you can either run explain analyze or enable timing for the client by typing \timing.

It is sometimes useful when working with SQL to grab random rowsets, for debugging stored procedures or simply grabbing a random ad for an ad rotator. Either way, different databases use different functions to select this randomization.

• Select a random row with MySQL:

  SELECT * FROM mytable
  ORDER BY RAND()
  LIMIT 1;
  

• Select a random row with PostgreSQL:

  SELECT * FROM mytable
  ORDER BY RANDOM()
  LIMIT 1;
  

• Select a random row with Microsoft SQL Server:

  SELECT TOP 1 column FROM table
  ORDER BY NEWID();
  

You can use RETURNS SETOF record in your plpgsql functions to return a set of rows. Here is an example of using pgsql to build a query using a table name parameter:

CREATE OR REPLACE FUNCTION test(text) RETURNS SETOF record AS '
DECLARE
  _table ALIAS FOR $1;
  _mycursor refcursor;
  _row record;
BEGIN
  OPEN _mycursor FOR EXECUTE ''SELECT * FROM '' || _table;
  FETCH _mycursor INTO _row;
  WHILE FOUND LOOP
    RETURN NEXT _row;
    FETCH _mycursor INTO _row;
  END LOOP;
  RETURN;
END
' LANGUAGE plpgsql;

Here is an example of calling such a function (the names and types of the result columns are required when returning SETOF record):

mydb=> SELECT * FROM test('mytable') AS (col1 integer, col2 text, col3 date);

Select a random row from a table with the following query:

SELECT * FROM table ORDER BY random() LIMIT 1;

Set the PROMPT1 variable using psql prompt escape sequences:

\set PROMPT1 '[%n@%M:%/]=%# '

• %n - User
• %M - Host ([local] or the domain or IP address of the server)
• %/ - Database
• %# - # if superuser, $ if regular user.

Use the following query to show all tables without primary keys:

SELECT
  nspname AS schema,
  relname AS table
FROM pg_class
  LEFT JOIN pg_constraint ON
    pg_constraint.contype = 'p' AND
    conrelid = pg_class.oid
  JOIN pg_namespace ON
    pg_namespace.oid = pg_class.relnamespace
WHERE
  relkind = 'r' AND contype IS NULL;

Set this variable in your ~/.psqlrc to stop on error when psql is used to run non-interactive scripts (e.g. cat file | psql ...):

\set ON_ERROR_STOP 1

Or use it from the command line:

$ psql ... -v ON_ERROR_STOP=1 ...

Use these queries to change the structure of a DOMAIN constraint:

ALTER DOMAIN foo DROP CONSTRAINT bar;
ALTER DOMAIN foo ADD CONSTRAINT bar CHECK (VALUE = 'baz'::text);

Given a table MYTABLE in namespace MYNAMESPACE, you can use this SQL to retrieve the primary key column name(s) for the table:

SELECT
  attname::text
FROM
  pg_attribute
JOIN
  pg_class ON pg_attribute.attrelid = pg_class.oid
JOIN
  pg_namespace ON pg_namespace.oid = pg_class.relnamespace
LEFT JOIN
  pg_constraint ON conrelid = pg_class.oid AND pg_constraint.contype = 'p'
WHERE
  pg_namespace.nspname = 'MYNAMESPACE' AND
  pg_class.relname = 'MYTABLE' AND
  pg_attribute.attnum = ANY (pg_constraint.conkey)
ORDER BY
  pg_attribute.attnum;

If you have a database mytemplate that you'd like to use as a template, update its datistemplate attribute in the pg_database relation:

UPDATE pg_database SET datistemplate = 't' WHERE datname = 'mytemplate';

Then you can use it as a template when creating other databases:

CREATE DATABASE otherdb TEMPLATE mytemplate;