porsager / porsager/postgres

Array serialization does not work with custom array types

Open
#592 6 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug
Dominant language
JavaScript
Stars
8.7k
Forks
374
Avg merge
11d 16h
Merged PRs (30d)
1

Description

Consider the following schema:

CREATE TABLE my_table (my_array text[]);

If you run:

sql.unsafe(`insert into my_table (my_array) values ($1)`, [['item']]);

...it works as expected.


Now instead of using a built-in type, change the array to be a custom array type:

CREATE DOMAIN my_custom_array_type AS text[]; 
CREATE TABLE my_table (my_array my_custom_array_type);

Running the same code:

sql.unsafe(`insert into my_table (my_array) values ($1)`, [['item']]);

...now fails with the following error:

Uncaught PostgresError: malformed array literal: "item"

There appears to be an issue with serializing arrays, but only for custom array types.

Why would you want to use such custom types? One possible reason is if you want to constrain the length of the array, e.g.

CREATE DOMAIN my_custom_array_type AS text[] 
  CONSTRAINT my_custom_array_type_check CHECK ((array_length(VALUE, 1) <= 3)) // At most 3 elements

It appears to have something to do with how we're defining the serializers for these custom types:
https://github.com/porsager/postgres/blob/364c3ebee57f3a7ce1fc36d5857b574ee72e507c/src/connection.js#L725-L736

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with src/connection.js lines 725-736 and reproduce the issue using the shown PostgreSQL schema and sql.unsafe call. Trace how serializers are selected for the custom array domain, then verify that inserting a JavaScript array works for both the built-in text[] type and the custom type.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, postgresql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.