Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

INSERT into an array column fails with a malformed array literal

ErrorERROR: array value must start with "{" or dimension information

Short answer

An array column accepts either a literal in curly braces, like '{1,2,3}', or an ARRAY[1,2,3] constructor. A plain string, a comma list or a JSON-style [1,2,3] is not a valid literal, so the value is refused. Rewrite the value in one of the two accepted forms, or let the driver send a real array parameter.

  • Applies to PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run the smallest possible insert with the ARRAY constructor. If it works, the column is fine and only your value's format was wrong.

    CREATE TEMP TABLE t (tags text[]);
    INSERT INTO t VALUES (ARRAY['a', 'b']);
    SELECT * FROM t;
  2. 2Match what you see, then jump to its fix

    1. 1The failing value looks like 'red,green' or just 'red'.The value is a plain string or a comma list, not an array literalFix
    2. 2The value starts with [ and has double-quoted strings, for example ["a","b"], often produced by JSON.stringify.A JSON array was sent where an array literal belongsFix
    3. 3Only values such as Smith, John or {draft} fail; plain words insert fine.An element contains a comma or a brace and was not double-quotedFix

What does the error look like?

  • ERROR: array value must start with "{" or dimension information
  • ERROR: malformed array literal

Cause 1 of 3

The value is a plain string or a comma list, not an array literal

1Check

An array literal is a string of the form '{ val1, val2 }'. Anything that does not start with a brace is not one.

SELECT 'red,green'::text[];

2Fix

Wrap the values in braces inside the quotes, or build the array with the constructor, where elements are ordinary single-quoted SQL strings.

INSERT INTO t VALUES ('{"red","green"}');
INSERT INTO t VALUES (ARRAY['red', 'green']);

3Verify

Read the row back.

SELECT tags, array_length(tags, 1) FROM t;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

A JSON array was sent where an array literal belongs

1Check

PostgreSQL's array literal can start with dimension information in square brackets, such as '[1:3]={1,2,3}'. A JSON array also starts with [ but has no = and braces, so it is not valid.

SELECT '["a","b"]'::text[];

2Fix

Send a real array: pass the JS or Python list as a bound parameter and let the driver encode it, or switch the column to jsonb if you really store JSON.

-- node-postgres: pass an array, not a JSON string
-- await client.query('INSERT INTO t (tags) VALUES ($1)', [['a', 'b']]);
-- or store JSON on purpose:
ALTER TABLE t ADD COLUMN tags_json jsonb;

3Verify

Insert from the application and select the row.

SELECT tags FROM t ORDER BY ctid DESC LIMIT 1;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

An element contains a comma or a brace and was not double-quoted

1Check

Inside the braces, an element with a comma or curly brace must be wrapped in double quotes, and the word NULL unquoted means a null element.

SELECT '{Smith, John}'::text[];

2Fix

Double-quote that element, or use ARRAY[] where each element is a normal SQL string.

SELECT '{"Smith, John","Lee"}'::text[];
SELECT ARRAY['Smith, John', 'Lee'];

3Verify

The result has two elements.

SELECT cardinality(ARRAY['Smith, John', 'Lee']);

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Use ARRAY[...] in hand-written SQL; it avoids brace and quote mistakes.
  • Pass arrays as bound parameters from application code instead of building strings.
  • Use jsonb for data that is really JSON, and array columns only for flat lists of one type.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.