type "geometry" does not exist on schema, but extension does

Question

Note: Not a duplicate of Postgis installation: type “geometry” does not exist

I'm trying to create a table using Postgis geometric columns on a new schema called test.

First I ran:

SET search_path TO test;

Then when I try to run the following statement:

CREATE TABLE spatials (id serial, name text, geo geometry, PRIMARY KEY (id));

I get the error type "geometry" does not exist

I've tried running both of the following:

CREATE EXTENSION postgis;
--AND:
CREATE EXTENSION postgis SCHEMA test;

But both result in the error extension "postgis" already exists

Note that it works fine when using the public schema.

Is there anything I'm missing here?

Answer 1

Sounds like Postgis is installed in the public schema not in the test schema. To verify you can run

SELECT nspname
       FROM pg_extension ext
            INNER JOIN pg_namespace nsp
                       ON nsp.oid = ext.extnamespace
       WHERE ext.extname = 'postgis';

which will give you the schema Postgis is installed in.

Then schema qualify the type like public.geometry or with whatever schema Postgis is installed in.