Character types in Postgres
When working with strings in PostgreSQL, understanding the available character types is crucial. As a new user, let’s explore these types in more detail.
1. character varying(n)
(or varchar(n)
)
-
Description:
- Variable-length character type.
- Can store strings up to
n
characters (not bytes) in length. - Excess characters beyond the specified length result in an error unless they are spaces (in which case the string is truncated).
-
Example:
-
Suppose we want to create a
users
table to store usernames. First, let’s create the table: -
Now we can insert a username:
-
-
Use Case:
- Use
varchar
when you need flexibility in string length, such as for user-generated content.
- Use
2. character(n)
(or char(n)
)
-
Description:
- Fixed-length, blank-padded character type.
- Similar to
character varying(n)
but always pads with spaces.
-
Example:
-
Let’s create an
employees
table to store employee IDs: -
Insert an employee ID:
-
-
Use Case:
- Use
char
when you require fixed-length strings (e.g., employee IDs).
- Use
3. bpchar
(unlimited length, blank-trimmed)
-
Description:
- Similar to
char
, but without a specified length. - Accepts strings of any length, and trailing spaces are insignificant.
- Similar to
-
Example:
-
Create a
products
table for storing product codes: -
Insert a product code:
-
-
Use Case:
- Use
bpchar
when you want to trim trailing spaces.
- Use
4. text
(variable unlimited length)
-
Description:
- PostgreSQL’s native string data type.
- Stores strings of any length.
-
Example:
-
Let’s create an
articles
table for storing article content: -
Insert article content:
-
-
Use Case:
- Use
text
for general-purpose text storage.
- Use
Operations and Considerations:
- All character types support standard string functions (e.g.,
LENGTH
,SUBSTRING
,CONCAT
). - Performance considerations:
text
is the most flexible but may have slightly slower indexing.- Fixed-length types (
char
,bpchar
) are faster for exact-length lookups.
- As a new user, start with
text
orcharacter varying
unless you have specific requirements. Feel free to experiment with different types based on your application needs!
For more detailed information, consult the official PostgreSQL documentation. If you have further questions, feel free to ask us on our Discord!
Was this page helpful?