I noticed that psql supports setting variables (\set for literals and \gset for query results) and conditional blocks (\if). It also supports loops (with \watch) and recursion of sorts (with \include). Can we express any computation in psql's scripting language? Kinda sounds like it.
This post is for fun. Don't do this. But it happens to also be a nice introduction to some intermediate usage of psql itself.
Let's write a fibonacci sequence calculator. In Python, fib.py:
def fib(n):
if n < 2:
return n
a = 0
b = 1
c = 0
while n > 1:
c = a + b
a = b
b = c
n -= 1
return b
if __name__ == "__main__":
print("nth fib to calculate: ")
n = int(input())
print(fib(n))If we pass in 1 we get 1, if we pass in 9 we get 34. Great!
psql, the programming language
Let's consider psql building blocks.
Variables
We can assign a variable to a literal with \set.
postgres=# \set a 1We can print the variable with \echo.
postgres=# \echo :a
1We can assign the result of a query to a variable with \gset.
postgres=# SELECT 1 + 2 AS x \gset
postgres=# \echo :x
3And we can reference any psql variable in a SQL query.
postgres=# SELECT :x * 2;
?column?
----------
6
(1 row)Conditional execution
We can conditionally execute psql statements with \if.
postgres=# \if 'true'
postgres=# \echo 'here'
here
postgres=# \else
postgres@# \echo 'there'
\echo command ignored; use \endif or Ctrl-C to exit current \if block
postgres@# \endifThe "command ignored" message is a bit confusing. The branches get evaluated immediately, not waiting until the \endif is reached. The message means this code will not ever get run. This message does not show up if run from a script with psql -f.
Let's see how that \if works the other way.
postgres=# \if 'false'
postgres@# \echo 'here'
\echo command ignored; use \endif or Ctrl-C to exit current \if block
postgres@# \else
postgres=# \echo 'there'
there
postgres=# \endifThe "expression" passed to \if (or \elif) seems to must be a truthy string:
a valid value is any unambiguous case-insensitive match for one of: true, false, 1, 0, on, off, yes, no. For example, t, T, and tR will all be considered to be true.
If you want to do a boolean comparison you'll have to execute it in a query and assign the result of the boolean operation to a variable you can then check.
postgres=# SELECT 1 = 2 AS ok \gset
postgres=# \if :ok
postgres@# \echo 'here'
\echo command ignored; use \endif or Ctrl-C to exit current \if block
postgres@# \else
postgres=# \echo 'there'
there
postgres=# \endifLoops
We can execute a query N times with \watch.
postgres=# SELECT 1 \watch count=3 interval=0
Wed Nov 26 11:42:00 2025 (every 0s)
?column?
----------
1
(1 row)
Wed Nov 26 11:42:00 2025 (every 0s)
?column?
----------
1
(1 row)
Wed Nov 26 11:42:00 2025 (every 0s)
?column?
----------
1
(1 row)But unfortunately we cannot combine setting a variable with \gset with \watch. For example we cannot increment a counter with \watch and \gset. Or at least I could not find a way.
postgres=# \set counter 0
postgres=# SELECT :counter + 1 AS counter \gset \watch interval=0 count=3
Wed Nov 26 11:43:27 2025 (every 0s)
counter
---------
1
(1 row)
Wed Nov 26 11:43:27 2025 (every 0s)
counter
---------
1
(1 row)
Wed Nov 26 11:43:27 2025 (every 0s)
counter
---------
1
(1 row)
postgres=# \echo :counter
1It appears that the \gset command only happens once while the query itself gets executed 3 times.
I was a bit stuck here!
Recursion
But then I noticed that you can paste in a file's literal text using \include. And that file you're pasting in can be the file you're executing. Recursion.
In counter.psql we can combine all of the above to create:
\if :{?counter}
\else
\set counter 3
\endif
\echo :counter
SELECT :counter = 0 AS done \gset
SELECT :counter - 1 AS counter \gset
\if :done
\else
\include counter.psql
\endifAnd run the script from the terminal.
$ psql postgres -f counter.psql
3
2
1
0Not too bad.
Fibonacci calculator in psql
Putting all of that together again we can write a recursive (and slightly more verbose) implementation of our Python code in fib.psql.
\if :{?n}
\else
\prompt 'nth fib to calculate: ' n
\set a 0
\set b 1
\set c 0
\endif
SELECT :a + :b AS c \gset
\set a :b
\set b :c
SELECT :n - 1 AS n \gset
SELECT :n < 0 AS zero, :n = 0 AS one, :n = 1 AS two, :n > 1 AS continue \gset
\if :zero
\echo nth = 0
\elif :one
\echo nth = 1
\elif :two
\echo nth = :b
\elif :continue
\include fib.psql
\endifLet's try it out! We pass in 1 we get 1. We pass in 9 we get 34.
$ psql postgres -f fib.psql
nth fib to calculate: 1
nth = 1
$ psql postgres -f fib.psql
nth fib to calculate: 9
nth = 34We pass in 40 we get 102334155, same as the Python.
$ psql postgres -f fib.psql
nth fib to calculate: 40
nth = 102334155
$ python3 fib.py
nth fib to calculate:
40
102334155We pass in 50 we crash.
$ psql postgres -f fib.psql
nth fib to calculate: 50
psql:fib.psql:10: ERROR: integer out of range
psql:fib.psql:10: ERROR: integer out of range
psql:fib.psql:10: ERROR: integer out of range
psql:fib.psql:10: ERROR: integer out of range
nth = 1836311903Easy fix. We cast to a BIGINT instead of letting the addition be on Postgres INTs.
\if :{?n}
\else
\prompt 'nth fib to calculate: ' n
\set a 0
\set b 1
\set c 0
\endif
SELECT :a::BIGINT + :b::BIGINT AS c \gset
\set a :b
\set b :c
SELECT :n - 1 AS n \gset
SELECT :n < 0 AS zero, :n = 0 AS one, :n = 1 AS two, :n > 1 AS continue \gset
\if :zero
\echo nth = 0
\elif :one
\echo nth = 1
\elif :two
\echo nth = :b
\elif :continue
\include fib.psql
\endifNow we can find the 50th fibonacci number.
$ psql postgres -f fib.psql
nth fib to calculate: 50
nth = 12586269025
$ python3 fib.py
nth fib to calculate:
50
12586269025But we overflow again by the 100th.
$ psql postgres -f fib.psql
nth fib to calculate: 100
psql:fib.psql:10: ERROR: bigint out of range
psql:fib.psql:10: ERROR: bigint out of range
psql:fib.psql:10: ERROR: bigint out of range
psql:fib.psql:10: ERROR: bigint out of range
psql:fib.psql:10: ERROR: bigint out of range
psql:fib.psql:10: ERROR: bigint out of range
psql:fib.psql:10: ERROR: bigint out of range
psql:fib.psql:10: ERROR: bigint out of range
nth = 7540113804746346429No problem. We cast to a NUMERIC(65, 0). We can get the 100th and 200th fibonacci number.
$ psql postgres -f fib.psql
nth fib to calculate: 100
nth = 354224848179261915075
$ psql postgres -f fib.psql
nth fib to calculate: 200
nth = 280571172992510140037611932413038677189525
$ python3 fib.py
nth fib to calculate:
100
354224848179261915075
$ python3 fib.py
nth fib to calculate:
200
280571172992510140037611932413038677189525Let's try the 1000th.
$ psql postgres -f fib.psql
nth fib to calculate: 1000
psql:fib.psql:23: error: fib.psql: Too many open filesWell. Maybe our recursion is bounded after all.