Postgres 16

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?.

CREATE TYPE diff_type AS (
    operation int,
    content text
);

CREATE TABLE temp_input (
 indication_request_id int, 
 para_id int, 
 line_number int, 
 document_id int, 
 modified_content text, 
 created_by text, 
 diffs diff_type[]
);

CREATE TYPE

CREATE TABLE

-- Just ask Postgres for correct syntax:
CREATE TEMP TABLE syntax_demo OF diff_type;
INSERT INTO syntax_demo VALUES
  (1, 'DDD')
, (2, 'foo')
, (3, 'Weird \string"');

SELECT ARRAY(SELECT t FROM syntax_demo t) AS proper_syntax;

CREATE TABLE

INSERT 0 3

proper_syntax

{"(1,DDD)","(2,foo)","(3,"Weird \\string""")"}

SELECT 1

-- Test
SELECT '{"(1,DDD)","(2,foo)","(3,\"Weird \\\\string\"\"\")"}'::diff_type[];

diff_type

{"(1,DDD)","(2,foo)","(3,"Weird \\string""")"}

SELECT 1

-- Cross test
SELECT (('{"(1,DDD)","(2,foo)","(3,\"Weird \\\\string\"\"\")"}'::diff_type[])[3]).content

content

Weird \string"

SELECT 1

-- Use proper syntax 
INSERT INTO temp_input (indication_request_id, para_id, line_number, document_id,modified_content,created_by, diffs)
VALUES (20,2893,10,18,'my content','user1', '{"(1,DDD)"}');

-- Proper syntax for multiple array elements
INSERT INTO temp_input (indication_request_id, para_id, line_number, document_id,modified_content,created_by, diffs)
VALUES (1,2,10,18,'my content','user1', '{"(1,DDD)","(2,foo)"}');

-- Proper syntax for multiple input rows with multiple array elements
INSERT INTO temp_input (indication_request_id, para_id, line_number, document_id,modified_content,created_by, diffs)
VALUES
  (2,3,10,18,'my content','user1', '{"(1,DDD)","(2,foo)"}')
, (3,4,10,18,'my content','user1', '{"(1,DDD)","(2,foo)","(3,\"Weird \\\\string\"\"\")"}')
;

INSERT 0 1

INSERT 0 1

INSERT 0 2

TABLE temp_input;

indication_request_id
para_id
line_number
document_id
modified_content
created_by
diffs

20
2893
10
18
my content
user1
{"(1,DDD)"}

1
2
10
18
my content
user1
{"(1,DDD)","(2,foo)"}

2
3
10
18
my content
user1
{"(1,DDD)","(2,foo)"}

3
4
10
18
my content
user1
{"(1,DDD)","(2,foo)","(3,"Weird \\string""")"}

SELECT 4

indication_request_id	para_id	line_number	document_id	modified_content	created_by	diffs
20	2893	10	18	my content	user1	{"(1,DDD)"}
1	2	10	18	my content	user1	{"(1,DDD)","(2,foo)"}
2	3	10	18	my content	user1	{"(1,DDD)","(2,foo)"}
3	4	10	18	my content	user1	{"(1,DDD)","(2,foo)","(3,"Weird \\string""")"}