CODEWITHSUNDEEP
postgresqlhstore
Giedrius
Giedrius

Reputation: 1688

postgresql use column name value when quoted with single quotes

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?

Upvotes: 0

Views: 212

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

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.

Upvotes: 1

Related Questions