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