# Databases : PostgreSQL : Introspection : SQL vs Catalog ## Types How the types you specify in `CREATE TABLE` statements translate to pg_catalog values. ``` pg_type pg_attribute Definition typname format_type( atttypid, atttypmod ) ────────────────────── ────────── ────────────────────────────────── bigint int8 bigint bigserial int8 bigint bool bool boolean boolean bool boolean char bpchar character(1) char( 50 ) bpchar character(50) character bpchar character(1) character( 50 ) bpchar character(50) character_varying varchar, character varying character_varying( 50 ) varchar, character varying(50) date date date decimal numeric numeric decimal( 5, 3 ) numeric numeric(5,3) double precision float8 double precision float float8 double precision float4 float4 real float8 float8 double precision int int4 integer int2 int2 smallint int4 int4 integer int8 int8 bigint json json json money money money numeric numeric numeric numeric( 5, 3 ) numeric numeric(5,3) real float4 real serial int4 int serial2 int2 smallint serial4 int4 int serial8 int8 bigint smallint int2 smallint smallserial int2 smallint text text text time time time without time zone timetz timetz time with time zone timestamp timestamp timestamp without time zone timestamptz timestamptz timestamp with time zone uuid uuid uuid varchar varchar character varying varchar( 50 ) varchar character varying(50) geometry( Point, 4326 ) geometry geometry(Point,4326) ``` To achieve parity between your hand-written SQL and SQL auto-generated from pg_catalog, limit your use to the following types: ``` boolean smallint, integer, bigint real, double precision, numeric, numeric(p,s), money character(n), character varying(n), text date, time with time zone, time without time zone, timestamp with time zone, timestamp without time zone json uuid geometry(Point,4326) ``` ## Columns Adding UNIQUE to a column... - Specifying UNIQUE as a column constraint produces the same result as specifying it as a table constraint with just that column. In fact, when adding a unique constraint to a column after-the-fact, it must be added as a table constraint. ``` creates index "TABLE_COLUMN_key" num_cols=1, unique=True, distinct_nulls=True, pkey=False, cols[N] creates constraint "TABLE_COLUMN_key" type=Unique, defer*=False, index_name=..., columns=[N] deleting column also deletes index + constraint ```