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
60FB782789
BBD2522744DF725EE0E0
null

2
2
1C5B329D3B
D5B81DB65F13FD2E21F2
null

3
3
63CCE507F9
A293841CDAD57020BB9C
null



SELECT 3




id
init_index
name
description


1
1
E24810E
DDF1220D25EB695AFBC223EEB1A5428A4AA675DBB36F9421B8

2
2
B7B151A
9E2C3611C5BD78D79AAB0C77F1749F42885A5A33BBD780627B

3
3
82DADD8
3CDF8EF55586D5B75A09B06DBF47BBEE52206925F1BAB5006F

4
4
0E21A94
1DFF1E4C6933774F4877DC3E2DA1ED50653B1DDE72F39CA284

5
5
0DA03BA
383EBC604886A01CC7DCA4EBA765E4998E41B1316351A5F88E

6
6
F1C3AE8
7392139EF32BEF5E2CDCF90717FEC35EB8A1F6FD7B0E40D0B4

7
7
35A8B43
D87527CD02CAD8A442DB3C8BC58FC6131F2023EEF2C201545E

8
8
E07E2D4
2AE626CD05CB2C8C00F22F19853C36ED477FBF32CE0773FC5B

9
9
632190D
1D66B2548F5D91DAF717F97B61A684B2FF1B38874EBE011645

10
10
8128DEA
06CA7DE9E988A676007028B3EDFD0135EE793120C28367AB2B



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
    account_inits.account_init_index,
    row_number () OVER () as profile_init_index,
    profile_init.name,
    profile_init.description
  FROM
    account_inits
    CROSS JOIN
    json_to_recordset(
      account_inits.profile_inits
    ) AS profile_init(
      name text,
      description text
    )
),
-- 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
;

SELECT
  id,
  account_id,
  profile_id
FROM
  account_profiles
ORDER BY
  id DESC
LIMIT 10;


new_accounts_count
new_profiles_count
new_account_profiles_count


4
15
15



SELECT 1




id
account_id
profile_id


18
7
25

17
7
24

16
7
23

15
7
22

14
7
21

13
7
20

12
7
19

11
7
18

10
7
17

9
6
16



SELECT 10
id	init_index	login	password	email
1	1	60FB782789	BBD2522744DF725EE0E0	null
2	2	1C5B329D3B	D5B81DB65F13FD2E21F2	null
3	3	63CCE507F9	A293841CDAD57020BB9C	null
id	init_index	name	description
1	1	E24810E	DDF1220D25EB695AFBC223EEB1A5428A4AA675DBB36F9421B8
2	2	B7B151A	9E2C3611C5BD78D79AAB0C77F1749F42885A5A33BBD780627B
3	3	82DADD8	3CDF8EF55586D5B75A09B06DBF47BBEE52206925F1BAB5006F
4	4	0E21A94	1DFF1E4C6933774F4877DC3E2DA1ED50653B1DDE72F39CA284
5	5	0DA03BA	383EBC604886A01CC7DCA4EBA765E4998E41B1316351A5F88E
6	6	F1C3AE8	7392139EF32BEF5E2CDCF90717FEC35EB8A1F6FD7B0E40D0B4
7	7	35A8B43	D87527CD02CAD8A442DB3C8BC58FC6131F2023EEF2C201545E
8	8	E07E2D4	2AE626CD05CB2C8C00F22F19853C36ED477FBF32CE0773FC5B
9	9	632190D	1D66B2548F5D91DAF717F97B61A684B2FF1B38874EBE011645
10	10	8128DEA	06CA7DE9E988A676007028B3EDFD0135EE793120C28367AB2B