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