public inbox for [email protected]help / color / mirror / Atom feed
Re: Determine server version from psql script 14+ messages / 6 participants [nested] [flat]
* Re: Determine server version from psql script @ 2025-03-23 01:47 Igor Korot <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Igor Korot @ 2025-03-23 01:47 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]> Hi, Adrian, On Sat, Mar 22, 2025, 7:42 PM Adrian Klaver <[email protected]> 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 protected] > > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 01:58 David G. Johnston <[email protected]> parent: Igor Korot <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: David G. Johnston @ 2025-03-23 01:58 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Saturday, March 22, 2025, Igor Korot <[email protected]> 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. ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 02:28 Igor Korot <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Igor Korot @ 2025-03-23 02:28 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Hi, David, On Sat, Mar 22, 2025, 8:58 PM David G. Johnston <[email protected]> wrote: > On Saturday, March 22, 2025, Igor Korot <[email protected]> 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. > David J. > > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 03:01 Tom Lane <[email protected]> parent: Igor Korot <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Tom Lane @ 2025-03-23 03:01 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: David G. Johnston <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Igor Korot <[email protected]> writes: > On Sat, Mar 22, 2025, 8:58 PM David G. Johnston <[email protected]> > 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 ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 17:38 Igor Korot <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 14+ messages in thread From: Igor Korot @ 2025-03-23 17:38 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Hi, Tom, On Sat, Mar 22, 2025, 10:01 PM Tom Lane <[email protected]> wrote: > Igor Korot <[email protected]> writes: > > On Sat, Mar 22, 2025, 8:58 PM David G. Johnston < > [email protected]> > > 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. > > 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 > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 17:44 David G. Johnston <[email protected]> parent: Igor Korot <[email protected]> 1 sibling, 1 reply; 14+ messages in thread From: David G. Johnston @ 2025-03-23 17:44 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sunday, March 23, 2025, Igor Korot <[email protected]> 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. ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 17:50 Adrian Klaver <[email protected]> parent: Igor Korot <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: Adrian Klaver @ 2025-03-23 17:50 UTC (permalink / raw) To: Igor Korot <[email protected]>; Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> 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 [email protected] ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 17:53 David G. Johnston <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: David G. Johnston @ 2025-03-23 17:53 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sunday, March 23, 2025, David G. Johnston <[email protected]> wrote: > On Sunday, March 23, 2025, Igor Korot <[email protected]> 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. ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 19:17 Pavel Stehule <[email protected]> parent: Igor Korot <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Pavel Stehule @ 2025-03-23 19:17 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: David G. Johnston <[email protected]>; Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> Hi ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <[email protected]> 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 > > Thank you. > > > On Sun, Mar 23, 2025 at 12:53 PM David G. Johnston > <[email protected]> wrote: > > > > On Sunday, March 23, 2025, David G. Johnston <[email protected]> > wrote: > >> > >> On Sunday, March 23, 2025, Igor Korot <[email protected]> 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. > > > > > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 19:27 David G. Johnston <[email protected]> parent: Pavel Stehule <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: David G. Johnston @ 2025-03-23 19:27 UTC (permalink / raw) To: Pavel Stehule <[email protected]>; +Cc: Igor Korot <[email protected]>; Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sunday, March 23, 2025, Pavel Stehule <[email protected]> wrote: > Hi > > ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <[email protected]> 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. ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-23 23:52 Igor Korot <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 14+ messages in thread From: Igor Korot @ 2025-03-23 23:52 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> 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 <[email protected]> wrote: > > On Sunday, March 23, 2025, David G. Johnston <[email protected]> wrote: >> >> On Sunday, March 23, 2025, Igor Korot <[email protected]> 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. > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-24 01:08 Igor Korot <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 2 replies; 14+ messages in thread From: Igor Korot @ 2025-03-24 01:08 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Pavel Stehule <[email protected]>; Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> 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 protected]> wrote: > > On Sunday, March 23, 2025, Pavel Stehule <[email protected]> wrote: >> >> Hi >> >> ne 23. 3. 2025 v 19:31 odesílatel Igor Korot <[email protected]> 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. > ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-24 01:15 David G. Johnston <[email protected]> parent: Igor Korot <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: David G. Johnston @ 2025-03-24 01:15 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: Pavel Stehule <[email protected]>; Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Sunday, March 23, 2025, Igor Korot <[email protected]> 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. ^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Determine server version from psql script @ 2025-03-24 01:34 Christophe Pettus <[email protected]> parent: Igor Korot <[email protected]> 1 sibling, 0 replies; 14+ messages in thread From: Christophe Pettus @ 2025-03-24 01:34 UTC (permalink / raw) To: Igor Korot <[email protected]>; +Cc: David G. Johnston <[email protected]>; Pavel Stehule <[email protected]>; Tom Lane <[email protected]>; Adrian Klaver <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> > On Mar 23, 2025, at 18:08, Igor Korot <[email protected]> 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 ) ^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-03-24 01:34 UTC | newest] Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-23 01:47 Re: Determine server version from psql script Igor Korot <[email protected]> 2025-03-23 01:58 ` David G. Johnston <[email protected]> 2025-03-23 02:28 ` Igor Korot <[email protected]> 2025-03-23 03:01 ` Tom Lane <[email protected]> 2025-03-23 17:38 ` Igor Korot <[email protected]> 2025-03-23 17:44 ` David G. Johnston <[email protected]> 2025-03-23 17:53 ` David G. Johnston <[email protected]> 2025-03-23 23:52 ` Igor Korot <[email protected]> 2025-03-23 19:17 ` Pavel Stehule <[email protected]> 2025-03-23 19:27 ` David G. Johnston <[email protected]> 2025-03-24 01:08 ` Igor Korot <[email protected]> 2025-03-24 01:15 ` David G. Johnston <[email protected]> 2025-03-24 01:34 ` Christophe Pettus <[email protected]> 2025-03-23 17:50 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox