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

TypeDescriptionExample
INTEGER (or INT)Whole numbers25, 100, -7
NUMERIC(p, s)Exact decimal numbers19.99
VARCHAR(n)Text, up to n characters'Alice', 'HR'
TEXTText of any length'A long note...'
BOOLEANTrue or falseTRUE, FALSE
DATECalendar date'2024-01-15'
TIMESTAMPDate and time'2024-01-15 14:30:00'

Other Data Types

TypeDescriptionExample
SMALLINTSmall whole numbers-32768 to 32767
BIGINTVery large whole numbers9223372036854775807
REAL, DOUBLE PRECISIONApproximate decimal numbers3.14159
CHAR(n)Text with a fixed length'Y', 'USA'
TIMETime of day'14:30:00'
TIMESTAMPTZDate and time with a time zone'2024-01-15 14:30:00+00'
JSONBJSON 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) and TEXT work the same way. The only difference is that VARCHAR(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.