Correct syntax for array of composite type

Question

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

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

INSERT INTO pencils(pencils_) VALUES('{("blue",5),("red",2)}');

This doesn't work and gives error:

Malformed array literal.

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

Answer 1

Advice so far is not optimal. There is a simpler solution and an actually applicable explanation.
When in doubt, just ask Postgres to show you:

CREATE TEMP TABLE pencil_count (  -- table also registers row type
  pencil_color varchar(30)
, count integer
);

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

Insert 2 basic rows:

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

See the syntax for the basic row type:

SELECT p::text AS p_row FROM pencil_count p;

  p_row
----------
 (red,1)
 (blue,2)

See the syntax for an array of rows:

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

       p_row_arr
------------------------
 {"(red,1)","(blue,2)"}

All you need is to enclose each row literal in double quotes - which is only necessary to disable the special meaning of the comma within each row type.
Additional (escaped) double quotes would be redundant noise while there are no additional special characters.

None of this has anything to do with escape string syntax, which has been turned off by default since Postgres 9.1. You would have to declare escape string syntax explicitly by prefixing E, like E'string\n'. But there is no good reason to do that.

db<>fiddle here
_{Old sqlfiddle}

Related answer with more explanation:

• Insert text with single quotes in PostgreSQL
• How to pass custom type array to Postgres function
• PL/pgSQL Array of Rows