User Tools

Site Tools


postgresql_basic_querys

Section: Description

Basic querys for PostgreSQL administration

Section: Objects

List table objects

SHOW INDEX FROM yourtable;
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

Table description

\d+ tablename

execute query

psql

\g

help

psql

\h
\?

Section: Main information

psql documentation

list databases

psql
\l
\l+

list schemas

psql

\dn

list current schema

show search_path;

change current schema

 set search_path=SCHEMANAME ;

list parameters

SELECT * FROM pg_settings ;

list users

SELECT * FROM pg_authid  ;
\du

list domains

\dD

pg internal tables/views

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

list tables in database

psql

\d

list Columns in table

psql

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
;

list functions

\da

list object description

\dd
\dd [pattern]

list data types

\dT

list functions

\da

Section: GRANTS

Column grants

SELECT * FROM information_schema.role_column_grants WHERE grantor NOT LIKE 'postgres' ;

Routine grants

SELECT * FROM information_schema.role_routine_grants WHERE grantor NOT LIKE 'postgres' ;

Table grants

SELECT * FROM information_schema.role_table_grants WHERE grantor NOT LIKE 'postgres' ;

Udt grants

SELECT * FROM information_schema.role_udt_grants WHERE grantor NOT LIKE 'postgres' ;

Usage grants

SELECT * FROM information_schema.role_usage_grants WHERE grantor NOT LIKE 'postgres' ;

Section: GRANTS

Sessions

SELECT datid,
    datname,
    pid,
    usename,
    client_addr,
    application_name,
    query
FROM pg_stat_activity
WHERE datname <>'postgres'
;

Section: GRANTS

Section: GRANTS

Section: GRANTS












postgresql_basic_querys.txt · Last modified: 2016/11/14 14:23 by dodger