What should I read?

2020-10-23 Thread W.P.
Hi there, I am (still) using 9.5 Postgres. On my new devices (OrangePi) default installed is 11.9. This also seems to be reasonable choice for my Intel laptop(s), as 11.9 offers logical replication (as Depesz explained me at some point of time) which is needed to replicate data between Intel6

Re: split_part for the last element

2020-10-23 Thread Nikhil Benesch
On Fri, Oct 23, 2020 at 2:35 PM Tom Lane wrote: > > "David G. Johnston" writes: > > On Fri, Oct 23, 2020 at 8:47 AM Nikhil Benesch > > wrote: > >> 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:

Re: split_part for the last element

2020-10-23 Thread Nikhil Benesch
On Fri, Oct 23, 2020 at 2:21 PM David G. Johnston wrote: > I'm torn here because this would be the first usage of this concept in > PostgreSQL (I think). Yeah, I also have some qualms about this design in the context of Postgres. Particularly because Postgres allows arrays to begin at negative i

Re: split_part for the last element

2020-10-23 Thread Tom Lane
"David G. Johnston" writes: > On Fri, Oct 23, 2020 at 8:47 AM Nikhil Benesch > wrote: >> 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' > I'm torn

Re: split_part for the last element

2020-10-23 Thread David G. Johnston
On Fri, Oct 23, 2020 at 8:47 AM Nikhil Benesch wrote: > 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' > Some thoughts: I'm torn here because this

Re: split_part for the last element

2020-10-23 Thread Nikhil Benesch
Right, that's option 2 in my original mail. There are several deficiencies with that idiom: * It is non-obvious. Sure, it might make sense to you and I, but to someone just learning SQL, it takes a minute to reason through why it works. They're also unlikely to invent the trick on their own. *

Re: split_part for the last element

2020-10-23 Thread PALAYRET Jacques
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 delim

split_part for the last element

2020-10-23 Thread Nikhil Benesch
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. (strin

Re: Conditional column filtering with pglogical replication

2020-10-23 Thread PALAYRET Jacques
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 |

Re: postgres materialized view refresh performance

2020-10-23 Thread Philip Semanchuk
> On Oct 23, 2020, at 9:52 AM, Ravi Krishna wrote: > >> My understanding is that when CONCURRENTLY is specified, Postgres implements >> the refresh as a series of INSERT, UPDATE, >> and DELETE statements on the existing view. So the answer to your question >> is no, Postgres doesn’t create a

Re: Conditional column filtering with pglogical replication

2020-10-23 Thread Fabrízio de Royes Mello
Em sex., 23 de out. de 2020 às 10:35, PALAYRET Jacques < 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 filte

Re: postgres materialized view refresh performance

2020-10-23 Thread Ravi Krishna
> My understanding is that when CONCURRENTLY is specified, Postgres implements > the refresh as a series of INSERT, UPDATE, > and DELETE statements on the existing view. So the answer to your question is > no, Postgres doesn’t create another table and > then swap it. The INSERTS/UPDATE/DELETE ha

Re: Hot backup in PostgreSQL

2020-10-23 Thread Stephen Frost
Greetings, * Mark Johnson (remi9...@gmail.com) wrote: > User managed backups in PostgreSQL work very similar to what you know from > Oracle. You first place the cluster in backup mode, then copy the database > files, and lastly take the cluster out of backup mode. The first and last > steps are

Conditional column filtering with pglogical replication

2020-10-23 Thread PALAYRET Jacques
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 s

Re: postgres materialized view refresh performance

2020-10-23 Thread Philip Semanchuk
> On Oct 22, 2020, at 3:53 PM, Ayub M wrote: > > There is a table t which is used in a mview mv, this is the only table in the > mview definition. > > create table t (c1 int, ..., c10 int > ); > > -- there is a pk on say c1 column > create materialized view mv as select c1, c2...c10 from >