postgres: Cannot insert an array of integers

Steps to reproduce

Create a table

CREATE TABLE public.test
(
    id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    "array" integer[] NOT NULL,
    CONSTRAINT test_pkey PRIMARY KEY (id)
)

Execute the code

await sql`
  insert into test ("array")
  values (${sql.array([1, 2, 3])})
`;

Actual result

Query is not executed and I get error back

Failed query:  insert into test
      ("array")
    values
      (array[$1,$2,$3]) [ '1', '2', '3' ]
PostgresError: column "array" is of type integer[] but expression is of type text[]
    at Object.ErrorResponse (/home/sergii/projects/fahr/morpheus/node_modules/postgres/lib/backend.js:130:33)
    at Socket.data (/home/sergii/projects/fahr/morpheus/node_modules/postgres/lib/connection.js:233:25)
    at Socket.emit (events.js:315:20)
    at Socket.EventEmitter.emit (domain.js:486:12)
    at addChunk (_stream_readable.js:309:12)
    at readableAddChunk (_stream_readable.js:284:9)
    at Socket.Readable.push (_stream_readable.js:223:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    at query (/home/sergii/projects/fahr/morpheus/node_modules/postgres/lib/index.js:169:36)
    at postgres (/home/sergii/projects/fahr/morpheus/node_modules/postgres/lib/index.js:70:12)
    at main (/home/sergii/projects/fahr/morpheus/src/example/index.ts:20:12)
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42804',
  hint: 'You will need to rewrite or cast the expression.',
  position: '52',
  file: 'parse_target.c',
  line: '592',
  routine: 'transformAssignedExpr',
  query: 'insert into test\n      ("array")\n    values\n      (array[$1,$2,$3])',
  parameters: [
    { type: 0, value: '1' },
    { type: 0, value: '2' },
    { type: 0, value: '3' }
  ]
}

Expected result

The row with array is inserted

Version: 2.0.0-beta.2

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 6
Comments: 17 (12 by maintainers)

Most upvoted comments

I do it by

INSERT INTO "ya_favourite_table" (
  "some_text_array_column",
  "some_integer_array_column"
) VALUES (
  ${sql.array(["alice", "bob", "charlie"])}::text[],
  ${sql.array([1, 2, 3])}::int[]
) RETURNING *;

I’m at 2.0.0-beta.0 and 2.0.0-beta.2

joshxyzhimself on Dec 5, 2020

In that case you’re good, cause Postgres doesn’t see it as text 😃 It sees {1,2,3}

It’s a good point! Maybe we could return that from sql.array() to arrive at a better solution for letting PostgreSQL handle the array.

Lol… oh my… Just checked my implementation for serializing arrays 🙈 I was sure I created arrays using parameter placeholders but we’re just creating a serialized array string… I’m sorry! Should definitely be possible to solve this in a better way then.

porsager on Dec 16, 2020