Thread: 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

Is it actually running in psql?

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

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

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

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

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

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

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

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