[DOC] PostgreSQL basic queries

Section: Description

Basic querys for PostgreSQL administration

Section: Usage

export PAGER=less

Permanent:

cat >> ~/.bash_profile<<EOF
export PAGER=less
EOF

Working modes:

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

psql

\g

Execute sql script

\i somedir/script2.sql

psql

\h
\?
\SET autocommit off

Section: Objects

SHOW INDEX FROM yourtable;
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';
\d+ tablename
psql
\l
\l+
SELECT datname FROM pg_database;
\db

&

SELECT spcname FROM pg_tablespace;

psql

\dn
show search_path;
 set search_path=SCHEMANAME ;
SELECT * FROM pg_settings ;
SELECT * FROM pg_authid  ;
\du
\dD
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')
ORDER BY np.nspname, c.relname
;

Where:

  • r = regular table
  • v = view

link

Section: objects information

\d
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')
;
\d+ "table_name"
SELECT table_catalog,
    table_schema,
    TABLE_NAME,
    column_name,
    ordinal_position,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable
FROM information_schema.columns
WHERE TABLE_NAME   = 'table_name'
ORDER BY ordinal_position
;
\da
\dd
\dd [pattern]
\dT

Section: GRANTS

SELECT * FROM information_schema.role_column_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;
SELECT * FROM information_schema.role_routine_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;
SELECT * FROM information_schema.role_table_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;
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 ;
SELECT * FROM information_schema.role_udt_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;
SELECT * FROM information_schema.role_usage_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

Section: DBA

SELECT datid,
    datname,
    pid,
    usename,
    client_addr,
    application_name,
    query
FROM pg_stat_activity
WHERE datname <>'postgres'
;
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;
ALTER USER "user_name" WITH PASSWORD 'new_password';

Section: GRANTS

Section: GRANTS

Section: GRANTS












  • postgresql/postgresql_basic_querys.txt
  • Last modified: 2018/05/11 17:44
  • by dodger