Search code examples
postgresqlnpgsqlhstore

How can I use an hstore column type with Npgsql?

I have a table with the following schema:

CREATE TABLE account
(
  id serial primary key,
  login varchar(40) not null,
  password varchar(40) not null,
  data hstore
);

I'd like to use an NpgsqlCommand object with parameters to retrieve and store the account data from my application. Which DbType do I have to use for the NpgsqlParameter? The enum NpgsqlDbType does not have a value for hstore. Can I use a Dictionary or HashTable as value of the NpgsqlParameter object?

When I use a JSON column I can create a parameter of type NpgsqlDbType.Text, use a library like JSON.Net to serialize an object to a JSON string and send an SQL statement like that:

INSERT INTO account (login, password, data) VALUES (:login, :password, :data::json)

Unfortunately this does not work with an hstore column. I get a syntax error when I try to do this:

INSERT INTO account (login, password, data) VALUES (:login, :password, :data::hstore)

The string I pass to the data parameter looks like this:

'key1 => "value1", key2 => "value2"'

Solution

  • Thank you, Francisco! I saw in the log that the single quotes (') at the beginning and the end of the string are escaped when they are passed to PostgreSQL. When I pass

    key1 => "value1", key2 => "value2"

    instead, I can insert the data into the hstore column.