SQL Data Types

A column’s type decides what values it accepts and how arithmetic on them behaves. Each example below shows a type doing something a newcomer would not predict.

Numbers: Exact vs Approximate

NUMERIC stores exact decimal digits; double precision stores a binary approximation.

-- numeric.sql
SELECT 0.1::float8 + 0.2::float8   AS float_sum,
       0.1::numeric + 0.2::numeric AS numeric_sum,
       0.1::float8 + 0.2::float8 = 0.3 AS float_equal;

$ psql -d sqldemo -f numeric.sql
      float_sum      | numeric_sum | float_equal 
---------------------+-------------+-------------
 0.30000000000000004 |         0.3 | f
(1 row)

0.1 has no exact binary representation, so the float sum is off in the 17th digit and the equality test fails. For money, quantities, and anything compared with =, use NUMERIC. Floats are for measurements where tiny relative error does not matter.

Integers Have Limits

INTEGER is 4 bytes and stops at 2,147,483,647.

-- overflow.sql
SELECT 2147483647::int + 1 AS int_plus_one;
SELECT 2147483647::bigint + 1 AS bigint_plus_one;

$ psql -d sqldemo -f overflow.sql
psql:overflow.sql:2: ERROR:  integer out of range
 bigint_plus_one 
-----------------
      2147483648
(1 row)

PostgreSQL raises an error instead of wrapping around to a negative number, which is safer than silent corruption, but a busy table with an INTEGER id can still hit this ceiling. BIGINT (8 bytes) goes to about 9.2 quintillion and is the usual choice for ids.

Text Types

TEXT has no length limit; VARCHAR(n) adds a check; CHAR(n) pads with spaces.

-- text_types.sql
CREATE TABLE codes (short VARCHAR(5), padded CHAR(5), free TEXT);
INSERT INTO codes VALUES ('ab', 'ab', 'ab');
SELECT length(padded) AS char_length, padded || '|' AS padded_concat,
       octet_length(padded) AS bytes_stored
FROM codes;
INSERT INTO codes (short) VALUES ('toolong');

$ psql -d sqldemo -f text_types.sql
CREATE TABLE
INSERT 0 1
 char_length | padded_concat | bytes_stored 
-------------+---------------+--------------
           2 | ab|           |            5
(1 row)

psql:text_types.sql:7: ERROR:  value too long for type character varying(5)

CHAR(5) stored ab padded to five bytes, yet length reports 2 and the concatenation drops the padding, because PostgreSQL treats trailing spaces in CHAR as insignificant. That inconsistency is why CHAR(n) is rarely worth using. VARCHAR(5) rejects the 7-character value outright instead of truncating it.

TIMESTAMP vs TIMESTAMPTZ

The same literal means different things in the two timestamp types once the session’s time zone changes.

-- timestamps.sql
CREATE TABLE events (plain TIMESTAMP, with_zone TIMESTAMPTZ);
SET TIME ZONE 'UTC';
INSERT INTO events VALUES ('2024-06-01 12:00', '2024-06-01 12:00');
SET TIME ZONE 'Asia/Kolkata';
SELECT plain, with_zone FROM events;

$ psql -d sqldemo -f timestamps.sql
CREATE TABLE
SET
INSERT 0 1
SET
        plain        |         with_zone         
---------------------+---------------------------
 2024-06-01 12:00:00 | 2024-06-01 17:30:00+05:30
(1 row)

with_zone was stored as the instant 12:00 UTC, so a session in India sees 17:30 with a +05:30 offset: the same moment, shown locally. plain has no zone at all and still says 12:00, which in Kolkata is five and a half hours earlier than what the writer meant. Use TIMESTAMPTZ for anything that happened at a real moment.

Date Arithmetic

Subtracting dates gives days; adding an INTERVAL moves a timestamp.

-- date_math.sql
SELECT DATE '2024-03-01' - DATE '2024-02-01'          AS days_in_feb_2024,
       DATE '2024-01-31' + INTERVAL '1 month'         AS jan31_plus_month,
       age(DATE '2024-06-15', DATE '1990-02-20')      AS age;

