Kokizzu提出的问题 -dba

Kokizzu

Asked: 2023-12-01 14:56:28 +0800 CST

恢复但仅表上丢失的数据

6

我已经在同一实例上的 2 个不同数据库上恢复了 2 个数据库，调用它geo并且restore1 架构是相同的，唯一的区别是geo数据库仅包含 <= 之前2023-11-23和 >= 之后的记录2023-12-01，而restore1包含 <= 2023-11-30，我如何复制这些每个表容易缺失值（2023-11-23直到数据库） 2023-11-30？restore1geo

我能想到的一种方法是，为每个表创建一个程序，从之前选择最新的 id 2023-11-24（geo称为X），然后选择 id 大于的所有记录X，然后手动将其插入geo数据库。

有没有更简单的方法？

有数百张表，但大多数都是这样的：

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)

Kokizzu

Asked: 2022-10-08 08:30:47 +0800 CST

截断表是否会刷新 BigQuery 上的物化视图？

5

只需要确保在截断表之前，依赖于该表的物化视图上的所有数据也会被擦除吗？

此外，如果在该表上发生插入后重新创建（删除并再次创建）物化视图，源表的旧行是否会出现在物化视图上？

Kokizzu

Asked: 2022-06-28 06:49:09 +0800 CST

如何将对象的非结构化数组变为行？

0

c.data是jsonb对象，像这样：{programs:[{...},{...}]}

c.data->'programs'是 jsonb 数组，像这样[{a:1},{b:2},{c:3,d:4}]或null

已经尝试过这个：

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

或这个

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

但它显示一个错误：

[22023] ERROR: cannot extract elements from a scalar

如何将里面的对象检索为对象data->'programs'行？

Kokizzu

Asked: 2021-06-15 23:50:12 +0800 CST

将 Clickhouse 数据目录挂载到另一个分区：DB::Exception: Settings profile `default` not found

1

我正在尝试将 clickhouse 数据目录移动到另一个分区/dev/sdb1。所以这就是我所做的：

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

但它在启动时显示错误：

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 显然即使没有新分区它也不会启动，所以可能 theconfig.xml或 themacro.xml是罪魁祸首

Kokizzu

Asked: 2021-06-01 00:52:35 +0800 CST

没有分片的 Clickhouse 复制

0

如何在没有分片的情况下在 ClickHouse 中进行复制（例如 1 个主设备，2 个从设备）？从示例中我可以看到总是有分片：

Kokizzu

Asked: 2021-05-22 06:23:25 +0800 CST

无法运行 Tarantool 复制

1

我正在尝试从文档中重现 tarantool 主复制

所以我为每个目录制作了master/master.lua, rep1/rep1.lua, , cd 并运行它，但它显示了类似这样的内容（10 多分钟，没有进展）：rep2/rep2.lua

@ /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

代码在这里：

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)

它出什么问题了？

Kokizzu

Asked: 2021-05-07 06:16:56 +0800 CST

如何从 kubernetes 恢复 percona 数据库？

0

我通过在舵图值上设置这些来获得备份工作（它根据文档工作）：

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

已成功上传至谷歌云存储：

然后我尝试恢复备份：

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

文件说：

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

问题是，我在备份目录中找不到任何名为copy-backup.sh或xtrabackup.stream的文件，是否有其他方法可以恢复备份，以及所有用 lz4 压缩的文件？

Kokizzu

Asked: 2020-08-25 02:37:47 +0800 CST

我们可以在 MySQL 中使用 x AS ( ... ) INSERT INTO y(k) SELECT * FROM x 吗？

0

我们可以做INSERT INTO SELECT，但是如果我有一堆WITH AS陈述（大约 27ish）SELECT呢？我目前的解决方案是进行查询，然后使用编程语言进行扫描（Golang就我而言），然后使用普通INSERT INTO语句一一插入。有没有一步解决方案来做到这一点？

Kokizzu

Asked: 2020-08-11 23:37:10 +0800 CST

有什么方法可以更新某个位置的 VARBINARY 值？

1

例如，如果我有 string '012345678'，并且我需要将位置号 4 更新为另一个字节/字符'x'，例如：

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

如何在VARBINARY数据类型中做到这一点？

Kokizzu

Asked: 2020-01-06 01:43:29 +0800 CST

在 PostgreSQL 10 上添加数据目录

1

我在/根分区上有一个 postgresql 数据库（63GB 中使用了 61GB），所以我要求 VPS 提供商添加更多磁盘（50GB 到/mnt/vdb1）

如何将另一个数据目录添加到该新分区（使用符号链接或其他东西）？

这样新创建的表不使用/var/lib/postgresql/10/main/base而是使用/mnt/vdb1。我一直在寻找，但似乎多个数据目录仅在旧PostgreSQL 6/7上可用？

Kokizzu

Asked: 2018-10-11 18:37:56 +0800 CST

数据目录中扩展名为 .1、.2 或 .3 的文件是什么

2

为什么我的 PostgreSQL 数据库文件夹中有以 .1 .2 .3 结尾的文件？

-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

Kokizzu

Asked: 2017-06-25 00:29:51 +0800 CST

如何在 PostgreSQL 中使 DISTINCT ON 更快？

20

我station_logs在 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)

我试图获得level_sensor基于submitted_at,的最后一个值station_id。大约有 400 个唯一station_id值，每个station_id.

