Kieran Kaelin

Backend Developer

PostgreSQL: Include files by relative path

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

\ir <filename>

Or, as a longer alternative:

\include_relative <filename>


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.

Collapsible Markdown

When presenting code or logs in Markdown, things tend to get out of hand quickly.
The <details> and <summary> HTML tags can be used to mitigate this, which hide certain parts of your document.
Be aware that Markdown specific syntax constructs within those HTML tags are only guaranteed to be rendered correctly by CommonMark and / or GFM compliant parsers (for example the GitHub Markdown parser).

	<summary>Click to expand this section!</summary>
	<h5>A nice Javascript pitfall!</h5>

	console.log(['1', '7', '11'].map(parseInt));

This Markdown snippet creates the following result:

Click to expand this section!
A nice Javascript pitfall!
console.log(['1', '7', '11'].map(parseInt));

Using dynamic variables in psql

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 $$
	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.