Why does Knex not recognize Postgres array columns correctly?

Question

I've got a table that looks something like the following:

create table if not exists gameTemplate00.construction (
    player          uuid                                constraint "[construction] Player foreign key"
                                                            references gameTemplate00.player(id),
    colony          uuid                                constraint "[construction] Colony foreign key"
                                                            references gameTemplate00.colony(id),
    location        text,                               -- All subcolonies at this location contribute to production
    investment      uint8   default 0                   not null,
    cost            uint8                               not null,
    history         uint8[3] default '{null,null,null}' not null,
    priority        uint2,
    allocation      allocation                          not null,
    repeat          boolean default false               not null,
    dispatch        text, -- what to do with the vessel once complete
    project         text,                               -- free form name for player, can be null
    constraint "[construction] Priority must be unique for a given subcolony" 
        unique(colony, location, priority)
);

When I query it and get the results back from Knex with:

            db('construction')
                .withSchema('gametemplate00')
                .where('colony', payload.colony)
                .where('location', payload.location)
            .then((constructionListResult: any) => {
                ws.send(JSON.stringify(constructionListResult));
                console.log(constructionListResult);
            })

It returns this:

 {
    player: '5f43f33b-dba6-43ca-bc0c-0516e5d29968',
    investment: '0',
    cost: '1000',
    history: '{NULL,NULL,NULL}',
    priority: '4',
    allocation: { kind: 'percent', amount: 0.35 },
    repeat: false,
    dispatch: null,
    project: 'whenever'
  }

Allocation is a jsonb domain, and it correctly recognizes that and builds out the json object for it. But the array is borked and displayed as a string.

Is this because I've got something misconfigured with Knex, or does it not recognize postgresql array columns at all? This is the least problematic example for me, but on others it will become a real pain to have to parse those out myself.

score 1 · Answer 1 · answered Mar 27 '20 at 21:46

This has something to do with it being an array of custom types (from the pguint postgres extension). While others in the past have had trouble with arrays, that's been taken care of for years at this point, and looks like it should work out of the box (for built-in types).

A similar problem sheds light.

Knex is already using another library called pg-types. If I include this in my code, I'm able to force it to treat the unsigned ints as proper integers:

import * as pg from 'pg';

const newTypes = {
    INT1:     16522,
    UINT1:    16532,
    UINT2:    16542,
    UINT4:    16552,
    UINT8:    16562,
}

// add statements like below for each of the custom types

pg.types.setTypeParser(newTypes.UINT8, (value: string) => {
    return parseInt(value);
});

There are also oids in the database for arrays for each of the custom types, those can be found with something like this:

SELECT oid, typname FROM pg_catalog.pg_type where typname like '_uint%';

But it's unclear to me how to actually parse those into proper arrays. I'll update the answer if/when I figure that out.

I haven't seen anyone adding custom type parser for arrays in `pg`. I hope to get update when you figure that out :) — Mikael Lepistö, Mar 30 '20 at 07:36

Why does Knex not recognize Postgres array columns correctly?

1 Answers1