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 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
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,
| id | init_index | login | password | |
|---|---|---|---|---|
| 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,
ERROR: column "name" does not exist LINE 104: name, ^