PostgreSQL - how to insert into array of composite type

Question

CREATE TYPE myenum AS ENUM ('title', 'link', 'text');
CREATE TYPE struct AS (
            type myenum,
            content varchar(300)
        );
CREATE TABLE "mytable" (
            id serial PRIMARY KEY,
            array struct[]
        );
INSERT INTO "mytable" VALUES (
            DEFAULT,
            '{"(\"title\",\"my title\")","(\"link\",\"www.google.com\")"}'
        );
INSERT INTO "mytable" VALUES (
            DEFAULT,
            ARRAY['(\"title\",\"my title\")', '(\"link\",\"www.google.com\")']
        );
INSERT INTO "mytable" VALUES (
            DEFAULT,
            ARRAY[('title','my title'), ('link','www.google.com')]
        );

i want to insert some data , and i tried many forms but they all can't insert success, and there are some error messages

error: malformed array literal: "{"("title","my title")","("link","www.google.com")"}"
error: malformed array literal: "{("title","my title"), ("link","www.google.com")}"
error: column "placeholder_array" is of type placeholder_struct[] but expression is of type text[]
error: column "placeholder_array" is of type placeholder_struct[] but expression is of type record[]

need helppppp! that's so hard and messy for me, thank you very much!!!!

MatBailie · Accepted Answer · 2021-05-11 10:49:39Z

2

1). Don't call your column array, that's a keyword.

2). Use ::struct to cast your tuples to that datatype.

CREATE TYPE myenum AS ENUM ('title', 'link', 'text');

CREATE TYPE struct AS (
            type myenum,
            content varchar(300)
        );

CREATE TABLE mytable (
            id serial PRIMARY KEY,
            val_array struct[]
        );

INSERT INTO mytable VALUES (
            DEFAULT,
            ARRAY[('title','my title')::struct, ('link','www.google.com')::struct]
        );

db<>fiddle demo

answered May 11, 2021 at 10:49

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eyebrowkang Over a year ago

the first question is because i just show it clearly, its name is not array actually, AND the second also MOST IMPORTANT , it does works!! thank you!!!

Collectives™ on Stack Overflow

PostgreSQL - how to insert into array of composite type

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related