PostgreSQL database-Data Type summary

Data type of PostgreSQL database

PostgreSQL supports a variety of data types, including integer types, floating-point types, Arbitrary precision values, datetime types, string types, binary types, Boolean types, and array types.

1, Integer type

PostgreSQL provides a variety of numeric data types, different data types provide different range of values, the larger the range of values that can be stored, the greater the storage space required.

The integer types provided by PostgreSQL are: mallint, int, bigint.

"Type name" "description" "Storage Requirements" "Value range"

smallint small range of integers 2 bytes -32768----32767

int normal-size integer 4 bytes -2147483648----2147483647

bigint large integers 8 bytes -9223372036854775808----9223372036854775807

2. Floating-point number type:

A floating-point number is used in PostgreSQL to represent decimals. There are two kinds of floating-point types: real and double precision, as follows:

"Type name", "description", "Storage Requirements"

Real 6-bit decimal digit Precision 4 bytes

Double precision 15-bit decimal digit Precision 8 bytes

3. Arbitrary precision Type

The use of numeric in PostgreSQL means that a type of arbitrary precision is a numeric value, denoted by numeric (m,n), where m is called precision and represents the total number of digits; n is known as the scale, which is the number of digits representing decimals. For example: 563.129 with a precision of 9 and a scale of 3

Note: The valid range of values for numeric is determined by the values of M and D. If you change m and fix D, the range of values will be larger with M. Also, if the user-specified precision exceeds the precision, it is rounded for processing. As follows:

testdb=# CREATE TABLE emp (t Numeric (5,1), y Numeric (5,3));

testdb=# INSERT INTO EMP values (9.12,9.156);

testdb=# select * from EMP;

T | Y

-----+-------

9.1 | 9.156

4. Date and Time type:

PostgreSQL has several data types that represent dates, mainly: time, date, timestamp, and interval. Each type has a valid range of values, and the system inserts a value of "0" into the database when you specify a value that is indeed not valid. The following types are described:

"Type name" "meaning" "date range" Storage Requirements "storage format"

Time is only used within one day 00:00:00---24:00:00 8 bytes HH:MM:SS

Date is used only for dates 4713BC---58784897AD 4 bytes yyyy-mm-dd

Timestamp date and time 4713BC---58784897AD 8 byte yyyy-mm-dd HH:MM:SS

Note: The time and timestamp types are without time zone (without the timezone) by default, and can be set to with timezone if required

4.1. Time: Type use

The time type is used in times and is stored with 8 bytes in the format: HH:MM:SS. HH denotes hours; mm denotes minutes; SS represents seconds.

Example:

testdb=# CREATE TABLE Test (t time);

testdb=# INSERT into test values (' 11:10:25 '), (' 23:45 '); ---Note that the first time value has a space at the end,

testdb=# select * from test;

T

----------

11:10:25

23:45:00

(2 rows)

As can be seen from the above results, both times are correctly inserted, and the format is: HH:MM:SS

testdb=# INSERT into test values (Current_time), (now ()); ---insert system current time

INSERT 0 2

testdb=# select * from test;

T

-----------------

11:10:25

23:45:00

04:56:57.034863

04:56:57.034863

(4 rows)

As can be seen from the above, the time zone will be displayed;

4.2. Date: Type

When a date type is used on a day value, there is no time section and the storage requires 4 bytes. The date format is: ' Yyyy-mm-dd ', where YYYY represents the year; MM represents the month; DD indicates the day;. When assigning a value to a field of type date, you can use data from a string type or numeric type to be inserted, as long as the date format is met.

①: A date in a string format of "Yyyy-mm-dd" or "YYYYMMDD". For example, enter ' 2018-12-31 ' or ' 20181231 ', and the date for inserting the database is 2018-12-31.

②: A date expressed in "Yy-mm-dd" or "YYMMDD" string format, where YY represents a two-bit year value. PostgreSQL interprets the two-bit year value using the following rules: "00-69" the conversion year value is ' 2000--2069 '; ' 70-99 ' The conversion year value is ' 1970-1999 ';

③: Inserts the current system date with current_date or Now ().

Example:

testdb=# CREATE TABLE emp (d date);

testdb=# INSERT INTO EMP values (' 1998-08-08 '), (' 19980808 '), (' 20180808 ');

testdb=# INSERT INTO EMP values (now ()); ---insert system current value

testdb=# select * from EMP;

D

------------

