Postgres array of custom types - how to query by custom type property

Question

I want to use Postgres array to store events per each sender. Each event is persisted as a Postgres custom type (object).

How can I query records by custom type properties? Is it possible or not?

If yes, can I put any index type on array column to speed up the queries?

Create custom type:

CREATE TYPE sender_event AS (
  event_timestamp TIMESTAMP(3),
  message TEXT
);

Create table with array of custom types:

CREATE TABLE sender_history (
    sender varchar(30),
    events  sender_event[]
);

Insert few records to the table:

INSERT INTO sender_history VALUES 
('sender1', ARRAY[(now(), 'Message text 1')::sender_event]),
('sender2', ARRAY[(now(), 'Message text 2')::sender_event])

How can I find all records which 'events' array contain object with "message" property equal to 'Message text 2'?

E.g. something like this (this does not work):

SELECT * FROM sender_history
WHERE events.message = 'Message text 2'

Expected result should be the second record in that table.

This query would be sooo much easier with a properly normalized data model. — user330315
– user330315, Commented Jul 10, 2019 at 9:52
You mean like persisting each event as a new row in a table (traditional way)? I am already doing it like this, but I want to try arrays, because the table will be smaller (faster vacuuming, less disk space required, etc...) — elBastarde
– elBastarde, Commented Jul 10, 2019 at 9:54
This is just an example. I have several other columns for each sender, and then I have to repeat the same value in all records, which is waste of space — elBastarde
– elBastarde, Commented Jul 10, 2019 at 9:56

user330315 · Accepted Answer · 2019-07-10 09:56:18Z

1

You need to unnest those arrays if you want to apply a condition:

SELECT sh.*
FROM sender_history sh
WHERE exists (select *
              from unnest(sh.events) as x
              where x.message = 'Message text 2');

But this would be better stored in a properly normalized one-to-many relationship.

Quote from the manual

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

answered Jul 10, 2019 at 9:56

user330315

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

Comments

S-Man · Accepted Answer · 2019-07-10 09:57:26Z

1

Searching an array for a certain element you need to expand it with unnest() before:

demo:db<>fiddle

SELECT * 
FROM 
    sender_history, 
    unnest(events) as event
WHERE event.message = 'Message text 2'

But nevertheless, I would really recommend to not use custom types in this case. Instead, as @a_horse_with_no_name said, you should normalize your table structure.

answered Jul 10, 2019 at 9:57

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

Postgres array of custom types - how to query by custom type property

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related