Non-structuring data comes from an HTML form, i.e. some fields may come, and some may not. They receive a backend on Zend Framework 2 and writes to the database. Well, or reads from the base.

I want to store these fields in the hstore. However, adding data to hstore, which is not very convenient to retrieve it as a string. You have to write an additional class, which, before adding / outputting, will parse the data for the hstore field.

Is it possible, when executing queries to certain fields on the Postgres side, to automatically perform hstore <-> record conversion, for example. Well, or at least hstor <-> json?

Most likely, you can write some kind of trigger, but it would be nice to see an example. Can someone tell me?

  • why not keep this data in json / jsonb? - nörbörnĂ«n
  • There will be information such as email / phone numbers, etc. fields that need to be searched. I thought on hstore create an index. And doing the type like %чего-Ń‚Đľ-Ń‚Đ°ĐĽ% when using json I think it will be too slow. - VEV
  • what is the problem? - jsonb allows you to have great gin indexes! the usual json, however, is also indexed with a bang a la "create index table_idx on table (json - >> 'email')" - nörbörnĂ«n
  • But about the indices for json - do not throw a reference? - VEV
  • postgresql.org/docs/9.4/static/datatype-json.html - nörbörnĂ«n

2 answers 2

Alternatively, if ::json not suitable:

 select to_json(('"a"=>1, "b"=>2'::hstore));
  • Yes, this is not the case. When using the constructor, the entire string is treated as the field name and the error Statement could not be executed (42703 - 7 - ERROR: column alm_leads.to_json\"(\"data\") does not exist ( VOV Error did Statement could not be executed (42703 - 7 - ERROR: column alm_leads.to_json\"(\"data\") does not exist
 select ('"a"=>1, "b"=>2'::hstore)::json; json ---------------------- {"a": "1", "b": "2"} (1 row)
  • If you use the query designer in Zend Framework 2, it is problematic to specify ::json in select. Therefore, we need a thread trigger for writing / reading hstore fields. - VEV
  • I am not Copenhagen in the Zend, but in my experience “problematic” is usually not equal to “not possible”. and if you want to fence problems and towns, plentifully salting them with triggers, then what's the problem - to create a container next to hstore with a json box, make an insert update trigger and put the contents of hstore into json. but why? ... - nörbörnĂ«n
  • Well, I ask - give an example :) On the existing code it is easier to figure out how to do it. - VEV
  • an example of what? trigger on insert / update? examples are complete in the documentation and in the network - nörbörnĂ«n
  • Link was very used to the article. But, I can google myself, you're right ... - VEV