Re: Interval in hours but not in days Leap second not taken into account
Laurenz Albe, you are right, thank you; actually, it depends (of course) on the
time zone:
# With GMT (no Daylight Saving Time):
SHOW timezone ;
TimeZone
--
GMT
SELECT timestamp with time zone '2022-03-29 12:00:00' - timestamp with time
zone '2022-03-26 12:00:00' ;
?column?
--
3 days
# With a time zone that IS dst (Daylight Saving Time):
SET timezone='Africa/Casablanca' ;
SHOW timezone ;
TimeZone
---
Africa/Casablanca
SELECT timestamp with time zone '2022-03-29 12:00:00' - timestamp with time
zone '2022-03-26 12:00:00' ;
?column?
-
3 days 01:00:00
Regards
- Mail original -
De: "PALAYRET Jacques"
À: "Laurenz Albe"
Cc: pgsql-gene...@postgresql.org
Envoyé: Lundi 27 Février 2023 09:50:02
Objet: Re: Interval in hours but not in days Leap second not taken into account
Does PostgreSQL take into account daylight saving time in its calendar?
For the last summer hour of the spring (Daylight Saving Time), on Sunday March
27, 2022:
SELECT to_timestamp('20220329 00:00:00','mmdd hh24:mi:ss') -
to_timestamp('20220320 00:00:00','mmdd hh24:mi:ss') intervalle ;
intervalle
9 days
Regards
- Mail original -
De: "Laurenz Albe"
À: "PALAYRET Jacques" , pgsql-gene...@postgresql.org
Envoyé: Lundi 27 Février 2023 09:23:37
Objet: Re: Interval in hours but not in days Leap second not taken into account
On Mon, 2023-02-27 at 07:26 +, PALAYRET Jacques wrote:
> # An interval in " years months ... seconds " given in seconds by
> EXTRACT(EPOCH ...) transtyped into INTERVAL :
> SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12
> seconds'::interval) ) || ' seconds')::interval ;
> interval
> -
> 27772:11:12
>
> # The same interval in seconds formated with TO_CHAR() :
> SELECT TO_CHAR((EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11
> minutes 12 seconds'::interval) ) || ' seconds')::interval, ' mm dd_
> hh24-mi-ss ') ;
> to_char
> ---
> 00 00_ 27754-11-12
>
> => The result is given in hours ... (not in days ...).
>
> It is logical that there are neither years nor months because they are not
> constant
> (leap year or not; a month can contain 31 30 ... days).
> I thought that days were eliminated because of the leap second (extra seconds
> inserted in the UTC time scale); obviously, this is not the case.
>
> # PostgreSQL does not take into account the additional second (leap second)
> in some calendar days ; eg. 2016, 31 dec. :
> SELECT to_timestamp('20170102 10:11:12','mmdd hh24:mi:ss') -
> to_timestamp('20161230 00:00:00','mmdd hh24:mi:ss') intervalle ;
> intervalle
> -
> 3 days 10:11:12
> --> With postgreSQL, a calendar day is always 86,400 seconds long.
>
> So, is there a reason for this (interval in hours ...) ?
The best explanation I have is "daylight savings time".
One day is not always 24 hours long.
If you keep the interval in hours, the result is always correct (if you
ignore leap seconds, which PostgreSQL doesn't account for).
Yours,
Laurenz Albe
Re: Interval in hours but not in days Leap second not taken into account
Does PostgreSQL take into account daylight saving time in its calendar?
For the last summer hour of the spring (Daylight Saving Time), on Sunday March
27, 2022:
SELECT to_timestamp('20220329 00:00:00','mmdd hh24:mi:ss') -
to_timestamp('20220320 00:00:00','mmdd hh24:mi:ss') intervalle ;
intervalle
9 days
Regards
- Mail original -
De: "Laurenz Albe"
À: "PALAYRET Jacques" , pgsql-gene...@postgresql.org
Envoyé: Lundi 27 Février 2023 09:23:37
Objet: Re: Interval in hours but not in days Leap second not taken into account
On Mon, 2023-02-27 at 07:26 +0000, PALAYRET Jacques wrote:
> # An interval in " years months ... seconds " given in seconds by
> EXTRACT(EPOCH ...) transtyped into INTERVAL :
> SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12
> seconds'::interval) ) || ' seconds')::interval ;
> interval
> -
> 27772:11:12
>
> # The same interval in seconds formated with TO_CHAR() :
> SELECT TO_CHAR((EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11
> minutes 12 seconds'::interval) ) || ' seconds')::interval, ' mm dd_
> hh24-mi-ss ') ;
> to_char
> ---
> 00 00_ 27754-11-12
>
> => The result is given in hours ... (not in days ...).
>
> It is logical that there are neither years nor months because they are not
> constant
> (leap year or not; a month can contain 31 30 ... days).
> I thought that days were eliminated because of the leap second (extra seconds
> inserted in the UTC time scale); obviously, this is not the case.
>
> # PostgreSQL does not take into account the additional second (leap second)
> in some calendar days ; eg. 2016, 31 dec. :
> SELECT to_timestamp('20170102 10:11:12','mmdd hh24:mi:ss') -
> to_timestamp('20161230 00:00:00','mmdd hh24:mi:ss') intervalle ;
> intervalle
> -
> 3 days 10:11:12
> --> With postgreSQL, a calendar day is always 86,400 seconds long.
>
> So, is there a reason for this (interval in hours ...) ?
The best explanation I have is "daylight savings time".
One day is not always 24 hours long.
If you keep the interval in hours, the result is always correct (if you
ignore leap seconds, which PostgreSQL doesn't account for).
Yours,
Laurenz Albe
Interval in hours but not in days Leap second not taken into account
Hello,
# An interval in " years months ... seconds " given in seconds by EXTRACT(EPOCH
...) transtyped into INTERVAL :
SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12
seconds'::interval) ) || ' seconds')::interval ;
interval
-
27772:11:12
# The same interval in seconds formated with TO_CHAR() :
SELECT TO_CHAR((EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes
12 seconds'::interval) ) || ' seconds')::interval, ' mm dd_ hh24-mi-ss ')
;
to_char
---
00 00_ 27754-11-12
=> The result is given in hours ... (not in days ...).
It is logical that there are neither years nor months because they are not
constant (leap year or not; a month can contain 31 30 ... days).
I thought that days were eliminated because of the leap second (extra seconds
inserted in the UTC time scale); obviously, this is not the case.
# PostgreSQL does not take into account the additional second (leap second) in
some calendar days ; eg. 2016, 31 dec. :
SELECT to_timestamp('20170102 10:11:12','mmdd hh24:mi:ss') -
to_timestamp('20161230 00:00:00','mmdd hh24:mi:ss') intervalle ;
intervalle
-
3 days 10:11:12
--> With postgreSQL, a calendar day is always 86,400 seconds long.
So, is there a reason for this (interval in hours ...) ?
Regards
- Météo-France -
PALAYRET Jacques
DCSC/GDC
jacques.palay...@meteo.fr
Fixe : +33 561078319
Re: PostgreSQL : error hint for LATERAL join
Hello,
Your point of view is interesting. May I discuss it ?
One answered to me on my question about " bug (ou not) in CTE Common Table
Expressions or Subqueries in the FROM Clause " :
" Its doesn’t have to seem logical to you, but this is how it is defined to
work and thus the observed behavior is not a bug. "
I thought about it and OK, even if regretted that the PostgreSQL language
sometimes ( rarely ) doesn't really make sense for me (given my logic).
So, are standards the rule or not ?
For me, one of the two following things should be true : either the hint (in
case of a lateral error) is incomplete or the possibility of " cross join
lateral " should be removed.
Of course, the idea of CROSS join doesn't make think about LATERAL idea.
But, is there a difference between a CROSS join and a INNER join with the " ON
TRUE " clause ?
Note : an inner join is a cross join with a clause ON (logical condition) and
OK, of course, an INNER join has not always " ON TRUE " clause, but it can.
I think you are right to write " Because the lateral takes precedence ".
LATERAL " takes precedence " over CROSS in the same way as INNER JOIN ... and
INNER JOIN ON TRUE
With LATERAL, joins are no more independant relations, neither CROSS or INNER.
SELECT *
FROM (
VALUES ('a'),('b')
) t (c1)
JOIN LATERAL (
VALUES ('b', c1), ('c',c1 || '*')
) u(d1) ON true
;
c1 | d1 | column2
++-
a | b | a
a | c | a*
b | b | b
b | c | b*
(4 lignes)
Regards
De: "David G. Johnston"
À: "PALAYRET Jacques"
Cc: "pgsql-general"
Envoyé: Vendredi 8 Avril 2022 15:36:34
Objet: Re: PostgreSQL : error hint for LATERAL join
On Fri, Apr 8, 2022 at 1:29 AM PALAYRET Jacques < [
mailto:jacques.palay...@meteo.fr | jacques.palay...@meteo.fr ] > wrote:
For a LATERAL join, I think the hint (in case of error) is incomplete :
" DÉTAIL : The combining JOIN type must be INNER or LEFT for a LATERAL
reference. "
to be replaced by :
" DÉTAIL : The combining JOIN type must be CROSS, INNER or LEFT for a LATERAL
reference. "
Note : it depends on what is needed
Regardless of whether the syntax works or not, the conceptual idea that a
lateral is also somehow a cross join is just wrong. A cross join, by
definition, takes two independent relations and performs a cartesian product
between them. A lateral join, by definition, takes a row from the left side of
the join, evaluates the right side using one or more columns from that rows,
then produces an output row for each row produced by the right side (copying
the left) - the inner/outer marker indicating what to do when the right side
produces zero rows.
If you use a non-trivial demonstration query (i.e., one that doesn't try to
multiply 1x1) this becomes more clear:
postgres=# SELECT *
FROM (
VALUES ('a'),('b')
) t (c1)
CROSS JOIN LATERAL (
VALUES ('b', c1), ('c',c1 || '*')
) u(d1)
;
c1 | d1 | column2
++-
a | b | a
a | c | a*
b | b | b
b | c | b*
(4 rows)
The presence of the cross join is misleading (if anything the error message is
sound advice and the behavior shown is wrong, but likely standard's mandated).
If it were a true cross join the relation u produced 4 unique rows and the
relation t produced 2, thus the output should have 8 rows. It only has four.
Because the lateral takes precedence here and only matches a subset of the
right-side output rows with the left side.
David J.
PostgreSQL : error hint for LATERAL join
Hello, For a LATERAL join, I think the hint (in case of error) is incomplete : " DÉTAIL : The combining JOIN type must be INNER or LEFT for a LATERAL reference. " to be replaced by : " DÉTAIL : The combining JOIN type must be CROSS, INNER or LEFT for a LATERAL reference. " Note : it depends on what is needed SELECT * FROM ( SELECT 'a' ) t (c1) LEFT OUTER JOIN LATERAL ( SELECT 'b', c1 ) u(d1) ON true ; c1 | d1 | c1 ++ a | b | a (1 ligne) SELECT * FROM ( SELECT 'a' ) t (c1) INNER JOIN LATERAL ( SELECT 'b', c1 ) u(d1) ON true ; c1 | d1 | c1 ++ a | b | a (1 ligne) SELECT * FROM ( SELECT 'a' ) t (c1) CROSS JOIN LATERAL ( SELECT 'b', c1 ) u(d1) ; c1 | d1 | c1 ++ a | b | a (1 ligne) Regards - Météo-France ----- PALAYRET JACQUES DCSC/GDC jacques.palay...@meteo.fr Fixe : +33 561078319
Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause
Constrained and forced, I am obliged to accept the behavior which is a means of using useful functionalities (standard SQL a priori). It's instructive for me. I don't know if it's the same behavior (more or less strict SQL standard) in other DBMS. For example, in Oracle it is not possible to have a SELECT statement without a FROM clause (using DUAL table), so maybe " ambiguous " columns are not handled in the same way. Anyway, thank you very much for the explanation. Regards De: "David G. Johnston" À: "PALAYRET Jacques" Cc: "PostgreSQL mailing lists" Envoyé: Mardi 22 Mars 2022 14:04:32 Objet: Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause On Tuesday, March 22, 2022, PALAYRET Jacques < [ mailto:jacques.palay...@meteo.fr | jacques.palay...@meteo.fr ] > wrote: According to me, there is only one condition in the main statement (SELECT [ http://w.id/ | w.id ] , [ http://w.name/ | w.name ] , w.elev FROM weather_stations ...) and it is : elev > 151 (correlation ou not correlation). In others words : for each line of table weather_stations), the only condition is : is the elev superior than the elev returned by the subquery, 151 ? Visibly, the correlated statement adds one condition ( [ http://w.id/ | w.id ] BETWEEN 3100 and 3199) in the main statement, but it's not logical for me, because of the parentheses. I agree your example query is written poorly and thus is confusing. But it is not possible for the system to distinguish a poorly written query from a goodly written one that uses the same functionality. As the functionality is useful, and the parentheses simply don’t isolate the subquery in the manner you ascribe to them, you’ll just need to adapt to reality. Its doesn’t have to seem logical to you, but this is how it is defined to work and thus the observed behavior is not a bug. David J.
Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause
Thanks a lot. Visibly, you are right. It's a correlated statement, OK, right. But in the subquery : ( SELECT x.elev FROM elev_Tlse_Blagnac AS x WHERE w.id BETWEEN 3100 and 3199 ) the WHERE clause (= w.id BETWEEN 3100 and 3199) is for the SELECT x.elev FROM elev_Tlse_Blagnac which is the value 151 (one line, one value), correlation or not. So, for me, it should NOT be a WHERE clause (a condition) for the MAIN statement. According to me, there is only one condition in the main statement (SELECT w.id, w.name, w.elev FROM weather_stations ...) and it is : elev > 151 (correlation ou not correlation). In others words : for each line of table weather_stations), the only condition is : is the elev superior than the elev returned by the subquery, 151 ? Visibly, the correlated statement adds one condition (w.id BETWEEN 3100 and 3199) in the main statement, but it's not logical for me, because of the parentheses. >From your point of view, it is the same statement than : - WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT w.id, w.name, w.elev FROM weather_stations AS w WHERE elev > (SELECT x.elev FROM elev_Tlse_Blagnac AS x) AND w.id BETWEEN 3100 and 3199; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) - For me, it's weird, not logical. Thanks again. Regards De: "Torsten Förtsch" À: "PALAYRET Jacques" Cc: "PostgreSQL mailing lists" Envoyé: Mardi 22 Mars 2022 11:16:19 Objet: Re: PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause This is what happens: WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT [ http://w.id/ | w.id ] , [ http://w.name/ | w.name ] , w.elev FROM weather_stations AS w WHERE elev > (SELECT x.elev FROM elev_Tlse_Blagnac AS x WHERE [ http://w.id/ | w.id ] BETWEEN 3100 and 3199); id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 rows) Note the use of aliases, w and x. You are using a correlated subquery. On Tue, Mar 22, 2022 at 10:46 AM PALAYRET Jacques < [ mailto:jacques.palay...@meteo.fr | jacques.palay...@meteo.fr ] > wrote: Hello, # Let's consider a table defined as follows : CREATE TABLE weather_stations( id integer, name varchar(30), elev integer ) ; # After loading, the content : id | name | elev --+--+-- 31069001 | TOULOUSE-BLAGNAC | 151 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 50003001 | AGON-COUTAINVILLE | 2 50195001 | GATHEMO | 330 (5 lignes) ### With CTE : # I'm suprised by the following result, the behavior of PostgreSQL ; is that a bug ? : = Statement 1 : = WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 ) ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) # According to me, the previous result is an error, because the parentheses are not taken into account. The column id is not part of elev_Tlse_Blagnac. # The same result as following, which is of course OK : = Statement 2 : = WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM elev_Tlse_Blagnac ) AND id BETWEEN 3100 and 3199 ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) ### Same weird behavior with subquery in FROM clause : # NOT OK (according to me), because the parentheses are not taken into account : = Statement 3 : = SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM (SELECT elev FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 ) ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) # OK, the parentheses are taken into account because there is no confusion with the column id (elev_Tlse_Blagnac has a column named id) : = Statement 4 : = SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 ) ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 50195001 | GATHEMO | 330 (3 lignes) # OK
PostgreSQL : bug (ou not) in CTE Common Table Expressions or Subqueries in the FROM Clause
Hello, # Let's consider a table defined as follows : CREATE TABLE weather_stations( id integer, name varchar(30), elev integer ) ; # After loading, the content : id | name | elev --+--+-- 31069001 | TOULOUSE-BLAGNAC | 151 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 50003001 | AGON-COUTAINVILLE | 2 50195001 | GATHEMO | 330 (5 lignes) ### With CTE : # I'm suprised by the following result, the behavior of PostgreSQL ; is that a bug ? : = Statement 1 : = WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 ) ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) # According to me, the previous result is an error, because the parentheses are not taken into account. The column id is not part of elev_Tlse_Blagnac. # The same result as following, which is of course OK : = Statement 2 : = WITH elev_Tlse_Blagnac AS ( SELECT elev FROM weather_stations WHERE id=31069001 ) SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM elev_Tlse_Blagnac ) AND id BETWEEN 3100 and 3199 ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) ### Same weird behavior with subquery in FROM clause : # NOT OK (according to me), because the parentheses are not taken into account : = Statement 3 : = SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM (SELECT elev FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 ) ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) # OK, the parentheses are taken into account because there is no confusion with the column id (elev_Tlse_Blagnac has a column named id) : = Statement 4 : = SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac WHERE id BETWEEN 3100 and 3199 ) ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 50195001 | GATHEMO | 330 (3 lignes) # OK (of course) : = Statement 5 : = SELECT id, name, elev FROM weather_stations WHERE elev > ( SELECT elev FROM (SELECT * FROM weather_stations WHERE id=31069001) elev_Tlse_Blagnac ) AND id BETWEEN 3100 and 3199 ; id | name | elev --+--+-- 31006001 | ALBIAC AGGLOMERATION | 289 31010001 | LUCHON-ANTIGNAC | 599 (2 lignes) Is that a PostgreSQL bug or not, statement 1 or statement 3 (yes according to me) ? Regards - Météo-France - PALAYRET JACQUES DCSC/GDC jacques.palay...@meteo.fr Fixe : +33 561078319
PostgreSQL : column value filtering in Logical Replication
Hello, Would it be possible to have a " Column value filtering in Logical Replication ", on the publication side ? Il would not be a " Column Filtering " neither a " row filtering ". It would be the possibility to send NULL (instead of the column value, for a publication table), when a where clause is unsatisfied. In others terms, a way to filter column values according to a logical condition. CREATE PUBLICATION [ FOR TABLE [ONLY] table_name [(colname [,…]) [WHERE ()] ] would become : CREATE PUBLICATION [ FOR TABLE [ONLY] table_name [(colname [WHERE ()] [,…]) [WHERE ()] ] -> The " col_where_clause " could be a where clause to filter or better a function to transform the column values. For example, with a col_where_clause (for column b) as : " b <= 50 " On publisher : a | b -+-- 111 | 44.4 222 | 55.5 333 | 33.3 on subscriber : a | b -+-- 111 | 44.4 222 | 333 | 33.3 Regards - Météo-France - PALAYRET JACQUES DCSC/MBD jacques.palay...@meteo.fr Fixe : +33 561078319
Re: Last updated time for a Schema of the table
Hello, I don't think so, as far as I know. But, you can put in place some triggesr to do that. i.e. https://www.postgresql.org/docs/13/plpgsql-trigger.html https://wiki.postgresql.org/wiki/Audit_trigger and so on Regards De: "paras paliya" À: pgsql-gene...@postgresql.org Envoyé: Lundi 2 Novembre 2020 10:53:06 Objet: Last updated time for a Schema of the table Hi All, I need the last updated time or alter time of the table. Like changing the column name or adding/deleting a column. Is there any table or view from which I can get? Thanks, Paras
Re: split_part for the last element
Hello,
reverse(split_part(reverse('foo bar baz'), ' ', 1)) -> 'baz'
Regards
- Mail original -
De: "Nikhil Benesch"
À: pgsql-general@lists.postgresql.org
Envoyé: Vendredi 23 Octobre 2020 17:47:16
Objet: split_part for the last element
Hi,
Suppose I need to split a string on a delimiter and select one of the
resulting components. If I want a specific component counting from the
start, that's easy:
split_part('foo bar baz', ' ', 1) -> 'foo'
But if I want the last component, I have several less-than-ideal options:
1. (string_to_array('foo bar baz', '
'))[cardinality(string_to_array('foo bar baz', ' ')) - 1]
2. reverse(split_part(reverse('foo bar baz'), ' ', 1))
3. (regexp_match('foo baz bar', '\S*$'))[1]
Option 1 is probably the most understandable, especially if you are
willing to introduce a temporary parts array:
select parts[cardinality(parts) - 1] from string_to_array('foo bar
baz', ' ') parts
But if the strings are long, this needlessly builds an array just to
throw it away. Option 2 has similar efficiency problems and is just
kind of silly. Option 3 is probably the best, but it's still a good
bit more complicated than a simple split_part invocation.
Is there another option I'm missing? Would there be interest in
extending split part so that negative indices counted from the end, as
in:
split_part('foo bar baz', ' ', -1) -> 'baz'
Or adding a split_part_end function in which positive indices counted
from the end:
split_part_end('foo bar baz', ' ', 1) -> 'baz'
I'd be happy to prepare a patch if so.
Cheers,
Nikhil
Re: Conditional column filtering with pglogical replication
The " row_filter " filters the rows and I don’t want to filter the row but only one (or several) column(s). Actually, I want a column value on the provider to be filtered on subcribers when replicating. For example, on provider : a | b | c | d -+--++-- 123 | 45.6 | 15 | abc 111 | 55.5 | 66 | def on subscriber : a | b | c | d -+--++-- 123 | 45.6 | 15 | abc 111 | | 66 | def -> b NULL on subscriber but the others columns are replicated. De: "Fabrízio de Royes Mello" À: "PALAYRET Jacques" Cc: "Postgres General" Envoyé: Vendredi 23 Octobre 2020 16:23:19 Objet: Re: Conditional column filtering with pglogical replication Em sex., 23 de out. de 2020 às 10:35, PALAYRET Jacques < [ mailto:jacques.palay...@meteo.fr | jacques.palay...@meteo.fr ] > escreveu: > > With PgLogical extension, I have tested the possibility of column filtering > (columns) and row filtering (row_filter). > But is there a way to do a conditional column filtering ? > I mean a way to filter a column based on a predicate, with pglogical (so the > filtered values won’t arrive on subscribers). > For example, with a replicated table t(a integer as Primary Key, b numeric, c > integer, d text), I would like to filter the column b when c between 10 and > 20. > Hello, According to the documentation [1] the row_filter is a normal PostgreSQL expression with the same limitations of CHECK constraints, so you can do something like: row_filter := 'c between 10 and 20 and b = ?' Regards, [1] [ https://github.com/2ndQuadrant/pglogical#row-filtering | https://github.com/2ndQuadrant/pglogical#row-filtering ] -- Fabrízio de Royes Mello Timbira - [ http://www.timbira.com.br/ | http://www.timbira.com.br/ ] PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Conditional column filtering with pglogical replication
Hello, With PgLogical extension, I have tested the possibility of column filtering (columns) and row filtering (row_filter). But is there a way to do a conditional column filtering ? I mean a way to filter a column based on a predicate, with pglogical (so the filtered values won’t arrive on subscribers). For example, with a replicated table t(a integer as Primary Key, b numeric, c integer, d text), I would like to filter the column b when c between 10 and 20. Regards - Météo-France - PALAYRET JACQUES DCSC/MBD jacques.palay...@meteo.fr Fixe : +33 561078319
Re: Removing Last field from CSV string
Hello,
Perhaps, a statement like :
substring(theString, 1, length(theString)-position(',' IN reverse(theString)))
with theString 'Class V,Class VI,Class VII,Competitive Exam,Class VIII*,Class'
for example.
Regards
- Météo-France -
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319
Re: Circles with circle() vs ST_Buffer() Equality equalities
Hello,
Sorry, forget my questions.
I checked the differences between the 2 polygons too quickly.
Actually, I checked only some points and some attributes :
SELECT ST_Area(geometry), ST_Perimeter(geometry),
ST_NPoints(ST_ExteriorRing(geometry))
FROM (
SELECT polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
UNION
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)
) t(geometry)
;
st_area | st_perimeter | st_npoints
---+--+
0.780361288064513 | 3.13654849054594 | 33
0.780361288064513 | 3.13654849054594 | 33
--> exactly the same results with the 2 polygons
...
--> But, NOT all the points are identical :
SELECT ST_AsText((g1).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
) t1(geometry)
)t11(g1)
EXCEPT
SELECT ST_AsText((g2).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)
) t2(geometry)
)t22(g2)
ORDER BY 1 ;
st_astext
---
POINT(0.509607359798385 1.90245483899194)
POINT(0.509607359798385 2.09754516100806)
POINT(0.538060233744357 2.19134171618254)
POINT(0.584265193848727 1.7222148834902)
POINT(0.584265193848727 2.2777851165098)
POINT(0.646446609406726 1.64644660940673)
POINT(0.646446609406726 2.35355339059327)
POINT(0.722214883490199 1.58426519384873)
POINT(0.722214883490199 2.41573480615127)
POINT(0.808658283817455 1.53806023374436)
POINT(0.808658283817455 2.46193976625564)
POINT(0.902454838991936 1.50960735979838)
POINT(0.902454838991936 2.49039264020162)
POINT(1 2.5)
POINT(1.46193976625564 2.19134171618254)
POINT(1.49039264020162 2.09754516100806)
(16 lignes)
SELECT ST_AsText((g1).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)
) t1(geometry)
)t11(g1)
EXCEPT
SELECT ST_AsText((g2).geom) FROM (
SELECT st_dumppoints(ST_ExteriorRing(geometry))
FROM (
SELECT polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
) t2(geometry)
)t22(g2)
ORDER BY 1 ;
st_astext
---
POINT(0.509607359798384 2.09754516100806)
POINT(0.509607359798385 1.90245483899193)
POINT(0.538060233744356 2.19134171618254)
POINT(0.584265193848726 2.2777851165098)
POINT(0.584265193848728 1.7222148834902)
POINT(0.646446609406725 2.35355339059327)
POINT(0.646446609406727 1.64644660940673)
POINT(0.722214883490197 2.41573480615127)
POINT(0.7222148834902 1.58426519384873)
POINT(0.808658283817453 2.46193976625564)
POINT(0.808658283817456 1.53806023374436)
POINT(0.902454838991934 2.49039264020161)
POINT(0.902454838991937 1.50960735979838)
POINT(0.998 2.5)
POINT(1.46193976625564 2.19134171618255)
POINT(1.49039264020161 2.09754516100807)
(16 lignes)
=> It is a bit weird, but it seems there are slight differences on some points.
For example :
POINT(1 2.5) for polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry
and
POINT(0.998 2.5) for ST_Buffer('POINT(1 2)'::geometry, 0.5::double
precision)
I guess it comes from rounding because the start point is not the same for the
2 polygons.
- Météo-France -
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319
Circles with circle() vs ST_Buffer() Equality equalities
Hello,
With PostgreSQL 10.11, I was trying to compare a circle made with the function
circle() to the « same » or similar one made thanks to the function
ST_Buffer().
-> The circle of type circle : circle('POINT(1 2)'::geometry::point,
0.5::double precision) :
SELECT ST_AsText(polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry) AS circle_cast_as_polygon ;
circle_cast_as_polygon
---
POLYGON((0.5 2,0.509607359798385 2.09754516100806,0.538060233744357
2.19134171618254,0.584265193848727 2.2777851165098,0.646446609406726
2.35355339059327,0.722214883490199 2.41573480615127,0.808658283817455
2.46193976625564,0.902454838991936 2.49039264020162,1 2.5,1.09754516100806
2.49039264020162,1.19134171618254
2.46193976625564,1.27778511650982.41573480615127,1.35355339059327
2.35355339059327,1.41573480615127 2.2777851165098,1.46193976625564
2.19134171618254,1.49039264020162 2.09754516100806,1.5
2,1.490392640201621.90245483899194,1.46193976625564
1.80865828381746,1.41573480615127 1.7222148834902,1.35355339059327
1.64644660940673,1.2777851165098 1.58426519384873,1.19134171618255
1.53806023374436,1.09754516100806 1.50960735979838,1 1.5,0.902454838991936
1.50960735979838,0.808658283817455 1.53806023374436,0.722214883490199
1.58426519384873,0.646446609406726 1.64644660940673,0.584265193848727
1.7222148834902,0.538060233744357 1.80865828381745,0.509607359798385
1.90245483899194,0.5 2))
-> The circle of polygon sub-type : ST_Buffer('POINT(1 2)'::geometry,
0.5::double precision) :
SELECT ST_AsText(ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)) AS
circle_as_polygon_via_ST_Buffer ;
circle_as_polygon_via_st_buffer
---
POLYGON((1.5 2,1.49039264020162 1.90245483899194,1.46193976625564
1.80865828381746,1.41573480615127 1.7222148834902,1.35355339059327
1.64644660940673,1.2777851165098 1.58426519384873,1.19134171618255
1.53806023374436,1.09754516100806 1.50960735979838,1 1.5,0.902454838991937
1.50960735979838,0.808658283817456 1.53806023374436,0.7222148834902
1.58426519384873,0.646446609406727 1.64644660940673,0.584265193848728
1.7222148834902,0.538060233744357 1.80865828381745,0.509607359798385
1.90245483899193,0.5 2,0.509607359798384 2.09754516100806,0.538060233744356
2.19134171618254,0.584265193848726 2.2777851165098,0.646446609406725
2.35355339059327,0.722214883490197 2.41573480615127,0.808658283817453
2.46193976625564,0.902454838991934 2.49039264020161,0.998
2.5,1.09754516100806 2.49039264020162,1.19134171618254
2.46193976625564,1.2777851165098 2.41573480615127,1.35355339059327
2.35355339059327,1.41573480615127 2.2777851165098,1.46193976625564
2.19134171618255,1.49039264020161 2.09754516100807,1.5 2))
Comparison :
SELECT ST_OrderingEquals(ST_Buffer('POINT(1 2)'::geometry, 0.5::double
precision) , polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry) ;
st_orderingequals
---
f
-> OK, with an ordering polygon equality
=> But, I was surprised by the result of the following 2 SQL queries, in
particular by the second one :
SELECT ST_Equals(ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision) ,
polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry) ;
st_equals
---
f <= weird for me !
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision) = polygon(32,
circle('POINT(1 2)'::geometry::point, 0.5::double precision))::geometry AS
BounderingEquals ;
bounderingequals
--
f <= weird for me !
Details (OK) :
SELECT box(polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry) AS box_from_circle_through_polygon ;
box_from_circle_through_polygon
-
(1.5,2.5),(0.5,1.5)
SELECT box( ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)) AS
box_from_ST_Buffer_polygon ;
box_from_st_buffer_polygon
(1.5,2.5),(0.5,1.5)
SELECT box(polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double
precision))::geometry) = box( ST_Buffer('POINT(1 2)'::geometry, 0.5::double
precision)) AS BounderingBoxEquals ;
bounderingboxequals
-
t
=> Have you any explanations on the result (=false) of st_equals (NOT ordering)
and bounderingequals ?
Thanks in advance.
Regards
- Météo-France -----
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319
Re: How do work tercile, percentile & funcion percentile_cont() ?
With Libre Office Calc, same result than with PostgreSQL :
=CENTILE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30};
0.33)
gives
10.57 (and NOT 10.5)
I didn't think so.
De: "PALAYRET Jacques"
À: pgsql-general@lists.postgresql.org
Envoyé: Mercredi 22 Avril 2020 10:15:26
Objet: Re: How do work tercile, percentile & funcion percentile_cont() ?
Of course, the same result with this table x(c1) for example :
SELECT c1, ntile(3) OVER(ORDER BY c1) FROM x ORDER BY c1 ;
c1 | ntile
+---
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
7 | 1
8 | 1
9 | 1
10 | 1
11 | 2
11 | 2
11 | 2
11 | 2
11 | 2
11 | 2
11 | 2
11 | 2
11 | 2
20 | 2
21 | 3
21 | 3
21 | 3
21 | 3
21 | 3
21 | 3
21 | 3
21 | 3
21 | 3
21 | 3
(30 lignes)
SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ;
percentile_cont
--
10.7
(1 ligne)
SELECT percentile_cont(2./3) WITHIN GROUP (ORDER BY c1) FROM x ;
percentile_cont
--
20.3
(1 ligne)
De: "PALAYRET Jacques"
À: pgsql-general@lists.postgresql.org
Envoyé: Mercredi 22 Avril 2020 10:00:49
Objet: How do work tercile, percentile & funcion percentile_cont() ?
Hello,
>From a table x(c1) containing 30 lines with integer values (column c1) from 1
>to 30 :
SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ;
percentile_cont
--
10.7
(1 ligne)
SELECT percentile_cont(2./3) WITHIN GROUP (ORDER BY c1) FROM x ;
percentile_cont
--
20.3
(1 ligne)
If ordering,
the first 10 lines (c1 from 1 to 10) have ntile(3) OVER(ORDER BY c1) = 1,
the 10 following lines (c1 from 11 to 20) have ntile(3) OVER(ORDER BY c1) = 2,
the last 10 lines (c1 from 21 to 30) have ntile(3) OVER(ORDER BY c1) = 3.
So, I though it should be :
percentile_cont(1./3) = 10.5 (the arithmetic average between 10 et 11) and not
10.7
percentile_cont(2./3) = 20.5 (the arithmetic average between 20 et 21) and not
20.3
Thank in advance for explanation
- Météo-France -
PALAYRET JACQUES
DCSC/MBD
jacques.palay...@meteo.fr
Fixe : +33 561078319
Re: How do work tercile, percentile & funcion percentile_cont() ?
Of course, the same result with this table x(c1) for example : SELECT c1, ntile(3) OVER(ORDER BY c1) FROM x ORDER BY c1 ; c1 | ntile +--- 1 | 1 2 | 1 3 | 1 4 | 1 5 | 1 6 | 1 7 | 1 8 | 1 9 | 1 10 | 1 11 | 2 11 | 2 11 | 2 11 | 2 11 | 2 11 | 2 11 | 2 11 | 2 11 | 2 20 | 2 21 | 3 21 | 3 21 | 3 21 | 3 21 | 3 21 | 3 21 | 3 21 | 3 21 | 3 21 | 3 (30 lignes) SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ; percentile_cont -- 10.7 (1 ligne) SELECT percentile_cont(2./3) WITHIN GROUP (ORDER BY c1) FROM x ; percentile_cont -- 20.3 (1 ligne) De: "PALAYRET Jacques" À: pgsql-general@lists.postgresql.org Envoyé: Mercredi 22 Avril 2020 10:00:49 Objet: How do work tercile, percentile & funcion percentile_cont() ? Hello, >From a table x(c1) containing 30 lines with integer values (column c1) from 1 >to 30 : SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ; percentile_cont -- 10.7 (1 ligne) SELECT percentile_cont(2./3) WITHIN GROUP (ORDER BY c1) FROM x ; percentile_cont -- 20.3 (1 ligne) If ordering, the first 10 lines (c1 from 1 to 10) have ntile(3) OVER(ORDER BY c1) = 1, the 10 following lines (c1 from 11 to 20) have ntile(3) OVER(ORDER BY c1) = 2, the last 10 lines (c1 from 21 to 30) have ntile(3) OVER(ORDER BY c1) = 3. So, I though it should be : percentile_cont(1./3) = 10.5 (the arithmetic average between 10 et 11) and not 10.7 percentile_cont(2./3) = 20.5 (the arithmetic average between 20 et 21) and not 20.3 Thank in advance for explanation - Météo-France ----- PALAYRET JACQUES DCSC/MBD jacques.palay...@meteo.fr Fixe : +33 561078319
How do work tercile, percentile & funcion percentile_cont() ?
Hello, >From a table x(c1) containing 30 lines with integer values (column c1) from 1 >to 30 : SELECT percentile_cont(1./3) WITHIN GROUP (ORDER BY c1) FROM x ; percentile_cont -- 10.7 (1 ligne) SELECT percentile_cont(2./3) WITHIN GROUP (ORDER BY c1) FROM x ; percentile_cont -- 20.3 (1 ligne) If ordering, the first 10 lines (c1 from 1 to 10) have ntile(3) OVER(ORDER BY c1) = 1, the 10 following lines (c1 from 11 to 20) have ntile(3) OVER(ORDER BY c1) = 2, the last 10 lines (c1 from 21 to 30) have ntile(3) OVER(ORDER BY c1) = 3. So, I though it should be : percentile_cont(1./3) = 10.5 (the arithmetic average between 10 et 11) and not 10.7 percentile_cont(2./3) = 20.5 (the arithmetic average between 20 et 21) and not 20.3 Thank in advance for explanation - Météo-France - PALAYRET JACQUES DCSC/MBD jacques.palay...@meteo.fr Fixe : +33 561078319
Re: One way replication in PostgreSQL
Hello, Thanks a lot for the suggested solutions. So, I can use WAL-shipping replication from Primary to the Secundary server, but it's only for full replication. Let's call " P " the provider/primary/master and " S " the subscriber/secundary/slave one. For partial replication (not all the tables), the solutions should use a third (intermediate / middle) server which could have both ways flow with the server P but only one way flow towards the server S. For example, a logical replication (pglogical or logical Postgresql replication) between server P and the middle server and then a WAL-shipping replication between middle server and server S. Is that right ? About the " FDW " solution in " an external server " (a middle one), is it possible to integrate the FDW in the P server to avoid the " external server " ? => What about the trigger-based replication systems like Slony or Londiste ; is it really necessary to have a connection or flow from the server S towards the server P ? Regards De: "PALAYRET Jacques" À: pgsql-general@lists.postgresql.org Envoyé: Lundi 3 Juin 2019 18:00:51 Objet: One way replication in PostgreSQL Hello, If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards provider/primary/master, witch replication systems can I use ? If possible, I would prefer partial replication (only some tables) to full base replication (all instances). Do trigger-based replication systems (like Slony or Londiste or others) need a connection or flow from subscriber to the provider ? Thanks in advance - Météo-France - PALAYRET JACQUES DCSC/MBD jacques.palay...@meteo.fr Fixe : +33 561078319
One way replication in PostgreSQL
Hello, If, for security reasons, I can't create a connection or a flow from subscriber/secundary/slave towards provider/primary/master, witch replication systems can I use ? If possible, I would prefer partial replication (only some tables) to full base replication (all instances). Do trigger-based replication systems (like Slony or Londiste or others) need a connection or flow from subscriber to the provider ? Thanks in advance - Météo-France - PALAYRET JACQUES DCSC/MBD jacques.palay...@meteo.fr Fixe : +33 561078319
