Define the type of data you need in PostgreSQL

Source: Internet
Author: User
Tags psql create domain

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

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.