CODEWITHSUNDEEP
postgresqldatabase-schema
alenm
alenm

Reputation: 1043

Postgres create database is adding another schema along with public by default

Postgres Level:
Newbie

SCENARIO
I was going through some Postgres tutorial and playing around with the chinook database and I must have done something to the search_path or user role because now whenever I type in CREATE DATABASE I will get 2 schemas by default public and chinook. I don't know why.

STEPS 

psql> CREATE DATABASE foo;
psql> \c foo

psql (12.2, server 11.6)
You are now connected to database "foo" as user "username".

psql> \dn

  Name   |  Owner
---------+----------
 chinook | username
 public  | postgres

psql> \dt chinook.*

            List of relations
 Schema  |     Name      | Type  | Owner
---------+---------------+-------+-------
 chinook | album         | table | username
 chinook | artist        | table | username
 chinook | cars          | table | username
 chinook | color         | table | username
 chinook | commitlog     | table | username
 chinook | customer      | table | username
 chinook | employee      | table | username
 chinook | genre         | table | username
 chinook | invoice       | table | username
 chinook | invoiceline   | table | username
 chinook | mediatype     | table | username
 chinook | playlist      | table | username
 chinook | playlisttrack | table | username
 chinook | track         | table | username

PROBLEM
A brand new database results in the chinook schema and all it's tables being added, along with the 'public schema.

  1. I want to revert back to when CREATE DATABASE foo would just create the database using the public schema only. I do not want chinook schema.

  2. How did I get to this situation?

Thanks

Upvotes: 1

Views: 1694

Answers (1)

fphilipe
fphilipe

Reputation: 10056

You must have modified your template1 database. From the docs on templates:

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the “template” from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases.

You can connect to that database and drop that schema:

$ psql -c "DROP SCHEMA chinook" template1

If you now create a new database it should no longer contain this schema.

Upvotes: 1

Related Questions