Differences

This shows you the differences between two versions of the page.

--- postgresql:basic_postgre_sql_querys [2018/04/17 08:45] – ↷ Page moved from basic_postgre_sql_querys to postgresql:basic_postgre_sql_querys dodger
+++ postgresql:basic_postgre_sql_querys [2018/05/11 15:43] (current) – removed dodger
@@ Line 1: / Line 1: @@
-====== Section: Description ======
-Basic querys for PostgreSQL administration
-====== Section: Usage ======
-===== Change pager =====
-<code>
-export PAGER=less
-</code>
-Permanent:
-<code>
-cat >> ~/.bash_profile<<EOF
-export PAGER=less
-EOF
-</code>
-Working modes:
-<code>
-postgres=# \pset pager on;
-Pager is used for long output.
-postgres=# \pset pager off;
-Pager is used for long output.
-postgres=# \pset pager always;
-Pager is used for long output.
-postgres=# \pset pager occasionally
-Pager is used for long output.
-postgres=# \pset pager at random
-Pager is used for long output.
-\pset: extra argument "random" ignored
-</code>
-===== execute query =====
-psql
-<code>
-\g
-</code>
-==== Execute sql script ====
-<code>
-\i somedir/script2.sql
-</code>
-===== help =====
-psql
-<code>
-\h
-\?
-</code>
-====== Section: Main information ======
-===== psql documentation =====
-[[http://www.postgresql.org/docs/7.4/static/app-psql.html]]
-===== list databases =====
-<code>psql</code>
-<code>
-\l
-</code>
-<code>
-\l+
-</code>
-===== list tablespaces =====
-<code>
-\db
-</code>
-&
-<code sql>
-SELECT spcname FROM pg_tablespace;
-</code>
-===== list schemas =====
-<code>
-\dn
-</code>
-===== list parameters =====
-<code sql>
-select * from pg_settings ;
-</code>
-===== list users =====
-<code sql>
-select * from pg_authid  ;
-select * from pg_user ;
-</code>
-<code sql>
-\du
-</code>
-===== list domains =====
-<code sql>
-\dD
-</code>
-===== pg internal tables/views =====
-<code sql>
-select c.relname, np.nspname, u.usename, c.relkind
-from pg_class c,
-    pg_user u,
-    pg_namespace np
-where u.usesysid=c.relowner
-and np.oid= c.relnamespace
-and c.relkind in ('r', 'v')
-;
-</code>
-Where:
-  * r = regular table
-  * v = view
-[[http://www.postgresql.org/docs/8.0/static/catalog-pg-class.html|link]]
-====== Section: objects information ======
-===== list tables in database =====
-psql
-<code>
-\d
-</code>
-<code sql>
-SELECT c.relname, np.nspname, u.usename, c.relkind
-FROM pg_class c,
-    pg_user u,
-    pg_namespace np
-WHERE u.usesysid=c.relowner
-AND np.oid= c.relnamespace
-AND c.relkind IN ('r', 'v')
-;
-</code>
-===== list columns in table =====
-<code>
-\d+ "table_name"
-</code>
-===== list functions =====
-<code sql>
-\da
-</code>
-===== list object description =====
-<code sql>
-\dd
-</code>
-<code sql>
-\dd [pattern]
-</code>
-===== list data types =====
-<code sql>
-\dT
-</code>
-===== list functions =====
-<code sql>
-\da
-</code>
-====== Section: GRANTS ======
-[[http://www.postgresql.org/docs/current/static/sql-grant.html|GRANT SYNTAX]]
-===== Column grants =====
-<code sql>
-SELECT * FROM information_schema.role_column_grants where grantee not in ('postgres', 'PUBLIC' ) ;
-</code>
-===== Routine grants =====
-<code sql>
-SELECT * FROM information_schema.role_routine_grants where grantee not in ('postgres', 'PUBLIC' ) ;
-</code>
-===== Table grants =====
-<code sql>
-SELECT * FROM information_schema.role_table_grants where grantee not in ('postgres', 'PUBLIC' ) ;
-</code>
-<code sql>
-select * from (select grantee, table_schema, table_name, string_agg( privilege_type, ', ' ) from information_schema.role_table_grants WHERE grantee NOT in ( 'postgres', 'PUBLIC' ) group by grantee, table_catalog, table_schema, table_name) t order by table_schema, grantee ;
-</code>
-===== Udt grants =====
-<code sql>
-SELECT * FROM information_schema.role_udt_grants where grantee not in ('postgres', 'PUBLIC' ) ;
-</code>
-===== Usage grants =====
-<code sql>
-SELECT * FROM information_schema.role_usage_grants where grantee not in ('postgres', 'PUBLIC' ) ;
-</code>
-====== Section: Sesiones ======
-===== Listar sesiones =====
-<code sql>SELECT pid,
-        usename,
---         application_name,
-        client_addr,
-        backend_start,
---         xact_start,
-        now()-query_start Query_duration,
---         state_change,
-        waiting,
-        state,
-        query
-from pg_stat_activity;
-</code>
-  * [[http://www.chrismiles.info/systemsadmin/databases/articles/viewing-current-postgresql-queries/|Link]]
-====== Section: GRANTS ======
-====== Section: GRANTS ======
-====== Section: GRANTS ======
-<code sql>
-</code>
-<code>
-</code>
-<code>
-</code>
-<code>
-</code>
-<code>
-</code>
-<code>
-</code>
-<code>
-</code>
-<code>
-</code>
-<code>
-</code>
-<code>
-</code>