Shulou(Shulou.com)06/01 Report--

Data type of postgresql database

Postgresql supports a variety of data types, mainly: integer type, floating-point type, arbitrary precision numeric value, date-time type, string type, binary type, Boolean type and array type.

1. Integer type

Postgresql provides a variety of numerical data types, different data types provide different ranges of values, the larger the range of values that can be stored, the more storage space will be 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 Murray Murray 32767

Int normal size integer 4 bytes-2147483648Murray Murray 2147483647

Bigint large Integer 8 Bytes-9223372036854775808 Meli Meli 9223372036854775807

2. Floating point number type:

Floating-point numbers are used in postgresql to represent decimals. There are two 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. Any precision type

In postgresql, numeric is used to indicate that the type of arbitrary precision is numeric, and it is represented by numeric (mdirection n), where m is called precision, which represents the total number of digits, and n is called scale, which is the number of decimal places. For example, the precision of 563.129 is 9 and the scale is 3.

Note: the range of valid values for numeric is determined by the values of M and D. If you change M and fix D, then the range of values will increase with the increase of M. In addition, if the precision specified by the user exceeds the precision, it is rounded for processing. As follows:

Testdb=# create table emp (t numeric (5pr 1), y numeric (5pr 3))

Testdb=# insert into emp values (9.12pc9.156)

Testdb=# select * from emp

T | y

-+-

9.1 | 9.156

4. Date and time type:

There are a variety of date data types in postgresql, mainly time, date, timestamp, and interval. Each type has a legal range of values, and the system inserts a "zero" value into the database when a truly illegal value is specified. The following types are described:

"Type name", "meaning", "date range", "Storage requirements", "Storage format"

Time is only used in the time of day 00VlV 0000MULLY 24Rose 0000Rose 00 8-byte HH:MM:SS

Date is only used for date 4713BC---58784897AD 4-byte YYYY-MM-DD

Timestamp date and time 4713BC---58784897AD 8-byte YYYY-MM-DD HH:MM:SS

Note: time and timestamp type, default is without time zone (without time zone); if necessary, can be set to with time zone (with time zone)

4.1C time: type usage

The time type is used for time and requires 8 bytes for storage in the format: HH:MM:SS. HH stands for hours, MM for minutes, and SS for seconds.

Example:

Testdb=# create table test (t time)

Testdb=# insert into test values ('11:10:25'), ('23 45');-notice 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 inserted correctly, 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 you can see from the above, the time zone is displayed.

4.2. Date: type

When the date type is used in a date value, there is no time part, and 4 bytes are required for storage. The date format is: 'YYYY-MM-DD', where YYYY represents year, MM represents month, DD represents day;. When assigning values to fields of type date, you can insert data of either a string type or a numeric type, as long as it conforms to the date format of date.

①: a date in the format of "YYYY-MM-DD" or "YYYYMMDD" string. For example, enter '2018-12-31' or '20181231' and the date inserted into the database is 2018-12-31.

②: a date in the format "YY-MM-DD" or "YYMMDD" string, where YY represents the annual value of two digits. Postgresql uses the following rules to interpret two-digit annual values: "00-69" conversion year value is "2000 murmur2069" conversion year value is "1970-1999" conversion year value is "2000-1999"

③: insert the current system date using 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 the current system 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 date and time values, leaving only the date portion when saved to the database.

4. 3, timestamp: type

The date format of 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 1111), (now ())

Testdb=# select * from emp

Ts

-

2018-08-10 11:00:02

2018-06-23 0514 purl 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')

INSERT 0 2

Testdb=# select * from emp

T

-

12:10:05-08

12:10:10-04

(2 rows)

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

5. String type:

String types are used to store string data. In addition to string data, you can also store other data, such as binary data for pictures and sounds. In addition to string comparisons that are case-sensitive or case-insensitive, pattern matching lookups can also be performed. The string types of postgresql include: char, varchar, and text.

"Type name"description"

Char (n) fixed length non-binary string, insufficient to fill in blanks

Varchar (n) variable length non-binary string with length limit

Text variable length non-binary string with no length limit

Note:

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 declared length, the value of type char will be filled with blanks, while the value of type varchar will only store shorter strings.

The white space filled in the char type is meaningless. For example, the white space filled in when comparing two char values will be ignored, and when converted to other string types, the white space in the charvalue will be deleted. Note that in the varchar and text values, the trailing white space is interesting.

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 the sql standard, many other sql database systems also have it.

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 the 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 the Boolean Boolean data type. Boolean is stored in 1 byte, providing two values: true (true) and false (false). In addition, users can replace true and false with other valid text values. The text value for the replacement of true is' tasking trueworthiness, and the text value for the substitution of false is' frankly accurate and accurate.

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 fields as one-dimensional or multi-dimensional arrays of fixed or variable length. The array type can be any basic type or user-defined type.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.