PostgreSQL Databases, Schemas and search_path

Foundations / Guide 4 of 47 / Beginner

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• TimeAbout 14 min

From PostgreSQL 15 the public schema is no longer writable by every user by default, which is a security improvement and another reason new tables end up somewhere unexpected.

• PackagingDebian cluster layout

This guide includes

Use this when \dt says the database has no tables and you know that it does. This matters because the search path decides what you can see without qualifying it - a table in a schema outside that path is invisible rather than missing.

• creating a database, and finding where you land inside it
• reading the search path
• creating a schema, and a table inside it
• watching the table disappear from \dt, and understanding exactly why

Before you start

• A role with CREATEDB.

1. 1

   Create a database and see where you land

   createdb is a shell wrapper around CREATE DATABASE, usable because the role has CREATEDB.

   Connecting to it, current_database() is appdb and current_schema() is public. A database contains schemas, and schemas contain tables - two levels of namespace where MySQL has one. MySQL's "database" is much closer to PostgreSQL's *schema* than to its database, which is why the two vocabularies trip people up in both directions.

   $createdb appdb ; echo "exit=$?"exit=0$psql -d appdb -c "SELECT current_database(), current_schema(), version()" current_database | current_schema |                                                             version------------------+----------------+--------------------------------------------------------------------------------------------------------------------------------- appdb            | public         | PostgreSQL 18.6 (Ubuntu 18.6-0ubuntu0.26.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit(1 row)

   Expected resultexit=0, then appdb and public.

   Success conditionYou have a database and know which schema you are in.

2. 2

   Read the search path

   "$user", public.

   Two entries. $user expands to a schema named after the connected role - which does not exist here, and is silently skipped. Then public, which does.

   The search path decides where an unqualified name is looked up and where a CREATE TABLE without a schema puts the table. It is the reason the next step behaves as it does.

   $psql -d appdb -c "SHOW search_path"   search_path----------------- "$user", public(1 row)

   Expected result"$user", public.

   Success conditionYou know which schemas are consulted for unqualified names.

3. 3

   Create a schema and a table in it

   A schema for a subject area - sales - owned by the role that will use it.

   \dn lists both schemas. Note public is owned by pg_database_owner, a role that exists to make ownership follow whoever owns the database.

   The table is created fully qualified as sales.regions, so it lands in sales rather than wherever the search path points. serial is PostgreSQL's auto-incrementing integer, and text is a first-class type with no length penalty - both are idiomatic here in a way they are not in MySQL.

   $psql -d appdb -c "CREATE SCHEMA sales AUTHORIZATION sysadmin"CREATE SCHEMA$psql -d appdb -c "\dn"      List of schemas  Name  |       Owner--------+------------------- public | pg_database_owner sales  | sysadmin(2 rows)$psql -d appdb -c "CREATE TABLE sales.regions (id serial PRIMARY KEY, name text NOT NULL)"CREATE TABLE

   Expected resultCREATE SCHEMA, two schemas listed, then CREATE TABLE.

   Success conditionYou have a table in a schema of its own.

4. 4

   Watch the table disappear

   \dt returns "Did not find any tables".

   The table exists. It was created successfully a second ago. But \dt lists tables on the search path, and the path is "$user", public - sales is on neither.

   \dt sales.* finds it immediately, owner and all.

   This is the most common confusion in PostgreSQL and it produces a genuinely alarming message. Nothing is missing; you are looking in the wrong namespace. The fixes are to qualify the name, or to put the schema on the path with SET search_path TO sales, public for the session or ALTER ROLE ... SET search_path permanently.

   $psql -d appdb -c "\dt"Did not find any tables.$psql -d appdb -c "\dt sales.*"           List of tables Schema |  Name   | Type  |  Owner--------+---------+-------+---------- sales  | regions | table | sysadmin(1 row)

   Expected resultDid not find any tables, then the same table listed under sales.

   Success conditionYou can diagnose a table that appears to be missing.

Troubleshooting

• ERROR: relation "x" does not exist on a table you just created.

  Why: It is in a schema not on the search path.

  Fix:SELECT schemaname, tablename FROM pg_tables WHERE tablename = 'x' finds it regardless of path.

• Tables land in public when you wanted them elsewhere.

  Why: Unqualified CREATE TABLE uses the first writable schema on the path.

  Fix:Qualify the name, or set the search path for the role or the session.

• permission denied for schema public on PostgreSQL 15+.

  Why: public is no longer writable by all roles by default.

  Fix:Grant explicitly, or give each role its own schema - which is what "$user" on the search path was designed for.

Official sources

• PostgreSQL 18 Documentation - Server Setup and Operation
• PostgreSQL 18 Documentation - Client Authentication (pg_hba.conf)
• PostgreSQL 18 Documentation - psql
• PostgreSQL 18 Documentation - Schemas and the Search Path
• Debian PostgreSQL common tools - pg_lsclusters