This page in other versions: 9.0 / 9.1 / 9.2 / 9.3 / 9.4  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3 / 8.4

Chapter 3. System Catalogs

Table of Contents
3.1. Overview
3.2. pg_aggregate
3.3. pg_am
3.4. pg_amop
3.5. pg_amproc
3.6. pg_attrdef
3.7. pg_attribute
3.8. pg_cast
3.9. pg_class
3.10. pg_constraint
3.11. pg_conversion
3.12. pg_database
3.13. pg_depend
3.14. pg_description
3.15. pg_group
3.16. pg_index
3.17. pg_inherits
3.18. pg_language
3.19. pg_largeobject
3.20. pg_listener
3.21. pg_namespace
3.22. pg_opclass
3.23. pg_operator
3.24. pg_proc
3.25. pg_rewrite
3.26. pg_shadow
3.27. pg_statistic
3.28. pg_trigger
3.29. pg_type

3.1. Overview

The system catalogs are the place where a relational database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL's system catalogs are regular tables. You can drop and recreate the tables, add columns, insert and update values, and severely mess up your system that way. Normally one should not change the system catalogs by hand, there are always SQL commands to do that. (For example, CREATE DATABASE inserts a row into the pg_database catalog -- and actually creates the database on disk.) There are some exceptions for especially esoteric operations, such as adding index access methods.

Most system catalogs are copied from the template database during database creation, and are thereafter database-specific. A few catalogs are physically shared across all databases in an installation; these are marked in the descriptions of the individual catalogs.

Table 3-1. System Catalogs

Catalog Name Purpose
pg_aggregate aggregate functions
pg_am index access methods
pg_amop access method operators
pg_amproc access method support procedures
pg_attrdef column default values
pg_attribute table columns ("attributes", "fields")
pg_cast casts (data type conversions)
pg_class tables, indexes, sequences ("relations")
pg_constraint check constraints, unique / primary key constraints, foreign key constraints
pg_conversion encoding conversion information
pg_database databases within this database cluster
pg_depend dependencies between database objects
pg_description descriptions or comments on database objects
pg_group groups of database users
pg_index additional index information
pg_inherits table inheritance hierarchy
pg_language languages for writing functions
pg_largeobject large objects
pg_listener asynchronous notification
pg_namespace namespaces (schemas)
pg_opclass index access method operator classes
pg_operator operators
pg_proc functions and procedures
pg_rewrite query rewriter rules
pg_shadow database users
pg_statistic optimizer statistics
pg_trigger triggers
pg_type data types

More detailed documentation of each catalog follows below.

Comments


Dec. 17, 2002, 9:27 p.m.

Here is s simple solution for finding the size of varchar's type column:

SELECT pg_class.relname, pg_attribute.attname, pg_type.typname,
pg_attribute.atttypmod-4
FROM pg_class, pg_attribute, pg_type
WHERE pg_attribute.attrelid = pg_class.oid
AND pg_attribute.atttypid = pg_type.oid
AND pg_class.relname = 'TABLE_NAME'
AND pg_attribute.attname = 'COLUMN_NAME'

Result:
.relname...|...attname...|.typname.|.atttypmod.
-----------+-------------+---------+-----------
TABLE_NAME | COLUMN_NAME | varchar | SIZE
(1 row)

Remeber that you have to decrease the value of the filed pg_attribute.atttypmod by 4, because all variable-length types begin with a length field of exactly 4 bytes.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group