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
Feedback
Was this page helpful?
Glad to hear it!
Sorry to hear that.