John Scalia <jayknowsunix@xxxxxxxxx> writes: > I've got something strange going on in one of my databases. I need to > update key-value pair to fix one of our configurations. The table is named > "configuration" and the hstore attribute is called "parameters". The update > I've been attempting looks like: > update configuration set parameters = parameters || '"CONNECTOR_TIME_OUT" > => "-1"'::hstore; That doesn't look right, because || and => have the same precedence. You'd need to write update configuration set parameters = parameters || ('"CONNECTOR_TIME_OUT" => "-1"'::hstore); But you'd be better off moving to use the hstore() function as Richard suggested, because the => operator is gone as of PG 9.2 or so. > This update works properly from the psql command line on one of my sandbox > databases, but this particular test database reports: ERROR: type "hstore" > does not exist. Probably a search path problem, but you've not provided enough details to diagnose. > Now \dx shows that hstore is an installed extension. I've had issues before > where I've had to qualify hstore using ::public.hstore, but in this case > that gives me different error stating that no operator matches the given > name and argument type. I'm betting this is hstore 1.1 or later, which hasn't got the => hstore operator, because we deprecated it some time before that and needed to get rid of it altogether to make room for the SQL-standard meaning of =>. Use the function. > [ later ] > Tried that, but received "ERROR: column "parameters" is of type > public.hstore but expression is of type text" I'm still betting on a search path problem, causing || to resolve as text||text not hstore||hstore. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin