Postgres 13

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?.
/*
  Types
*/

CREATE TYPE profile_init AS (
  name text,
  description text
);

CREATE TYPE account_init AS (
  login text,
  password text,
  email text,
  -- an array of `profile_init`s
  profile_inits json
);

/*
  Tables
*/

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  init_index bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  login text NOT NULL,
  password text NOT NULL,
  email text
);

CREATE TABLE profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  init_index bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  name text,
  description text
);

CREATE TABLE account_profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  account_id bigint NOT NULL REFERENCES accounts,
  profile_id bigint NOT NULL REFERENCES profiles
);

CREATE TYPE



CREATE TYPE



CREATE TABLE



CREATE TABLE



CREATE TABLE


/*
  Helper functions.
*/

-- random string generator
-- https://www.simononsoftware.com/random-string-in-postgresql/#combined-md5-and-sql
CREATE FUNCTION random_string(length integer)
RETURNS text
LANGUAGE SQL
AS $$ 
  SELECT upper(
    substring(
      (
        SELECT 
          string_agg(
            md5(
              CAST (random() AS text)
            ), 
            ''
          )
        FROM 
          generate_series(
            1,
            CAST (CEIL(length / 32.) AS integer)
          ) 
      ), 
      1, 
      length
    ) 
  );
$$;

--sequence generator
CREATE FUNCTION create_series(amount integer)
RETURNS TABLE (
  index_id bigint
)
LANGUAGE SQL
AS $BODY$
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, amount)
$BODY$;

CREATE FUNCTION



CREATE FUNCTION


/*
  Simple inserts
*/

-- accounts 
WITH account_inits AS (
  SELECT
    row_number () OVER () AS init_index,
    (random_string(10)) AS login,
    (random_string(20)) AS password,
    (NULL) AS email
  FROM
    create_series(3)
),
new_accounts AS (
  INSERT INTO accounts
    (
      init_index,
      login,
      password,
      email
    )
  SELECT
    init_index,
    login,
    password,
    email
  FROM
    account_inits
  ORDER BY
    init_index ASC
  RETURNING
    *
)
SELECT
  id,
  init_index,
  login,
  password,
  email
FROM
  new_accounts
ORDER BY
  id ASC
;

-- profiles
WITH profile_inits AS (
  SELECT
    row_number () OVER () AS init_index,
    (random_string(7)) AS name,
    (random_string(50)) AS description
  FROM
    create_series(10)
),
new_profiles AS (
  INSERT INTO profiles
    (
      init_index,
      name,
      description
    )
  SELECT
    init_index,
    name,
    description
  FROM
    profile_inits
  ORDER BY
    init_index ASC
  RETURNING
    *
)
SELECT
  id,
  init_index,
  name,
  description
FROM
  new_profiles
ORDER BY
  id ASC
;

-- create random relations
WITH input_accounts AS (
  SELECT
    row_number () OVER () AS init_index,
    id
  FROM
    accounts
),
input_profiles AS (
  SELECT
    row_number () OVER () AS init_index,
    id
  FROM
    profiles
),
new_account_profiles AS (
  INSERT INTO account_profiles
    (
      account_id,
      profile_id
    )
  SELECT
    input_accounts.id AS account_id,
    input_profiles.id AS profile_id
  FROM
    input_accounts
    INNER JOIN
    input_profiles
    ON
      input_accounts.init_index = input_profiles.init_index
  RETURNING
    *
)
SELECT
  id,
  account_id,
  profile_id
FROM
  new_account_profiles
ORDER BY
  id ASC
;


id
init_index
login
password
email


1
1
69283A7474
AA0A3999EC030DA3F5D6
null

2
2
A35CC7CD9B
303F34C36F0692802F0B
null

3
3
3AEDAFD5C8
663E517216E12A61F6B0
null



SELECT 3




id
init_index
name
description


1
1
229BC41
0739E4CDC373AB1CFB37EC5F41CDFC5FED93C7C41C6E7726BF

