PostgreSQL mini cookbook: Dealing with the system tables
PostgreSQL mini cookbook: Dealing with the system tables, those tricks back to 2001 and still works today. 😉
Seeing what tables exist
You want to check if a table already exists.
Solution
There is a view called pg_tables or the system table pg_class - you can query either. There is less information in pg_tables but it is easier to understand.
List all the tables, similar to \dt:
SELECT * FROM pg_tables
WHERE schemaname!='pg_catalog'
AND schemaname!='information_schema';
SELECT * FROM pg_tables WHERE tablename LIKE 'foo%';
SELECT * FROM pg_class WHERE relname LIKE 'foo%';
Example: view all user defined tables:
=# SELECT * FROM pg_tables
WHERE schemaname!='pg_catalog'
AND schemaname!='information_schema';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+---------------+------------+------------+------------+----------+-------------
public | film_category | foo | | t | f | t
public | staff | foo | | t | f | t
public | country | foo | | t | f | t
public | inventory | foo | | t | f | t
public | rental | foo | | t | f | t
public | store | foo | | t | f | t
public | city | foo | | t | f | t
public | actor | foo | | t | f | t
public | payment | foo | | t | f | t
public | address | foo | | t | f | t
public | language | foo | | t | f | t
public | film_actor | foo | | t | f | t
public | category | foo | | t | f | t
public | film | foo | | t | f | t
public | customer | foo | | t | f | t
(15 rows)
Example: view all pg_catalog tables:
=# SELECT * FROM pg_tables
-# WHERE schemaname='pg_catalog';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-------------------------+------------+------------+------------+----------+-------------
pg_catalog | pg_statistic | foo | | t | f | f
pg_catalog | pg_type | foo | | t | f | f
pg_catalog | pg_authid | foo | pg_global | t | f | f
pg_catalog | pg_attribute | foo | | t | f | f
pg_catalog | pg_proc | foo | | t | f | f
pg_catalog | pg_user_mapping | foo | | t | f | f
pg_catalog | pg_attrdef | foo | | t | f | f
pg_catalog | pg_constraint | foo | | t | f | f
pg_catalog | pg_index | foo | | t | f | f
pg_catalog | pg_operator | foo | | t | f | f
pg_catalog | pg_opfamily | foo | | t | f | f
pg_catalog | pg_opclass | foo | | t | f | f
pg_catalog | pg_am | foo | | t | f | f
pg_catalog | pg_amop | foo | | t | f | f
pg_catalog | pg_amproc | foo | | t | f | f
pg_catalog | pg_largeobject_metadata | foo | | t | f | f
pg_catalog | pg_aggregate | foo | | t | f | f
pg_catalog | pg_rewrite | foo | | t | f | f
pg_catalog | pg_trigger | foo | | t | f | f
pg_catalog | pg_event_trigger | foo | | t | f | f
pg_catalog | pg_description | foo | | t | f | f
pg_catalog | pg_cast | foo | | t | f | f
pg_catalog | pg_enum | foo | | t | f | f
pg_catalog | pg_namespace | foo | | t | f | f
pg_catalog | pg_conversion | foo | | t | f | f
pg_catalog | pg_depend | foo | | t | f | f
pg_catalog | pg_database | foo | pg_global | t | f | f
pg_catalog | pg_db_role_setting | foo | pg_global | t | f | f
pg_catalog | pg_tablespace | foo | pg_global | t | f | f
pg_catalog | pg_pltemplate | foo | pg_global | t | f | f
pg_catalog | pg_auth_members | foo | pg_global | t | f | f
pg_catalog | pg_shdepend | foo | pg_global | t | f | f
pg_catalog | pg_shdescription | foo | pg_global | t | f | f
pg_catalog | pg_ts_config | foo | | t | f | f
pg_catalog | pg_ts_config_map | foo | | t | f | f
pg_catalog | pg_ts_dict | foo | | t | f | f
pg_catalog | pg_ts_parser | foo | | t | f | f
pg_catalog | pg_ts_template | foo | | t | f | f
pg_catalog | pg_extension | foo | | t | f | f
pg_catalog | pg_foreign_data_wrapper | foo | | t | f | f
pg_catalog | pg_foreign_server | foo | | t | f | f
pg_catalog | pg_foreign_table | foo | | t | f | f
pg_catalog | pg_default_acl | foo | | t | f | f
pg_catalog | pg_seclabel | foo | | t | f | f
pg_catalog | pg_shseclabel | foo | pg_global | t | f | f
pg_catalog | pg_collation | foo | | t | f | f
pg_catalog | pg_range | foo | | t | f | f
pg_catalog | pg_largeobject | foo | | t | f | f
pg_catalog | pg_class | foo | | t | f | f
pg_catalog | pg_inherits | foo | | t | f | f
pg_catalog | pg_language | foo | | t | f | f
(51 rows)
Example: view all information_schema tables:
=# SELECT * FROM pg_tables
-# WHERE schemaname='information_schema';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
--------------------+-------------------------+------------+------------+------------+----------+-------------
information_schema | sql_implementation_info | foo | | f | f | f
information_schema | sql_languages | foo | | f | f | f
information_schema | sql_packages | foo | | f | f | f
information_schema | sql_sizing | foo | | f | f | f
information_schema | sql_sizing_profiles | foo | | f | f | f
information_schema | sql_features | foo | | f | f | f
information_schema | sql_parts | foo | | f | f | f
(7 rows)
Viewing triggers and their functions
You want a list of triggers and the functions they call on certain tables.
Solution
To see the triggers and functions on tables foo_xxx use:
SELECT cl.relname,
tr.tgname AS triggername,
tr.tgenabled,
fn.proname AS func_name
FROM pg_trigger AS tr,
pg_class AS cl,
pg_proc AS fn
WHERE tr.tgrelid=cl.oid
AND tr.tgfoid=fn.oid
AND cl.relname ˜ '^foo%';
Example:
=# SELECT cl.relname,
-# tr.tgname AS triggername,
-# tr.tgenabled,
-# fn.proname AS func_name
-# FROM pg_trigger AS tr,
-# pg_class AS cl,
-# pg_proc AS fn
-# WHERE tr.tgrelid=cl.oid
-# AND tr.tgfoid=fn.oid;
relname | triggername | tgenabled | func_name
---------------+------------------------------+-----------+-------------------------
film | film_fulltext_trigger | O | tsvector_update_trigger
address | last_updated | O | last_updated
store | last_updated | O | last_updated
address | RI_ConstraintTrigger_a_72626 | O | RI_FKey_restrict_del
address | RI_ConstraintTrigger_a_72627 | O | RI_FKey_cascade_upd
customer | RI_ConstraintTrigger_c_72628 | O | RI_FKey_check_ins
...
Viewing function bodies
You want to see function definition: the source for a previously defined function.
Solution
You can use either of the following:
\df+ myfunc
or
SELECT oid, proname, prosrc
FROM pg_proc
WHERE proname='myfunc';
Example:
=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+----------------------------+------------------+---------------------------------------------------------------------+---------
public | _group_concat | text | text, text | normal
public | film_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal
public | film_not_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal
public | get_customer_balance | numeric | p_customer_id integer, p_effective_date timestamp without time zone | normal
public | group_concat | text | text | agg
public | inventory_held_by_customer | integer | p_inventory_id integer | normal
public | inventory_in_stock | boolean | p_inventory_id integer | normal
public | last_day | date | timestamp without time zone | normal
public | last_updated | trigger | | trigger
public | rewards_report | SETOF customer | min_monthly_purchases integer, min_dollar_amount_purchased numeric | normal
(10 rows)
=# \df film_in_stock
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+---------------+------------------+-----------------------------------------------------------------+--------
public | film_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal
(1 row)
=# \df+ film_in_stock
List of functions
Schema | Name | Result data type | Argument data types | Type | Security | Volatility | Owner | Language | Source code | Description
--------+---------------+------------------+-----------------------------------------------------------------+--------+----------+------------+-------+----------+--------------------------------------------+-------------
public | film_in_stock | SETOF integer | p_film_id integer, p_store_id integer, OUT p_film_count integer | normal | invoker | volatile | ming | sql | +|
| | | | | | | | | SELECT inventory_id +|
| | | | | | | | | FROM inventory +|
| | | | | | | | | WHERE film_id = $1 +|
| | | | | | | | | AND store_id = $2 +|
| | | | | | | | | AND inventory_in_stock(inventory_id);+|
| | | | | | | | | |
(1 row)
Example: Use pg_proc table to view function definition:
=# SELECT oid, proname, prosrc
-= FROM pg_proc
-= WHERE proname='film_in_stock';
oid | proname | prosrc
-------+---------------+--------------------------------------------
72408 | film_in_stock | +
| | SELECT inventory_id +
| | FROM inventory +
| | WHERE film_id = $1 +
| | AND store_id = $2 +
| | AND inventory_in_stock(inventory_id);+
| |
(1 row)
Viewing rules
You want to see what rules you have defined for a particular view/table.
Solution
Use the pg_rules system table.
SELECT * FROM pg_rules WHERE tablename='foo';
Example:
=# SELECT * FROM pg_rules;
schemaname | tablename | rulename | definition
------------+-------------+---------------+--------------------------------------------------------------
pg_catalog | pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS
| | | ON UPDATE TO pg_catalog.pg_settings DO INSTEAD NOTHING;
pg_catalog | pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS
| | | ON UPDATE TO pg_catalog.pg_settings
| | | WHERE (new.name = old.name)
| | | DO SELECT set_config(old.name, new.setting, false)
| | | AS set_config;
Viewing indexes
You want to see what indexes you have defined for a particular view/table.
Solution
Use the pg_indexes system table.
The view pg_indexes provides access to useful information about each index in the database.
pg_indexes Columns:
| Name | Type | References | Description |
|---|---|---|---|
| schemaname | name | pg_namespace.nspname | Name of schema containing table and index |
| tablename | name | pg_class.relname | Name of table the index is for |
| indexname | name | pg_class.relname | Name of index |
| tablespace | name | pg_tablespace.spcname | Name of tablespace containing index (null if default for database) |
| indexdef | text | Index definition (a reconstructed CREATE INDEX command) |
Example: List all the indexes and its definition:
=# SELECT tablename,
-# indexname,
-# indexdef AS definition
-# FROM pg_indexes
-# WHERE
-# schemaname = 'public'
-# ORDER BY
-# tablename,
-# indexname;
tablename | indexname | definition
-------------+---------------------+---------------------------------------------------------------------------------------------------
actor | actor_pkey | CREATE UNIQUE INDEX actor_pkey ON public.actor USING btree (actor_id)
actor | idx_actor_last_name | CREATE INDEX idx_actor_last_name ON public.actor USING btree (last_name)
address | address_pkey | CREATE UNIQUE INDEX address_pkey ON public.address USING btree (address_id)
address | idx_fk_city_id | CREATE INDEX idx_fk_city_id ON public.address USING btree (city_id)
category | category_pkey | CREATE UNIQUE INDEX category_pkey ON public.category USING btree (category_id)
city | city_pkey | CREATE UNIQUE INDEX city_pkey ON public.citUSING btree (city_id)
You can also use \d <tablename>:
=# \d actor
Table "public.actor"
Column | Type | Modifiers
-------------+-----------------------------+----------------------------------------------------------
actor_id | integer | not null default nextval('actor_actor_id_seq'::regclass)
first_name | character varying(45) | not null
last_name | character varying(45) | not null
last_update | timestamp without time zone | not null default now()
Indexes:
"actor_pkey" PRIMARY KEY, btree (actor_id)
"idx_actor_last_name" btree (last_name)
Referenced by:
TABLE "film_actor" CONSTRAINT "film_actor_actor_id_fkey" FOREIGN KEY (actor_id) REFERENCES actor(actor_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
last_updated BEFORE UPDATE ON actor FOR EACH ROW EXECUTE PROCEDURE last_updated()
References
OmniLock - Block / Hide App on iOS
Block distractive apps from appearing on the Home Screen and App Library, enhance your focus and reduce screen time.
DNS Firewall for iOS and Mac OS
Encrypted your DNS to protect your privacy and firewall to block phishing, malicious domains, block ads in all browsers and apps
Ad