PostgreSQL inserts strings with spaces. For details, see the image.

Unsolved. In the study

 

Solution:

Data Type problems:

Table 8-4. Character Type

Name

Description

Character varying (N), Varchar (N)

Variable Length with length restrictions

Character (N), Char (N)

Fixed Length, insufficient fill blank

Text

Variable Length, no length limit

SQL defines two basic character types:Character varying (N)AndCharacter (N), HereNIs a positive integer. Both types can be stored at mostNCharacter string. An error occurs when you try to store a longer string to these types of fields unless the characters that exceed the length are blank. In this case, the string is truncated to the maximum length. This exception seems a little weird because it is required by the SQL standard. If the string to be stored is shorter than the declared length, the type isCharacter
And the type isCharacter varyingThe value will only store shorter strings.

If we explicitly convert a valueCharacter varying (N)OrCharacter (N), The extra-long value will be truncatedNCharacters, and no error is thrown. This is also the requirement of SQL standards.

Varchar (N)AndChar (N)They areCharacter varying (N)AndCharacter (N)Is not declared LengthCharacterEqual
Character (1)If no length is specifiedCharacter varying, Then this type accepts any length string. The latter is an extension of PostgreSQL.

In addition, PostgreSQL providesTextType, which can store strings of any length. Although the typeTextIt is not an SQL standard, but it is also available in many other SQL database systems.

CharacterPhysically, All numeric values of the type are filled with white space to the specified length.NStore and display data in this way. However, the padding space is silent. When comparing twoCharacterWhen the value is specified, the white space is not followed. When converting to another string type,CharacterThe white space in the value will be deleted. Note thatCharacter varyingAndTextIn the value, the white space at the end isSemantic.

The storage requirements of these types are 4 bytes plus the actual string.CharacterThen add the padding byte. Long strings will be automatically compressed by the system, so there may be less physical requirements on the disk. Long values are also stored in the background table, so that they do not interfere with quick access to short field values. In any case, the maximum string that can be stored is about 1 GB. Allowed to appear in the Data Type DeclarationNThe maximum value is smaller than this. Modifying this behavior makes no sense, because the number of characters and the number of bytes in Multi-byte encoding may vary greatly. If you want to store long strings with no specific upper limit, useText
Or if there is no length declarative wordCharacter varyingInstead of selecting any length limit.

Tip]There is no performance difference between these three types, but they are usedCharacterThe storage size is increased. Although in some other database systems,Character (N)It has certain performance advantages, but not in PostgreSQL. In most casesTextOrCharacter varying

 

 

By the way:

 

 

Name aliases description

Bigint	Int8	Signed 8-byte integer
Bigserial	Serial8	Auto-increment 8-byte integer
Bit [(N )]		Fixed Length string
Bit varying [(N )]	Varbit	Variable-length string
Boolean	Bool	Logical Boolean value (true/false)
Box		Rectangle in the plane
Bytea		Binary data ("byte array ")
Character varying [(N )]	Varchar [(N )]	Variable-length string
Character [(N )]	Char [(N )]	Fixed Length string
CIDR		IPv4 or IPv6 network address
Circle		Circle in the plane
Date		Calendar date (year, month, day)
Double Precision	Float8	Double-precision floating point number (8 bytes)
Inet		IPv4 or IPv6 host address
Integer	Int,Int4	Signed 4-byte integer
Interval [Fields ] [(P)]		Interval
Line		Infinite upper line in a plane
Lseg		Line Segments in a plane
Macaddr		MAC address
Money		Currency amount
Numeric [(P , S )]	Decimal [(P , S )]	Optional accurate numbers
Path		Geometric path in the plane
Point		Point in the plane
Polygon		Closed geometric path in the plane
Real	Float4	Single-precision floating point number (4 bytes)
Smallint	Int2	Signed two-byte integer
Serial	Serial4	Auto-increment 4-byte integer
Text		Variable-length string
Time [(P )] [Without time zone]		Time in a day (excluding the time zone)
Time [(P )] With Time Zone	Timetz	Start time, including the time zone
Timestamp [(P )] [Without time zone]		Date, time, and timestamp (excluding the time zone)
Timestamp [(P )] With Time Zone	Timestamptz	Date, time, timestamp, Including the time zone
Tsquery		Text Search and query
Tsvector		Text Search document
Txid_snapshot		User-level transaction ID Snapshot
UUID		UUID
XML		XML data

 

Detailed description:

PostgreSQL data type ------- conversion from others

Table 8-2. Numerical type

Name

Storage space

Description

Range

Smallint

2 bytes

Small integer

-32768 to + 32767

Integer

4 bytes

Common Integers

-2147483648 to + 2147483647

Bigint

8 bytes

A large integer

-9223372036854775808 to 9223372036854775807

Decimal

Variable Length

Precise and precise user declaration

Unlimited

Numeric

Variable Length

Precise and precise user declaration

Unlimited

Real

4 bytes

Precision changing, inaccurate

6-digit decimal number precision

Double Precision

8 bytes

Precision changing, inaccurate

15-digit decimal number

Serial

4 bytes

Auto-incrementing integer

1 To 2147483647

Bigserial

8 bytes

A large range of auto-incrementing Integers

1 To 9223372036854775807

