The column type in the development table is bytea, but it is not the desired result when it selects:
Modify parameters in postgres configuration file
bytea_output = 'escape'
This default is hex type and is modified to escape.
Is it still the result I want when viewing:
select encode(data::bytea,'hex') from data_from_target limit 1;
Supplement: Data types and daily practice notes of PostgreSQL
A data type is a set of the same value types defined on its data structure in a programming language and a set of operations on the same value set. The value storage of data types cannot be separated from variables, so one of the functions of variables is to use it to store data types of the same value set. The data type determines how to store a collection representing these values in the computer's memory. Variables generally follow the principle of declaring first and then using it. In the database, variables are fields. Using fields to represent a set of sets of the same value type is actually the principle of declaration first and then use.
PostgreSQL supports rich data types, including general data types and unconventional data types. General data types include numerical types, currency types, character types, date types, boolean types, enumeration types, etc. Unconventional data types include binary data types, geometric types, network address types, bit string types, text search types, UUID types, XML types, JSON types, array types, composite types, range types, Domain types, OID types, pg_lsn types and pseudo-Types types.
1. Numerical type*
1.1 Integer
The integer types in PostgreSQL include small integer, integer, and large integer, which are represented by smallint, integer, and bigint. Although all three can store the same data types, their respective storage sizes and storage ranges are different. See the table below:
| name | describe | Storage space | scope |
|---|---|---|---|
| SMALLINT | Small range integer, alias INT2. | 2 bytes | -32,768 - +32,767 |
| INTEGER | Commonly used integer, alias INT4. | 4 bytes | -2,147,483,648 - +2,147,483,647 |
| BIGINT | Large range of integers, aliased as INT8. | 8 bytes | -9,223,372,036,854,775,808 - 9,223,372,036,854,775,807 |
As shown in the following example, in PostgreSQL, smallint, integer, and bigint data types can be identified using the extension writing of int2, int4, and int8.
Example:
hrdb=# --Create a table of integer data typeshrdb=# CREATE TABLE IF NOT EXISTS tab_num(v1 smallint,v2 smallint,v3 int,v4 int,v5 bigint,v6 bigint);
CREATE TABLE
hrdb=# --Table field commentshrdb=# COMMENT ON COLUMN tab_num.v1 IS 'Small integer minimum range';COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v2 IS 'small integer maximum range';COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v3 IS 'Integer Minimum Range';COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v4 IS 'Integer Maximum Range';COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v5 IS 'large integer minimum range';COMMENT
hrdb=# COMMENT ON COLUMN tab_num.v6 IS 'large integer maximum range';COMMENT
hrdb=# --Describe the data typehrdb=# \d+ tab_num
Table "public.tab_num"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------+-----------+----------+---------+---------+--------------+----------------
v1 | smallint | | | | plain | | Small integer minimum range
v2 | smallint | | | | plain | | Small integer maximum range
v3 | integer | | | | plain | | Minimum range of integer type
v4 | integer | | | | plain | | Maximum range of integer type
v5 | bigint | | | | plain | | 大Minimum range of integer type
v6 | bigint | | | | plain | | 大Maximum range of integer type
hrdb=# --Insert range values of different integershrdb=# INSERT INTO tab_num
hrdb-# VALUES (-32768,
hrdb(# 32767,
hrdb(# -2147483648,
hrdb(# 2147483647,
hrdb(# -9223372036854775808,
hrdb(# 9223372036854775807);
INSERT 0 1
hrdb=# --Query resultshrdb=# SELECT * FROM tab_num;
v1 | v2 | v3 | v4 | v5 | v6
--------+-------+-------------+------------+----------------------+---------------------
-32768 | 32767 | -2147483648 | 2147483647 | -9223372036854775808 | 9223372036854775807
(1 row)
As shown above, the query result is the most value of inserting different integer ranges, which also means that the boundaries of different integer ranges are included. In actual production scenarios, SMALLINT, INTEGER and BIGINT types store various ranges of numbers, that is, integers. An error will be caused when trying to store values outside the range.
The commonly used type is INTEGER because it provides the best balance between scope, storage space, performance. Generally, the SMALLINT type will be used only if the value range is determined not to exceed SMALLINT. BIGINT is only used when the range of INTEGER is insufficient, because the former is relatively fast.
In addition, you can also use int2, int4, and int8 to represent smallint, integer, and bigint when creating tables. As shown in the following example:
hrdb=# /*
hrdb*# smallint, integer, bigint
Use hrdb*# data types separately
hrdb*# int2,int4,int8 instead
hrdb*# */
hrdb-# CREATE TABLE IF NOT EXISTS tab_numint(v1 int2,v2 int2,v3 int4,v4 int4,v5 int8,v6 int8);
CREATE TABLE
hrdb=# --Describe table definition and data typehrdb=# \d+ tab_numint
Table "public.tab_numint"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------+-----------+----------+---------+---------+--------------+-------------
v1 | smallint | | | | plain | |
v2 | smallint | | | | plain | |
v3 | integer | | | | plain | |
v4 | integer | | | | plain | |
v5 | bigint | | | | plain | |
v6 | bigint | | | | plain |
1.2Arbitrary precision type and floating point type
Any precision type numeric and decimal can store numbers with a large range, and the storage size is variable, with a maximum of 131,072 digits before the decimal point and a maximum of 16,383 digits after the decimal point. It can use floating point types, precise decimals to keep a few bits, or participate in the calculation to get accurate values, but compared to floating point types, its calculations are slower. Numeric is usually recommended for storing currency amounts or other requirements to calculate accurate values. See the table below for details:
| name | describe | Storage space | scope |
|---|---|---|---|
| NUMERIC[(p[,s])],DECIMAL[(p[,s])] | The value range of precision p is [1,1000], and the value range of scale s is [0,p]. Description: p is the total number of digits, s is the decimal number | User statement accuracy. Every four bits (decimal bits) take up two bytes, and then add eight bytes to the entire data. | If the accuracy is not specified, the maximum is 131,072 digits before the decimal point and the maximum is 16,383 digits after the decimal point. |
| real | Variable accuracy | 4 bytes | 6-digit decimal accuracy |
| double precision | Variable accuracy | 8 bytes | 15-digit decimal accuracy |
Example: Any precision type
hrdb=# --Arbitrary precision type examplehrdb=# CREATE TABLE IF NOT EXISTS tab_any_precision(col1 numeric(10,4),col2 decimal(6,4),col3 real,col4 double precision,col5 float4,col6 float8);
CREATE TABLE
hrdb=# --field commentshrdb=# COMMENT ON COLUMN tab_any_precision.col1 IS ' means that the maximum number of integer digits is 6, and only 4 decimal places';COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col2 IS 'represents that the maximum number of integer digits is 2, and the decimal is retained 4 bits';COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col3 IS 'Represents a numerical type with variable 6-bit precision';COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col4 IS 'Number type that represents variable 15-bit precision';COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col5 IS 'same real';COMMENT
hrdb=# COMMENT ON COLUMN tab_any_precision.col6 IS 's same double precision';COMMENT
hrdb=# --View table definitionhrdb=# \d+ tab_any_precision
Table "public.tab_any_precision"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------+-----------+----------+---------+---------+--------------+-----------------------------------
col1 | numeric(10,4) | | | | main | | Indicates that the maximum number of integer digits is6,Decimals are reserved only4Bit
col2 | numeric(6,4) | | | | main | | Indicates that the maximum number of integer digits is2,Decimals are reserved4Bit
col3 | real | | | | plain | | Indicates variable6Bit精度的数值类型
col4 | double precision | | | | plain | | Indicates variable15Bit精度的数值类型
col5 | real | | | | plain | | samereal
col6 | double precision | | | | plain | | samedouble precision
hrdb=# --Insert any accuracy testhrdb=# INSERT INTO tab_any_precision
hrdb-# VALUES(202004.26,20.2004,20.200426,20.203415341535157,20.200426,20.203415341535157);
INSERT 0 1
hrdb=# INSERT INTO tab_any_precision
hrdb-# VALUES(202004.26105,20.20045,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575);
INSERT 0 1
hrdb=# --You can find that the decimal parts of col1 and col2 can exceed 4 bits, but only 4 bits are retained when reading, and follow the principle of rounding, as followshrdb=# SELECT * FROM tab_any_precision;
col1 | col2 | col3 | col4 | col5 | col6
-------------+---------+---------+------------------+---------+------------------
202004.2600 | 20.2004 | 20.2004 | 20.2034153415352 | 20.2004 | 20.2034153415352
202004.2611 | 20.2005 | 20.2004 | 20.2034153415352 | 20.2004 | 20.2034153415352
(2 rows)
hrdb=# /*
hrdb*# If the maximum number of integers inserted in col1 exceeds 6, an error will be reported.
hrdb*# If the maximum number of integers inserted in col2 exceeds 2, an error will be reported.
hrdb*# real and double precision have no restrictions.
hrdb*# */
hrdb-# INSERT INTO tab_any_precision
hrdb-# VALUES(2020042.610,20.2004,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575);
ERROR: numeric field overflow
DETAIL: A field with precision 10, scale 4 must round to an absolute value less than 10^6.
hrdb=# INSERT INTO tab_any_precision
hrdb-# VALUES(202004.26105,202.200,20.2004267,20.2034153415351573,20.2004264,20.2034153415351575);
ERROR: numeric field overflow
DETAIL: A field with precision 6, scale 4 must round to an absolute value less than 10^2.
hrdb=#
1.3 Sequence Type
SMALLSERIAL, SERIAL and BIGSERIAL types are not real data types, they are just conceptual conveniences to set unique identities in tables. Therefore, create an integer field and arrange its default value to be read from a sequence generator. A NOT NULL constraint is applied to ensure that NULL is not inserted. In most cases, the user may also want to attach a UNIQUE or PRIMARY KEY constraint to avoid accidentally inserting duplicate values, but this is not automatic. Finally, subordinate the sequence generator to that field, so that the field or table is deleted as well.
| name | describe | Storage space | scope |
|---|---|---|---|
| SMALLSERIAL | Two-byte sequence integer | 2 bytes | 1 - 32,767 |
| SERIAL | Four-byte sequence integer | 4 bytes | 1 - 2,147,483,647 |
| BIGSERIAL | Eight-byte sequence integer | 8 bytes | 1 - 9,223,372,036,854,775,807 |
Example:
hrdb=# --Create a sequence type tablehrdb=# CREATE TABLE tab_serial(col1 smallserial,col2 serial,col3 bigserial);
CREATE TABLE
hrdb=# --field commentshrdb=# COMMENT ON COLUMN tab_serial.col1 IS 'small integer sequence, starting from 1, with a maximum value of 32767';COMMENT
hrdb=# COMMENT ON COLUMN tab_serial.col2 IS 'small integer sequence, starting from 1, with a maximum value of 2147483647';COMMENT
hrdb=# COMMENT ON COLUMN tab_serial.col3 IS 'small integer sequence, starting from 1, with a maximum value of 9223372036854775807';COMMENT
hrdb=# --View table definitionhrdb=# \d+ tab_serial
Table "public.tab_serial"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+----------+-----------+----------+------------------------------------------+---------+--------------+--------------------------------------------------
col1 | smallint | | not null | nextval('tab_serial_col1_seq'::regclass) | plain | | Small integer sequence，from1start，The maximum value is32767
col2 | integer | | not null | nextval('tab_serial_col2_seq'::regclass) | plain | | Small integer sequence，from1start，The maximum value is2147483647
col3 | bigint | | not null | nextval('tab_serial_col3_seq'::regclass) | plain | | Small integer sequence，from1start，The maximum value is9223372036854775807
hrdb=# --Insert datahrdb=# INSERT INTO tab_serial VALUES(1,1,1);
INSERT 0 1
hrdb=# INSERT INTO tab_serial VALUES(32767,2147483647,9223372036854775807);
INSERT 0 1
hrdb=# --If the value inserted is greater than the range of the sequence integer value, the ERROR will be out of bounds of the integer type.hrdb=# INSERT INTO tab_serial VALUES(32767,2147483647,9223372036854775808);
ERROR: bigint out of range
hrdb=# INSERT INTO tab_serial VALUES(32767,2147483648,9223372036854775807);
ERROR: integer out of range
hrdb=# INSERT INTO tab_serial VALUES(32768,2147483647,9223372036854775807);
ERROR: smallint out of range
hrdb=# --Of course, since it is a sequence type, you can insert the default valuehrdb=# INSERT INTO tab_serial
hrdb-# VALUES(default,default,default);
INSERT 0 1
Through the above example, we can know that smallserial, serial, and bigserial are equivalent to first creating a sequence, and then specifying different integer data types in the creation table. The following example:
hrdb=# --Create the sequence firsthrdb=# CREATE SEQUENCE IF NOT EXISTS serial_small
hrdb-# INCREMENT BY 1
hrdb-# START WITH 1
hrdb-# NO CYCLE;
CREATE SEQUENCE
hrdb=# --Create the table againhrdb=# CREATE TABLE IF NOT EXISTS tab_test_serial(
hrdb(# col1 smallint default nextval('serial_small'),
hrdb(# col2 integer default nextval('serial_small'),
hrdb(# col3 bigint default nextval('serial_small')
hrdb(# );
CREATE TABLE
hrdb=# --Insert datahrdb=# INSERT INTO tab_test_serial VALUES(default);
INSERT 0 1
hrdb=# --Query datahrdb=# SELECT * FROM tab_test_serial ;
col1 | col2 | col3
------+------+------
1 | 2 | 3
(1 row)
2. Currency data type
Currency type stores currency amounts with fixed decimal precision.
Detailed information about currency data types are as follows:
| name | Storage capacity | describe | scope |
|---|---|---|---|
| money | 8 bytes | Currency amount | -92233720368547758.08 to +92233720368547758.07 |
Example:
hrdb=# --Create a currency data type tablehrdb=# CREATE TABLE IF NOT EXISTS tab_money(amounts money);
CREATE TABLE
hrdb=# --field commentshrdb=# COMMENT ON COLUMN tab_money.amounts IS 'Amount';COMMENT
hrdb=# --Insert valuehrdb=# INSERT INTO tab_money VALUES('20.00');
INSERT 0 1
hrdb=# --Query datahrdb=# SELECT * FROM tab_money;
amounts
---------
$20.00
(1 row)
It should be noted here that if the amount of the inserted currency data type does not explicitly specify the currency symbol, then the currency symbol in this area is output by default. The output of 20.00 as shown in the above example is $20.00.
If it is RMB, how to deal with it?
There are two solutions. The first is to use the translate function; the second is to modify the local area currency symbol display parameters.
hrdb=# --Method 1: Use the translate function directly to convert the $ symbol to ￥ symbolhrdb=# SELECT translate(amounts::varchar,'$','￥') FROM tab_money; translate ----------- ￥20.00 (1 row) hrdb=# --Method 2: Modify the regional currency symbol display parametershrdb=# --View the local area currency symbol display parametershrdb=# show lc_monetary ; lc_monetary ------------- en_US.UTF-8 (1 row) hrdb=# --Modify the regional currency symbol display parametershrdb=# ALTER SYSTEM SET lc_monetary = 'zh_CN.UTF-8'; ALTER SYSTEM hrdb=# --Reload dynamic parametershrdb=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) hrdb=# --Review the local area currency symbol display parametershrdb=# show lc_monetary; lc_monetary ------------- zh_CN.UTF-8 (1 row) hrdb=# --Re-query datahrdb=# SELECT * FROM tab_money; amounts --------- ￥20.00 (1 row)
As a special data type, currency symbols need to be calculated to prevent the problem of accuracy loss.
This problem-solving method requires converting the currency type to a numeric type to avoid loss of precision.
hrdb=# INSERT INTO tab_money VALUES('20.22');
INSERT 0 1
hrdb=# SELECT * FROM tab_money ;
amounts
---------
￥20.00
￥20.22
(2 rows)
hrdb=# --Solutions for currency data types to avoid accuracy lossSELECT amounts::numeric::float8 FROM tab_money;
amounts
---------
20
20.22
Kind tips:
When the value of one money type is divided by another money type value, the result is a double precision (that is, a pure number, not a money type); the currency units cancel each other during the operation.
Three Boolean type
PostgreSQL provides standard boolean values. The boolean status is true or false and unknown. If it is unknown, it means that the boolean value is null.
| name | describe | Storage space | Get the value |
|---|---|---|---|
| BOOLEAN | Boolean type | 1 byte | true: true false: false null: unknown |
Example:
hrdb=# --Create a boolean type tablehrdb=# CREATE TABLE IF NOT EXISTS tab_boolean(col1 boolean,col2 boolean);
CREATE TABLE
hrdb=# --Insert the Boolean state value, the state value can be any of the followinghrdb=# INSERT INTO tab_boolean VALUES(TRUE,FALSE);--Specific usageINSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('true','false');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('True','False');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('TRUE','FALSE');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('1','0');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('on','off');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('ON','OFF');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('y','n');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('Y','N');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('yes','no');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('Yes','No');
INSERT 0 1
hrdb=# INSERT INTO tab_boolean VALUES('YES','NO');
INSERT 0 1
hrdb=# SELECT * FROM tab_boolean ;
col1 | col2
------+------
t | f
t | f
t | f
t | f
t | f
t | f
t | f
t | f
t | f
t | f
t | f
t | f
(12 rows)
The boolean type is widely used in business environments, such as turning on and off the mobile phone, 1 means power on and 0 means power off or not in the service area. When logging in and logging out on mobile APP, 1 means login, 0 means logout, WeChat login status, 1 means login successful, 0 means login failed (may be caused by network or password error), etc. I will not give examples one by one here.
Four Character Types
SQL defines two main character types: character varying(n) and character(n). n at that point is a positive number. Both character types can store n (non-byte) length strings. If the stored character length exceeds the length of the character type constraint, it will cause an error unless the extra characters are spaces.
| name | describe | Storage space |
|---|---|---|
| CHAR(n)CHARACTER(n) | A fixed-length string is insufficient to fill in spaces. n refers to the character length. If the accuracy is not n, the default accuracy is 1. | Maximum is 10MB. |
| VARCHAR(n)CHARACTER VARYING(n) | Variable length string. n refers to the character length. | Maximum is 10MB. |
| TEXT | Variable length string. | The maximum is 1G-8023B (i.e. 1073733621B). |
Note that, in addition to the size limit for each column, the total size of each tuple cannot exceed 1G-8023B (i.e. 1073733621B).
In PostgreSQL, in addition to the above character data types, there are two special character types as follows:
| name | describe | Storage space |
|---|---|---|
| name | The internal type used for object names. | 64 bytes |
| “char” | Single-byte internal type. | 1 byte |
Example:
hrdb=# --Create a character type tablehrdb=# CREATE TABLE IF NOT EXISTS tab_chartype(
hrdb(# col1 char(15),
hrdb(# col2 varchar(15),
hrdb(# col3 text,
hrdb(# col4 name,
hrdb(# col5 "char" );
CREATE TABLE
hrdb=# --field commentshrdb=# COMMENT ON COLUMN tab_chartype.col1 IS 'represents a string with a fixed length of 15';COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col2 IS 'represents a string with a length of 15';COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col3 IS 'represents variable-length string, which is an extended string of varchar';COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col4 IS 'Internal type for object name';COMMENT
hrdb=# COMMENT ON COLUMN tab_chartype.col5 IS 'represents a single-byte type';COMMENT
hrdb=# --Insert datahrdb=# INSERT INTO tab_chartype
hrdb-# VALUES('sungsasong','sungsasong','sungsasong','tab_chartype','s');
INSERT 0 1
hrdb=# --Insert data containing spaceshrdb=# INSERT INTO tab_chartype
hrdb-# VALUES('sungsa song','sung sas ong','sung sa song ','tab_chartype','s');
INSERT 0 1
hrdb=# -- Calculate the length of strings stored in different data typeshrdb=# SELECT char_length(col1),char_length(col2),char_length(col3),char_length(col4),char_length(col5)
hrdb-# FROM tab_chartype ;
char_length | char_length | char_length | char_length | char_length
-------------+-------------+-------------+-------------+-------------
10 | 10 | 10 | 12 | 1
11 | 12 | 13 | 12 | 1
Kind tips:
In the above example, although the character length of the statistically-col1 fixed length is 15 stored is 10 and 11, in fact, the length occupied by the col1 column in the storage is 15. Moreover, when calculating the length, spaces are also treated as a character.
Five Binary data types
In PostgreSQL, there are two types of binary data, one is bytea hex format and the other is bytea escape format.
| name | describe | Storage space |
|---|---|---|
| BYTEA | Variable-length binary string | 4 bytes plus the actual binary string. The maximum is 1G-8203 bytes. |
Note: In addition to the size limit for each column, the total size of each tuple cannot exceed 1G-8203 bytes. Example
hrdb=# --Create two tables in bytea formatshrdb=# CREATE TABLE IF NOT EXISTS tab_bytea(col1 bytea,col2 bytea);
CREATE TABLE
hrdb=# --field commentshrdb=# COMMENT ON COLUMN tab_bytea.col1 IS 'bytea hex format binary string';COMMENT
hrdb=# COMMENT ON COLUMN tab_bytea.col2 IS 'bytea escape format binary string';COMMENT
hrdb=# --Insert data, the first value represents single quotes, the output hexadecimal value is \x27, and the second is the escape hexadecimal value fhrdb=# INSERT INTO tab_bytea
hrdb-# VALUES('\047',E'\xF');
INSERT 0 1
hrdb=# --Insert data, the first value represents a backslash, the value prohibited by output 16 is \x5c, the second value is the escaped hexadecimal value fchrdb=# INSERT INTO tab_bytea
hrdb-# VALUES('\134',E'\\xFC');
INSERT 0 1
hrdb=# --View the resultshrdb=# SELECT * FROM tab_bytea;
col1 | col2
------+------
\x27 | \x0f
\x5c | \xfc
Notice:
In fact, bytea multiple hexadecimal values use E'\xFC' similar to the rawtohex function in Oracle. However, the value converted by the rawtohex function in Oracle is a capital hexadecimal string. In fact, if you want to insert E'\xFG' in col2 in the above table, it will prompt that G is not a valid hexadecimal character.
It should also be noted that if E'\xF' is used to contain only a single hexadecimal character, use a backslash. If there are multiple hexadecimal characters, two backslashes are needed, such as E'\xFE'.
As follows: The hextoraw function here is a UDF function I implemented in custom.
6 Date and time data type
PostgreSQL supports rich date and time data types as follows:
| name | describe | Storage space |
|---|---|---|
| DATE | Date and time | 4 bytes (actual storage space size is 8 bytes) |
| TIME [§] [WITHOUT TIME ZONE] | Only used within one day. p represents the accuracy after the decimal point, and the value range is 0-6. | 8 bytes |
| TIME [§] [WITH TIME ZONE] | Only used within one day, with time zone. p represents the accuracy after the decimal point, and the value range is 0-6. | 12 bytes |
| TIMESTAMP[§] [WITHOUT TIME ZONE] | Date and time. p represents the accuracy after the decimal point, and the value range is 0-6. | 8 bytes |
| TIMESTAMP[§][WITH TIME ZONE] | Date and time, with time zone. The alias for TIMESTAMP is TIMESTAMPTZ. p represents the accuracy after the decimal point, and the value range is 0-6. | 8 bytes |
| reltime | Relative time interval. The format is: X years X mons X days XX:XX:XX. | 4 bytes |
6.1 Date input
The input of date and time may be in any reasonable format, including ISO-8601 format, SQL-compatible format, traditional POSTGRES format, or other forms. The system supports custom date input in the order of day, month and year. If the DateStyle parameter is set to MDY, it will be analyzed according to "month-day-year", if it is set to DMY, it will be analyzed according to "day-month-year", and if it is set to YMD, it will be analyzed according to "year-month-day".
The text input of dates needs to be surrounded by single quotes, and the syntax is as follows:
type [ ( p ) ] 'value'
The p in the optional precision declaration is an integer representing the number of digits in the fractional part of the second field.
Example:
hrdb=> --Create a date input table
hrdb=> CREATE TABLE tab_datetype(col1 date);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_datetype.col1 IS 'Date Type，By defaultdatestylestyle(MDY)';
COMMENT
hrdb=> --Insert data
hrdb=> INSERT INTO tab_datetype VALUES(date '04-26-2020');
INSERT 0 1
hrdb=> --existMDYstyle下，SupportedYMDInput method，But it does not supportDMYOr other format input，An error will be reported as follows
hrdb=> INSERT INTO tab_datetype VALUES(date '22-04-2020');
ERROR: date/time field value out of range: "22-04-2020"
LINE 1: INSERT INTO tab_datetype VALUES(date '22-04-2020');
^
HINT: Perhaps you need a different "datestyle" setting.
hrdb=> --Solution,RevisedatestyleFormat
hrdb=> --View the current databasedatestyleFormat
hrdb=> show datestyle;
DateStyle
-----------
ISO, MDY
(1 row)
hrdb=> --会话级别RevisedatestyleFormat
hrdb=> SET datestyle = 'DMY';
SET
hrdb=> --Insert again 22-04-2020
hrdb=> INSERT INTO tab_datetype VALUES(date '22-04-2020');
INSERT 0 1
hrdb=> --Query data
hrdb=> SELECT * FROM tab_datetype ;
col1
------------
2020-04-26
2020-04-22
6.2 Time input
Time types include time [ (p) ] without time zone andtime [ (p) ] with time zone。 If only writingtimeEquivalent totime without time zone。That is, time format without time zone Iftime without time zoneTime zone is declared in input of type，This time zone will be ignored。 Example： hrdb=> --Time without time zone hrdb=> SELECT time '13:22:25'; time ---------- 13:22:25 (1 row) hrdb=> SELECT time without time zone '20:20:18'; time ---------- 20:20:18 (1 row) hrdb=> SELECT time with time zone '18:20:20'; timetz ------------- 18:20:20+08 (1 row)
6.3 Special Time Type
The special time type is represented by reltime, which represents the real time calculated value. If 100 is represented by 00:01:40.
Example:
hrdb=> --createreltimeTime data type table
hrdb=> CREATE TABLE tab_reltime(col1 varchar,col2 reltime);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_reltime.col1 IS 'Original Time Text Time';
COMMENT
hrdb=> COMMENT ON COLUMN tab_reltime.col2 IS 'The time represented by reltime is calculated based on the actual time to get the displayed result';
COMMENT
hrdb=> --Insert data
hrdb=> INSERT INTO tab_reltime VALUES('125','125');
INSERT 0 1
hrdb=> INSERT INTO tab_reltime VALUES('10 DAYS','10 DAYS');
INSERT 0 1
hrdb=> INSERT INTO tab_reltime VALUES('420 DAYS 12:00:23','420 DAYS 12:00:23');
INSERT 0 1
hrdb=> --Query data
hrdb=> SELECT * FROM tab_reltime;
col1 | col2
-------------------+-------------------------------
125 | 00:02:05
10 DAYS | 10 days
420 DAYS 12:00:23 | 1 year 1 mon 25 days 06:00:23
Kind tips:
For reltime time input, text type input needs to be used, that is, single quotes are included.
6.4 Other time types
Other time types include timestamps and interval time data types, as shown below:
Example:
hrdb=> --Create timestamps and interval schedules
hrdb=> CREATE TABLE tab_timestamp_interval(col1 timestamp with time zone,col2 timestamp without time zone,col3 interval day to second);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col1 IS 'Timestamp with time zone';
COMMENT
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col2 IS 'Timestamp without time zone';
COMMENT
hrdb=> COMMENT ON COLUMN tab_timestamp_interval.col1 IS 'Interval Time Type';
COMMENT
hrdb=> --Insert data
hrdb=> INSERT INTO tab_timestamp_interval
hrdb-> VALUES('2020-04-26 13:20:34.234322 CST',
hrdb(> '2020-04-08 14:40:12.234231+08',
hrdb(> '165');
INSERT 0 1
hrdb=> INSERT INTO tab_timestamp_interval
hrdb-> VALUES('2020-04-25 14:56:34.223421',
hrdb(> '2020-04-09 18:54:12.645643 CST',
hrdb(> '10 YEAR 3 MONTH 25 DAYS 14 HOUR 32 MINUTE 19 SECOND');
INSERT 0 1
hrdb=> --Query data
hrdb=> SELECT * FROM tab_timestamp_interval;
col1 | col2 | col3
-------------------------------+----------------------------+----------------------------------
2020-04-27 03:20:34.234322+08 | 2020-04-08 14:40:12.234231 | 00:02:45
2020-04-25 14:56:34.223421+08 | 2020-04-09 18:54:12.645643 | 10 years 3 mons 25 days 14:32:19
Time data types are widely used in business applications, such as mobile APP login time, logout time, financial business transaction time, etc.
Seven Network address type
PostgreSQL also provides network address types for storing data types for two major IP families (IPv4 IPv6 address) addresses and MAC addresses.
| name | Storage space | describe |
|---|---|---|
| cidr | 7 or 19 bytes | IPv4 or IPv6 network |
| inet | 7 or 19 bytes | IPv4 or IPv6 hosts and networks |
| macaddr | 6 bytes | MAC address |
cidr (Classless Inter-Domain Routing) type, saves an IPv4 or IPv6 network address. Declare the network format as address/y, address represents IPv4 or IPv6 addresses, and y represents the number of binary digits of the subnet mask. If y is omitted, the mask part is calculated using an existing category of network numbering system, but the required input data already includes all bytes required to determine the mask.
The inet type holds the host's IPv4 or IPv6 address within a data area, and an optional subnet. The number of digits of the network address in the host address represents the subnet ("subnet mask"). If the subnet mask is 32 and the address is IPv4, this value does not represent any subnet, but only one host. In IPv6, the address length is 128 bits, so 128 bits represent a unique host address.
The input format of this type is address/y. Address represents IPv4 or IPv6 addresses, and y is the binary number of digits of the subnet mask. If /y is omitted, the subnet mask is 32 for IPv4 and 128 for IPv6, so this value means there is only one host. If this value means there is only one host, /y will not be displayed.
The basic difference between inet and cidr types is that inet accepts subnet masks, while cidr does not.
The macaddr type stores the MAC address, that is, the Ethernet card hardware address (although the MAC address is used for other purposes).
Example:
hrdb=> --createIPAddress andMACAddress table
hrdb=> CREATE TABLE tab_icm(col1 cidr,col2 inet,col3 macaddr);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_icm.col1 IS 'Storage IPv4 or IPv6 network address types';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col2 IS 'Storing IPv4 or IPv6 network address types and subnets';
COMMENT
hrdb=> COMMENT ON COLUMN tab_icm.col3 IS 'Storage device MAC address';
COMMENT
hrdb=> --Insert data
hrdb=> INSERT INTO tab_icm VALUES('10.10.20.10/32','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10.10.20/24','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10.10/16','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('10/8','10.10.20.10','00-50-56-C0-00-08');
INSERT 0 1
hrdb=> INSERT INTO tab_icm VALUES('fe80::81a7:c17c:788c:7723/128','fe80::81a7:c17c:788c:7723','00-50-56-C0-00-01');
INSERT 0 1
hrdb=> --Query data
SELECT * FROM tab_icm;
col1 | col2 | col3
-------------------------------+---------------------------+-------------------
10.10.20.10/32 | 10.10.20.10 | 00:50:56:c0:00:08
10.10.20.0/24 | 10.10.20.10 | 00:50:56:c0:00:08
10.10.0.0/16 | 10.10.20.10 | 00:50:56:c0:00:08
10.0.0.0/8 | 10.10.20.10 | 00:50:56:c0:00:08
fe80::81a7:c17c:788c:7723/128 | fe80::81a7:c17c:788c:7723 | 00:50:56:c0:00:01
(5 rows)
8 Geometric data types
PostgreSQL supports collection data types, which are used to store geometric data in GIS (Geographic Information System) environments, and are used for map surveying, urban transportation trajectories, map circle maps and other scenarios.
PostgreSQL supports the following geometric data types:
point
Line (ray)
Line segment
rectangle
Path (including open paths [open paths are similar to polygons] and closed paths)
Polygons
round
For the above geometric types, points are the basis of other geometric types.
| name | Storage space | illustrate | Expression form |
|---|---|---|---|
| point | 16 bytes | Points in planes | (x,y) |
| lseg | 32 bytes | (Limited) Line Segment | ((x1,y1),(x2,y2)) |
| box | 32 bytes | rectangle | ((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> (center and radius) |
For all geometric data types, the horizontal coordinates and vertical coordinates are implemented using the horizontal coordinates and vertical coordinates above the two-dimensional coordinates. The calculation is also performed in two-dimensional coordinates.
Example:
hrdb=> --Create a geometric data type table
hrdb=> CREATE TABLE tab_geometric(col1 point,col2 lseg,col3 box,col4 path,col5 path,col6 polygon,col7 circle);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_geometric.col1 IS 'Basic structural points of two-dimensional geometry(x,y)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col2 IS 'Line segment((x1,y1),(x2,y2))';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col3 IS 'rectangle((x1,y1),(x1,y2),(x2,y1),(x2,y1)),';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col4 IS 'Open path((x1,y1),(x2,y2),(x3,y3),...)';
COMMENT
hrdb=> drop table tab_geometric ;
DROP TABLE
hrdb=> --Create a geometric data type table
hrdb=> CREATE TABLE tab_geometric(col1 point,col2 lseg,col3 box,col4 path,col5 path,col6 polygon,col7 circle);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_geometric.col1 IS 'Basic structural points of two-dimensional geometry(x,y)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col2 IS 'Line segment[(x1,y1),(x2,y2)]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col3 IS 'rectangle((x1,y1),(x1,y2)),';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col4 IS 'Open path[(x1,y1),(x2,y2),(x3,y3),...]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col5 IS 'Closed path[(x1,y1),(x2,y2),(x3,y3),...,(xn,yn)]';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col6 IS 'Polygons,相当于Closed path((x1,y1),(x2,y2),(x3,y3),...,(xn,yn)';
COMMENT
hrdb=> COMMENT ON COLUMN tab_geometric.col7 IS 'A set of coordinate points as the center and radiusrconstitute<(x,y),r>';
COMMENT
hrdb=> --Insert data
hrdb=> INSERT INTO tab_geometric
hrdb-> VALUES('(1,2)',
hrdb(> '[(1,2),(2,3)]',
hrdb(> '((1,2),(1,3))',
hrdb(> '[(1,2),(2,3),(2,4),(1,3),(0,2)]',
hrdb(> '[(1,2),(2,3),(3,4)]',
hrdb(> '((1,2),(2,3),(2,4),(1,3),(0,2))',
hrdb(> '<(2,3),3>');
INSERT 0 1
hrdb=> --Query data
hrdb=> SELECT * FROM tab_geometric;
col1 | col2 | col3 | col4 | col5 | col6 | col7
-------+---------------+-------------+---------------------------------+---------------------+---------------------------------+-----------
(1,2) | [(1,2),(2,3)] | (1,3),(1,2) | [(1,2),(2,3),(2,4),(1,3),(0,2)] | [(1,2),(2,3),(3,4)] | ((1,2),(2,3),(2,4),(1,3),(0,2)) | <(2,3),3>
9 JSON data types
JSON data type can be used to store JSON (JavaScript Object Notation) data. Data can be stored as text, but the JSON data type is more conducive to checking that each stored value is available JSON value.
In PostgreSQL, there are two types of JSON data types, native JSON and JSONB. The main difference is the difference in efficiency. The JSON data type copies the input text, so it needs to be converted during parsing, and the input speed block is input. JSONB decomposes the input text and stores it in binary, so there is no need to convert it during parsing, processing speed blocks, but the input speed is relatively slow. In addition, JSONB data types also support indexing.
Example:
hrdb=> --createJSONData Type Table
hrdb=> CREATE TABLE tab_json(col1 json,col2 jsonb);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_json.col1 IS 'Storing json input text';
COMMENT
hrdb=> COMMENT ON COLUMN tab_json.col1 IS 'Storing JSON converted binary text';
COMMENT
hrdb=> --Insert data
hrdb=> --Insert data
hrdb=> INSERT INTO tab_json
hrdb-> VALUES('{"Jiangsu Province":"Nanjing City","Gansu Province":"Lanzhou City","Beijing":"Beijing"}',
hrdb(> '{"Hubei Province":"Wuhan City","Sichuan Province":"Chengdu City","Shaanxi Province":"Xi'an City"}');
INSERT 0 1
hrdb=> --Givecol1create索引，Will not be supported。col2Support indexing
hrdb=> CREATE INDEX idx_col1 ON tab_json USING GIN(col1);
ERROR: data type json has no default operator class for access method "gin"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
hrdb=> CREATE INDEX idx_col2 ON tab_json USING GIN(col2);
CREATE INDEX
hrdb=> --Query data
hrdb=> SELECT * FROM tab_json;
col1 | col2
---------------------------------------------------------+--------------------------------------------------------------
{"Jiangsu Province":"Nanjing City","Gansu Province":"Lanzhou City","Beijing":"Beijing"} | {"Sichuan Province": "Chengdu City", "Hubei Province": "Wuhan City", "Shaanxi Province": "Xi'an City"}
Kind tips:
Using jsonb type, you can use PL/PYTHON to map to dictionaries, lists, etc. represented in Python.
Ten Array data type
ostgreSQL supports array data types and supports multidimensional arrays. The biggest advantage of an array is that it is accessed according to the array subscript. At this time, the subscript is equivalent to an index and has a fast processing speed. However, arrays also have disadvantages. For example, when deleting or adding array elements, you need to move the elements in the array forward or backward, which leads to slower deleting or adding array tuples.
Example:
hrdb=> --Create an array table
hrdb=> CREATE TABLE tab_array(col1 text[],col2 integer[][],col3 integer ARRAY[3]);
CREATE TABLE
hrdb=> --Field Comments
hrdb=> COMMENT ON COLUMN tab_array.col1 IS 'Text type one-dimensional array';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col2 IS 'Integer type two-dimensional array';
COMMENT
hrdb=> COMMENT ON COLUMN tab_array.col3 IS 'Declare an array of length 3';
COMMENT
hrdb=> --Insert data
hrdb=> INSERT INTO tab_array
hrdb-> VALUES('{"Jiangsu Province","Gansu Province","Beijing"}',
hrdb(> '{1,2,3,4,5}',
hrdb(> '{21,22,31}');
INSERT 0 1
hrdb=> INSERT INTO tab_array
hrdb-> VALUES('{"Tianjin City","Hubei Province","Shaanxi City"}',
hrdb(> '{5,4,3,2,1}',
hrdb(> '{21,22,31,44}');
INSERT 0 1
hrdb=> --Query data
hrdb=> SELECT * FROM tab_array;
col1 | col2 | col3
------------------------+-------------+---------------
{Jiangsu Province,Gansu Province,Beijing} | {1,2,3,4,5} | {21,22,31}
{Tianjin,Hubei Province,Shaanxi City} | {5,4,3,2,1} | {21,22,31,44}
(2 rows)
hrdb=> --Access elements of an array in a specified column
hrdb=> SELECT col1[1],col2[3],col3[4] FROM tab_array;
col1 | col2 | col3
--------+------+------
Jiangsu Province | 3 |
Tianjin | 3 | 44
Through the above example, it can be found that in PostgreSQL, although the length of the array is declared, PostgreSQL does not limit the length of the array.
At the same time, accessing the array element starts with subscript 1, and there will be no array out-of-bounds exception in PostgreSQL. If the subscript access of the array exceeds the length of the element, PostgreSQL will return a row of empty values.
The above is an introduction to common data types. However, in PostgreSQL, in addition to the above data types, there are other data types, such as XML data types, text search data types, UUID data types, composite data types, range types, pseudo-types such as any, anyelement, internal, etc., which will not be introduced one by one here.