I would like to support it fully. I'm not on postgres 9.2 on any of my apps yet -- would you be able to provide a simple script example? I can work that into a failing test & then passing test and then, boom, feature.

Will do.

Once travis-ci gets 9.2 I'll roll out support for using JSON.parse and JSON.stringify for the json OID.

+1 for this feature.

I totally agree. Anyone know off the top of their head the status on pg @ 9.2 on travis-ci?

I do not, sorry.

On Mar 22, 2013, at 9:45 AM, Brian C notifications@github.com wrote:

I totally agree. Anyone know off the top of their head the status on pg @ 9.2 on travis-ci?

—
Reply to this email directly or view it on GitHub.

Now that we've got Postgres9.2 on travis I can work on adding in some tests and support for the JSON oids. They'll only run if SELECT version() returns >= 9.2

fantastic, thanks @brianc !

Uhhh I kinda jacked this up by branching off another feature branch but it should merge cleanly since it was the last thing in the history. git pwns us all.

Tests passing.

Postgres @ 9.1 (travis's default version)
https://travis-ci.org/brianc/node-postgres/jobs/6539359#L182

Postgres @ 9.2
https://travis-ci.org/brianc/node-postgres/jobs/6539359#L430

💃 🎉

@nicholasf npm install pg @ v1.1.0 for built in json support.

Apologies if I'm in the wrong spot here, but do we need the "contains" (@>) operator to also work with this?

Have put an example in the open hstore parser issue.

Not sure if this is a general parsing issue, or something specific to json/hstore?

Everything in the query text gets passed directly to the backend PostgreSQL server without any manipulation what so ever by node or node-postgres apart from Buffer("YOUR QUERY TEXT HERE").toString('utf8') so if you're getting a syntax error from the back end 99% likely it's something wrong with your query.

Think the issue I'm having is with prepared statements and hstore under pg v1.1.0:

Running this...

db.query('SELECT * FROM stuff WHERE data @> $1::hstore;', [{x:1}]);

.... raises the following entry in my Postgres.app log....

ERROR:  Unexpected end of string
STATEMENT:  SELECT * FROM stuff WHERE data @> $1::hstore;

... hinting that $1 is not being expanded?

Prepared statements with hstore parameters do work OK inside PLSQL :

CREATE TABLE stuff(id SERIAL PRIMARY KEY, data HSTORE);
INSERT INTO stuff (data) VALUES ('x=>"1", y=>10'::hstore);
PREPARE test (hstore) AS SELECT * FROM stuff WHERE data @> $1::hstore;
EXECUTE test('x=>1');

=> 1 row

Somewhere along the way the JSON is not being accepted as a parameter by postgres? (probably something I'm doing wrong, but could someone confirm the above code and schema work from them?)

Thanks for any ideas, and a great library 👍

The issue is I am confusing hstore with JSON :) [slaps forehead]

Was hoping that Postgres would provide a converter, but no luck. Maybe 9.3 will have something.

In the mean time, wrote a little JS function:

var json_to_hstore = function(json) {
  var a = [], i = 0, k;
  for (k in json) {
    if (json.hasOwnProperty(k)) { 
      a[i] = '"' + k + '"=>"' + encodeURIComponent(JSON.stringify(json[k])) + '"';
      i++;
    }   
  }
  return a.join(', ');
};

Which might help someone in this way:

s = 'SELECT * FROM stuff WHERE data @> $1::hstore;'
db.query(s, [json_to_hstore({x:1})]);