~2 min read|
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
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
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 ... );
Now, that I had that information, I could think about how to access specific properties.
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 = 'Property';
Or, the inverse - all records where the first property is not equal to
SELECT id, groups_reso FROM public.metadata_fields where groups_reso <> 'Property';
Attentive readers may notice that the index used here is
 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.
Hi there and thanks for reading! My name's Stephen. I live in Chicago with my wife, Kate, and dog, Finn. Want more? See about and get in touch!