/* Code-Comments */

Postgres Data Types: Array

June 10, 2019

When looking around a table in Postgres today, I noticed a curious looking field. groups_reso looked like a collection, but when I looked at the Data Type, it was listed as _text. underscore data type

I didn’t know what the underscore meant, and looking into the data types didn’t immediately yield the confirmation I sought, so while I felt like it was an Array, I couldn’t be sure.2

data types

Fortunately, I had access to the create table script and could eventually confirm my suspicion that it was in fact an Array.

create table if not exists metadata_fields (
    id text
    ...
    , groups_reso text[]
    ...
);

Accessing Array Properties

Now, that I had that information, I could think about how to access specific properties.

For example, Property is always in the first position of a collection if it’s present, so, let’s say I wanted to only select rows which included that property, I would do:

SELECT id, groups_reso
FROM public.metadata_fields
where groups_reso[1] = 'Property';

Or, the inverse - all records where the first property is not equal to Property:

SELECT id, groups_reso
FROM public.metadata_fields
where groups_reso[1] <> 'Property';

Attentive readers may notice that the index used here is [1] to refer to the first property. That’s because ”[b]y default, PostgreSQL uses one-based numbering for array elements.”1

Lots more to learn, but at least I can now move forward.

Footnotes


Stephen Weiss

Thanks for reading! My name's Stephen Weiss. I live in Chicago with my wife, Kate, and dog, Finn.
Click here to see the archives of my weeks in review and sign up yourself!