Pg-promise: Approach for `INSERT`ing an empty array

[ggoodman]

Consider a table with a column tags that is of type TEXT[]. When building an insert statement like

var data = {
    other_field: 'something',
    tags: [], // Empty array
};
var stmt = `INSERT INTO table (other_field, tags) VALUES($1, $2)`;

return db.one(stmt, [data.other_field, data.tags]);

The resulting query will look something like this:

INSERT INTO table (other_field, tags) VALUES('something', array[]);

Postgres takes offence to this and responds with:

ERROR:  cannot determine type of empty array at character 544
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].

What approach do you recommend using with pg-promise to resolve this ambiguity in a clean and portable way?

[vitaly-t]

There is only one solution to this. Since in such special case PostgreSQL parser cannot deduce type from the data, you have to cast it explicitly:

INSERT INTO table (other_field, tags) VALUES('something', array[]::text[]);

i.e. change your query to:

var stmt = `INSERT INTO table (other_field, tags) VALUES($1, $2::text[])`;

[vitaly-t]

Release v6.7.1 changes these things.