Kokizzu's questions

Tenho 2 bancos de dados já restaurados em 2 bancos de dados diferentes na mesma instância, chamo-o geoe restore1 o esquema é idêntico, a única diferença é que geoo banco de dados contém apenas registros antes de <= 2023-11-23e depois de >= 2023-12-01, enquanto restore1contém <= 2023-11-30, como posso copiá-los valores ausentes ( 2023-11-23até 2023-11-30o banco restore1de geodados) para cada tabela facilmente?

uma maneira que eu poderia pensar é criar um programa, para cada tabela, selecionando o último id antes 2023-11-24de geo(chamá-lo X), em seguida, selecionar todos os registros com id maior que Xe, em seguida, inseri-lo manualmente no geobanco de dados.

Existe alguma maneira mais simples?

existem centenas de tabelas, mas a maioria delas é algo assim:

restore1=# \d station_logs_599
                                              Table "public.station_logs_599"
         Column         |              Type              | Collation | Nullable |                 Default
------------------------+--------------------------------+-----------+----------+------------------------------------------
 id                     | integer                        |           | not null | nextval('station_logs_id_seq'::regclass)
 created_at             | timestamp(6) without time zone |           |          |
 updated_at             | timestamp(6) without time zone |           |          |
 deleted_at             | timestamp(6) without time zone |           |          |
 submitted_at           | timestamp(6) without time zone |           | not null |
 sequence               | integer                        |           |          |
 level_sensor           | double precision               |           |          |
 accel_x                | double precision               |           |          |
 accel_y                | double precision               |           |          |
 accel_z                | double precision               |           |          |
 power_current          | double precision               |           |          |
 ip_address             | character varying(50)          |           |          |
 log_type               | integer                        |           |          |
 station_id             | integer                        |           | not null |
 power_voltage          | double precision               |           |          |
 data                   | jsonb                          |           |          |
 is_deleted             | boolean                        |           | not null | false
 temperature            | double precision               |           |          |
 wind_speed             | double precision               |           |          |
 soil_moisture          | double precision               |           |          |
 wind_direction         | double precision               |           |          |
 raindrop               | double precision               |           |          |
 humidity               | integer                        |           |          |
 barometric_pressure    | double precision               |           |          |
 wind_speed_average     | double precision               |           |          |
 wind_gust              | double precision               |           |          |
 wind_direction_average | double precision               |           |          |
 rain_rate              | double precision               |           |          |
Indexes:
    "station_logs_599_epoch" btree (date_part('epoch'::text, submitted_at))
    "station_logs_599_submitted_at" btree (submitted_at)
    "uniq_sid_sat_599" UNIQUE CONSTRAINT, btree (submitted_at)
Foreign-key constraints:
    "station_logs_599_station_fk" FOREIGN KEY (station_id) REFERENCES stations(id)

Só preciso ter certeza antes de truncar uma tabela, todos os dados na visão materializada que dependem dessa tabela também serão apagados?

Além disso, se a visualização materializada for recriada (descartada e criada novamente) após as inserções acontecerem nessa tabela, as linhas antigas da tabela de origem serão apresentadas na visualização materializada?

c.dataé o objeto jsonb, assim:{programs:[{...},{...}]}

c.data->'programs'é matriz jsonb, assim [{a:1},{b:2},{c:3,d:4}]ounull

Já tentei isso:

SELECT p
FROM Clients c , jsonb_array_elements(c.data->'programs') p

ou isto

SELECT p
FROM Clients c , jsonb_array_elements(COALESCE(c.data->'programs','[]'::JSONB)) p

mas apresenta um erro:

[22023] ERROR: cannot extract elements from a scalar

como posso recuperar o objeto dentro data->'programs'como linhas de objeto?

Estou tentando mover o diretório de dados da clickhouse para outra partição /dev/sdb1. Então aqui está o que eu fiz:

