In PostgreSQL the system determines which table is meant by following a search path, which is a list of schemas to look in.
The first matching table in the search path is taken to be the one wanted, otherwise, if there is no match a error is raised, even if matching table names exist in other schemas in the database.
To show the current search path you can use the following command:
SHOW search_path;
And to put the new schema in the path, you could use:
SET search_path TO myschema;
Or if you want multiple schemas:
SET search_path TO myschema, public;
Reference: https://www.postgresql.org/docs/current/static/ddl-schemas.html
Answer from Ciro Pedrini on Stack OverflowIn PostgreSQL the system determines which table is meant by following a search path, which is a list of schemas to look in.
The first matching table in the search path is taken to be the one wanted, otherwise, if there is no match a error is raised, even if matching table names exist in other schemas in the database.
To show the current search path you can use the following command:
SHOW search_path;
And to put the new schema in the path, you could use:
SET search_path TO myschema;
Or if you want multiple schemas:
SET search_path TO myschema, public;
Reference: https://www.postgresql.org/docs/current/static/ddl-schemas.html
\l - Display database
\c - Connect to database
\dn - List schemas
\dt - List tables inside public schemas
\dt schema1.* - List tables inside a particular schema.
For example: 'schema1'.
Videos
I've been using PostgreSQL for over 15 years and yet I never fully understood the term schema or how namespaces are used. This causes some confusion when reading docs.
I understand the general concept of namespaces but first of all is schema just a word for namespace?
Can existing databases be configured with namespaces, can they use multiple namespaces?
Is this valuable for security if you can configure users to have access to the databases in a certain namespace without giving them access to databases?
Hi PostgreSQL subreddit,
I'm curious how folks are leveraging schemas, and preferably an at-scale use case/example.
I my several years, and handful of companies, of software engineering, I've only had to work with a simple public schema, and it has worked fine, but I'm curious how others are using schemas, if at all.
Thanks in advance!
To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA
select schema_name
from information_schema.schemata;
More details in the manual
alternatively:
select nspname
from pg_catalog.pg_namespace;
More details about pg_catalog in the manual
When using the psql command line, you may list all schema with command \dn.
What you're seeing is mostly one user interface's way of displaying the structure of a PostgreSQL database.
If you were using pgAdminIII, which is just another administrative interface, each database would usually show two "catalogs": information_schema and pg_catalog. It would also have a schema named "public".
Database objects named "pg_*" are system objects. The pg_toast schema holds TOAST storage for large tables.
The information_schema views are part of the SQL standard. They're supposed to provide a vendor-independent way to determining information that's stored in vendor-specific system tables.
You can make a good case for not exposing every namespace as if it were a user-level schema. (Internally, these are namespaces. Try select * from pg_namespace;.) The only schema in a newly created database that really matters to users is "public".
If you keep the default search_path and use the default schema public (automatically), you don't have to bother with schemas at all.
But if your database grows, chances are you will happily make use of them to organize objects and privileges. By setting the search_path per user / database / session / ... you can manage schemas precedence any way you want. I have assembled a couple of ways to do this in this related answer on SO.
Schemas are much like directories in the file system:
If you don't schema-qualify an object name (myschema.mytable), the first schema in your search_path is assumed when creating objects. That's like the "current directory" in the file system.
All schemas in the search_path are searched in sequence when using objects. Just like with a search path in the file system.
But:
There is no "root schema", and no nesting. You can't store anything "without schema". Every object is created inside a particular schema. (Except special objects on the database-cluster level, like roles and databases.)
The current role needs privileges to access a schema. Schemas are extremely useful if you want to organize things and still be able to access other "areas" in a simple query.
MySQL does not have anything similar. You would use a separate database for many things solved with a separate schema in PostgreSQL.