PostgreSQL Create table type, alter and drop type

Create postgresql type

Create table type, alter and drop type. Syntax and examples.

Syntax

CREATE TYPE type_name 
AS ( 	attribute_name_1 data_type, 
		attribute_name_2 data_type, ... 
		attribute_name_n data_type );

CREATE TYPE type_name 
	AS ENUM ( [ 'label' [, ... ] ] );

Example

CREATE TYPE customer_type 
	AS (t_id numeric, t_name varchar);

CREATE TYPE log_status AS ENUM ('ok', 'not ok');
CREATE TABLE log(
    id numeric,
    message text,
    status log_status
);

Alter postgresql type

Syntax

ALTER TYPE type_name RENAME TO new_type_name;
ALTER TYPE type_name OWNER TO new_owner; 
ALTER TYPE type_name SET SCHEMA new_schema;

Example

ALTER TYPE customer_type RENAME TO customer_type_2;
ALTER TYPE customer_type OWNER TO scott;
ALTER TYPE log_status SET SCHEMA customers;

Drop postgresql type

Syntax

DROP TYPE [ IF EXISTS ] type_name [ CASCADE | RESTRICT ]

Example

DROP TYPE customer_type;