sudo systemctl stop clickhouse-server
mv /var/lib/clickhouse /var/lib/clickhouse-orig
mkdir /var/lib/clickhouse
chown clickhouse:clickhouse /var/lib/clickhouse
mount -o user /dev/sdb1 /var/lib/clickhouse 
cp -Rv /var/lib/clickhouse-orig/* /var/lib/clickhouse/
chown -Rv clickhouse:clickhouse /var/lib/clickhouse
sudo systemctl start clickhouse-server

mas mostra um erro ao iniciar:

Processing configuration file '/etc/clickhouse-server/config.xml'.
Sending crash reports is disabled
Starting ClickHouse 21.6.4.26 with revision 54451, build id: 12B138DBA4B3F1480CE8AA18884EA895F9EAD439, PID 10431
starting up
OS Name = Linux, OS Version = 5.4.0-1044-gcp, OS Architecture = x86_64
Calculated checksum of the binary: 26864E69BE34BA2FCCE2BD900CF631D4, integrity check passed.
Setting max_server_memory_usage was set to 882.18 MiB (980.20 MiB available * 0.90 max_server_memory_usage_to_ram_ratio)
DB::Exception: Settings profile `default` not found
shutting down
Stop SignalListener thread

EDIT aparentemente mesmo sem nova partição não inicia, então provavelmente o config.xmlou o macro.xmlé o culpado

Como fazer replicação (1 master, 2 slave por exemplo) no ClickHouse sem sharding? Tudo o que posso ver nos exemplos é sempre ter fragmentação:

• Apresentação da Altinidade
• Exemplo de composição do Docker
• Blogue procurado pelo programador
• Blog QuidQuid
• Blogue FatalErrors
• artigo zergon321 no dev.to
• Edição 2161 da Clickhouse, mas nenhum exemplo

Estou tentando reproduzir a replicação mestre do taranol da documentação

então eu fiz master/master.lua, rep1/rep1.lua, rep2/rep2.lua, cd para cada diretório e o executei, mas ele mostra algo assim (por mais de 10 minutos, sem progresso):

@ /tmp/master
└─[0] <> tarantool master.lua
2021-05-21 21:12:43.941 [884153] main/103/master.lua C> Tarantool 2.7.2-14-ga308ba8e2
2021-05-21 21:12:43.941 [884153] main/103/master.lua C> log level 5
2021-05-21 21:12:43.941 [884153] main/103/master.lua I> wal/engine cleanup is paused
2021-05-21 21:12:43.941 [884153] main/103/master.lua I> mapping 268435456 bytes for memtx tuple arena...
2021-05-21 21:12:43.941 [884153] main/103/master.lua I> Actual slab_alloc_factor calculated on the basis of desired slab_alloc_factor = 1.044274
2021-05-21 21:12:43.941 [884153] main/103/master.lua I> mapping 134217728 bytes for vinyl tuple arena...
2021-05-21 21:12:43.942 [884153] main/103/master.lua I> instance uuid b78ffe3e-d8d3-4f98-b213-dae4ac0b6858
2021-05-21 21:12:43.942 [884153] iproto/101/main I> binary: bound to 0.0.0.0:13301
2021-05-21 21:12:43.942 [884153] main/103/master.lua I> connecting to 2 replicas
2021-05-21 21:12:43.942 [884153] main/111/applier/[email protected]:33301 I> can't connect to master
2021-05-21 21:12:43.942 [884153] main/111/applier/[email protected]:33301 coio.cc:108 !> SystemError connect, called on fd 17, aka 127.0.0.1:59352: Connection refused
2021-05-21 21:12:43.942 [884153] main/111/applier/[email protected]:33301 I> will retry every 1.00 second
2021-05-21 21:12:43.942 [884153] main/110/applier/[email protected]:23301 I> can't connect to master
2021-05-21 21:12:43.942 [884153] main/110/applier/[email protected]:23301 coio.cc:108 !> SystemError connect, called on fd 16, aka 127.0.0.1:49188: Connection refused
2021-05-21 21:12:43.942 [884153] main/110/applier/[email protected]:23301 I> will retry every 1.00 second
2021-05-21 21:12:45.944 [884153] main/110/applier/[email protected]:23301 I> remote master e242f602-6591-4eb5-9194-f64d7541ea67 at 127.0.0.1:23301 running Tarantool 2.7.2
2021-05-21 21:12:47.945 [884153] main/111/applier/[email protected]:33301 I> remote master 7d21d786-f0fa-440c-8b19-4c2f66fe47fe at 127.0.0.1:33301 running Tarantool 2.7.2
2021-05-21 21:12:47.945 [884153] main/103/master.lua I> connected to 2 replicas
2021-05-21 21:12:47.945 [884153] main/111/applier/[email protected]:33301 I> failed to authenticate
2021-05-21 21:12:47.945 [884153] main/111/applier/[email protected]:33301 box.cc:2381 E> ER_LOADING: Instance bootstrap hasn't finished yet
2021-05-21 21:12:47.945 [884153] main/111/applier/[email protected]:33301 I> will retry every 1.00 second

@ /tmp/rep1
└─[0] <> tarantool rep1.lua
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua C> Tarantool 2.7.2-14-ga308ba8e2
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua C> log level 5
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua I> wal/engine cleanup is paused
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua I> mapping 268435456 bytes for memtx tuple arena...
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua I> Actual slab_alloc_factor calculated on the basis of desired slab_alloc_factor = 1.044274
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua I> mapping 134217728 bytes for vinyl tuple arena...
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua I> instance uuid e242f602-6591-4eb5-9194-f64d7541ea67
2021-05-21 21:12:45.793 [884163] iproto/101/main I> binary: bound to 0.0.0.0:23301
2021-05-21 21:12:45.793 [884163] main/103/rep1.lua I> connecting to 2 replicas
2021-05-21 21:12:45.794 [884163] main/111/applier/[email protected]:33301 I> can't connect to master
2021-05-21 21:12:45.794 [884163] main/111/applier/[email protected]:33301 coio.cc:108 !> SystemError connect, called on fd 17, aka 127.0.0.1:59388: Connection refused
2021-05-21 21:12:45.794 [884163] main/111/applier/[email protected]:33301 I> will retry every 1.00 second
2021-05-21 21:12:45.794 [884163] main/110/applier/[email protected]:13301 I> remote master b78ffe3e-d8d3-4f98-b213-dae4ac0b6858 at 127.0.0.1:13301 running Tarantool 2.7.2
2021-05-21 21:12:47.796 [884163] main/111/applier/[email protected]:33301 I> remote master 7d21d786-f0fa-440c-8b19-4c2f66fe47fe at 127.0.0.1:33301 running Tarantool 2.7.2
2021-05-21 21:12:47.796 [884163] main/103/rep1.lua I> connected to 2 replicas
2021-05-21 21:12:47.796 [884163] main/110/applier/[email protected]:13301 I> failed to authenticate
2021-05-21 21:12:47.796 [884163] main/110/applier/[email protected]:13301 box.cc:2381 E> ER_LOADING: Instance bootstrap hasn't finished yet
2021-05-21 21:12:47.796 [884163] main/110/applier/[email protected]:13301 I> will retry every 1.00 second

@ /tmp/rep2
└─[0] <> tarantool rep2.lua
2021-05-21 21:12:47.435 [884173] main/103/rep2.lua C> Tarantool 2.7.2-14-ga308ba8e2
2021-05-21 21:12:47.435 [884173] main/103/rep2.lua C> log level 5
2021-05-21 21:12:47.435 [884173] main/103/rep2.lua I> wal/engine cleanup is paused
2021-05-21 21:12:47.435 [884173] main/103/rep2.lua I> mapping 268435456 bytes for memtx tuple arena...
2021-05-21 21:12:47.435 [884173] main/103/rep2.lua I> Actual slab_alloc_factor calculated on the basis of desired slab_alloc_factor = 1.044274
2021-05-21 21:12:47.435 [884173] main/103/rep2.lua I> mapping 134217728 bytes for vinyl tuple arena...
2021-05-21 21:12:47.436 [884173] main/103/rep2.lua I> instance uuid 7d21d786-f0fa-440c-8b19-4c2f66fe47fe
2021-05-21 21:12:47.436 [884173] iproto/101/main I> binary: bound to 0.0.0.0:33301
2021-05-21 21:12:47.436 [884173] main/103/rep2.lua I> connecting to 2 replicas
2021-05-21 21:12:47.437 [884173] main/110/applier/[email protected]:13301 I> remote master b78ffe3e-d8d3-4f98-b213-dae4ac0b6858 at 127.0.0.1:13301 running Tarantool 2.7.2
2021-05-21 21:12:47.437 [884173] main/111/applier/[email protected]:23301 I> remote master e242f602-6591-4eb5-9194-f64d7541ea67 at 127.0.0.1:23301 running Tarantool 2.7.2
2021-05-21 21:12:47.437 [884173] main/103/rep2.lua I> connected to 2 replicas
2021-05-21 21:12:47.437 [884173] main/110/applier/[email protected]:13301 I> failed to authenticate
2021-05-21 21:12:47.437 [884173] main/110/applier/[email protected]:13301 box.cc:2381 E> ER_LOADING: Instance bootstrap hasn't finished yet
2021-05-21 21:12:47.437 [884173] main/110/applier/[email protected]:13301 I> will retry every 1.00 second

o código está aqui:

master.lua

box.cfg{
  listen = 13301,
        replication = {'replicator:[email protected]:23301',  -- master URI
                 'replicator:[email protected]:33301'}, -- replica URI
  read_only = false
}
box.once("schema", function()
   box.schema.user.create('replicator', {password = 'password'})
   box.schema.user.grant('replicator', 'replication') -- grant replication role
   box.schema.space.create("test")
   box.space.test:create_index("primary")
   print('box.once executed on master')
end)

rep1.lua

box.cfg{
  listen = 23301,
  replication = {'replicator:[email protected]:13301',  -- master URI
                 'replicator:[email protected]:33301'}, -- replica URI
  read_only = true
}
box.once("schema", function()
   box.schema.user.create('replicator', {password = 'password'})
   box.schema.user.grant('replicator', 'replication') -- grant replication role
   box.schema.space.create("test")
   box.space.test:create_index("primary")
   print('box.once executed on replica 1')
end)

rep2.lua

box.cfg{
  listen = 33301,
  replication = {'replicator:[email protected]:13301',  -- master URI
                 'replicator:[email protected]:23301'}, -- replica URI
  read_only = true
}
box.once("schema", function()
   box.schema.user.create('replicator', {password = 'password'})
   box.schema.user.grant('replicator', 'replication') -- grant replication role
   box.schema.space.create("test")
   box.space.test:create_index("primary")
   print('box.once executed on replica 2')
end)

O que há de errado com isso?

Eu obtive os trabalhos de backup definindo-os nos valores do gráfico do leme (funciona de acordo com a documentação):

    google-cloud-storage-s3:
      type: s3
      s3:
        bucket: gcp_bucket
        credentialsSecret: google-cloud-storage-s3-backup
        region: us-west2
        endpointUrl: https://storage.googleapis.com/

Ele foi carregado com sucesso no armazenamento em nuvem do Google:

Então tentei restaurar o backup:

gsutil -m cp -r \                                      
  "gs://gcp_bucket/percona-2021-05-06-00:00:11-full.md5" \
  "gs://gcp_backup/percona-2021-05-06-00:00:11-full.sst_info/" \
  "gs://gcp_backup/percona-2021-05-06-00:00:11-full/" \
  .
| [1.1k/1.1k files][  2.7 GiB/  2.7 GiB] 100% Done 942.3 KiB/s ETA 00:00:00     
Operation completed over 1.1k objects/2.7 GiB. 

sudo apt install percona-server-server percona-xtrabackup-80

A documentação dizia:

service mysqld stop
rm -rf /var/lib/mysql/*
cat xtrabackup.stream | xbstream -x -C /var/lib/mysql # --> where is this from?
xtrabackup --prepare --target-dir=/var/lib/mysql 
chown -R mysql:mysql /var/lib/mysql
service mysqld start

O problema é que não consegui encontrar nenhum arquivo nomeado copy-backup.shou xtrabackup.streamnos diretórios de backup, existe outra maneira de restaurar o backup, também todos os arquivos compactados com lz4?

Podemos fazer , mas e INSERT INTO SELECTse eu tiver um monte de WITH ASdeclarações (cerca de 27ish) para o SELECTpapel? Minha solução atual é fazer a consulta, em seguida, digitalizar usando a linguagem de programação ( Golangno meu caso) e inseri-la usando a INSERT INTOinstrução normal, uma por uma. Houve uma solução passo para fazer isso?

por exemplo, se eu tiver string '012345678'e precisar atualizar a posição número 4 em outro byte/caractere 'x'por exemplo:

INSERT INTO foo(pk,pos) VALUES('abcdefghi',5)
ON DUPLICATE KEY UPDATE
  pk = CONCAT(
     SUBSTRING(pk,1,VALUES(pos)-1),
     SUBSTRING(VALUES(pk),VALUES(pos),1),
     SUBSTRING(pk,VALUES(pos)+1)
  );

-- simpler example:
SELECT CONCAT(SUBSTRING('abcdefghi',1,5-1),SUBSTRING('123456789',5,1),SUBSTRING('abcdefghi',5+1));
-- abcd5fghi

como fazer isso no VARBINARYtipo de dados?

Eu tenho um banco de dados postgresql na /partição raiz (61 GB usados ​​de 63 GB), então pedi ao provedor VPS para adicionar mais disco (50 GB a /mnt/vdb1)

Como adicionar outro diretório de dados a essa nova partição (usando link simbólico ou algo assim)?

Para que as tabelas recém-criadas não usem, /var/lib/postgresql/10/main/basemas usem /mnt/vdb1. Eu estive procurando, mas parece que vários diretórios de dados estão disponíveis apenas no antigo PostgreSQL 6/7 ?

Por que há arquivos que terminam em .1 .2 .3 na pasta do meu banco de dados PostgreSQL?

-rw------- 1 postgres postgres 1073741824 Oct 11 09:32 412rw95.2
-rw------- 1 postgres postgres   67100672 Oct 11 09:32 41295.3
-rw------- 1 postgres postgres 1073741824 Oct 11 09:07 41296
-rw------- 1 postgres postgres 1073741824 Oct 11 07:27 41296.1

Eu tenho uma tabela station_logsem um banco de dados PostgreSQL 9.6:

    Column     |            Type             |    
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 submitted_at  | timestamp without time zone | 
 level_sensor  | double precision            | 
Indexes:
    "station_logs_pkey" PRIMARY KEY, btree (id)
    "uniq_sid_sat" UNIQUE CONSTRAINT, btree (station_id, submitted_at)

Estou tentando obter o último level_sensorvalor com base em submitted_at, para cada station_id. Existem cerca de 400 station_idvalores exclusivos e cerca de 20 mil linhas por dia por station_id.

Antes de criar o índice:

EXPLAIN ANALYZE
SELECT DISTINCT ON(station_id) station_id, submitted_at, level_sensor
FROM station_logs ORDER BY station_id, submitted_at DESC;

Exclusivo (custo=4347852.14..4450301.72 linhas=89 largura=20) (tempo real=22202.080..27619.167 linhas=98 loops=1)
   -> Classificar (custo=4347852.14..4399076.93 linhas=20489916 largura=20) (tempo real=22202.077..26540.827 linhas=20489812 loops=1)
         Chave de classificação: station_id, submit_at DESC
         Método de classificação: mesclagem externa Disco: 681040kB
         -> Seq Scan em station_logs (custo=0.00..598895.16 linhas=20489916 largura=20) (tempo real=0.023..3443.587 linhas=20489812 loops=$
 Tempo de planejamento: 0,072 ms
 Tempo de execução: 27690,644 ms

Criando índice:

CREATE INDEX station_id__submitted_at ON station_logs(station_id, submitted_at DESC);

Após criar o índice, para a mesma consulta:

Exclusivo (custo=0,56..2156367,51 linhas=89 largura=20) (tempo real=0,184..16263.413 linhas=98 loops=1)
   -> Varredura de índice usando station_id__submitted_at em station_logs (custo=0.56..2105142.98 linhas=20489812 largura=20) (tempo real=0.181..1$
 Tempo de planejamento: 0,206 ms
 Tempo de execução: 16263,490 ms

Existe uma maneira de tornar essa consulta mais rápida? Como 1 segundo, por exemplo, 16 segundos ainda é muito.

Qual é o método correto (aconselhável) para fazer backup de um cassandra ou scylladb para que possamos restaurá-lo no ambiente de desenvolvimento com facilidade?

Como criar INSERT INTOdeclarações, usando SELECTdeclaração? Preciso dele porque quero deletar 10k de registros de uma tabela, mas se algum dia algum desse registro precisar ser restaurado, quero fazer isso facilmente. Não quero fazer backup de toda a tabela, porque só preciso restaurar algumas das linhas excluídas.

Se eu tiver linhas assim:

lec1 major1 class1 10
lec1 major2 class1 40
lec1 major1 class2 30
lec1 major3 class3 35
lec2 major1 class3 15
lec2 major3 class3 10

E eu preciso disso para combinar a proporção em:

lec1 major1 0.2 -- 10/(10+40)
lec1 major2 0.8 -- 40/(10+40)
lec1 major1 1 -- 30/30
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

Ou algo parecido com isto:

lec1 major1 1.2 -- 10/(10+40) + 30/30
lec1 major2 0.8 -- 40/(10+40)
lec1 major3 1 -- 35/35
lec2 major1 0.6 -- 15/(10+15)
lec2 major3 0.4 -- 10/(10+15)

Que consulta deve ser feita?

Podemos criar um índice para uma chave/valor de um tipo de dados JSONB?

Por exemplo, para estes esquemas:

CREATE TABLE x (
  id BIGSERIAL,
  data JSONB
);
CREATE TABLE y (
  id BIGSERIAL,
  data JSONB
);

A consulta lenta:

SELECT *
FROM x
  LEFT JOIN y
    ON (y.data->>'x_id')::BIGINT = x.id

Como criar um índice para y.data->>'x_id'que possa ser usado para esse tipo de consulta?

Tenho uma mysql.usertabela com este conteúdo:

select host, user, password from user;

| localhost  | root | *75267AEB7355CEEE80EAB92D19FF50095AD9BB3E |
| linux      | root |                                           |
| localhost  |      |                                           |
| linux      |      |                                           |

É seguro remover as últimas 3 linhas? já que nunca entro no banco de dados sem senha? ou esses são usuários padrão do MySQL no Windows que não devem ser excluídos?

Esta função usada para mesclar 2 JSONBe excluir determinada chave quando o valor é nulo, consulte esta questão.

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE key NOT IN (SELECT key FROM json_union WHERE value ='null');
$$ LANGUAGE SQL;

Primeira pergunta , por que value = 'null'funciona, mas value IS NULLnão é?

Segunda pergunta , há alguma razão ruim para usar WHERE value <> 'null'em vez de WHERE key NOT IN (SELECT key FROM ...)?

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> 'null';
$$ LANGUAGE SQL;

Terceira pergunta , por que essa função também está incorreta (sempre retorna NULL)? É porque UNION ALL altera todos os registros resultantes para TEXT?

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB
     FROM json_union
     WHERE value <> (NULL::JSONB)
$$ LANGUAGE SQL;

-- check function:
SELECT COALESCE(jsonb_merge('{}','{"b":null}'),'{"x":"its empty"}'::JSONB) FROM xxx;
      coalesce      
--------------------
 {"x": "its empty"}

A partir deste artigo, tentei atualizar ou excluir a propriedade de uma JSONBcoluna:

CREATE TABLE xxx (id BIGSERIAL, data JSONB);
INSERT INTO xxx(data) VALUES( '{"a":1,"b":2}' );
SELECT * FROM data;
 id |       data       
----+------------------
  1 | {"a": 1, "b": 2}

crie a função de atualização:

CREATE FUNCTION jsonb_merge(JSONB, JSONB) 
RETURNS JSONB AS $$
WITH json_union AS (
    SELECT * FROM JSONB_EACH($1)
    UNION ALL
    SELECT * FROM JSONB_EACH($2)
) SELECT JSON_OBJECT_AGG(key, value)::JSONB FROM json_union;
$$ LANGUAGE SQL;

teste:

-- replace
UPDATE xxx SET data = jsonb_merge(data,'{"b":3}') WHERE id = 1;
SELECT * FROM xxx;
 id |       data       
----+------------------
  1 | {"a": 1, "b": 3}

-- append
UPDATE xxx SET data = jsonb_merge(data,'{"c":4}') WHERE id = 1;
SELECT * FROM xxx;
 id |           data       
----+-------------------------
  1 | {"a": 1, "b": 3, "c": 4}

A questão é:

1. existe alguma desvantagem de usar JSONB_EACH(jsonb_merge) em vez de JSONB_EACH_TEXT(do artigo) neste caso?

2. como modificar o jsonb_mergeassim se o valor da propriedade do segundo parâmetro for null(algo como {"b":null}) o valor seria apagado?

.

-- remove
UPDATE xxx SET data = jsonb_merge(data,'{"b":null}') WHERE id = 1;
SELECT * FROM xxx;
 id |       data       
----+-----------------
  1 | {"a": 1, "c": 4}

O jsonb_column @> '{"key":value}'::jsonboperador é igual a jsonb_column->'key' = value? em termos de resultado, desempenho e índices que serão utilizados?