I need a logical / cleaner query to get all the schema names that I have created
select schema_name
from "information_schema"."schemata"
AND
SELECT nspname
FROM pg_catalog.pg_namespace;
Returns a superset of schema names that have system created schema which I don't need.
I just want the schema names that I have created.
Try filtering from information_schema.schemata the pg_* schemas and the information_schema itself:
SELECT * from information_schema.schemata
WHERE NOT schema_name LIKE 'pg_%' AND schema_name <> 'information_schema'
Excluding the schemas owned by postgres, as suggested by Gordon, works only with the condition that postgres isn't the owner of any other schema but the system schemas. In case you created a schema with the user postgres, it will be excluded from the result set.
If you don't want the built-in schemas, then use:
select schema_name
from "information_schema"."schemata"
where schema_owner <> 'postgres';
If you want the ones that you specifically created, then check for the ones where you are the owner.
select
*,
nspowner::regrole as role_name
from pg_namespace
where nspowner = 'postgres'::regrole;
returns all schemes where owner is postgres user. Change it to your user name to get schemes owned by you.
Below query solved my problem
SELECT distinct pg_namespace.nspname
FROM pg_catalog.pg_inherits INNER JOIN
pg_catalog.pg_class ON (pg_inherits.inhrelid = pg_class.oid) INNER JOIN
pg_catalog.pg_namespace ON (pg_class.relnamespace = pg_namespace.oid)
WHERE pg_namespace.nspname <> 'public';