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

As I explicitly set my search path prior to any of this, I don't think
that's an issue. Of course, I could have simply have set it wrongly. In any
case, adding ::hstore after the parameters threw a "type hstore does not
exist" and enclosing the whole CONNECTOR_TIME_OUT... inside parenthesis
caused the same does not exist error.

You are correct, though, Tom, that the hstore extension is 1.1. Is there
some other, not =>, construct I should be using? Curiously, I just checked
my personal sandbox database (9.5) and the hstore there is 1.3, but like I
said earlier the update succeeds on that one.

On Wed, Aug 3, 2016 at 9:34 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> John Scalia <jayknowsunix(at)gmail(dot)com> 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
>