$ psql -d sqldemo -f date_math.sql
 days_in_feb_2024 |  jan31_plus_month   |           age           
------------------+---------------------+-------------------------
               29 | 2024-02-29 00:00:00 | 34 years 3 mons 23 days
(1 row)

2024 is a leap year, so February has 29 days. Adding a month to January 31 cannot land on February 31, so PostgreSQL clamps it to the last day of February. age returns an interval in years, months, and days rather than a plain number of days.

Boolean

BOOLEAN accepts several spellings on input and prints t or f in psql.

-- boolean.sql
SELECT 'yes'::boolean AS yes, 'off'::boolean AS off, '1'::boolean AS one,
       NULL::boolean IS UNKNOWN AS null_is_unknown;

$ psql -d sqldemo -f boolean.sql
 yes | off | one | null_is_unknown 
-----+-----+-----+-----------------
 t   | f   | t   | t
(1 row)

A nullable boolean has three states, true, false, and unknown, so decide whether NULL is allowed before you start writing WHERE NOT flag.

JSON vs JSONB

JSON keeps the text you gave it; JSONB parses and normalises it.

-- json.sql
SELECT '{"b": 1, "a": 2, "a": 3}'::json  AS as_json,
       '{"b": 1, "a": 2, "a": 3}'::jsonb AS as_jsonb;

$ psql -d sqldemo -f json.sql
         as_json          |     as_jsonb     
--------------------------+------------------
 {"b": 1, "a": 2, "a": 3} | {"a": 3, "b": 1}
(1 row)

JSONB kept only the last "a", reordered the keys, and normalised spacing. That parsing is what lets JSONB be indexed and queried quickly; the JSON lesson goes further.

Arrays

Any type can be an array. PostgreSQL arrays start at index 1.

-- arrays.sql
SELECT tags[1] AS first, tags[0] AS zeroth, array_length(tags, 1) AS n,
       'sql' = ANY(tags) AS has_sql
FROM (SELECT ARRAY['sql', 'postgres', 'db'] AS tags) t;

$ psql -d sqldemo -f arrays.sql
 first | zeroth | n | has_sql 
-------+--------+---+---------
 sql   |        | 3 | t
(1 row)

tags[0] is not an error, it is NULL: reading outside the array returns nothing. Arrays suit small lists read with the row, like tags; if you need to join or constrain the elements, a separate table is usually better.

Casting

::type and CAST(x AS type) convert values; a bad conversion is an error, not a guess.

-- casting.sql
SELECT '42'::int + 1 AS casted, CAST('2024-02-29' AS date) AS leap_day;
SELECT 'abc'::int;

$ psql -d sqldemo -f casting.sql
 casted |  leap_day  
--------+------------
     43 | 2024-02-29
(1 row)

psql:casting.sql:3: ERROR:  invalid input syntax for type integer: "abc"
LINE 1: SELECT 'abc'::int;
               ^

The first two casts are valid. 'abc' cannot become an integer, and PostgreSQL shows you the exact input it rejected.

Practice

1. Show that NUMERIC keeps exact cents when you add 0.10 ten times.

-- practice_cents.sql
SELECT sum(0.10::numeric) AS numeric_total, sum(0.10::float8) AS float_total
FROM generate_series(1, 10);

$ psql -d sqldemo -f practice_cents.sql
 numeric_total |    float_total     
---------------+--------------------
          1.00 | 0.9999999999999999
(1 row)

2. Display the with_zone value from timestamps.sql in New York time.

-- practice_ny.sql
SELECT with_zone AT TIME ZONE 'America/New_York' AS new_york_clock FROM events;

$ psql -d sqldemo -f practice_ny.sql
   new_york_clock    
---------------------
 2024-06-01 08:00:00
(1 row)

3. Add 30 days and 1 month to January 31, 2024, and compare the results.

-- practice_month_vs_days.sql
SELECT DATE '2024-01-31' + 30                  AS plus_30_days,
       (DATE '2024-01-31' + INTERVAL '1 month')::date AS plus_1_month;

$ psql -d sqldemo -f practice_month_vs_days.sql
 plus_30_days | plus_1_month 
--------------+--------------
 2024-03-01   | 2024-02-29
(1 row)