3

I use primary pgAdmin to browse and edit my PostgreSQL database. Now I would like to use PhpStorm.

In PhpStorm I can browse my tables, but I can not edit data. When I try I get error:

[42704] ERROR: type "hstore" does not exist

Kde: compilation of PL/pgSQL function "on_update" near line 3

Function on_update is on update trigger and it save old row to history table and it uses hstore type.

PhpStorm uses postgresql-9.4-1201.jdbc4.jar driver. I don't know if it is driver error or PhpStorm error. I know that in pgAdmin it works and in PhpStorm not.

Vojtěch Bartoš
  • 213
  • 1
  • 2
  • 9
  • You need to run `create extension hstore` as the Postgres superuser: https://www.postgresql.org/docs/current/static/sql-createextension.html – a_horse_with_no_name Jul 20 '16 at 11:54
  • I've heard this advice already. Is it realy this case? I mean.. It works in pgAdmin so the extension has to be created already or not? – Vojtěch Bartoš Jul 20 '16 at 12:03
  • The error message is clear: the extension is **not** installed and the function `on_update` is using `hstore`. I guess pgAdmin doesn't use that function (wherever that function comes from) – a_horse_with_no_name Jul 20 '16 at 12:05
  • Ok. I will try. Thanks for quick reply. – Vojtěch Bartoš Jul 20 '16 at 12:15
  • I am afraid it did not work. I checked our build process and it contains `CREATE EXTENSION IF NOT EXISTS hstore;` so the extension is installed. – Vojtěch Bartoš Jul 21 '16 at 06:43
  • The error message clearly contradicts your last comment. You might have installed that extension in a different database. Or it's installed in a separate schema and that schema is not in the search_path of the user you are using. – a_horse_with_no_name Jul 21 '16 at 06:51

1 Answers1

3

I work with same environment as Vojtěch and I have found that the extension is indeed created and present. But in different schema (public) then the current connection operates (the PostgreSQL search_path). There is probably bug in PhpStorm as its not respecting PostgreSQL user's default search_path.

Some workarounds (for DB console only):

  • In database console you use RESET SEARCH_PATH; statement.
  • You can enforce search_path on JDBC connection, see the question.
Community
  • 1
  • 1
Jan Drábek
  • 124
  • 6