lundi 20 juin 2016

Does the Postgres HSTORE() function only operate on text/integer datatypes in 9.5?

When I use the PostgreSQL 9.5 hstore() construction function on a row object in a query, it seems to only return columns in that row that are the text or integer datatypes. I can see that my timestamp and character varying columns are by default omitted from the HSTORE object that comes back.

In PostgreSQL 9.3 constructing a hstore object would include timestamp and character varying columns of the row as well, how can I get this behavior back?

Specifically my table's DDL looks like:

CREATE TABLE public.sessions
(
  id integer NOT NULL DEFAULT nextval('sessions_id_seq'::regclass),
  session_id character varying(255) NOT NULL,
  data text,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  CONSTRAINT sessions_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

Below is the output of running a hstore constructor; note that the session_id, created_at, and updated_at columns (keys) do not get added to the hstore even when their values are not null:

select hstore(sessions.*) from sessions limit 1;

hstore                                                                    
---------------------------------------------------------------
"id"=>"15435216", "data"=>"DjfBv="
(1 row)

Aucun commentaire:

Enregistrer un commentaire