2

Given a custom enum: CREATE TYPE vehicle AS ENUM ('car', 'truck', 'bicycle');

And a table like so:

CREATE TABLE vehicle_events (
  timestamp timestamptz NOT NULL DEFAULT current_timestamp,
  labels vehicle[] NOT NULL,
  mentions int4[] NOT NULL DEFAULT '{}'
);

When this is queried from our node application like so:

SELECT * FROM vehicle_events;

This returns a json structure like so:

[{"timestamp": "January, 06 2016 23:04:56", "labels": "{'car'}", "mentions": [1,2,3,4]}]

My question really is, why is the labels array coming back as a string(note also that the mentions array of a base type is not)? Is this because it is an array of an ENUM? If so, how can I force it into a regular array? And why is postgres returning it in this manner?

sqlfiddle link: http://sqlfiddle.com/#!15/637ce/1

4
  • "[1,2,3,4]" would be a string, while [1,2,3,4] isn't. What makes you think it is a string? Your own code shows the opposite.
    – vitaly-t
    Commented Jan 7, 2016 at 0:51
  • the labels array is returning as a string, rather then the mentions array: "{'car'}"
    – Abraham P
    Commented Jan 7, 2016 at 7:07
  • Perhaps another bug within node-postgres. Just add your own converter for the enums, via pg-types - it's quite easy.
    – vitaly-t
    Commented Jan 7, 2016 at 12:09
  • See github.com/brianc/node-pg-types/issues/56
    – Bergi
    Commented Oct 1, 2019 at 9:21

1 Answer 1

7

That is how postgres arrays look if they aren't being interpreted as arrays. If you use to_json() in the query, the array will return appropriately.

Like: SELECT to_json(labels) as labels FROM vehicle_events;

1
  • 3
    Do you happen to know if there is another way of doing this? Without having to explicitly cast-to-json?
    – Edward
    Commented Dec 8, 2017 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.