Optionally, CREATE SCHEMA can include subcommands to create objects within the new schema. A CREATE command specifying an unqualified object name creates the object in the current schema (the one at the front of the search path, which can be determined with the function current_schema). Named objects are accessed either by “ qualifying” their names with the schema name as a prefix, or by setting a search path that includes the desired schema(s). The schema name must be distinct from the name of any existing schema in the current database.Ī schema is essentially a namespace: it contains named objects (tables, data types, functions, and operators) whose names can duplicate those of other objects existing in other schemas. Check permissions in a table FROM information_schema.CREATE SCHEMA enters a new schema into the current database. JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) List all assigned roles for the users r.rolname, WHERE grantee = 'user_to_check' ORDER BY table_name List all grants for an specific user SELECT table_catalog, table_schema, table_name, privilege_type psql -c "\l+" -H -q postgres > out.html: Generate a html report of your databases (source: Daniel Westermann).nf: Annotations of all 269 nf settings for PostgreSQL 10.Quite interesting if you need to tune-up a postgres setup. A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more.PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way.100 psql Tips: Name says all, lots of useful tips!.Postgres Weekly newsletter: The best way IMHO to keep up to date with PG news.To obtain the CREATE TABLE query of a table, any visual GUI like pgAdmin allows to easily, but else you can use pg_dump, e.g.: pg_dump -t '.' -schema-only ( source).Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted): ANALYZE VERBOSE.File to save PostgreSQL credentials and permissions (format: hostname:port:database:username:password): chmod 600 ~/.pgpass.Show IP of the DB Instance: SELECT inet_server_addr().$ echo "bind "^R " em-inc-search-prev " > $HOME/.editrc Get all indexes from all tables of a schema:.SELECT * FROM pg_indexes WHERE tablename='_table_name_' AND schemaname='_schema_name_' : Show table indexes.show statement_timeout : Show current user's statement timeout.SELECT pg_size_pretty(pg_database_size('_database_name_')) : Show DB space in use.SELECT pg_size_pretty(pg_total_relation_size('_table_name_')) : Show DB table space in use.SELECT * FROM pg_views WHERE viewname='_viewname_' : List view (including the definition).SELECT * FROM pg_proc WHERE proname='_procedurename_': List procedure/function.Parameters differ but can be checked at the official documentation. There are many CREATE choices, like CREATE DATABASE _database_name_, CREATE TABLE _table_name_. Show available extensions: SELECT * FROM pg_available_extension_versions How to add user who executed a PG statement to log (editing nf):Ĭheck Extensions enabled in postgres: SELECT * FROM pg_extension.Now you will get tons of details of every statement, error, and even background tasks like VACUUMs.Changing verbosity & querying Postgres log:ġ) First edit the config file, set a decent verbosity, save and restart postgres:.grant _test2_ to _test1_ : Allow _test1_ to set its role as _test2_.set role _test_ : Change role for current session to _test_.create role _test2_ noinherit login password _passsword_ : Create a role with username and password.create role _test1_: Create a role with an existing username.\du _username_: List a username if present. ![]() \! _bash_command_: execute _bash_command_ (e.g.\copy (SELECT * FROM _table_name_) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV.\x: Pretty-format query results instead of the not-so-useful ASCII tables.\df+ _function_ : Show function SQL code.\dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones).\d+ _table_: More detailed table definition including description and physical disk size.\d _table_: Show table definition (columns, etc.) including triggers.\?: Show help (list of available commands with an explanation).Most \d commands support additional param of _schema_.name_ and accept wildcards like *.* -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS).-E: will describe the underlaying queries of the \ commands (cool for learning!).Some interesting flags (to see all, use -h or -help depending on your psql version):
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |