CodesJava

Easy learning with example program codes

Data Types in PostgreSQL

PostgreSQL Data Types

It is necessary to specify the type of data that can be stored and processed in a PostgreSQL database along with the specification of the type of operations that can be performed on that type of data. This purpose is served by the various data types supported by the PostgreSQL database including integer data type, floating point data type, string data type, boolean data type, etc. PostgreSQL supports a lot of the standard data types of SQL which are divided into various categories.

 

Numeric Data Types:

There are mainly Nine numeric data types that PostgreSQL supports. These are listed below:

Data TypeRangeStorage SizeDescription
INTEGER-2147483648 to 2147483647 (signed)

0 to 4294967295 (unsigned)

4 BytesNormal sized integer value.
SMALLINT-32768 to 32767 (signed)

0 to 65535 (unsigned)

2 BytesSmall integer value.
BIGINT-9223372036854775808 to 9223372036854775807 (signed)

0 to 18446744073709551615 (unsigned)

8 BytesLarge integer value.
DOUBLE

PRECISION

15 Decimal digits precision.8 BytesVariable precision floating point number.
DECIMAL131072 before decimal; 16383 after decimalvariableUser Specified precision fixed point number.
NUMERIC131072 before decimal; 16383 after decimalvariableUser Specified precision fixed point number.
REAL6 Decimal digits precision.4 BytesVariable precision floating point number.
SERIAL1 to 21474836474 BytesAuto Incrementing Integer.
BIGSERIAL1 to 92233720368547758078 BytesLarge Auto Incrementing Integer

 

Date and Time Data Types:

There are mainly five date and time data types that PostgreSQL supports. These are listed below:

Data TypeSizeRangeResolutionDescription
TIMESTAMP [ (p) ] [ without time zone ]8 Bytes4713 BC to 294276 AD1 microsecond / 14 digitsBoth date and time
TIMESTAMP [ (p) ] [with time zone]8 Bytes4713 BC to 294276 AD1 microsecond / 14 digitsBoth date and time
DATE4 Bytes4713 BC to 5874897 AD1 dayDate (no time of day)
TIME [ (p) ] [ without time zone ]8 Bytes00:00:00 to 24:00:001 microsecond / 14 digitsTime of day (no date)
TIME [ (p) ] [with time zone]12 Bytes00:00:00+1459 to 24:00:00-14591 microsecond / 14 digitsTime of day (no date)
INTERVAL [ fields ] [ (p) ]12 Bytes-178000000 years to 178000000 years1 microsecond / 14 digitsTime interval

 

String Data Types:

There are mainly five string data types that PostgreSQL supports. These are listed below:

Data Type Maximum SizeDescription
CHAR(size)255 charactersHere, size is equal to the number of characters to store. It is used for fixed-length strings with space padded on right to equal size characters.
VARCHAR(size)255 charactersHere, size is equal to the number of characters to store. It is used for variable-length strings.
TEXT(size)65,535 charactersHere, size is equal to the number of characters to store.
CHARACTER(size)255 charactersHere, size is equal to the number of characters to store. It is used for fixed-length strings with space padded on right to equal size characters.
CHARACTER VARYING(size)255 charactersHere, size is equal to the number of characters to store. It is used for variable-length strings.

 

Boolean Data Types:

There are mainly one boolean data types that PostgreSQL supports.

Data Type SizeDescription
BOOLEAN1 ByteSpecifies the state of True or False.

 

Monetary Data Types:

There are mainly one monetary data types that PostgreSQL supports.

Data Type SizeRangeDescription
MONEY8 Bytes-92233720368547758.08 to +92233720368547758.07Specifies the Currency Amount.

 

Geometric Data Types:

There are mainly eight geometric data types that PostgreSQL supports. These are listed below:

Data Type SizeDescription
POINT16 BytesPoint on a plane.
LINE32 BytesInfinite line.
LSEG32 BytesFinite line segment.
BOX32 BytesRectangular box.
PATH16+16n BytesClosed path.
PATH16+16n BytesOpen path
POLYGON40+16n BytesPolygon.
CIRCLE24 BytesCircle.

 

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status