Обсуждение: [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.
On 21 July 2017 04:13:47 GMT+02:00, Igor Korot <ikorot01@gmail.com> 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
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
see the below for other ways to query the setting.
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.
Hi, guys,
On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer
<andreas@a-kretschmer.de> wrote:
> On 21 July 2017 04:13:47 GMT+02:00, Igor Korot <ikorot01@gmail.com> 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
On Thu, Jul 20, 2017 at 10:19 PM, Andreas Kretschmer
<andreas@a-kretschmer.de> 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.
Hi, David, On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikorot01@gmail.com> 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. >
ALso, I presume there is no special libpg function, right? Thank you. On Thu, Jul 20, 2017 at 10:44 PM, Igor Korot <ikorot01@gmail.com> wrote: > Hi, David, > > On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston > <david.g.johnston@gmail.com> wrote: >> On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikorot01@gmail.com> 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. >>
On Thursday, July 20, 2017, Igor Korot <ikorot01@gmail.com> wrote:
Hi, David,
On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikorot01@gmail.com> 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.
On Thursday, July 20, 2017, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, July 20, 2017, Igor Korot <ikorot01@gmail.com> wrote:Hi, David,
On Thu, Jul 20, 2017 at 10:23 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> On Thu, Jul 20, 2017 at 7:13 PM, Igor Korot <ikorot01@gmail.com> 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.
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
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
John R Pierce <pierce@hogranch.com> 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
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 100001 ?
-- john r pierce, recycling bits in santa cruz
John R Pierce <pierce@hogranch.com> 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 100001 ?
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
Hi, guys, On Thu, Jul 20, 2017 at 11:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > John R Pierce <pierce@hogranch.com> 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 100001 ? > > 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
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.
David et al, On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot <ikorot01@gmail.com> 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.
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 <ikorot01@gmail.com> wrote: > David et al, > > On Fri, Jul 21, 2017 at 12:00 PM, David G. Johnston > <david.g.johnston@gmail.com> wrote: >> On Fri, Jul 21, 2017 at 8:49 AM, Igor Korot <ikorot01@gmail.com> 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.
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 (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
John McKown
Hi, John, On Sat, Jul 22, 2017 at 8:44 AM, John McKown <john.archie.mckown@gmail.com> wrote: > On Fri, Jul 21, 2017 at 10:21 PM, Igor Korot <ikorot01@gmail.com> 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
Igor Korot <ikorot01@gmail.com> 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
On Sat, 2017-07-22 at 10:49 -0400, Tom Lane wrote: > Igor Korot <ikorot01@gmail.com> 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
Thx. The split_part() works perfectly. On Sat, Jul 22, 2017 at 10:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Igor Korot <ikorot01@gmail.com> 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