Difference between revisions of "Database Schema"
Jump to navigation
Jump to search
↑ https://www.postgresql.org/docs/current/ddl-schemas.html
↑ https://www.postgresql.org/docs/current/ddl-schemas.html
↑ https://stackoverflow.com/a/10353730
↑ https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql
| Line 7: | Line 7: | ||
== Example == | == Example == | ||
* [[CREATE SCHEMA]] | * [[CREATE SCHEMA]] | ||
| − | |||
Grant examples on schemas: | Grant examples on schemas: | ||
| Line 13: | Line 12: | ||
* <code>[[GRANT USAGE ON]] [[SCHEMA]] your_schema TO [[GROUP]] your_group;</code> | * <code>[[GRANT USAGE ON]] [[SCHEMA]] your_schema TO [[GROUP]] your_group;</code> | ||
| − | |||
| − | |||
| − | |||
* Show/List [[schema]]s: | * Show/List [[schema]]s: | ||
** <code>select [[nspname]] from [[pg_catalog]].pg_namespace;</code> | ** <code>select [[nspname]] from [[pg_catalog]].pg_namespace;</code> | ||
| Line 21: | Line 17: | ||
** <code>[[\dn]]</code> | ** <code>[[\dn]]</code> | ||
** <code>[[\dn+]]</code> | ** <code>[[\dn+]]</code> | ||
| + | |||
| + | [[PostgreSQL]]: <code>[[pg_catalog]]</code> and <code>[[public]]</code>. | ||
Revision as of 09:06, 10 October 2022
wikipedia:Database schema are analogous to directories at the operating system level, except that schemas cannot be nested. [1] Typical schemas usage scenarios [2]:
- To allow many users to use one database without interfering with each other.
- To organize database objects into logical groups to make them more manageable.
- Third-party applications can be put into separate schemas so they do not collide with the names of other objects.
Example
Grant examples on schemas:
GRANT USAGE ON ALL SEQUENCES IN SCHEMA your_schema TO mygrp;[3]GRANT USAGE ON SCHEMA your_schema TO GROUP your_group;
- Show/List schemas:
select nspname from pg_catalog.pg_namespace;select schema_name from information_schema.schemata;[4]\dn\dn+
PostgreSQL: pg_catalog and public.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;
Related
- Liquibase
- PostgreSQL, Amazon Redshift
- GRANT
- Role
- PostgreSQL: catalogs
GRANT SELECT- Index, Table, Schema, Sequence, Views
See also
- PostgreSQL:
pg_dump, pg_restore,pgAdmin, pg_config, psql, pg_ctl, pg_isready, initdb, Role, Schema, DBeaver, Navicat, DataGrip, OmniDB,Adminer, docker-compose.xml PostgreSQL, PostgreSQL version, PostgreSQL logs,postgresql.conf,pg_hba.conf, $HOME/.pg_service.conf, Create database (PostgreSQL), Create user,createdb,GRANT,pg_stat, PostgreSQL VACUUM, EXPLAIN,pg stat activity, Autovacuum, ALTER DATABASE, PostgreSQL statistics collector, Shared buffers, EXPLAIN (PostgreSQL),EXPLAIN ANALYZE, Bitmap scan,EXPLAIN VERBOSE,EXPLAIN VERBOSE (PostgresSQL),WAL,ALTER USER,CREATE ROLE, CREATE USER,\du,show users,\l, The Statistics Collector, pganalyze,cron.schedule, Master,pg_tables, PostgreSQL replication,CREATE, SET, TOAST, PgBouncer, Restore DB, Index, meta-commands, Table, foreign table, Schema, Sequence, Views, materialized view, Table Partitioning, monitoring, PostgreSQL System Administration Functions, PostgreSQL extension, privileges, logging, PGTune, PostgreSQL parameter tunning, PostgreSQL modules, pgbench, PostgreSQL users
Advertising:
