PG_ATTRIBUTE¶
PG_ATTRIBUTE records information about table columns.
Name | Type | Description |
|---|---|---|
attrelid | oid | Table to which the column belongs |
attname | name | Column name |
atttypid | oid | Column type |
attstattarget | integer | Controls the level of details of statistics collected for this column by ANALYZE.
For scalar data types, attstattarget is both the target number of "most common values" to collect, and the target number of histogram bins to create. |
attlen | smallint | Copy of pg_type.typlen of the column's type |
attnum | smallint | Number of a column. |
attndims | integer | Number of dimensions if the column is an array; otherwise, the value is 0. |
attcacheoff | integer | This column is always -1 on disk. When it is loaded into a row descriptor in the memory, it may be updated to cache the offset of the columns in the row. |
atttypmod | integer | Type-specific data supplied at table creation time (for example, the maximum length of a varchar column). This column is used as the third parameter when passing to type-specific input functions and length coercion functions. The value will generally be -1 for types that do not need ATTTYPMOD. |
attbyval | boolean | Copy of pg_type.typbyval of the column's type |
attstorage | "char" | Copy of pg_type.typstorage of this column's type |
attalign | "char" | Copy of pg_type.typalign of the column's type |
attnotnull | boolean | A not-null constraint. It is possible to change this column to enable or disable the constraint. |
atthasdef | boolean | Indicates that this column has a default value, in which case there will be a corresponding entry in the pg_attrdef table that actually defines the value. |
attisdropped | boolean | Whether the column has been dropped and is no longer valid. A dropped column is still physically present in the table but is ignored by the analyzer, so it cannot be accessed through SQL. |
attislocal | boolean | Whether the column is defined locally in the relation. Note that a column can be locally defined and inherited simultaneously. |
attcmprmode | tinyint | Compressed modes for a specific column The compressed mode includes:
|
attinhcount | integer | Number of direct ancestors this column has. A column with an ancestor cannot be dropped nor renamed. |
attcollation | oid | Defined collation of a column |
attacl | aclitem[] | Permissions for column-level access |
attoptions | text[] | Property-level options |
attfdwoptions | text[] | Property-level external data options |
attinitdefval | bytea | attinitdefval stores the default value expression. ADD COLUMN in a row-store table must use this column. |
attkvtype | tinyint | kv_type attribute of a column. Values:
|
Example¶
Query the field names and field IDs of a specified table. Replace t1 and public with the actual table name and schema name, respectively.
SELECT attname,attnum FROM pg_attribute WHERE attrelid=(SELECT pg_class.oid FROM pg_class JOIN pg_namespace ON relnamespace=pg_namespace.oid WHERE relname='t1' and nspname='public') and attnum>0;
attname | attnum
------------------+--------
product_id | 1
product_name | 2
product_quantity | 3
(3 rows)