PostgreSQL 中定義自己需要的資料類型

來源:互聯網
上載者:User

標籤:

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中建立自己的列舉資料型別

[[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, 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

PostgreSQL 中定義自己需要的資料類型

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.