A weird CREATE EXTENSION WITH error of postgres

Question

I init app_auth database, create extension pgcrypto with app_auth schema, but tests show i create the extension into the public schema, why?

-- init
DROP SCHEMA IF EXISTS app_auth CASCADE;
CREATE SCHEMA app_auth;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA app_auth;

-- test1: without schema
SET search_path = app_auth;
SELECT gen_random_bytes(10); -- "ERROR: function gen_random_bytes(integer) does not exist"

-- test2: with schema app_auth
SET search_path = app_auth;
SELECT app_auth.gen_random_bytes(10); -- "ERROR: function gen_random_bytes(integer) does not exist"

-- test3: with schema public
SET search_path = app_auth;
SELECT public.gen_random_bytes(10); -- "it works"

Looks like the extension already existed. Try \dx with psql. — Laurenz Albe
– Laurenz Albe, Commented Jan 16, 2018 at 14:37
Thanks, you are right~, I've found the extension already existed in public, is there a safe way to init the database schema app_auth to make the test SELECT app_auth.gen_random_bytes(10); always pass? — ZhongHua Yan
– ZhongHua Yan, Commented Jan 16, 2018 at 14:43

user330315 · Accepted Answer · 2018-01-16 14:46:50Z

3

You can move the extension to a different schema:

alter extension pgcrypto set schema app_auth;

is there a safe way to init the database schema app_auth to make the test ... pass?

Another option is to first drop the extension

DROP SCHEMA IF EXISTS app_auth CASCADE;
DROP EXTENSION IF EXISTS pgcrypto;
CREATE SCHEMA app_auth;
CREATE EXTENSION pgcrypto WITH SCHEMA app_auth;

answered Jan 16, 2018 at 14:46

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

A weird CREATE EXTENSION WITH error of postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related