PostgreSQL data types and casting

PostgreSQL goes far beyond standard SQL with powerful data types: JSONB for semi-structured data, arrays for lists within a single column, precise NUMERIC for financial calculations, and the versatile :: casting operator.

The :: casting operator, ARRAY types, and JSONB are PostgreSQL-specific. MySQL has JSON support but with different syntax. SQL Server uses CAST() and has no native arrays. The concepts transfer across databases, but the syntax differs.

pg_casting.sql
sql
-- PostgreSQL :: casting operator
SELECT
  price::INTEGER AS price_rounded,
  order_purchase_timestamp::DATE AS order_date,
  ROUND(AVG(price)::NUMERIC, 2) AS avg_price_precise
FROM order_items oi
JOIN orders o ON oi.order_id = o.order_id
GROUP BY price, order_purchase_timestamp
LIMIT 10;

-- NUMERIC precision for financial calculations
SELECT
  ROUND(SUM(price)::NUMERIC, 2) AS total_revenue,
  ROUND(AVG(price)::NUMERIC, 4) AS avg_price
FROM order_items;

The :: operator is PostgreSQL shorthand for CAST. Always use ::NUMERIC before ROUND for precise decimal output.

PostgreSQL goes beyond standard SQL with native array support. ARRAY_AGG lets you collect multiple values into a single array column.

pg_arrays.sql
sql
-- ARRAY_AGG: collect values into an array
SELECT
  o.order_id,
  ARRAY_AGG(p.product_category_name) AS categories
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id
HAVING COUNT(*) > 1
LIMIT 10;

ARRAY_AGG collects all values into a PostgreSQL array. Useful for seeing all items in a single order on one row.

PostgreSQL also has first-class JSONB support. You can build JSON objects from query results and query into JSON columns with special operators.

pg_jsonb.sql
sql
-- Build JSONB objects from query results
SELECT
  o.order_id,
  jsonb_build_object(
    'status', o.order_status,
    'items', COUNT(oi.order_item_id),
    'total', SUM(oi.price)
  ) AS order_summary
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.order_status
LIMIT 10;

-- JSONB operators: -> returns JSON, ->> returns text
-- SELECT data->'key' vs data->>'key'

jsonb_build_object creates JSON from columns. -> returns JSON type, ->> returns text. JSONB is binary and indexable.

Always use JSONB. It stores data in a binary format that is faster to query, can be indexed with GIN indexes, and supports containment operators (@>). JSON stores raw text and must be reparsed on every access. The only reason to use JSON is if you need to preserve exact formatting or key order.

Flashcards: Flashcards

Loading practice…

Quiz: Quiz

Loading practice…

Fill in the blanks: PostgreSQL types

Loading practice…

Code playground: JSON order summary

Loading practice…