Thread: 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
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.palayret@meteo.fr
Fixe : +33 561078319
PALAYRET JACQUES
DCSC/MBD
jacques.palayret@meteo.fr
Fixe : +33 561078319
Em sex., 23 de out. de 2020 às 10:35, PALAYRET Jacques <jacques.palayret@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.
>
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
>
> 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,
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
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" <fabrizio@timbira.com.br>
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
Cc: "Postgres General" <pgsql-general@postgresql.org>
Envoyé: Vendredi 23 Octobre 2020 16:23:19
Objet: Re: Conditional column filtering with pglogical replication
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>
Cc: "Postgres General" <pgsql-general@postgresql.org>
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 <jacques.palayret@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.
>
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
>
> 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,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento