How to insert in a Postgres table, a hstore value having one key and an array of values of as corresponding value?

Question

So I have to create a table in Postgres to store the content of the table displayed on the image (events_logs). I figured out that I should use the hstore type for the PARAMS columns and everything is fine except for the third row where the key is linked to an array and not a string like the other ones.

Here is the script I have used and I am getting a syntax error for the third row data insertion:

CREATE TABLE events_logs(      
          EVENT_DATE timestamp without time zone NOT NULL,
          USER_ID VARCHAR(25) NOT NULL,
          EVENT_ID integer NOT NULL,
          EVENT_VALUE NUMERIC,
          PARAMS hstore,
          PRIMARY KEY (EVENT_DATE,USER_ID,EVENT_ID),
          CONSTRAINT events_logs_event_id_fkey FOREIGN KEY (EVENT_ID)
              REFERENCES events (EVENT_ID) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE NO ACTION
);

INSERT INTO events_logs (EVENT_DATE,USER_ID,EVENT_ID,EVENT_VALUE,PARAMS) VALUES
('2017-09-13 12:01:00','aed-355-dg25',3,NULL,''),
('2017-09-13 12:05:00','aed-355-dg25',2,NULL,'term => [“lamp”, “blue”]'),
('2017-09-13 12:06:00','ryf-734-em0',3,NULL,''),
('2017-09-13 12:08:00','ryf-734-em0',2,NULL,'term => “rug”');

I have also tried term => {“lamp”, “blue”} and term => ARRAY[“lamp”, “blue”] as well as variations that I got from across forums but none of them is giving me the expected result.

So my questions are:

Is it possible to associate an array of elements to a single key in an hstore value?

Is there a type that is a better fit for this requirement other than hstore?

user330315 · Accepted Answer · 2020-06-29 09:21:42Z

2

Is there a type that is a better fit for this requirement other than hstore?

Yes: jsonb - which has the added benefit that it's a built-in type and doesn't need a CREATE EXTENSION to be used.

In that case your INSERT should look like this:

INSERT INTO events_logs 
  (EVENT_DATE,USER_ID,EVENT_ID,EVENT_VALUE,PARAMS) 
VALUES
  ('2017-09-13 12:01:00','aed-355-dg25',3,NULL,''),
  ('2017-09-13 12:05:00','aed-355-dg25',2,NULL,'{"term": ["lamp", "blue"]}'),
  ('2017-09-13 12:06:00','ryf-734-em0',3,NULL,''),
  ('2017-09-13 12:08:00','ryf-734-em0',2,NULL,'{"term": "rug"}');

Alternatively simply store the params without the term keyword, and a simple array: e.g. '["lamp", "blue"]' or '["rug"]'. I would always store the list parameters as an array, even if it's just one value. It makes accessing the elements easier, because you don't need to distinguish between single values and multiple values.

If this is always just a list of keys, you can also consider a native array: text[]

answered Jun 29, 2020 at 9:21

user330315

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

1 Comment

godson KALIPE Over a year ago

Thank you. I used this and my problem was solved. :-)

Collectives™ on Stack Overflow

How to insert in a Postgres table, a hstore value having one key and an array of values of as corresponding value?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related