Re: [GENERAL] Backward compatibility
Thx. The split_part() works perfectly. On Sat, Jul 22, 2017 at 10:49 AM, Tom Lane wrote: > Igor Korot writes: >> But it works incorrectly - it should return: >> 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 >> (Red Hat 6.3.1-1), 64-bit >> i.e. without the word "PosgreSQL", since '\s' should match the (first) >> space in the version(). > > position() is not a regex operation, it's just a plain substring match. > > regression=# SELECT position( '\s' in version() ) ; > position > -- > 0 > (1 row) > > You hardly need any flexibility for this anyway, so I'd just do > > regression=# SELECT position( ' ' in version() ) ; > position > -- >11 > (1 row) > > Although possibly what you really want is split_part(). > > regression=# select split_part(version(), ' ', 2); > split_part > > 9.5.7 > (1 row) > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On Sat, 2017-07-22 at 10:49 -0400, Tom Lane wrote: > Igor Korot writes: > > But it works incorrectly - it should return: > > 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 > > 20161221 > > (Red Hat 6.3.1-1), 64-bit > > i.e. without the word "PosgreSQL", since '\s' should match the > > (first) > > space in the version(). > > position() is not a regex operation, it's just a plain substring > match. > > regression=# SELECT position( '\s' in version() ) ; > position > -- > 0 > (1 row) > > You hardly need any flexibility for this anyway, so I'd just do > > regression=# SELECT position( ' ' in version() ) ; > position > -- > 11 > (1 row) > > Although possibly what you really want is split_part(). > > regression=# select split_part(version(), ' ', 2); > split_part > > 9.5.7 > (1 row) > > regards, tom lane > > An alternative select:- SELECT version(), (regexp_split_to_array( version(), E'\\s+'))[2] Cheers, Rob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
Igor Korot writes: > But it works incorrectly - it should return: > 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 > (Red Hat 6.3.1-1), 64-bit > i.e. without the word "PosgreSQL", since '\s' should match the (first) > space in the version(). position() is not a regex operation, it's just a plain substring match. regression=# SELECT position( '\s' in version() ) ; position -- 0 (1 row) You hardly need any flexibility for this anyway, so I'd just do regression=# SELECT position( ' ' in version() ) ; position -- 11 (1 row) Although possibly what you really want is split_part(). regression=# select split_part(version(), ' ', 2); split_part 9.5.7 (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
Hi, John, On Sat, Jul 22, 2017 at 8:44 AM, John McKown wrote: > On Fri, Jul 21, 2017 at 10:21 PM, Igor Korot wrote: >> >> Hi, guys, >> Below query does not even run: >> >> SELECT version(), substring( version() from position( '\s' in version() ) >> ); >> >> Could you spot the error? >> > > works for me. > > psql > psql (9.5.7) > Type "help" for help. > > joarmc=# SELECT version(), substring( version() from position( '\s' in > version() ) ); > version > | > substring > -+ > - > PostgreSQL 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 > 20161221 (Red Hat 6.3.1-1), 64-bit | PostgreSQL 9.5.7 on > x86_64-redhat-linux-gnu, co > mpiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit > (1 row) Weird. I started a new session of psql and it now works. But it works incorrectly - it should return: 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit i.e. without the word "PosgreSQL", since '\s' should match the (first) space in the version(). Thank you. > > > > -- > Veni, Vidi, VISA: I came, I saw, I did a little shopping. > > Maranatha! <>< > John McKown -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On Fri, Jul 21, 2017 at 10:21 PM, Igor Korot wrote: > Hi, guys, > Below query does not even run: > > SELECT version(), substring( version() from position( '\s' in version() ) > ); > > Could you spot the error? > > works for me. psql psql (9.5.7) Type "help" for help. joarmc=# SELECT version(), substring( version() from position( '\s' in version() ) ); version | substring -+ - PostgreSQL 9.5.7 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit | PostgreSQL 9.5.7 on x86_64-redhat-linux-gnu, co mpiled by gcc (GCC) 6.3.1 20161221 (Red Hat 6.3.1-1), 64-bit (1 row) -- Veni, Vidi, VISA: I came, I saw, I did a little shopping. Maranatha! <>< John McKown
Re: [GENERAL] Backward compatibility
Hi, guys, Below query does not even run: SELECT version(), substring( version() from position( '\s' in version() ) ); Could you spot the error? On Fri, Jul 21, 2017 at 12:11 PM, Igor Korot wrote: > David et al, > > On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston > wrote: >> On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot wrote: >>> >>> MySQL uses this: >>> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html. >>> Is it safe to assume that PostgreSQL calculates the version the same way? >> >> >> Yes and no. Things are changing with this next release. The next two major >> releases will be: >> >> 10.x (or 10.0.x using historical nomenclature - 1000xx) >> 11.x (or 11.0.x using historical nomenclature - 1100xx) >> >> For prior releases the major versions are: >> >> 9.2.x >> 9.3.x >> 9.4.x >> 9.5.x >> 9.6.x >> >> If you want to consider the 9 to be "major" and the .[2-6] to be minor for >> mechanical purposes that's fine but the change from 9.5 to 9.6 is a major >> change with backward incompatibilities - which a minor change doesn't allow. >> In the new setup the thing you call "minor" will always remain at zero in >> order to eventually mitigate the need to have this kind of discussion. Since >> it is always going to be "0" we simply omit printing it. > > I just need to split the version by ".". > > But if the next releases will not increment second value and will > number the releases > as 10.0.0, 10.0.1, 10.0.2, then this schema won't work. > > Thank you. > >> >> David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
David et al, On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston wrote: > On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot wrote: >> >> MySQL uses this: >> https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html. >> Is it safe to assume that PostgreSQL calculates the version the same way? > > > Yes and no. Things are changing with this next release. The next two major > releases will be: > > 10.x (or 10.0.x using historical nomenclature - 1000xx) > 11.x (or 11.0.x using historical nomenclature - 1100xx) > > For prior releases the major versions are: > > 9.2.x > 9.3.x > 9.4.x > 9.5.x > 9.6.x > > If you want to consider the 9 to be "major" and the .[2-6] to be minor for > mechanical purposes that's fine but the change from 9.5 to 9.6 is a major > change with backward incompatibilities - which a minor change doesn't allow. > In the new setup the thing you call "minor" will always remain at zero in > order to eventually mitigate the need to have this kind of discussion. Since > it is always going to be "0" we simply omit printing it. I just need to split the version by ".". But if the next releases will not increment second value and will number the releases as 10.0.0, 10.0.1, 10.0.2, then this schema won't work. Thank you. > > David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot wrote: > MySQL uses this: > https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html. > Is it safe to assume that PostgreSQL calculates the version the same way? > Yes and no. Things are changing with this next release. The next two major releases will be: 10.x (or 10.0.x using historical nomenclature - 1000xx) 11.x (or 11.0.x using historical nomenclature - 1100xx) For prior releases the major versions are: 9.2.x 9.3.x 9.4.x 9.5.x 9.6.x If you want to consider the 9 to be "major" and the .[2-6] to be minor for mechanical purposes that's fine but the change from 9.5 to 9.6 is a major change with backward incompatibilities - which a minor change doesn't allow. In the new setup the thing you call "minor" will always remain at zero in order to eventually mitigate the need to have this kind of discussion. Since it is always going to be "0" we simply omit printing it. David J.
Re: [GENERAL] Backward compatibility
Hi, guys, On Thu, Jul 20, 2017 at 11:58 PM, Tom Lane wrote: > John R Pierce writes: >> On 7/20/2017 8:40 PM, Tom Lane wrote: >>> Hm, we need to update that text for the new 2-part version numbering >>> scheme, don't we? > >> will 10 return like 100100 if its 10.1, or 11 ? > > The latter. The two middle digits will be zeroes henceforth, unless > we somehow get into a situation where the minor version needs to > exceed 99. MySQL uses this: https://dev.mysql.com/doc/refman/5.7/en/mysql-get-server-version.html. Is it safe to assume that PostgreSQL calculates the version the same way? Thank you. > > regards, tom lane > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
John R Pierce writes: > On 7/20/2017 8:40 PM, Tom Lane wrote: >> Hm, we need to update that text for the new 2-part version numbering >> scheme, don't we? > will 10 return like 100100 if its 10.1, or 11 ? The latter. The two middle digits will be zeroes henceforth, unless we somehow get into a situation where the minor version needs to exceed 99. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On 7/20/2017 8:40 PM, Tom Lane wrote: Applications might use this function to determine the version of the database server they are connected to. The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as 80105, and version 8.2 will be returned as 80200 (leading zeroes are not shown). Zero is returned if the connection is bad. Hm, we need to update that text for the new 2-part version numbering scheme, don't we? will 10 return like 100100 if its 10.1, or 11 ? -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Backward compatibility
John R Pierce writes: > odds are pretty good that... > |PQserverVersion| > Returns an integer representing the backend version. > int PQserverVersion(const PGconn *conn); > Actually invokes `show server_version_num;' Just for the record, it doesn't invoke that; it doesn't have to, because the server sends that info at connection start and libpq just saves it. But either way should always give the same answer. > Applications might use this function to determine the version of the > database server they are connected to. The number is formed by > converting the major, minor, and revision numbers into > two-decimal-digit numbers and appending them together. For example, > version 8.1.5 will be returned as 80105, and version 8.2 will be > returned as 80200 (leading zeroes are not shown). Zero is returned > if the connection is bad. Hm, we need to update that text for the new 2-part version numbering scheme, don't we? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On 7/20/2017 7:57 PM, David G. Johnston wrote: Actually, The docs do cover how to do this directly in libpq. odds are pretty good that... |PQserverVersion| Returns an integer representing the backend version. int PQserverVersion(const PGconn *conn); Applications might use this function to determine the version of the database server they are connected to. The number is formed by converting the major, minor, and revision numbers into two-decimal-digit numbers and appending them together. For example, version 8.1.5 will be returned as 80105, and version 8.2 will be returned as 80200 (leading zeroes are not shown). Zero is returned if the connection is bad. Actually invokes `show server_version_num;' -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Backward compatibility
On 7/20/2017 7:46 PM, Igor Korot wrote: ALso, I presume there is no special libpg function, right? libpq would only be able to return the libpq version, which might not be the same as the server version. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On Thursday, July 20, 2017, David G. Johnston wrote: > On Thursday, July 20, 2017, Igor Korot > wrote: > >> Hi, David, >> >> On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston >> wrote: >> > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote: >> >> >> Is there a query or a libpg function which can return the version of >> >> the server I'm running? >> > > > > Its PQ not PG - and I'd doubt it but I don't directly use libpq. > > Actually, The docs do cover how to do this directly in libpq. David J.
Re: [GENERAL] Backward compatibility
On Thursday, July 20, 2017, Igor Korot wrote: > Hi, David, > > On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston > > wrote: > > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot > wrote: > > >> Is there a query or a libpg function which can return the version of > >> the server I'm running? > > Its PQ not PG - and I'd doubt it but I don't directly use libpq. > Is there a way to get a version_major, version_minr and version_extra? > > There is no 'extra'. The last two digits are the minor and everything before is the major. Usually you'd use inequality comparisons so there'd be no point. If here is a way beside parsing I'm not aware of it. David J.
Re: [GENERAL] Backward compatibility
ALso, I presume there is no special libpg function, right? Thank you. On Thu, Jul 20, 2017 at 10:44 PM, Igor Korot wrote: > Hi, David, > > On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston > wrote: >> On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote: >>> >>> Hi, ALL, >>> According to the documentation PostgreSQL 9.6 (latest) supports >>> >>> CREATE INDEX IF NOT EXIST >>> >>> However, the version 9.4 and below supports only >>> >>> CREATE INDEX. >>> >>> Is there a query or a libpg function which can return the version of >>> the server I'm running? >> >> >> SHOW server_version_num; -- this is better than "version()" since you don't >> have to parse text >> >> https://www.postgresql.org/docs/9.6/static/runtime-config-preset.html#GUC-SERVER-VERSION-NUM > > draft=# SHOW server_version_num; > server_version_num > > 90124 > (1 row) > > Is there a way to get a version_major, version_minr and version_extra? > > Thank you. > >> >> see the below for other ways to query the setting. >> >> https://www.postgresql.org/docs/9.6/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION >> >>> >>> And in the latter case - is there a way to check if the index exist? >>> >>> I guess I will have to query "information_schema" again... >>> >> >> Yep, not much demand for multiple ways to do the same thing in this area... >> >> David J. >> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
Hi, David, On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston wrote: > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote: >> >> Hi, ALL, >> According to the documentation PostgreSQL 9.6 (latest) supports >> >> CREATE INDEX IF NOT EXIST >> >> However, the version 9.4 and below supports only >> >> CREATE INDEX. >> >> Is there a query or a libpg function which can return the version of >> the server I'm running? > > > SHOW server_version_num; -- this is better than "version()" since you don't > have to parse text > > https://www.postgresql.org/docs/9.6/static/runtime-config-preset.html#GUC-SERVER-VERSION-NUM draft=# SHOW server_version_num; server_version_num 90124 (1 row) Is there a way to get a version_major, version_minr and version_extra? Thank you. > > see the below for other ways to query the setting. > > https://www.postgresql.org/docs/9.6/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION > >> >> And in the latter case - is there a way to check if the index exist? >> >> I guess I will have to query "information_schema" again... >> > > Yep, not much demand for multiple ways to do the same thing in this area... > > David J. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On Thu, Jul 20, 2017 at 7:23 PM, Igor Korot wrote: > On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer > wrote: > > > >>Is there a query or a libpg function which can return the version of > >>the server I'm running? > > > Select version(); > > Here is the results: > > draft=# SELECT version(); > > > version > > > - > PostgreSQL 9.1.24 on x86_64-apple-darwin, compiled by > i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. > build 5658) (LLVM build 2335.6), 64-bit > (1 row) > > Is there a way to get just "9.1.24" without everything else? > SHOW server_version_num; 90124 should be the result (don't have that version installed to copy-paste) 90506 is the version I have at my fingertips. David J.
Re: [GENERAL] Backward compatibility
On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot wrote: > Hi, ALL, > According to the documentation PostgreSQL 9.6 (latest) supports > > CREATE INDEX IF NOT EXIST > > However, the version 9.4 and below supports only > > CREATE INDEX. > > Is there a query or a libpg function which can return the version of > the server I'm running? > SHOW server_version_num; -- this is better than "version()" since you don't have to parse text https://www.postgresql.org/docs/9.6/static/runtime-config-preset.html#GUC-SERVER-VERSION-NUM see the below for other ways to query the setting. https://www.postgresql.org/docs/9.6/static/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION > And in the latter case - is there a way to check if the index exist? > > I guess I will have to query "information_schema" again... > > Yep, not much demand for multiple ways to do the same thing in this area... David J.
Re: [GENERAL] Backward compatibility
Hi, guys, On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer wrote: > On 21 July 2017 04:13:47 GMT+02:00, Igor Korot wrote: >>Hi, ALL, >>According to the documentation PostgreSQL 9.6 (latest) supports >> >>CREATE INDEX IF NOT EXIST >> >>However, the version 9.4 and below supports only >> >>CREATE INDEX. >> >>Is there a query or a libpg function which can return the version of >>the server I'm running? >> >> > > Select version(); Here is the results: draft=# SELECT version(); version - PostgreSQL 9.1.24 on x86_64-apple-darwin, compiled by i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2335.6), 64-bit (1 row) Is there a way to get just "9.1.24" without everything else? Or maybe the server can perform parsing for me? Thank you. > > > Regards, Andreas > > > -- > 2ndQuadrant - The PostgreSQL Support Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward compatibility
On 21 July 2017 04:13:47 GMT+02:00, Igor Korot wrote: >Hi, ALL, >According to the documentation PostgreSQL 9.6 (latest) supports > >CREATE INDEX IF NOT EXIST > >However, the version 9.4 and below supports only > >CREATE INDEX. > >Is there a query or a libpg function which can return the version of >the server I'm running? > > Select version(); Regards, Andreas -- 2ndQuadrant - The PostgreSQL Support Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backward compatibility
Hi, ALL, According to the documentation PostgreSQL 9.6 (latest) supports CREATE INDEX IF NOT EXIST However, the version 9.4 and below supports only CREATE INDEX. Is there a query or a libpg function which can return the version of the server I'm running? And in the latter case - is there a way to check if the index exist? I guess I will have to query "information_schema" again... Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backward Compatibility
[EMAIL PROTECTED] writes: > I need to be able to talk to the v6.3 server from the new machine. You'll have to install a 6.3.x libpq and psql for that, I'm afraid. regards, tom lane
[GENERAL] Backward Compatibility
I have an old Postgres server (v6.3) running on a machine which I do not control and cannot update. I have a new machine which came with Postgres 7.0.2, which I do control. I need to be able to talk to the v6.3 server from the new machine. Can this be done? When I try I get: newMachine$ psql -h oldMachine myDatabase psql: Unsupported frontend protocol. Please reply to: [EMAIL PROTECTED] Thanks, Dick
