This is the first day of my participation in the Gwen Challenge in November. Check out the details: the last Gwen Challenge in 2021
The following queries need to locate specific objects and conditions are required. Tables, views, and indexes need to be executed in the corresponding database.
Query all databases
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
chis | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
chisapp | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.UTF- 8 - | en_US.UTF- 8 - |
syd | postgres | UTF8 | en_US.UTF- 8 - | en_US.UTF- 8 - |
template0 | postgres | UTF8 | en_US.UTF- 8 - | en_US.UTF- 8 - | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF- 8 - | en_US.UTF- 8 - | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF- 8 - | en_US.UTF- 8 - |
(7 rows)
Copy the codeExample Query all user roles
SELECT rolname,
CASE rolcanlogin WHEN 'true' THEN 'user' WHEN 'false' THEN 'role' END AS TYPE
FROM pg_roles
WHERE rolname !~ '^pg_'
ORDER BY 1;
rolname | type
------------------------+------
admin | user
all_read | role
anay_app | user
anay_app_group | role
anay_owner | user
anay_read | user
anay_read_group | role
apply | user
apply_app | user
apply_app_group | role
apply_owner | user
apply_read | user
Copy the codeQuery all schemas in the database
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
Name | Owner
-------------+-------------------
apply | apply_owner
bis | bis_owner
cams | cams_owner
chisbak | postgres
comm | comm_owner
cvms | cvms_owner
ecg | ecg_owner
emis | emis_owner
emr | emr_owner
event | event_owner
ext | ext_owner
finance |Finance_owner........................... ........................... ...........................Copy the codeView all tables of all schemas in the database
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r'.'p'.' ')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
ORDER BY 1.2;
Schema | Name | Type | Owner
----------+-------------------------------------------------+-------------------+----------------
apply | apply_a | table | apply_owner
apply | apply_b | table | apply_owner
apply | apply_c | table | apply_owner
bis | bis_a | table | bis_owner
bis | bis_b | table | bis_owner
bis | bis_c | table |Bis_owner........................... ........................... ...........................Copy the codeView all views of all schemas in the database
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v'.'m')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
ORDER BY 1.2;
Schema | Name | Type | Owner
----------+------------------------------------------------+-------------------+----------------
hrms | hr_view_a | view | hrms_owner
hrms | hr_view_b | view | hrms_owner
lis | lis_v_a | view | lis_owner
lis | lis_v_b | view | lis_owner
lis | lis_v_c | view | lis_owner
mr | v_mr_a | view | mr_owner
mr | v_mr_b | view | mr_owner
mr | v_mr_c | view | mr_owner
v | v_a | view |V_owner........................... ........................... ...........................Copy the codeView all indexes in the database
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
c2.relname as "Table"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i'.'I'.' ')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
ORDER BY 1.2;
Schema | Name | Type | Owner | Table
--------+------------------------------------------+------------+----------------+-----------------------------------
apply | apply_pk1 | index | apply_owner | apply_1
apply | apply_pk2 | index | apply_owner | apply_2
apply | apply_pk3 | index | apply_owner | apply_3
bis | apply_idx1 | index | bis_owner | apply_a
bis | apply_idx2 | index | bis_owner | apply_b
bis | apply_idx3 | index | bis_owner |Apply_c........................... ........................... ...........................Copy the code