gogoWebsite

PostgreSQL data type summary

Updated to 7 hours ago

Compared with other databases, PostgreSQL database supports more data types, including commonly used numerical types, string types, date/time types, and geometric types, network address types, xml types and json types. You can also use CREATE TYPE to add data types yourself. This article mainly introduces the main data types of PostgreSQL database.

1. Numerical type

Commonly used numerical types are shown in the following table:

name Alias Storage size scope describe Compare with other databases
smallint int2 2 bytes -225~215-1 Signed 2-byte integer Smallint in MySQL, number(5) in Oracle
integer int,int4 4 bytes -231~231-1 Signed 4-byte integer int in MySQL, number(38,0) in Oracle
bigint int8 8 bytes -263~263-1 Signed 8-byte integer Bigint in MySQL, number in Oracle(38)
real float4 4 bytes Single precision floating point number float in MySQL, binary_float in Oracle
double precision float8 8 bytes Double precision floating point number Double in MySQL, binary_double or number in Oracle
numeric [(p, s)] decimal [(p, s)] Accurate numbers with precision can be selected decimal[(p, s)] in MySQL, NUMBER[(p, s)] in Oracle
money 8 bytes -92233720368547758.08~+92233720368547758.07 Currency amount

2. Self-increasing type

Commonly used self-increment types are shown in the following table:

name Alias Storage size scope describe Compare with other databases
smallserial serial2 2 bytes 2 bytes self-incremental integer Self-incremented fields in MySQL and sequences in Oracle
serial serial4 4 bytes 4-byte self-incremental integer Self-incremented fields in MySQL and sequences in Oracle
bigserial serial8 8 bytes 8 bytes self-incremental integer Self-incremented fields in MySQL and sequences in Oracle

3. Boolean type

name Alias Storage size scope describe Compare with other databases
boolean bool 1 byte true/false boolean in MySQL, number(1) in Oracle

4. Binary type

name Alias Storage size scope describe Compare with other databases
bytea Binary data blob in MySQL, blob in Oracle

5. Bit string type

name Alias Storage size scope describe Compare with other databases
bit [ (n) ] Fixed length bit string bit in MySQL [ (n) ]
bit varying [ (n) ] varbit[ (n) ] Variable length bit string

6. String type

name Alias Storage size scope describe Compare with other databases
character (n) char (n)，bpchar(n) n bytes Fixed-length string, if insufficient, will be filled with spaces char(n) in MySQL
character varying (n) varchar (n) Up to n bytes Variable length string varchar(n) in MySQL, varchar(n) in Oracle
bpchar n characters Variable unlimited length strings, insufficient characters are filled with spaces
text Variable length string Text in MySQL, clob in Oracle

7. Date and time type

name Alias Storage size scope describe Compare with other databases
timestamp [ (p) ] [without time zone] 8 bytes 4713BC~294276AD Date and time without time zone
timestamp [ (p) ] [with time zone] 8 bytes 4713BC~294276AD Date and time with time zones
date 4 bytes 4713BC~5874897AD date
time [ (p) ] [without time zone] 8 bytes 00:00:00~24:00:00 time
time [ (p) ] [with time zone] 12 bytes 00:00:00+1559~24:00:00-1559 Times with time zones
interval [fields] [(p)] 16 bytes -178000000years~178000000years Time interval

8. Enumeration type

Enumeration type is a data type that contains a series of ordered static values. It needs to be declared before use.
Enumeration type usageCREATE TYPETo create it as follows:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Once the enum is created, it can be used as a predefined type of PostgresSQL

CREATE TABLE person (
	name text,
	current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');

9. Geometric Types

PostgreSQL provides geometric types such as points, lines, rectangles, polygons, etc., which is also different from other databases, as shown in the following table:

name Alias Storage size describe Expression form
point 16 bytes Points in planes (x,y)
line 32 bytes Infinitely long straight lines {A,B,C}
lseg 32 bytes Limited line segment ((x1,y1),(x2,y2))
box 32 bytes Rectangular frame ((x1,y1),(x2,y2))
path 16+16n bytes Closed path (similar to polygons) ((x1,y1),…)
path 16+16n bytes Open path [(x1,y1),…]
polygon 40+16n bytes Polygons (similar to closed paths) ((x1,y1),…)
circle 24 bytes round <(x,y),r>

10. Network address type

PostgreSQL provides unique types for IPv4, IPv6 and Ethernet MAC addresses, as shown in the following table:

name Storage size describe
cidr 7 or 19 bytes IPv4 and IPv6 network addresses
inet 7 or 19 bytes IPv4 and IPv6 network addresses and host addresses
macaddr 6 bytes Ethernet MAC address
macaddr8 8 bytes Ethernet MAC address (EUI-64)

11. XML Type

The xml type can be used to store XML data. When inserting the data, the input data will be checked, so that data that does not meet the XML standards cannot be stored in the database. It also provides a function to perform security check on its type. You can use the function xmlparse to convert a string into xml data, as shown below:

XMLPARSE ( {DOCUMENT | CONTENT} value)

Examples are as follows:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

You can also use the following two methods

xml '<foo>bar</foo>'
'<foo>bar</foo>'::xml

12. JSON type

JSON data type can be used to store JSON data. When inserting data, it will automatically detect whether the string is legal JSON data. It also provides rich functions.

name Storage size describe
json Text json data
jsonb Reparsed binary json data

12.1. The difference between JSON and JSONB

  1. The JSON type stores the input data intact into the database (JSON syntax check will be performed before storage), and the data needs to be re-parsed when used. The JSONB type parses JSON into binary format when stored. There is no need to parse again when using it. The performance will be higher when using JSONB.
  2. The extra spaces, key order and duplicate keys between keys in a JSON string will be retained, while JSONB will not retain extra spaces and duplicate keys.
  3. JSONB supports indexing on it, but JSON does not

12.2. Mapping of JSON types and PostgreSQL database types

When converting a JSON type string to a JSONB type, the data type in the JSON string is actually converted to a type in the PostgreSQL database, and the mapping is shown in the following table:

JSON Type PostgreSQL Type Things to note
string text Pay attention to some limitations of character sets
number numeric There are no "NaN" and "infinity" values ​​in PostgreSQL in JSON
boolean boolean JSON can only accept lowercase "true" and "false"
null (none) NULL in SQL means different meanings