Data Types in SQL

A column's data type defines which values it accepts, how they are stored and compared, and which operations apply to them. Standard groups: numbers (exact integers and numeric, approximate real / double precision), strings (char, varchar, text), date and time (date, time, timestamp, timestamptz, interval) and boolean. PostgreSQL adds uuid, jsonb, arrays, bytea for binary data, enums and more. Type names and limits differ between databases; this section uses PostgreSQL.

Applies to: PostgreSQL 18.

Details

Numbers

Type	Size	Range or precision	Use for
smallint	2 bytes	−32,768 to 32,767	Small counters
integer (int)	4 bytes	About ±2.1 billion	Most integers
bigint	8 bytes	About ±9.2 × 10¹⁸	IDs of large tables, counters
numeric(p, s) (decimal)	Variable	Exact, up to 131,072 digits before the point	Money, anything that must add up exactly
real, double precision (float)	4, 8 bytes	Approximate, 6 and 15 significant digits	Measurements, scientific values

• Floating-point types cannot store most decimal fractions exactly: 0.1 + 0.2 is 0.30000000000000004 in double precision and 0.3 in numeric.
• Overflow is an error, not a wraparound: 2147483647::integer + 1 fails with integer out of range.

Auto-Generated IDs

Option	Notes
integer GENERATED ALWAYS AS IDENTITY	SQL standard; rejects manually inserted values unless OVERRIDING SYSTEM VALUE
GENERATED BY DEFAULT AS IDENTITY	Allows explicit values
serial, bigserial	Older PostgreSQL shorthand for an integer with a sequence; prefer identity
uuid DEFAULT uuidv7()	Globally unique, can be generated by the client; v7 is time-ordered, which keeps indexes compact, but reveals the creation time (PostgreSQL 18)

Strings

Type	Behavior
char(n)	Fixed length, padded with spaces; rarely useful
varchar(n)	Up to n characters; storing a longer value is an error (an explicit ::varchar(n) cast truncates)
text	Any length

In PostgreSQL text and varchar have the same performance; varchar(n) is only a length check. MySQL and SQL Server differ here.

Date and Time

Type	Stores
date	Calendar date: 2029-02-14
time	Time of day without a date
timestamp	Date and time without a time zone: stored and returned as written
timestamptz	A moment in time: converted to UTC on input, shown in the session's time zone
interval	A duration: interval '1 day 2 hours'

Use timestamptz for events (created at, paid at). timestamp fits only "wall clock" values that must not move between time zones, such as a store's opening hour.

Examples

CREATE TABLE events (
  local_time timestamp,
  happened_at timestamptz
);

SET TIME ZONE 'UTC';
INSERT INTO events VALUES ('2029-02-14 08:47', '2029-02-14 08:47');
SELECT * FROM events;
-- local_time          | happened_at
-- 2029-02-14 08:47:00 | 2029-02-14 08:47:00+00

SET TIME ZONE 'America/New_York';
SELECT * FROM events;
-- local_time          | happened_at
-- 2029-02-14 08:47:00 | 2029-02-14 03:47:00-05   <- the same moment, shown in New York time

Other Types

Type	Notes
boolean	true, false or NULL; accepts 'yes', '1', 't' as input
jsonb	Parsed binary JSON: indexable (GIN), supports ->, ->>, @>; keys are deduplicated and reordered
json	Stored as text exactly as given; parsed on every access
integer[], text[]	Arrays of any type
bytea	Binary data; large files usually go to object storage, with a URL in the table
Enum (CREATE TYPE mood AS ENUM (…))	A fixed list of values; adding a value needs ALTER TYPE

Examples

SELECT '{"tags": ["a", "b"], "price": 10}'::jsonb -> 'tags' ->> 0;  -- a
SELECT '{"tags": ["a", "b"]}'::jsonb @> '{"tags": ["a"]}';           -- true
SELECT '{"b": 1, "a": 2, "a": 3}'::jsonb;                            -- {"a": 3, "b": 1}

Follow-up Questions

• Why not store money in float? Binary floating point cannot represent most decimal fractions, so sums drift by fractions of a cent. Use numeric or integer cents.
• timestamp or timestamptz? timestamptz for anything that happened at a specific moment; it is converted to UTC when stored and shown in the session's time zone.
• json or jsonb? jsonb almost always: it can be indexed and queried efficiently. json only when the exact original text (key order, duplicates) must be kept.
• Auto-increment id or UUID? Integers are smaller and faster; UUIDs can be generated anywhere and do not reveal the number of rows. UUIDv7 keeps inserts ordered.
• When is a jsonb column a bad idea? When its fields are always the same and are filtered, joined or constrained: then they should be normal columns.

Common Mistakes

• Storing money in real or double precision.
• "timestamp stores the time zone": in PostgreSQL neither type stores the zone; timestamptz stores a UTC moment and converts on output.
• "varchar(255) is faster than text": in PostgreSQL it is not.
• Using serial in new schemas instead of identity columns.

See Also

• Constraints
• NoSQL Databases