The numeric type can store up to 1000-bit precision numbers for accurate computation. We particularly recommend that you use it for monetary amount and other situations requiring precise calculation. However, the arithmetic operation on the numeric type is much slower than the integer type or the floating point type described in the next section.

In the subsequent content, we will use the following term: a numeric-typeScale)Is the number of digits in the decimal part,Precision)Is the number of all data bits, that is, the total number of digits on both sides of the decimal point. Therefore, the accuracy of the number 23.5141 is 6 and the scale is 4. You can consider the integer scale to zero.

The maximum precision and scale of the numeric field can be configured. To declare a field of the numeric type, you can use the following syntax:

Numeric (Precision,Scale)

The precision must be positive, and the scale can be zero or positive. In addition,

Numeric (Precision)

The scale is 0. Declaration without any accuracy or scale

Numeric

Create a value that can store any precision and scale until the maximum precision is reached. A field of this type will not convert the input value to any specific scale, the numeric field with a scale declaration converts the input value to this scale. The SQL standard requires that the default scale is 0 (that is, the precision of converting to an integer ). We think this is useless. If you care about portability, you 'd better always declare accuracy and scale clearly.

If the scale of a value to be stored is higher than the scale declared by the field, the system will try to round (rounding) the value to the specified decimal place. Then, if the number of digits on the left of the decimal point exceeds the declared precision minus the declared scale, an error is thrown.

Numeric data values are physically stored without any leading or suffix. Therefore, the precision and scale declared on the field are both the maximum value, rather than the fixed allocation. In this respect, the numeric type is more similar to varchar (N) Instead of char (N). The actual storage is two bytes per four decimal digits, and then the additional overhead of eight bytes is added to the entire data.

In addition to common numeric values, the numeric type allows the use of special values Nan to indicate "not a number ". Any operation on Nan generates another Nan. If you write these values as a constant in an SQL command, you must put single quotation marks around them, such as update table set X = 'nan '. The Nan string is case-insensitive during input.

Decimal and numeric are equivalent. Both types are SQL standards.

Table 8-4. Character Type

Name

Description

Character varying (N), Varchar (N)

Variable Length with length restrictions

Character (N), Char (N)

Fixed Length, insufficient fill blank

Text

Variable Length, no length limit

SQL defines two basic character types:Character varying (N)AndCharacter (N), Here
NIs a positive integer. Both types can be stored at mostNCharacter string. An error occurs when you try to store a longer string to these types of fields unless the characters that exceed the length are blank. In this case, the string is truncated to the maximum length. This exception seems a little weird because it is required by the SQL standard. If the string to be stored is shorter than the declared length, the type is
CharacterAnd the type isCharacter varyingThe value will only store shorter strings.

If we explicitly convert a valueCharacter varying (N)OrCharacter (N), The extra-long value will be truncated
NCharacters, and no error is thrown. This is also the requirement of SQL standards.

Varchar (N)AndChar (N)They areCharacter varying (N)And
Character (N)Is not declared LengthCharacterEqualCharacter (1)If no length is specified
Character varying, Then this type accepts any length string. The latter is an extension of PostgreSQL.

In addition, PostgreSQL providesTextType, which can store strings of any length. Although the typeTextIt is not an SQL standard, but it is also available in many other SQL database systems.

CharacterPhysically, All numeric values of the type are filled with white space to the specified length.NStore and display data in this way. However, the padding space is silent. When comparing two
CharacterWhen the value is specified, the white space is not followed. When converting to another string type,CharacterThe white space in the value will be deleted. Note that
Character varyingAndTextIn the value, the white space at the end isSemantic.

The storage requirements of these types are 4 bytes plus the actual string.CharacterThen add the padding byte. Long strings will be automatically compressed by the system, so there may be less physical requirements on the disk. Long values are also stored in the background table, so that they do not interfere with quick access to short field values. In any case, the maximum string that can be stored is about 1 GB. Allowed to appear in the Data Type Declaration
NThe maximum value is smaller than this. Modifying this behavior makes no sense, because the number of characters and the number of bytes in Multi-byte encoding may vary greatly. If you want to store long strings with no specific upper limit, use
TextOr if there is no length declarative wordCharacter varyingInstead of selecting any length limit.

Tip]There is no performance difference between these three types, but they are usedCharacterThe storage size is increased. Although in some other database systems,Character (N)It has certain performance advantages, but not in PostgreSQL. In most cases
TextOrCharacter varying.

Table 8-9. date/time type

Name

Storage space

Description

Minimum value

Maximum Value

Resolution

Timestamp [(P)] [Without time zone]

8 bytes

Date and Time

4713 BC

5874897 ad

1 mS/14 bits

Timestamp [(P)] With Time Zone

8 bytes

Date and Time With Time Zone

4713 BC

5874897 ad

1 mS/14 bits

Interval [(P)]

12 bytes

Interval

-178000000 years

178000000 years

1 mS/14 bits

Date

4 bytes

Only for date

4713 BC

5874897 ad

1 day

Time [(P)] [Without time zone]

8 bytes

Used only in one day

00:00:00

24:00:00

1 mS/14 bits

Time [(P)] With Time Zone

12 bytes

It is only used in a day, with a time zone

00:00:00 + 1459

24:00:00-1459

1 mS/14 bits