PostgreSQL 中定义自己需要的数据类型
PostgreSQL解决某系数据库中的tinyint数据类型问题,创建自己需要的数据类型如下：
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)
PostgreSQL中创建自己的枚举数据类型
[postgres@minion4 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, 1000) id;
INSERT 0 1000
test=# INSERT INTO test.test2
test-# SELECT id,
test-# 'AAA' || to_char(id, 'FM000')
test-# FROM generate_series(1, 100) id;
INSERT 0 100
test=# analyze test.test1;
ANALYZE
test=# analyze test.test2;
ANALYZE
