PostgreSQL: Character Data Types (Text and Strings)
Explore PostgreSQL's various character data types, including VARCHAR, CHAR, and TEXT, and understand their differences and usage.
PostgreSQL Character Data Types
PostgreSQL offers several data types for storing character data (text). Choosing the right data type depends on whether you need fixed-length or variable-length strings and the maximum length of the strings you anticipate storing. This guide explores PostgreSQL's character data types and provides examples of their usage.
PostgreSQL Character Data Types
| Data Type | Description |
|---|---|
char(n) or character(n) |
Fixed-length string of length n. If the stored string is shorter than n, it is padded with spaces on the right to reach length n. If longer, it is truncated to length n. Defaults to char(1) if n is not specified. |
varchar(n) or character varying(n) |
Variable-length string, storing up to n characters. If n is not specified, there's no length limit (it becomes equivalent to the `text` type). |
text |
Variable-length string with no length limit. |
Example: Creating and Using a Table with Character Columns
(Note: Screenshots from the original text are not included here. Please refer to the original document for visual verification of the commands' success. The descriptions below aim to convey the information present in those screenshots.)
- Create Table: Create a table named `Char_demo` with columns `A` (
CHAR(1)) and `B` (CHAR(10)). - Insert Data: Attempt to insert data. You'll encounter errors if you try to insert strings longer than the defined column lengths (for `CHAR` types; `VARCHAR` will truncate).
- Correct Insert: Insert data with correctly-sized strings.
- Select Data: Use `SELECT` to retrieve all data.