CODEWITHSUNDEEP
sqldatabasepostgresql
eyebrowkang
eyebrowkang

Reputation: 21

PostgreSQL - how to insert into array of composite type

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!!!!

Upvotes: 1

Views: 1245

Answers (1)

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 2

Related Questions