1998-08-08

1998-08-08

2018-08-08

2018-06-23

(4 rows)

Note: the now () function returns a date and time value that retains only the date part when it is saved to the database.

4.3. Timestamp: type

The date format for timestamp is YYYY-MM-DD HH:MM:SS. 8 bytes are required for storage. Such as:

testdb=# CREATE TABLE EMP (TS timestamp);

testdb=# INSERT INTO EMP values (' 2018-08-10 11:00:02 '), (now ());

testdb=# select * from EMP;

Ts

---------------------------

2018-08-10 11:00:02

2018-06-23 05:14:17.52538

(2 rows)

4.4. Create a date and time type with a time zone

testdb=# CREATE TABLE emp (t time with time zone);

CREATE TABLE

testdb=# INSERT INTO EMP values (' 12:10:05 PST '), (' 12:10:10 ');

INSERT 0 2

testdb=# select * from EMP;

T

-------------

12:10:05-08

12:10:10-04

(2 rows)

Where the PST is the West 8 zone, if you do not specify a time zone, the default is the East 8 zone.

5. String Type:

String types are used to store string data, in addition to the string data that can be stored, as well as other data, compared to slices and sound binary data. In addition to string comparisons that can be distinguished or case insensitive, pattern matching lookups can be performed. The string types of PostgreSQL include: char, varchar, and text.

"Type name" "description"

char (n) fixed-length non-binary string, insufficient padding

varchar (n) Variable-length non-binary string with length limitation

Text variable length non-binary string, no length limit

Attention:

char (n) varchar (n), where n is a positive integer. Represents a string that can store n characters. If the string to be stored is shorter than the length of the declaration, a numeric value of type char will fill with whitespace, and a value of type varchar will store only the shorter string.

Whitespace filled in char types is meaningless. For example, when you compare two char values, the padding whitespace is ignored, and when converted to other string types, the whitespace inside the char value is deleted, noting that the trailing whitespace is interesting in varchar and text values.

Example:

testdb=# CREATE TABLE emp (v1 char (5), V2 varchar (10));

CREATE TABLE

testdb=# INSERT INTO EMP values (' AB ', ' ab '), (' abc ', ' abc '), (' AB ', ' ab ');

INSERT 0 3

testdb=# select * from EMP;

V1 | V2

-------+-------

AB | Ab

ABC | Abc

AB | Ab

(3 rows)

testdb=# Select Concat (' (' (', V1, ') '), Concat (' (', ', V2, ') ') from EMP;

concat | Concat

---------+---------

(AB) | (AB)

(ABC) | (ABC)

(AB) | (AB)

(3 rows)

--text Type:

PostgreSQL provides the text type, which can store strings of any length. Although the text type is not a SQL standard, many other SQL database systems also have.

Example:

testdb=# CREATE TABLE emp (t1 text);

testdb=# INSERT INTO EMP values (' AB '), (' AGCD '), (' AB ');

testdb=# select * from EMP;

T1

-------

Ab

Agcd

Ab

(3 rows)

testdb=# Select Concat (' (', T1, ') ') from EMP;

Concat

---------

(AB)

(AGCD)

(AB)

(3 rows)

6. Binary type

PostgreSQL supports two types of character data: text strings and binary strings, and PostgreSQL provides a bytea type for storing binary strings. The Bytea type storage space is 4 bytes plus the actual binary string.

testdb=# CREATE TABLE emp (b bytea); ^

testdb=# INSERT INTO EMP values (E ' \\000 ');

INSERT 0 1

testdb=# select * from EMP;

B

------

\x00

(1 row)

7. Boolean type

PostgreSQL provides a Boolean Boolean data type. Boolean is stored with 1 bytes, providing true (true), False (false) two values. In addition, users can replace true and false with other valid text values. The literal value in place of true is ' t ' true ' y ', ' 1 ' ' yes '; the literal value substituted for false is ' F ' ' n ', ' 0 '

testdb=# CREATE TABLE EMP (b boolean);

CREATE TABLE

testdb=# INSERT INTO EMP values (TRUE), (false), (' Y '), (' No '), (' 0 ');

INSERT 0 5

testdb=# select * from EMP;

B

---

T

F

T

F

F

(5 rows)

8. Array type

PostgreSQL allows you to define a field as a one-dimensional or multidimensional array of fixed length or length. The array type can be any base type or user-defined type.

PostgreSQL database-Data Type summary