Subject: Re: Determine server version from psql script

From: Igor Korot <[email-masked]>
Date: Sat, 22 Mar 2025 20:47:05 -0500

Hi, Adrian,

On Sat, Mar 22, 2025, 7:42 PM Adrian Klaver <[email-masked]>
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>

> >
> > 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
> [email-masked]
>
>

---

From: "David G. Johnston" <[email-masked]>
Date: Sat, 22 Mar 2025 18:58:50 -0700

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

---

From: Igor Korot <[email-masked]>
Date: Sat, 22 Mar 2025 21:28:14 -0500

Hi, David,

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

---

From: Tom Lane <[email-masked]>
Date: Sat, 22 Mar 2025 23:01:41 -0400

>      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

---

From: Igor Korot <[email-masked]>
Date: Sun, 23 Mar 2025 12:38:18 -0500

Hi, Tom,

> select current_setting('server_version_num')::int >= 130000 as v13

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.

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 23 Mar 2025 10:44:01 -0700

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

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

David J.

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 23 Mar 2025 10:50:45 -0700

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

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 23 Mar 2025 10:53:04 -0700

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

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

David J.

---

From: Pavel Stehule <[email-masked]>
Date: Sun, 23 Mar 2025 20:17:02 +0100

Hi

ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <[email-masked]> 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

Regards

Pavel

>
> Thank you.
>
>
> On Sun, Mar 23, 2025 at 12:53 PM David G. Johnston
> <[email-masked]> wrote:
> >
> >> No.  You have to drop the trigger if it does exist and then create the
> new one.
> >
> >

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 23 Mar 2025 12:27:19 -0700

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

---

From: Igor Korot <[email-masked]>
Date: Sun, 23 Mar 2025 18:52:22 -0500

Hi,

[code]

What am I doing wrong?

Thank you.

On Sun, Mar 23, 2025 at 12:53 PM David G. Johnston
<[email-masked]> wrote:
>
> On Sunday, March 23, 2025, David G. Johnston <[email-masked]> wrote:
>>
>> 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.
>

---

From: Igor Korot <[email-masked]>
Date: Sun, 23 Mar 2025 20:08:11 -0500

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 <
[email-masked]> wrote:
> IOW, you can’t use >130000 because that will match v13.1 which is 130001
in integer format.

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 23 Mar 2025 18:15:54 -0700

> WHEN new.current_rank IS NULL

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

  [ WHEN ( *condition* ) ]

David J.

---

From: Christophe Pettus <[email-masked]>
Date: Sun, 23 Mar 2025 18:34:24 -0700

> On Mar 23, 2025, at 18:08, Igor Korot <[email-masked]> 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 )