Define the type of data you need in PostgreSQL

PostgreSQL solves the tinyint data type problem in a system database, creating the data types that you need:

CREATE DOMAIN tinyint

As smallint

CONSTRAINT tinyint_check Check (value >= 0 and value <= 255);

ALTER DOMAIN tinyint

OWNER to Postgres;

COMMENT on DOMAIN tinyint

is ' tinyint type between 0 and 255 ';

postgres=# CREATE TABLE Test_domain (ID tinyint);

CREATE TABLE

postgres=# INSERT INTO Test_domain values (1);

INSERT 0 1

postgres=# INSERT into Test_domain values (0);

INSERT 0 1

postgres=# INSERT into Test_domain values (255);

INSERT 0 1

postgres=# INSERT into Test_domain values (256);

Error:value for domain tinyint violates check constraint "Tinyint_check"

Statement:insert into Test_domain values (256);

Error:value for domain tinyint violates check constraint "Tinyint_check"

postgres=# INSERT into Test_domain values (-1);

Error:value for domain tinyint violates check constraint "Tinyint_check"

Statement:insert into Test_domain values (-1);

Error:value for domain tinyint violates check constraint "Tinyint_check"

postgres=# INSERT into Test_domain values (100);

INSERT 0 1

postgres=# select * from Test_domain;

Id

-----

1

0

255

100

(4 rows)

Create your own enumeration data type in PostgreSQL

[Email protected] bin]$./PSQL test test

Psql (9.3.9)

Type ' help ' for help.

test=# CREATE TYPE user_enum as enum (' Enum1 ', ' enum2 ', ' enum3 ');

CREATE TYPE

test=# \dt

List of data types

Schema | Name | Description

--------+-----------+-------------

Public | User_enum |

(1 row)

test=# Select OID from Pg_type where typname= ' user_enum ';

Oid

-------

16902

(1 row)

test=# CREATE SCHEMA test;

CREATE SCHEMA

test=# CREATE TABLE Test.test1 (

Test (# Column1 int not NULL,

Test (# Column2 int not NULL,

Test (# Column3 text,

Test (# Column4 TIMESTAMPTZ,

Test (# column5 timestamp,

Test (# column6 varchar (10),

Test (# Column7 char (10),

Test (# COLUMN8 User_enum,

Test (# CONSTRAINT T1_pkey PRIMARY KEY (column1)

Test (#);

CREATE TABLE

test=# CREATE TABLE Test.test2 (

Test (# Column1 int not NULL,

Test (# Column2 text,

Test (# CONSTRAINT T2_pkey PRIMARY KEY (column1)

Test (#);

CREATE TABLE

test=# INSERT into Test.test1

test-# SELECT ID,

test-# ID% 10,

test-# to_char (ID, ' FM00000 '),

test-# ' 2015-09-09 ':: Timestamptz + ((id% 100) | | ' Days '):: Interval,

test-# ' 2015-09-09 ':: Timestamp + ((id% 100) | | ' Days '):: Interval,

test-# ID% 10,

test-# ID% 10,

test-# ' enum1 ':: User_enum

test-# from Generate_series (1, +) ID;

INSERT 0 1000

test=# INSERT into Test.test2

test-# SELECT ID,

test-# ' AAA ' | | To_char (ID, ' FM000 ')

test-# from Generate_series (1, +) ID;

INSERT 0 100

test=# analyze Test.test1;

ANALYZE

test=# analyze Test.test2;

ANALYZE

Define the type of data you need in PostgreSQL