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".
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.
I think you need to relogin for that. With ALTER USER ... SET you change
Session defaults for run-time configuration variables
Also from ALTER ROLE SET manual:
Role-specific variable settings take effect only at login;
But don't apply changes to current session. If you want immediate change use:
SET search_path TO bla;
It will change path on session level
In PostGres the exact command would be
ALTER USER your-user set SEARCH_PATH = 'schema_name';
I have tried to create table in pgAdmin4 in a certain designated schema.
However I always come across the following error
ERROR: no schema has been selected to create in
LINE 4: CREATE TABLE concept (
^which is very annoying.
Can I set a default schema where I do not have to alter all the SQL commands?
I am copy-pasta-ing a bunch of SQL to set up a database, I just do no want to change stuff so often.
I tried
set search_path='eat_my_default_schema';
it just does not work well
Thanks!
SET search_path TO myschema, morepaths , $supports_also_so_variables.
If i am making db schema and tables for app that can have multiple installations i usually create scripts for ; set search path... ; create tablee xxx style ; just in case i need to deploy same app twice into same db and have own namespace for them.
set search_path is set for session, so if you have strange problems when using some app, then app kills session after every query
I'm as annoyed that people don't seem to understand the question/problem, as I myself have this question, like:
Where in pgAdmin4 do I set session settings/variables like search_path for all the queries?
No, I don't want (in these case(s) ) include it in the query as I want to copy-paste use elsewhere where the schema names a re a tad different
No, I don't want it in the top of the query, as it messes with things explain, as I want it to be set every time I reconnect for this server during these tests.