2
2
C1689B9
F11C182397025E404F084A043908E4723E94B710FF7FBF161C

3
3
899E926
26FE388936277BABD8D56C4E6D0AD0784A9D0B23692F1F4E8A

4
4
E081CCC
B5E977E430D6D7E2807EE06C7528A1541A41FADCB0D7703CC9

5
5
4359855
CBF52DBB2A93F0D24E49F8EE91E4DF55CF0FC2B441B10DB76E

6
6
B2AD526
F559B3E88E2E70CC53746D92AF92948ECB47E60F13BE9EEE28

7
7
FB0CAB3
BA36ED9AE99E18D49F16AA61932D615CC4AD6B2988EE57507B

8
8
39C3BDC
EC850CF4605FEC5AFF5A05273A7D24A17DA569AFC302E592B3

9
9
A6B3DA4
6F82EE75DCF633FC1E300024E02F9ADE37749F8AE8DC670594

10
10
1348107
767F6EF70A8EE64C31C5A870E7A8ACCBBBCF96B46614CBEE6E



SELECT 10




id
account_id
profile_id


1
1
1

2
2
2

3
3
3



SELECT 3


/*
  Combined insert.
*/

-- transform top level json array into a set of records
WITH account_inits AS (
  SELECT
    row_number () OVER () as account_init_index,
    login,
    password,
    email,
    profile_inits
  FROM
    json_to_recordset(
      $json$
      [
        {
          "login": "3ED4ECBBC9",
          "password": "E67EDDB6033D02140BB4",
          "email": "a@b",
          "profile_inits": null
        },
        {
          "login": "C86D7E2CF0",
          "password": "75404617C000A0EB070C",
          "profile_inits": [
            {
              "name":"C1B549E",
              "description":"1313CB6F876EA62837A15C20D78A8FA3FC926008FA289AE722"
            }
          ]
        },
        {
          "login": "C51D77BF87",
          "password": "605509993A05EE393081",
          "email": null,
          "profile_inits": [
            {},
            { "name": "2E35813" },
            { "description": "AF2A372263233827253DE19DB8E12798EEF59C311CFE9CEFAB" },
            { "name": "EE899CD", "description": null },
            { "name": null, "description": "CB4CEE63293E16988E58805FED223943E7CAFDF6F417393B30" }
          ]
        },
        {
          "login": "4229D47E2C",
          "password": "CE4E14ED6AD77CBC71B5",
          "email": "b@c",
          "profile_inits": [
            {
              "name": "956F079",
              "description": "BC1E803500773076940C0052D289AAB9952DD47D4954447C8E"
            },
            {
              "name": "99B327B",
              "description": "C4C9702836B1E05CC49D4E205CC8292D017FF3C1BE179CA435"
            },
            {
              "name": "D8EF1A8",
              "description": "554A01F7DBA0C889AF014CBF7EA938DED791FB3A3A50C932E5"
            },
            {
              "name": "91151DB",
              "description": "A86CA083BD509F23FD515C045C8BE32D4B57E3A3940FB8BFD4"
            },
            {
              "name": "31EC363",
              "description": "7008C341EDDBB93B3B1D5904E5EF1FCAE01EB25AC2A5E51761"
            },
            {
              "name": "E7E11D7",
              "description": "0C313B46ADD0E946D24854EA5651379C9D4D56656BFBC6312F"
            },
            {
              "name": "32F1C7C",
              "description": "5B08641B0A3F7359C929E14EEE58502DACA40CF830FF923A7B"
            },
            {
              "name": "23C85ED",
              "description": "7CB34E28022DD84E96D9825CD5E0CB0774D548F56762CF2A6C"
            },
            {
              "name": "1800D37",
              "description": "589850742C3A3A1FC2E9130494069847CCB426636B7F7440F4"
            }
          ]
        }
      ]
      $json$
    ) AS input_init(
      login text,
      password text,
      email text,
      profile_inits json
    )
),
-- transform nested profile inits into profile inits
profile_inits AS (
  SELECT
    -- need a reference to original account init
    -- for later joins
    account_init_index,
    row_number () OVER () as profile_init_index,
    name,
    description
    -- this json array has to be expanded
    -- and joined on `account_init_index`
    -- on itself I presume
    profile_inits
  FROM
    account_inits
),
-- create new accounts
new_accounts AS (
  INSERT INTO accounts
    (
      init_index,
      login,
      password,
      email
    )
  SELECT
    account_init_index AS init_index,
    login,
    password,
    email
  FROM
    account_inits
  RETURNING
    *
),
-- create new profiles
new_profiles AS (
  INSERT INTO profiles
    (
      init_index,
      name,
      description
    )
  SELECT
    profile_init_index AS init_index,
    name,
    description
  FROM
    profile_inits
  RETURNING
    *
),
-- create new profile account relations
new_account_profiles AS (
  -- join new accounts and their inits
  WITH input_accounts AS (
    SELECT
      account_inits.account_init_index,
      new_accounts.id AS account_id
    FROM
      account_inits
      INNER JOIN
      new_accounts
      ON
        account_inits.account_init_index = new_accounts.init_index
  ),
  -- join new profiles and their inits
  input_profiles AS  (
    SELECT
      profile_inits.account_init_index,
      profile_inits.profile_init_index,
      new_profiles.id AS profile_id
    FROM
      profile_inits
      INNER JOIN
      new_profiles
      ON
        profile_inits.profile_init_index = new_profiles.init_index

  ),
  -- join inputs
  account_profile_pairs AS (
    SELECT
      input_accounts.account_id,
      input_profiles.profile_id
    FROM
      input_accounts
      INNER JOIN
      input_profiles
      ON
        input_accounts.account_init_index = input_profiles.account_init_index
  )
  INSERT INTO account_profiles
    (
      account_id,
      profile_id
    )
  SELECT
    account_id,
    profile_id
  FROM
    account_profile_pairs
  RETURNING
    *
)
SELECT
  (
    SELECT
      count(*)
    FROM
      new_accounts
  ) AS new_accounts_count,
  (
    SELECT
      count(*)
    FROM
      new_profiles
  ) AS new_profiles_count,
  (
    SELECT
      count(*)
    FROM
      new_account_profiles
  ) AS new_account_profiles_count
