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

Currently, Npgsql doesn't support hstore datatype.

It will be sent and received as text from Npgsql.

I hope it helps.

On Mon, Dec 5, 2011 at 09:25, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
>
> 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

--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior