0

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)
Eternal Rubyist
  • 3,445
  • 4
  • 34
  • 51

1 Answers1

0

No. All columns should be included. Even with NULL values. (I just tested to verify.)

Maybe you are inadvertently using a different table with the same name in a different schema?
Aside: you can simplify to (equivalent):

SELECT hstore(sessions) FROM public.sessions LIMIT 1;

I schema-qualified public.sessions to make sure the right table is used.
I assume you are aware of the role of the schema search path?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Hmm qualifying the schema with `public.sessions` didn't do fix it, good thought though. I'm relatively positive it's the only `sessions` table in that database let alone schema. Perhaps is an issue with the operating system/distribution of postgres I am using. I confirmed it is working perfectly on my Ubuntu installation of 9.3 (with `postgresql-contrib-9.3` installed from apt-get to for the hstore module), whereas the problematic one was installed via the MSI installer on Windows 10 and is version of 9.5.3. – Eternal Rubyist Jun 19 '16 at 02:57
  • @nomizzz: Well, there has to be *some* explanation, but I don't see how that would make the difference. I tested 9.5.3 on Windows 7 and it works as expected. – Erwin Brandstetter Jun 19 '16 at 03:23
  • Agreed -- now that I know that's the expected behavior on 9.5 as well it's most likely some configuration setting or scoping setting different in the defaults that I'm missing. I'll report back when I figure it out. – Eternal Rubyist Jun 19 '16 at 03:59