PostgreSQL data type

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