crosindependent.blogg.se - Postgresql cheat sheet

#Postgresql cheat sheet how to#
#Postgresql cheat sheet password#

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

#Postgresql cheat sheet how to#

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_.

#Postgresql cheat sheet password#

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):