PostgreSQL COUNT Function

The COUNT() function in PostgreSQL is an aggregate function that returns the number of rows or non-NULL values in a result set. It's one of the most commonly used functions for data analysis and reporting.

Syntax

-- Count all rows
COUNT(*)

-- Count non-NULL values in a column
COUNT(column_name)

-- Count distinct values
COUNT(DISTINCT column_name)

-- Count with filter
COUNT(*) FILTER (WHERE condition)

Official Documentation

Example usage

-- Count all rows
SELECT COUNT(*) AS total_users FROM users;

-- Count non-NULL values
SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS users_with_email,
    COUNT(phone) AS users_with_phone
FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT country) AS unique_countries
FROM users;

-- Count with GROUP BY
SELECT
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY status;

-- Count with conditions (FILTER clause)
SELECT
    COUNT(*) AS total_orders,
    COUNT(*) FILTER (WHERE status = 'completed') AS completed_orders,
    COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders,
    COUNT(*) FILTER (WHERE total_amount > 100) AS high_value_orders
FROM orders;

-- Count with HAVING
SELECT
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- Combined aggregations
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS total_orders,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
GROUP BY month
ORDER BY month DESC;

-- Conditional counting (legacy method)
SELECT
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM accounts;

Common issues

1. NULL handling: COUNT(column) ignores NULL values, while COUNT(*) counts all rows including those with NULLs.
2. Performance: COUNT(*) on large tables can be slow without proper indexing.
3. DISTINCT performance: COUNT(DISTINCT column) can be expensive on large datasets.
4. Empty result: COUNT(*) returns 0 for empty result sets, never NULL.

Best practices

1. Use COUNT(*) when you want the total number of rows, including NULLs.
2. Use COUNT(column) to count non-NULL values in a specific column.
3. For better performance on large tables, consider using approximate counts or materialized views.
4. Use FILTER clause (PostgreSQL 9.4+) instead of CASE WHEN for cleaner conditional counting.

Frequently Asked Questions

Q: What's the difference between COUNT(*) and COUNT(column)?
A: COUNT() counts all rows including those with NULL values. COUNT(column) counts only rows where the specified column is not NULL. For example, if a table has 100 rows but 10 have NULL emails, COUNT() returns 100 while COUNT(email) returns 90.

Q: How do I count unique/distinct values?
A: Use COUNT(DISTINCT column): SELECT COUNT(DISTINCT user_id) FROM orders. This counts the number of unique non-NULL values.

Q: Why is COUNT(*) slow on my large table?
A: PostgreSQL's MVCC architecture requires scanning the table to get an exact count. For approximate counts, use SELECT reltuples::BIGINT FROM pg_class WHERE relname = 'table_name'. Consider creating materialized views or counters for frequently counted queries.

Q: Can I count rows that match multiple conditions?
A: Yes, use the FILTER clause: COUNT(*) FILTER (WHERE status = 'active' AND created_at > '2025-01-01') or use CASE WHEN for older PostgreSQL versions.

Q: Does COUNT return NULL for empty results?
A: No, COUNT always returns a number. For empty result sets, it returns 0, never NULL.