Postgres Lab Starting database… Playground

PostgreSQL data types

Every column has a type, and choosing well prevents bad data and wasted space.

  • Integers: smallint (±32 thousand), integer (±2.1 billion), bigint (huge). Use bigint for ids on tables that could grow very large.
  • Exact decimals: numeric(p, s), with p digits in total and s after the point. Always use it for money.
  • Floating point: real, double precision. Fast but approximate, fine for measurements, never for money.
  • Text: text (any length), varchar(n) (with a length limit), char(n) (padded; rarely useful). In PostgreSQL text is just as fast as varchar.
  • Boolean: true, false, or NULL.
  • Date/time: date, time, timestamp, timestamptz, interval.
  • PostgreSQL specials: uuid, jsonb, arrays (text[]), inet/cidr for IP addresses, ranges (daterange), bytea for binary data, and your own ENUM and composite types.

Syntax

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
SELECT value::type, pg_typeof(value);

Examples

Edit the code and run it. Output is printed the way psql prints it.

15.1numeric vs floating point

SELECT 0.1::float8 + 0.2::float8 = 0.3 AS float_equal,
       0.1::numeric + 0.2::numeric = 0.3 AS numeric_equal,
       (0.1::float8 + 0.2::float8)::text AS float_sum;

Floats store binary approximations, so 0.1 + 0.2 isn’t exactly 0.3. numeric is exact, which is why money columns in this database use numeric(10,2).

15.2What type is it?

SELECT pg_typeof(42)              AS a,
       pg_typeof(42.0)            AS b,
       pg_typeof('hi'::text)      AS c,
       pg_typeof(now())           AS d,
       pg_typeof(ARRAY[1,2,3])    AS e,
       pg_typeof('{"a":1}'::jsonb) AS f,
       gen_random_uuid()          AS a_uuid;

Note that a literal like 42.0 is numeric, not float. gen_random_uuid() creates a random UUID, often used as a primary key that can be generated anywhere.

15.3ENUM types and ranges

DROP TABLE IF EXISTS tickets;
DROP TYPE IF EXISTS priority;
CREATE TYPE priority AS ENUM ('low', 'medium', 'high', 'urgent');

CREATE TABLE tickets (
  id       serial PRIMARY KEY,
  subject  text NOT NULL,
  level    priority NOT NULL DEFAULT 'medium',
  active   daterange
);

INSERT INTO tickets (subject, level, active) VALUES
  ('Login broken',   'urgent', '[2024-06-01,2024-06-03)'),
  ('Typo on page',   'low',    '[2024-06-02,2024-06-10)'),
  ('Slow checkout',  'high',   '[2024-06-05,2024-06-06)');

SELECT subject, level, active,
       active @> date '2024-06-02' AS open_on_jun_2
FROM tickets
ORDER BY level DESC;
  • An ENUM only accepts its listed values and sorts in declaration order, so urgent sorts above high.
  • daterange stores a span; [ includes the start and ) excludes the end.
  • @> means “contains”.
  • Try inserting a ticket with level 'critical' to see the error.