postgresql use column name value when quoted with single quotes

Question

I'm trying to update hstore key value with another table reference column. Syntax as simple as

SET misc = misc || ('domain' => temp.domain)

But I get error because everything in parenthesis should be quoted:

SET misc = misc || ('domain=>temp.domain')::hstore

But this actually inserts temp.domain as a string and not its value. How can I pass temp.domain value instead?

Answer 1

You can concatenate text with a subquery, and cast the result to type hstore.

create temp table temp (
  temp_id integer primary key, 
  domain text
);
insert into temp values (1, 'wibble');

select ('domain => ' || (select domain from temp where temp_id = 1) )::hstore as key_value
from temp

key_value
hstore
--
"domain"=>"wibble"

Updates would work in a similar way.