Postgres' sleep functions are not particularly useful, as they should only be utilized for demonstration purposes (like provoking locking situations and then trying to find out who blocked whom). After years of using the pg_sleep() function, which takes seconds as its input argument, I one day discovered that there are more convenient functions that even accept human readable input!

SELECT now();
SELECT pg_sleep_for('5 minutes');
SELECT  /* then do something …. */
SELECT pg_sleep_until('tomorrow 03:00');

More tips and tricks from our very own Kaarel Moppel!

Working on a project, you might consider that one folder is more important than others. For example, src folder might be more relevant to you as a developer than doc, test, or samples, you name it. Or you may want source files to be listed first, e.g. *.c, *.go...

To order the list of files in git diff output, one may use git's diff.orderFile option.

If you are working with PostgreSQL for example, you may want such an order:

src/include/* 
src/common/* 
src/port/* 
config/* 
src/makefiles/* 
src/template/* 
src/backend/* 
src/fe_utils/* 
src/bin/* 
src/interfaces/libpq/* 
src/pl/* 
contrib/* 
src/interfaces/* 
doc/* 
src/test/*

To achieve this:

• create a file with the proper list, e.g. .gitorderfile
• run git config diff.orderFile .gitorderfile

You're done!

Postgres automatically escapes all occurrences of escape sequences if strings aren't prefixed with the E escape constant. This can lead to unexpected results:

select array_to_string(array['first line', 'second line', 'third line'], '\n');

           array_to_string
-------------------------------------
 first line\nsecond line\nthird line

Prefixing the separator string with the escape constant tells Postgres to interpret the sequence literally.

select array_to_string(array['first line', 'second line', 'third line'], E'\n');

 array_to_string
-----------------
 first line     +
 second line    +
 third line

There's no need to prefix the template string when using format, as E-strings are substituted in literally.

select format('first line%ssecond line', E'\n');

   format
-------------
 first line +
 second line

PostgreSQL includes many useful utility functions. One of those is the age function.

SELECT age(timestamp '1999-06-20');

           age
-------------------------
 20 years 3 mons 13 days

Basics

-- a row including only nulls is null
$ SELECT ROW(NULL, NULL) IS NULL;
-> true

-- a row including no nulls is not null
$ SELECT ROW(10, 10) IS NOT NULL;
-> true

Unusual

-- this row is not NULL
$ SELECT ROW(10, NULL) IS NULL;
-> false

-- but it's also not NOT NULL
$ SELECT ROW(10, NULL) IS NOT NULL;
-> false

-- there is a difference between `NOT ROW(...) IS NULL` and `ROW(...) IS NOT NULL`
$ SELECT NOT ROW(10, NULL) IS NULL;
-> true

Wait, what?

-- as mentioned above, a row including only nulls is null
$ SELECT ROW(NULL) IS NULL;
-> true

-- this does not apply recursively, though
$ SELECT ROW(ROW(NULL)) IS NULL;
-> false

What's going on there?

Values inside of a composite type are checked for NULL value equality, which is not the same as recursively checking with IS NULL!

This behavior is explained in this twitter post.

We can (ab)use this to check if a value is literally NULL or just a value that IS NULL:

SELECT  value              AS value,
        value      IS NULL AS is_null,
        ROW(value) IS NULL AS is_null_value
FROM    (VALUES (NULL), (ROW(NULL))) AS x(value);

 value  | is_null | is_null_value
--------+---------+---------------
 <null> | t       | t
 ()     | t       | f

Check out this blog post for more information on NULL behavior.

With PostgreSQL, the DEFAULT PRIVILEGES can be set as follows:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
	GRANT SELECT ON TABLES TO PUBLIC;

All tables in schema public created in the future will be selectable by everyone (PUBLIC).

This does only apply for tables created by the user, who ran ALTER DEFAULT PRIVILEGES.

You can include files in psql using the relative path like so:

\ir <filename>

Or, as a longer alternative:

\include_relative <filename>

Note:

When using \ir from a file that was just included using \ir, the path will be interpreted relative to the nearest file in the include tree.

Problem

The following .sql script will error out prematurely:

create table if not exists foo (
	bar text not null,
	baz text not null
);

copy foo (bar, baz) from stdin (format csv, delimiter ';', header true);
bar;baz
Lorem;ipsum
dolor;sit
amet,;consectetur
\.

Here's the accompanying psql output:

CREATE TABLE
psql:<stdin>:11: ERROR:  missing data for column "baz"
CONTEXT:  COPY foo, line 5: "\."

Solution

Adding a newline after the \. termination sequence fixes the error.

When developing in PostgreSQL, you may run into the fringe situation of being unable to use PL/pgSQL and instead having to fallback to plain psql (as was my case when writing tests with pgTap, since PL/pgSQL insists on using PERFORM over SELECT for void-returning functions).

Writing these tests in plain SQL quickly results in bloated code, since you have to reuse certain IDs over and over again, even when using CTEs.
While psql supports variables in the form of \set {name} {value}, these can not be dynamically set (i.e. using the result of a query).

However, it is possible to abuse runtime parameters (SET {name} TO {value}) for this purpose by making use of PL/pgSQLs EXECUTE, as shown in the following example:

DO $$
BEGIN
	EXECUTE format('SET %I TO %L', 'var.my_test_variable', (SELECT 1));
END $$;

Then, once you have returned to your plain SQL block, you may use SELECT current_setting('var.my_test_variable') to retrieve the value.

If used often, you could even move the EXECUTE block into its own function, receiving both name and value of the runtime parameter, and thus further removing unnecessary boilerplate code.

Sometimes it's necessary to manually specify a constraint name, which should then ideally follow some sort of naming convention or pattern.

Postgres already has an implicit naming convention in place, which goes like this:

{tablename}_{columnname(s)}_{suffix}

• pkey for primary key constraints

  • Single column

    create table foo (
      bar integer primary key
    );

                                Table "public.foo"
     Column |  Type   | Collation | Nullable |             Default
    --------+---------+-----------+----------+---------------------------------
     bar    | integer |           | not null |
    Indexes:
        "foo_pkey" PRIMARY KEY, btree (bar)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      primary key (bar, bar)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           | not null |
     baz    | integer |           | not null |
    Indexes:
        "foo_pkey" PRIMARY KEY, btree (bar, baz)

• key for unique constraints

  • Single column

    create table foo (
      bar integer unique
    );

                Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
    Indexes:
      "foo_bar_key" UNIQUE CONSTRAINT, btree (bar)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      unique (bar, baz)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
     baz    | integer |           |          |
    Indexes:
        "foo_bar_baz_key" UNIQUE CONSTRAINT, btree (bar, baz)

• excl for exclusion constraints

  create table foo (
    bar text,
    baz text,
    exclude using gist (bar with =, baz with =)
  );

               Table "public.foo"
   Column | Type | Collation | Nullable | Default
  --------+------+-----------+----------+---------
   bar    | text |           |          |
   baz    | text |           |          |
  Indexes:
      "foo_bar_baz_excl" EXCLUDE USING gist (bar WITH =, baz WITH =)

• idx for indices

  Indices can not be created without manually specifying a name.

• fkey for foreign key constraints

  • Single column

    create table foo (
      bar integer primary key
    );
    create table qux (
      bar integer references foo
    );

                    Table "public.qux"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
    Foreign-key constraints:
        "qux_bar_fkey" FOREIGN KEY (bar) REFERENCES foo(bar)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      primary key(bar, baz)
    );
    create table qux (
      bar integer,
      baz integer,
      foreign key(bar, baz) references foo (bar, baz)
    );

                    Table "public.qux"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
     baz    | integer |           |          |
    Foreign-key constraints:
        "qux_bar_fkey" FOREIGN KEY (bar, baz) REFERENCES foo(bar, baz)

• check for check constraints

  • Single column

    create table foo (
      bar integer check (id > 10)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
    Check constraints:
        "foo_bar_check" CHECK (id > 10)

  • Multiple columns

    create table foo (
      bar integer,
      baz integer,
      check (bar = baz)
    );

                    Table "public.foo"
     Column |  Type   | Collation | Nullable | Default
    --------+---------+-----------+----------+---------
     bar    | integer |           |          |
     baz    | integer |           |          |
    Check constraints:
        "foo_check" CHECK (bar = baz)

• seq for sequences

  create table foo (
    id serial
  );

                              Table "public.foo"
  Column |  Type   | Collation | Nullable |             Default
  --------+---------+-----------+----------+---------------------------------
   id     | integer |           | not null | nextval('foo_id_seq'::regclass)