Re: What's the best way in Postgres9 to store a bunch of arbitrary boolean values for a row?

On 12/05/2011 03:31 PM, Mike Christensen wrote:
>> That'll get slow. It'll work and is IMO better than all the other options
>> you suggested, but I'd probably favour hstore over it.
> The hstore module sounds fantastic!
>
> I'm curious as to how these columns are serialized back through the
> driver, such as Npgsql. Do I get the values as strings, such as a
> comma delimited key/value pair list? Or would I need to do some
> custom logic to deserialize them?

It depends on what Npgsql supports, really. The server sends hstore
values as text; what the client does with them depends on the client. I
don't really do C# and .NET so I'm not the one to turn to for advice on
that side. Ideally a hstore would be parsed and converted to a hash map
by the database driver. At present I don't know of any that do this
natively, though I may well be out of date on this. For PgJDBC there's
code around (not AFAIK yet integrated into PgJDBC proper) to do it.

In many (most?) cases you'll want to interact with hstore fields using
the hstore-provided types and operators, eg.

SELECT somefield SET hstorecol = hstorecol - "somekey";

See: http://www.postgresql.org/docs/current/static/hstore.html

If you're working via some ORM layer (as it sounds like) you may have to
use native queries or explain to it about the hstore types and
operators. That's the usual problem when trying to use database-specific
not-quite-relational features like hstore through a layer that tries to
be db-independent and purely relational. I don't have any experience
with Castle ActiveRecord. When I've used hstore with hibernate I've
always done it by direct native queries.

--
Craig Ringer