Node-Postgres/Knex returning CITEXT[] as a string in JS, instead of an array of strings

Question

I'm using Knex, which itself uses package "pg" (aka "node-postgres").

If you SELECT some rows from a table with a TEXT[] column, all is well... in JS you get an array of strings.

But if you're using a CITEXT[] column, instead you just get back a string in JS like:

"{First-element,Second-element}"

Normally when you want to instruct the pg package on how to return specific postgres types, you can do something like this:

import {types} from 'pg';
types.setTypeParser(types.builtins.TIMESTAMPTZ, 'text');
types.setTypeParser(types.builtins.TIMESTAMP, 'text');
types.setTypeParser(types.builtins.DATE, 'text');
types.setTypeParser(types.builtins.TIME, 'text');
types.setTypeParser(types.builtins.TIMETZ, 'text');

The types.builtins.* constants have values that are hardcoded OID numbers for the known built-in types in postgres. Those OID numbers are the same across all postgres installations.

However due to the fact that CITEXT[] is an extension, the OID numbers for the CITEXT + CITEXT[] types will be different on every server, e.g. with the following SQL query:

SELECT typname, oid, typarray FROM pg_type WHERE typname like '%citext%';

On my development server I get:

typname|oid  |typarray|
-------|-----|--------|
citext |17459|17464   |
_citext|17464|0       |

But on my production server I get:

typname|oid  |typarray|
-------|-----|--------|
citext |18618|18623   |
_citext|18623|0       |

How can I solve this?

Some hacky options that I really don't want to do are:

Find out all the different OID values for all my servers and hard code them in - very hacky and really don't want to do this.
Write code specifically for every table/column that manually converts the strings to array - also hacky and repetitive
When the node process initializes, get the server's OID value for the server and then call the types.setTypeParser() function with that dynamic value - also not very good

How can I solve this for all tables/columns without these hacky solutions?

Mikael Lepistö · Accepted Answer · 2020-11-02 08:45:15Z

1

I don't believe there is any way how to do it without querying DB.

I would probably query the correct OID number before starting the node app and store it to an environment variable and then initialize pg types with the value from process.env.

That is also a bit hacky, but at least the hack is mostly encapsulated out of the application code.

answered Nov 2, 2020 at 8:45

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Node-Postgres/Knex returning CITEXT[] as a string in JS, instead of an array of strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related