创建索引之前：

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

唯一（成本=4347852.14..4450301.72 行=89 宽度=20）（实际时间=22202.080..27619.167 行=98 循环=1）
   -> 排序（成本=4347852.14..4399076.93 行=20489916 宽度=20）（实际时间=22202.077..26540.827 行=20489812 循环=1）
         排序键：station_id，submitted_at DESC
         排序方法：外部合并磁盘：681040kB
         -> Seq Scan on station_logs (cost=0.00..598895.16 rows=20489916 width=20) (实际时间=0.023..3443.587 rows=20489812 loops=$
 规划时间：0.072 ms
 执行时间：27690.644 ms

创建索引：

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

创建索引后，对于相同的查询：

唯一（成本=0.56..2156367.51 行=89 宽度=20）（实际时间=0.184..16263.413 行=98 循环=1）
   -> 在 station_logs 上使用 station_id__submitted_at 进行索引扫描（成本=0.56..2105142.98 行=20489812 宽度=20）（实际时间=0.181..1$
 规划时间：0.206 ms
 执行时间：16263.490 ms

有没有办法让这个查询更快？例如 1 秒，16 秒仍然太多。

Kokizzu

Asked: 2017-03-02 22:39:18 +0800 CST

执行 Cassandra/Scylladb 备份的正确方法

2

备份 cassandra 或 scylladb 的正确（建议）方法是什么，以便我们可以轻松地在开发环境中恢复它？

Kokizzu

Asked: 2016-04-16 05:23:35 +0800 CST

在 PostgreSQL 中从 SELECT 创建 INSERT INTO

5

如何创建INSERT INTO语句，使用SELECT语句？我需要它是因为我想从表中删除 10k 条记录，但如果有一天需要恢复其中的一些记录，我想轻松地做到这一点。我不想备份整个表，因为我只需要恢复一些已删除的行。

Kokizzu

Asked: 2016-02-17 21:05:11 +0800 CST

PostgreSQL 中每组的比率

0

如果我有这样的行：

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

我需要它将比率组合成：

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)

或者是这样的：

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)

需要做什么查询？

Kokizzu

Asked: 2015-10-07 01:52:47 +0800 CST

我们可以为 JSONB 数据类型的键/值创建索引吗？

5

我们可以为 JSONB 数据类型的键/值创建索引吗？

例如，对于这些架构：

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

慢查询：

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

如何创建y.data->>'x_id'可用于此类查询的索引？

Kokizzu

Asked: 2015-05-21 16:42:20 +0800 CST

MySQL 默认用户

0

我有mysql.user这个内容的表：

select host, user, password from user;

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

删除最后 3 行是否安全？因为我从来没有在没有密码的情况下登录数据库？或者那些是不应删除的 Windows 上 MySQL 的默认用户？

Kokizzu

Asked: 2015-03-21 02:22:57 +0800 CST

对 JSONB_EACH 结果的 UNION ALL 的说明

2

该函数用于合并2 JSONB，并在值为null时删除某个key，参考这个问题。

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;

第一个问题，为什么value = 'null'有效，但value IS NULL不是？

第二个问题WHERE value <> 'null'，使用instead of有什么不好的理由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;

第三个问题，为什么这个函数也不正确（总是返回NULL）？是因为 UNION ALL 将所有结果记录更改为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"}

Kokizzu

Asked: 2015-03-20 22:06:34 +0800 CST

PostgreSQL 从 JSONB 列更新和删除属性

3

在这篇文章中，我尝试更新或删除JSONB列的属性：

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}

创建更新函数：

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;

测试：

-- 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}

问题是：

JSONB_EACH在这种情况下，使用(jsonb_merge) 而不是JSONB_EACH_TEXT（来自文章）有什么缺点吗？
jsonb_merge如果第二个参数属性值是null（类似于{"b":null}）值将被删除，如何修改？

.

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

Kokizzu

Asked: 2015-01-09 00:52:01 +0800 CST

PostgreSQL JSONB @> 运算符是否等于 ->''=？

7

运算jsonb_column @> '{"key":value}'::jsonb符等于? jsonb_column->'key' = value在将使用的结果、性能和索引方面？

恢复但仅表上丢失的数据

截断表是否会刷新 BigQuery 上的物化视图？

如何将对象的非结构化数组变为行？

将 Clickhouse 数据目录挂载到另一个分区：DB::Exception: Settings profile `default` not found

没有分片的 Clickhouse 复制

无法运行 Tarantool 复制

如何从 kubernetes 恢复 percona 数据库？

我们可以在 MySQL 中使用 x AS ( ... ) INSERT INTO y(k) SELECT * FROM x 吗？

有什么方法可以更新某个位置的 VARBINARY 值？

在 PostgreSQL 10 上添加数据目录

数据目录中扩展名为 .1、.2 或 .3 的文件是什么

如何在 PostgreSQL 中使 DISTINCT ON 更快？

执行 Cassandra/Scylladb 备份的正确方法

在 PostgreSQL 中从 SELECT 创建 INSERT INTO

PostgreSQL 中每组的比率

我们可以为 JSONB 数据类型的键/值创建索引吗？

MySQL 默认用户

对 JSONB_EACH 结果的 UNION ALL 的说明

PostgreSQL 从 JSONB 列更新和删除属性

PostgreSQL JSONB @> 运算符是否等于 ->''=？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

Kokizzu's questions