PostgreSql hstore extension with public prefix issue

Question

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,

Answer 1

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