Data Types in PostgreSQL
Every column in a table has a data type. The data type decides what kind of values the column can hold, like numbers, text or dates.
PostgreSQL is strict about data types. If a column is a number, PostgreSQL will not let you store text like 'hello' in it. This helps keep your data correct.
Common PostgreSQL Data Types
| Type | Description | Example |
|---|---|---|
| INTEGER (or INT) | Whole numbers | 25, 100, -7 |
| NUMERIC(p, s) | Exact decimal numbers | 19.99 |
| VARCHAR(n) | Text, up to n characters | 'Alice', 'HR' |
| TEXT | Text of any length | 'A long note...' |
| BOOLEAN | True or false | TRUE, FALSE |
| DATE | Calendar date | '2024-01-15' |
| TIMESTAMP | Date and time | '2024-01-15 14:30:00' |
Other Data Types
| Type | Description | Example |
|---|---|---|
| SMALLINT | Small whole numbers | -32768 to 32767 |
| BIGINT | Very large whole numbers | 9223372036854775807 |
| REAL, DOUBLE PRECISION | Approximate decimal numbers | 3.14159 |
| CHAR(n) | Text with a fixed length | 'Y', 'USA' |
| TIME | Time of day | '14:30:00' |
| TIMESTAMPTZ | Date and time with a time zone | '2024-01-15 14:30:00+00' |
| JSONB | JSON documents | '{"color": "red"}' |
| TEXT[] | A list (array) of text values | '{red,blue}' |
PostgreSQL also has some types that many other databases do not have, like JSONB and arrays. You will learn them at the end of the course.
Note: In PostgreSQL,
VARCHAR(n)andTEXTwork the same way. The only difference is thatVARCHAR(n)has a maximum length.
Example: Defining a Table with Data Types
This is how the employees table was created:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
department VARCHAR(50),
gender VARCHAR(50)
);
You will learn how to create tables later in the course. For now, notice that each column has a name and a data type.
Example: See a Table's Columns
psql has a special command, \d, that describes a table. It starts with a backslash and has no semicolon, because it is a psql command and not SQL:
\d employees
Output:
Table "public.employees"
Column | Type | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(50) | | |
age | integer | | |
department | character varying(50) | | |
gender | character varying(50) | | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (id)
You can see each column and its type. PostgreSQL shows the full type names: INT is shown as integer, and VARCHAR(50) as character varying(50).
Choosing the right data type helps PostgreSQL store your data correctly. Answer the questions below to check what you learned.