CODEWITHSUNDEEP
postgresqldatabase-schema
zauzaj
zauzaj

Reputation: 1226

PostgreSql hstore extension with public prefix issue

I've struggled with my PostgreSql and hstore extension, so in my sql dump file hstore fields are created with this script:

CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;

CREATE TABLE public.identity_devices (
    ...
    metadata public.hstore
);

So I'm getting an error 

structure.sql:2058: ERROR:  type "public.hstore" does not exist

But in case I use 

metadata hstore

everything works fine.

Also, have to note that this happens only on our CI where we're running as user runner but owner of public schema is postgres.

If needs some more data, please let me know and I will provide.

Regards,

Upvotes: 0

Views: 2260

Answers (2)

zauzaj
zauzaj

Reputation: 1226

As we're using Postgres9.3 we have to install hstore manually, on our CI they use Postgres9.6 which automatically add hstore on Db creation.

Also, it puts hstore in namespace pg_catalog which turned out was the main reason why hstore wasn't visible by public.hstore.

So what I had to do is to do one step after creating db on CI, which drop particular extension and creates new one in correct schema.

Anyway, the problem wasn't so hard as it was tricky and weird.

Upvotes: 1

JGH
JGH

Reputation: 17906

You can check if and where the extension is already installed using the command \dx hstore

Upvotes: 1

Related Questions