• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. SQL Reference
  5. Data Types
  6. Object Identifier Types

Object Identifier Types

Object identifiers (OIDs) are used internally by DWS as primary keys for various system catalogs. OIDs are not added to user-created tables by the system. The OID type represents an object identifier.

The OID type is currently implemented as an unsigned four-byte integer. So, using a user-created table's OID column as a primary key is discouraged.

Table 1 Object identifier types

Name

Reference

Description

Examples

OID

-

Numeric object identifier

564182

CID

-

A command identifier. This is the data type of the system columns cmin and cmax. Command identifiers are 32-bit quantities.

-

XID

-

A transaction identifier. This is the data type of the system columns xmin and xmax. Transaction identifiers are also 32-bit quantities.

-

TID

-

A row identifier. This is the data type of the system column ctid. A row ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

-

REGCONFIG

pg_ts_config

Text search configuration

english

REGDICTIONARY

pg_ts_dict

Text search dictionary

simple

REGOPER

pg_operator

Operator name

+

REGOPERATOR

pg_operator

Operator with argument types

*(integer,integer) or -(NONE,integer)

REGPROC

pg_proc

Indicates the name of the function.

sum

REGPROCEDURE

pg_proc

Function with argument types

sum(int4)

REGCLASS

pg_class

Relation name

pg_type

REGTYPE

pg_type

Data type name

integer

The OID type is used for a column in the database system catalog.

For example:

SELECT oid FROM pg_class WHERE relname = 'pg_type';
 oid  
------
 1247
(1 row)

The alias type for OID is REGCLASS which allows simplified search for OID values.

For example:

SELECT attrelid,attname,atttypid,attstattarget FROM pg_attribute WHERE attrelid = 'pg_type'::REGCLASS;
 attrelid |  attname   | atttypid | attstattarget 
----------+------------+----------+---------------
     1247 | xc_node_id     |       23 |             0
     1247 | tableoid       |       26 |             0
     1247 | cmax           |       29 |             0
     1247 | xmax           |       28 |             0
     1247 | cmin           |       29 |             0
     1247 | xmin           |       28 |             0
     1247 | oid            |       26 |             0
     1247 | ctid           |       27 |             0
     1247 | typname        |       19 |            -1
     1247 | typnamespace   |       26 |            -1
     1247 | typowner       |       26 |            -1
     1247 | typlen         |       21 |            -1
     1247 | typbyval       |       16 |            -1
     1247 | typtype        |       18 |            -1
     1247 | typcategory    |       18 |            -1
     1247 | typispreferred |       16 |            -1
     1247 | typisdefined   |       16 |            -1
     1247 | typdelim       |       18 |            -1
     1247 | typrelid       |       26 |            -1
     1247 | typelem        |       26 |            -1
     1247 | typarray       |       26 |            -1
     1247 | typinput       |       24 |            -1
     1247 | typoutput      |       24 |            -1
     1247 | typreceive     |       24 |            -1
     1247 | typsend        |       24 |            -1
     1247 | typmodin       |       24 |            -1
     1247 | typmodout      |       24 |            -1
     1247 | typanalyze     |       24 |            -1
     1247 | typalign       |       18 |            -1
     1247 | typstorage     |       18 |            -1
     1247 | typnotnull     |       16 |            -1
     1247 | typbasetype    |       26 |            -1
     1247 | typtypmod      |       23 |            -1
     1247 | typndims       |       23 |            -1
     1247 | typcollation   |       26 |            -1
     1247 | typdefaultbin  |      194 |            -1
     1247 | typdefault     |       25 |            -1
     1247 | typacl         |     1034 |            -1
(38 rows)