INSERT into an array column fails with a malformed array literal
ERROR: array value must start with "{" or dimension informationShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The failing value looks like 'red,green' or just 'red'.The value is a plain string or a comma list, not an array literalFix - 2
The 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
Only 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
- 1
What does the error look like?
ERROR: array value must start with "{" or dimension informationERROR: 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
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
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
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
Databases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix