PostgreSQL data type

Source: Internet
Author: User
Tags postgresql ranges



The data type specifies what type of data to store in the table field. When you create a table, you must use the data type for each column.
There are three main types of data types in Potgresql. In addition, users canCREATE TYPEcreate their own custom data types using SQL commands.



The following are the main three types of data types in PostgreSQL:


    • Numeric data type
    • String data type
    • Date/Time Data type
Numeric data type


Numeric data types are used to specify numeric data in a table.


name Description Storage size Range
smallint storing integers, small ranges 2 bytes -32768 to +32767
integer Stores integers. Use this type to store a typical integer 4 bytes -2147483648 to +2147483647
bigint Stores integers, large ranges. 8 bytes -9223372036854775808 to 9223372036854775807
decimal User-specified precision, precise Variable A maximum of 131,072 digits before the decimal point; A maximum of 16,383 digits after the decimal point.
numeric User-specified precision, precise Variable A maximum of 131,072 digits before the decimal point; A maximum of 16,383 digits after the decimal point.
real Variable precision, imprecise 4 bytes 6-bit digital accuracy
double Variable precision, imprecise 8 bytes 15-bit digital accuracy
serial Auto Increment integer 4 bytes 1 to 2147483647
bigserial Large auto-incrementing integers 8 bytes 1 to 9223372036854775807
String data type


The string data type is used to represent the value of a type.


Data Type Description
char(size) Heresizeis the number of characters to store. Fixed-length strings, the right-hand spaces are padded to equal-sized characters.
character(size) Heresizeis the number of characters to store. A fixed-length string. The space on the right fills in equal-sized characters.
varchar(size) Heresizeis the number of characters to store. A variable-length string.
character varying(size) Heresizeis the number of characters to store. A variable-length string.
text A variable-length string.
Date/Time Data type


Date/Time data types are used to represent columns that use date and time values.


/tr>
name Description Storage size Minimum Value Maximum Value Degree of resolution
timestamp [(p)] without time zone Date and time (no time zone) 8 bytes 4713 bc 294276 ad 1 μs/14 digits
timestamp [(p)] with time zone included Date and time with time zone 8 bytes 4713 BC 294276 ad
dat E date (no time) 4 bytes 4713 BC 5874897 ad 1 μs/14-digit
time [(p)] without time zone times (no dates) 8 bytes 00:00:00 24:00:00< /td> 1 μs/14 digits
time [(p)] with time zone only times with time zone 12 bytes 00:00:00+1459 24:00:00-1459 1 μs/14 digits
interval [f Ields][p] time interval 12 bytes -178 million year 178 million year 1 microseconds/14 digits
Some other data types are Boolean types:
name Description Storage size
boolean The state that it specifiestrueorfalse. 1 bytes
Currency type:
name Description Storage size Range
money Currency Amount 8 bytes -92233720368547758.08 to +92233720368547758.07
Geometry type:


The geometry data type represents a two-dimensional spatial object. The most fundamental type: point -forms the basis of all other types.


name Storage size represents Description
point 16 bytes The point on a plane (x, Y)
line 32 bytes Infinite Line (not fully implemented) ((X1,y1), (X2,y2))
lseg 32 bytes Finite segment ((X1,y1), (X2,y2))
box 32 bytes Rectangle box ((X1,y1), (X2,y2))
path 16+16n bytes Closed path (similar to polygon) ((x1,y1),...)
polygon 40+16n bytes Polygons (similar to Closed paths) ((x1,y1),...)
circle 24 bytes Round <(x,y),r>(center point and radius)


PostgreSQL data type


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.