Determine server version from psql script

On 3/22/25 17:31, Igor Korot wrote:

Hi, All,
I have a big script that populated the DB for me.

The language used for the script?

Is it actually running in psql?

I made it based on the latest available version.

However,  i have version 13 installed on my Linux box and so trying to
execute "CREATE OR REPLACE TRIGGER..." will fail.

Is there a way to determine the server version from such a script?

From here:

https://www.postgresql.org/docs/13/runtime-config-preset.html#GUC-SERVER-VERSION-NUM

show server_version_num;

Though how you use that is going to depend on the answers to the first
two questions.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 3/22/25 17:31, Igor Korot wrote:

Is there a way to determine the server version from such a script?

show server_version_num;

psql already populates its SERVER_VERSION_NUM variable from that
for you.

regards, tom lane

Hi, Adrian,

On Sat, Mar 22, 2025, 7:42 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 3/22/25 17:31, Igor Korot wrote:

Hi, All,
I have a big script that populated the DB for me.

The language used for the script?

What do you mean?
Its just a text file with bunch of create table/insert into

Is it actually running in psql?

Yes, i run "psql - d draft -a -f <filename>

On Saturday, March 22, 2025, Igor Korot <ikorot01@gmail.com> wrote:

Is it actually running in psql?

Yes, i run "psql - d draft -a -f <filename>

Then read the psql docs. Your version has \if and you’ll find server
version listed as the available client variables.

David J.

Hi, David,

On Sat, Mar 22, 2025, 8:58 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Saturday, March 22, 2025, Igor Korot <ikorot01@gmail.com> wrote:

Is it actually running in psql?

Yes, i run "psql - d draft -a -f <filename>

Then read the psql docs. Your version has \if and you’ll find server
version listed as the available client variables.

I was hoping for something like

If server_version >= X:
CREATE OR REPLACE TRIGGER...
else:
CREATE TRIGGER...

Python-like syntax here as I'm not sure how to do it properly...

Thank you.

Igor Korot <ikorot01@gmail.com> writes:

On Sat, Mar 22, 2025, 8:58 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

Then read the psql docs. Your version has \if and you’ll find server
version listed as the available client variables.

I was hoping for something like

If server_version >= X:
CREATE OR REPLACE TRIGGER...

psql's \if doesn't (yet) have any native expression evaluation
ability, so you have to farm out the ">=" comparison. The
psql docs suggest relying on the server to do it, which would
go along the lines of

select current_setting('server_version_num')::int >= 130000 as v13
\gset
\if :v13
... do something
\else
... do something else
\endif

You could also do the comparison client-side, along the lines of

\set v13 `expr :SERVER_VERSION_NUM \>= 130000`
\if :v13
... etc

But that introduces assorted platform dependencies and requires
close attention to correct shell quoting, so it's seldom
preferable IMO.

regards, tom lane

Hi, Tom,

On Sat, Mar 22, 2025, 10:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Igor Korot <ikorot01@gmail.com> writes:

On Sat, Mar 22, 2025, 8:58 PM David G. Johnston <

david.g.johnston@gmail.com>

wrote:

Then read the psql docs. Your version has \if and you’ll find server
version listed as the available client variables.

I was hoping for something like

If server_version >= X:
CREATE OR REPLACE TRIGGER...

psql's \if doesn't (yet) have any native expression evaluation
ability, so you have to farm out the ">=" comparison. The
psql docs suggest relying on the server to do it, which would
go along the lines of

select current_setting('server_version_num')::int >= 130000 as v13
\gset
\if :v13
... do something
\else
... do something else
\endif

Thank for the code.

2 things, however.
1. Apparently CREATE OR RELACE TRIGGER syntax is available since v17, which
is the current one. So I hadto adjust the numbers.. 😀
2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier version?

Thank you.

On Sunday, March 23, 2025, Igor Korot <ikorot01@gmail.com> wrote:

2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
version?

No. You have to drop the trigger if it does exist and then create the new
one.

David J.

On 3/23/25 10:38, Igor Korot wrote:

Hi, Tom,

2 things, however.
1. Apparently CREATE OR RELACE TRIGGER syntax is available since v17,
which is the current one. So I hadto adjust the numbers.. 😀

Really?:

https://www.postgresql.org/docs/14/sql-createtrigger.html

"CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name ..."

--
Adrian Klaver
adrian.klaver@aklaver.com

On Sunday, March 23, 2025, David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Sunday, March 23, 2025, Igor Korot <ikorot01@gmail.com> wrote:

