add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select version();
version
PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit 
SELECT 1
DROP SCHEMA IF EXISTS "test" CASCADE;
CREATE SCHEMA "test";

DROP TABLE IF EXISTS "test"."source";
CREATE TABLE "test"."source" (
"id" uuid NOT NULL DEFAULT gen_random_uuid(),
"title" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::text,
PRIMARY KEY ("id")
);

INSERT INTO "test"."source" ("id", "title") VALUES ('4536262a-d4a4-4843-946c-59e8cc680687', '来源可爱');
INSERT INTO "test"."source" ("id", "title") VALUES ('5e593bfa-a9b1-4206-bbf0-75745aab5472', '来源猫咪');

DROP TABLE IF EXISTS "test"."source_i18n";
CREATE TABLE "test"."source_i18n" (
"id" bigserial NOT NULL,
"source_id" uuid NOT NULL,
"title" text COLLATE "pg_catalog"."default",
"language_code" text COLLATE "pg_catalog"."default" NOT NULL,
PRIMARY KEY ("id")
);

INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (1, '4536262a-d4a4-4843-946c-59e8cc680687', 'Sourced Cute', 'en-US');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (2, '4536262a-d4a4-4843-946c-59e8cc680687', '来源可爱', 'zh-CN');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (3, '4536262a-d4a4-4843-946c-59e8cc680687', '來源可愛', 'zh-TW');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (4, '4536262a-d4a4-4843-946c-59e8cc680687', '來源可愛', 'zh-HK');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (5, '4536262a-d4a4-4843-946c-59e8cc680687', 'ラブリーから', 'jp-JP');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (6, '5e593bfa-a9b1-4206-bbf0-75745aab5472', 'Sourced Cat', 'en-US');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (7, '5e593bfa-a9b1-4206-bbf0-75745aab5472', '来源猫咪', 'zh-CN');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (8, '5e593bfa-a9b1-4206-bbf0-75745aab5472', '來源貓咪', 'zh-TW');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (9, '5e593bfa-a9b1-4206-bbf0-75745aab5472', '來源貓咪', 'zh-HK');
INSERT INTO "test"."source_i18n" ("id", "source_id", "title", "language_code") VALUES (10, '5e593bfa-a9b1-4206-bbf0-75745aab5472', 'ソースキャッツ', 'jp-JP');
DROP SCHEMA

CREATE SCHEMA

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

DROP TABLE

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1
SELECT
"source".*,
i18n.items as i18n
FROM "test"."source" AS "source"
LEFT JOIN
(
SELECT
"source_id",
JSONB_BUILD_OBJECT(
'title', JSONB_OBJECT_AGG(language_code, title)
) AS items
FROM "test"."source_i18n"
GROUP BY "source_id"
) AS "i18n"
ON "i18n"."source_id" = "source"."id";
id title i18n
4536262a-d4a4-4843-946c-59e8cc680687 来源可爱 {"title": {"en-US": "Sourced Cute", "jp-JP": "ラブリーから", "zh-CN": "来源可爱", "zh-HK": "來源可愛", "zh-TW": "來源可愛"}}
5e593bfa-a9b1-4206-bbf0-75745aab5472 来源猫咪 {"title": {"en-US": "Sourced Cat", "jp-JP": "ソースキャッツ", "zh-CN": "来源猫咪", "zh-HK": "來源貓咪", "zh-TW": "來源貓咪"}} 
SELECT 2