The difference between Postgresql character variation and character
SQL Standard
SQL defines two basic character types: character varying(n) and character(n), where n is a positive integer. Both types can store strings of up to n characters.
Attempting to store longer strings into fields of these types produces an error unless characters beyond the length are blank, in which case the string will be truncated to the maximum length.
This weird-looking exception is required by the SQL standard. If the string to be stored is shorter than the declared length, the value of type character will be filled with blanks; while the value of type character varying will just store a shorter string.
If we explicitly convert a value to character varying(n) or character(n) , then the extra-long value will be truncated into n characters and no error will be thrown. This is also a requirement of the SQL standard.
postgreSQL character type
| Name | Description |
|---|---|
| character varying(n), varchar(n) | Longer, length limit |
| character(n), char(n) | Set length, not enough to fill the gap |
| text | Change length, no length limit |
Differences in length
varchar(n) and char(n) are alias for character varying(n) and character(n) respectively. Characters with no length are equal to character(1); character variation is not used with length, so this type accepts strings of any length. The latter is an extension of PostgreSQL.
Additionally, PostgreSQL provides the text type, which can store strings of any length. Although the text type is not the SQL standard, many SQL database systems have it.
Physical storage
Data of type character are physically filled with spaces (original: space) to the specified length n and are stored and displayed in this way. However, the filled blanks are speechless.
When comparing two character values, the filled blanks will be ignored. When converted to other string types, the spaces in the character value will be deleted. Note that in character varying and text types, the ending blanks are semantic.
These types of storage requirements are 4 bytes plus the actual string, and if it is a character plus padded bytes (the space mentioned above).
Long strings will be automatically compressed by the system, so there may be less physical requirements on disk. Long strings are also stored in the background table so that they do not interfere with quick access to short fields (my understanding is: disk addressing paths or fewer times? If you know, please point them out!!!). Regardless, the maximum string allowed to be stored is about 1GB.
Allows the maximum value of n that appears in the data type declaration to be less than that maximum. Changing this value is not very useful because the number of characters and bytes may be completely different when using multibyte character encoding.
If you want to store long strings without a specific upper limit, use text or character varying without length declaration words instead of setting length limits.
Performance differences
There is no performance difference between these three types, but the storage size is increased when using character.
Although character(n) has certain performance advantages in some other database systems, it is not in PostgreSQL.
In most cases, text or character variation should be used.
refer to:pg 8.2.23 Documentation
Postgresql's character varying = bytea problem
The issue of Java developing Postgresql database compatible applications is somewhat different from Oracle:
Java type maps different database types. Oracle jdbc driver handles that Java String types can be mapped to the database Number (Integer) normally, while Postgres will make an error.
In addition, it is a common error when using hibernate: operator does not exist: character varying = bytea problem, don't be misled by this description, it is not a comparison of type character varying = bytea in SQL statements, but when using parameter binding using hibernate and the value of the parameter is null, hibernate or Postgresql driver maps this parameter to varbinary type, Postgresql considers varbinary as bytea type
The above is personal experience. I hope you can give you a reference and I hope you can support me more.