SQL#

See also

PostgreSQL guide

Name conventions#

  • Timestamp columns: created_at, updated_at and deleted_at. (Some projects use created and modified.)

Code style#

Paginate data#

Do not use LIMIT with OFFSET. OFFSET becomes more inefficient as its value increases. Instead, filter on the table’s primary key, which has near-constant performance. For example:

SELECT id, mycolumn
FROM mytable
WHERE
    id > %s
    AND myfilter = %s
ORDER BY id
LIMIT 1000

Load and dump data#

Use the copy meta-command instead of the COPY command, so that file accessibility and privileges are those of the user, not the server – such that no SQL superuser privileges are required.

Code format#

Format SQL files with pg_format, which has web and command-line interfaces.

Web#

  1. Open https://sqlformat.darold.net

  2. Paste your SQL text

  3. Set Functions to Lower case

  4. Click Format my code

CLI#

On macOS, using Homebrew, install it with:

brew install pgformatter

Then, change into the project’s directory and run, for example:

find . -name '*.sql' -exec pg_format -f 1 -o {} {} \;

Reference#