2. Is there a way to do CREATE TRIGGER IF NOT EXIST for the earlier
version?

No. You have to drop the trigger if it does exist and then create the new
one.

Well, you can always query the catalogs directly to answer the question
“does this trigger exist”.

David J.

Hi

ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <ikorot01@gmail.com> napsal:

Hi,

[code]
SELECT current_setting('server_version_num')::int > 130000 as v13
\gset
\if :v13
CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSERT ON
playersinleague WHEN new.current_rank IS NULL
BEGIN
UPDATE playersinleague SET current_rank = 1 + (SELECT
coalesce( max( current_rank ), 0 ) FROM playersinleague WHERE id =
new.id) WHERE rowid = new.rowid;
psql:draft_pg.sql:44265: ERROR: syntax error at or near "TRIGGER"
LINE 1: CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSER...
^
[/code]

What am I doing wrong?

the test should be

SELECT current_setting('server_version_num')::int > =140000 as v14

\if :v14
...

CREATE OR REPLACE is supported from PostgreSQL 14

https://www.postgresql.org/docs/14/sql-createtrigger.html

Regards

Pavel

On Sunday, March 23, 2025, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <ikorot01@gmail.com> napsal:

Hi,

[code]
SELECT current_setting('server_version_num')::int > 130000 as v13

SELECT current_setting('server_version_num')::int > =140000 as v14

IOW, you can’t use >130000 because that will match v13.1 which is 130001 in
integer format.

David J.

Hi,

[code]
SELECT current_setting('server_version_num')::int > 130000 as v13
\gset
\if :v13
CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSERT ON
playersinleague WHEN new.current_rank IS NULL
BEGIN
UPDATE playersinleague SET current_rank = 1 + (SELECT
coalesce( max( current_rank ), 0 ) FROM playersinleague WHERE id =
new.id) WHERE rowid = new.rowid;
psql:draft_pg.sql:44265: ERROR: syntax error at or near "TRIGGER"
LINE 1: CREATE OR REPLACE TRIGGER playersinleague_insert AFTER INSER...
^
[/code]

What am I doing wrong?

Thank you.

On Sun, Mar 23, 2025 at 12:53 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

Hi,
This is what :

[code[
\else
DROP TRIGGER IF EXISTS playersinleague_insert ON playersinleague;
CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague
WHEN new.current_rank IS NULL
BEGIN
UPDATE playersinleague SET current_rank = 1 + (SELECT coalesce(
max( current_rank ), 0 ) FROM playersinleague WHERE id = new.id) WHERE
rowid = NEW.rowid;
UPDATE playersinleague SET original_rank = current_rank WHERE rowid
= new.rowid;
END;
\endif
[/code]

And I'm getting this:

[code]
\else
DROP TRIGGER IF EXISTS playersinleague_insert ON playersinleague;
psql:draft_pg.sql:44269: NOTICE: trigger "playersinleague_insert" for
relation "playersinleague" does not exist, skipping
DROP TRIGGER
CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague
WHEN new.current_rank IS NULL
BEGIN
UPDATE playersinleague SET current_rank = 1 + (SELECT coalesce(
max( current_rank ), 0 ) FROM playersinleague WHERE id = new.id) WHERE
rowid = new.rowid;
psql:draft_pg.sql:44272: ERROR: syntax error at or near "new"
LINE 1: ...eague_insert AFTER INSERT ON playersinleague WHEN new.curren...
^

[/code]

What is the problem niw?

Thank you.

On Sun, Mar 23, 2025 at 2:27 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Sunday, March 23, 2025, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <ikorot01@gmail.com> napsal:

Hi,

[code]
SELECT current_setting('server_version_num')::int > 130000 as v13

SELECT current_setting('server_version_num')::int > =140000 as v14

IOW, you can’t use >130000 because that will match v13.1 which is 130001

in integer format.

On Sunday, March 23, 2025, Igor Korot <ikorot01@gmail.com> wrote:

CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague
WHEN new.current_rank IS NULL

When the syntax shows parentheses you are required to write them.

[ WHEN ( *condition* ) ]

David J.

On Mar 23, 2025, at 18:08, Igor Korot <ikorot01@gmail.com> wrote:
CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague WHEN new.current_rank IS NULL

The WHEN predicate has to be enclosed in parenthes:

CREATE TRIGGER playersinleague_insert AFTER INSERT ON playersinleague WHEN ( new.current_rank IS NULL )