Re: Proper syntax to update an hstore key-value pair

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux