CODEWITHSUNDEEP
postgresqlhstore
godson KALIPE
godson KALIPE

Reputation: 45

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

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?

events logs table

Upvotes: 0

Views: 800

Answers (1)

user330315
user330315

Reputation:

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[]

Upvotes: 2

Related Questions