Search code examples
postgresqlhstore

Insert columns value into hstore data type in postgres

I have a table in postgres which has this three columns id_0,turnr and tags. tags column data type is hstore. currently i am using this query which is not working

INSERT INTO relation_15_02_2020 (tags)
VALUES
   (
   '
       "type"=>"restriction",
       "restriction"=>"(select distinct(turnr) from relation_15_02_2020  ) "
       '
   );

How can i add 

"type"=>"restriction",
"restriction"=>" turnr value for respective id

desired output for id_0 =1 tags 

 {"type"=>"restriction","restriction"=>"NoRightTurn"}

enter image description here

Solution

  • You want to update the rows, not insert new ones:

    update relation_15_02_2020 
  set tags = hstore(array['type', 'restriction'], array['restriction', turnr])
where id_0 = 1;

    alternatively

    update relation_15_02_2020 
  set tags = hstore('type', 'restriction')||hstore('restriction', turnr)
where id_0 = 1;