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

Question

CREATE TYPE pencil_count AS(
    pencil_color varchar(30),
    count integer
);

CREATE TYPE pencil_count_with_date(
date_ date,
pencil_count pencil_count[]
);

CREATE TABLE pencils(id serial, pencils_ pencil_count_with_date[]);

INSERT INTO pencils(pencils_) 
VALUES('{"(\"2016-03-13\",{"(\"blue\",1)","(\"red\",2)"})"}');

What would be the correct syntax if I want to add this composite array without using ARRAY[...]?

please add some ; to your first two commands. DDL needs semicolons, too ... — wildplasser
– wildplasser, Commented Mar 13, 2016 at 23:01
(1) Why don't you want to use array[...], that makes everything so much easier. (2) Isn't this the same thing as your other question recent question? — mu is too short
– mu is too short, Commented Mar 14, 2016 at 0:12

Community · Accepted Answer · 2017-05-23 12:07:50Z

Using literal string will be less readable when you add new nested levels:

CREATE TYPE pencil_count AS(pencil_color varchar(30)
       ,"count" int);

CREATE TYPE pencil_count_with_date AS(date_ date
                                     ,pencil_count pencil_count[]);

CREATE TABLE pencils(id serial, pencils_ pencil_count_with_date[]);

INSERT INTO pencils(pencils_) 
VALUES('{"(
               \"2016-03-13\",
               \"{
                   \"\"(Blue,5)\"\",
                   \"\"(Red,2)\"\"
               }\"
          )"}');   


SELECT pencils_[1].pencil_count[1].pencil_color
FROM pencils;

SqlFiddleDemo

Remarks:

You need to quote each level with " escaped by \ based on nest level.
This kind of schema looks like trying to introduce OO world to databases. It could be harder to maintain and slower than normalized version. Related question.
You could quote string literals with $$ if needed.
Using ARRAY and ROW could be easier to spot where each level starts and stops.

Erwin Brandstetter · Accepted Answer · 2023-11-12 22:16:46Z

1

Again, ask Postgres. Extending on the procedure for your previous question:

CREATE TEMP TABLE pencil_count (
  pencil_color varchar(30)
, count integer
);

CREATE TABLE pencil_count_with_date (
  date_ date
, pencil_count pencil_count[]
);

CREATE TABLE pencils (
  id serial
, pencils_ pencil_count_with_date[]
);

Ask Postgres for each nested level:

INSERT INTO pencil_count VALUES ('red' , 1), ('blue', 2);

SELECT ARRAY(SELECT p FROM pencil_count p)::text AS p_row_arr;

-- with result from above:
INSERT INTO pencil_count_with_date(date_, pencil_count)
VALUES ('2016-04-14', '{"(red,1)","(blue,2)"}')
     , ('2016-04-14', '{"(red,3)","(blue,4)"}');

SELECT ARRAY(SELECT p FROM pencil_count_with_date p)::text AS p2_row_arr;

-- with result from above:
INSERT INTO pencils(pencils_)
VALUES
  ('{"(2016-04-14,\"{\"\"(red,1)\"\",\"\"(blue,2)\"\"}\")"
    ,"(2016-04-15,\"{\"\"(red,3)\"\",\"\"(blue,4)\"\"}\")"}');

SELECT id, pencils_::text FROM pencils;

Result:

id | pencils_
---+-------------------------------------------------------
1  | {"(2016-04-14,\"{\"\"(red,1)\"\",\"\"(blue,2)\"\"}\")"
     ,"(2016-04-15,\"{\"\"(red,3)\"\",\"\"(blue,4)\"\"}\")"}

fiddle
_{Old sqlfiddle}

I fully agree with advice so far: Multiple levels of nested row types is typically complex and inefficient. Consider normalization.

More in my answer to your previous question:

Correct syntax for array of composite type

edited Nov 12, 2023 at 22:16

answered Mar 14, 2016 at 4:54

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

1 Comment

Maulik Modi Over a year ago

Is it alright if we use array of nested composite to pass as input parameters to postgresFunction and insert them into normalised tables? Appreciate if there is an example. Example - Each Shipment has multiple Shipment Items and multiple Contact address. We would like to load up 100 shipments in one postgres function call.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related