Postgres insert data into composite type

Question

I am working in Postgres. I have the below composite type

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

This is my table

CREATE temp 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[]
);

In the table I am using diffs composite type.

Below I am inserting data into the table.

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

This I get this error:

ERROR: malformed array literal: "{(1,"DDD")"}"

Answer 1

Proof of concept

The correct syntax for your demo command is:

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 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\"\"\")"}')
;

Just ask Postgres for correct syntax (psql excerpt):

test=> BEGIN;
BEGIN
test=*> CREATE TYPE diff_type AS (
test(*>     operation int,
test(*>     content text
test(*> );
CREATE TYPE
test=*> CREATE TEMP TABLE syntax_demo OF diff_type;
CREATE TABLE
test=*> INSERT INTO syntax_demo VALUES
test-*>   (1, 'DDD')
test-*> , (2, 'foo')
test-*> , (3, 'Weird \string"');
INSERT 0 3
test=*> 
test=*> SELECT ARRAY(SELECT t FROM syntax_demo t) AS proper_syntax;
                    proper_syntax                     
------------------------------------------------------
 {"(1,DDD)","(2,foo)","(3,\"Weird \\\\string\"\"\")"}   -- !!!
(1 row)

test=*> ROLLBACK;

Test:

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

Reverse test:

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

fiddle
(Note the currently faulty display in dbfiddle.uk: it swallows the inner level of \ escapes.)

As you can see, you need enclosing double-quotes around each row literal (= composite element type), but not around the nested string unless it contains special characters. Then you must escape those, which gets messy quickly with multiple layers of nesting at this point. Just ask Postgres to do it for you.

See:

• Correct syntax for array of composite type
• Syntax for array of nested composite type without using ARRAY[]

Better yet

Don't go there to begin with. Like Laurenz commented, more often than not, composite data types in the table definition indicate a misunderstanding of proper DB design. Consider a half-way normalized schema.

Answer 2

You need to surround each element of the array with quotes, and then escape the quotes inside the complex type's literal:

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\")"}');

SQLFiddle Demo

Answer 3

This is how you create a literal holding an array of negotiation.diff_type:

array['(1,abc)','(2,def)']::negotiation.diff_type[]