;

ERROR:  column "name" does not exist
LINE 104:     name,
              ^
id	init_index	login	password	email
1	1	69283A7474	AA0A3999EC030DA3F5D6	null
2	2	A35CC7CD9B	303F34C36F0692802F0B	null
3	3	3AEDAFD5C8	663E517216E12A61F6B0	null
id	init_index	name	description
1	1	229BC41	0739E4CDC373AB1CFB37EC5F41CDFC5FED93C7C41C6E7726BF
2	2	C1689B9	F11C182397025E404F084A043908E4723E94B710FF7FBF161C
3	3	899E926	26FE388936277BABD8D56C4E6D0AD0784A9D0B23692F1F4E8A
4	4	E081CCC	B5E977E430D6D7E2807EE06C7528A1541A41FADCB0D7703CC9
5	5	4359855	CBF52DBB2A93F0D24E49F8EE91E4DF55CF0FC2B441B10DB76E
6	6	B2AD526	F559B3E88E2E70CC53746D92AF92948ECB47E60F13BE9EEE28
7	7	FB0CAB3	BA36ED9AE99E18D49F16AA61932D615CC4AD6B2988EE57507B
8	8	39C3BDC	EC850CF4605FEC5AFF5A05273A7D24A17DA569AFC302E592B3
9	9	A6B3DA4	6F82EE75DCF633FC1E300024E02F9ADE37749F8AE8DC670594
10	10	1348107	767F6EF70A8EE64C31C5A870E7A8ACCBBBCF96B46614CBEE6E