Find odd numbers in SQL using PostgreSQL

In PostgreSQL, use mod(value, 2) <> 0 to check whether an integer value is odd. This tutorial uses the PostgreSQL dialect and a bigint input, so the expression has a defined integer domain. SQL syntax and numeric conversions can differ in other database systems.

Check a PostgreSQL integer fixture

WITH samples(value) AS (
  VALUES
    (-15::bigint),
    (-14::bigint),
    (0::bigint),
    (15::bigint),
    (NULL::bigint)
)
SELECT value, mod(value, 2) <> 0 AS is_odd
FROM samples;

The negative and positive fifteens produce true; negative fourteen and zero produce false. The missing value produces NULL. That last outcome is useful: missing data has not become a successful even-number check.

PostgreSQL supports both the mod function and the % remainder operator for appropriate numeric types. Its mathematical functions reference documents them. Comparing the remainder with zero handles negative odd inputs without assuming that the remainder must be positive one.

Filter odd IDs and label missing values

To select rows from a table whose ticket_id column is an integer, put the condition directly in the filter:

SELECT ticket_id, title
FROM tickets
WHERE mod(ticket_id, 2) <> 0
ORDER BY ticket_id;

A WHERE condition keeps rows where its result is true. Rows with a NULL identifier do not pass this predicate. If a report must label missing values, use a separate CASE branch rather than silently converting NULL to zero:

SELECT ticket_id,
       CASE
         WHEN ticket_id IS NULL THEN 'missing'
         WHEN mod(ticket_id, 2) <> 0 THEN 'odd'
         ELSE 'even'
       END AS parity
FROM tickets;

Choose an exact integer domain

Choose the column type before choosing the parity expression. The numeric type documentation distinguishes exact integer types from decimal and floating-point representations. Applying a nonzero remainder test to an unrestricted fractional value does not establish that it is an odd integer. Avoid casting a decimal column merely to force the expression to run; that conversion changes the value being classified.

Distinguish stored IDs from row positions

Odd identifier values also differ from every other row in a result set. IDs can have gaps, and ordering can change. If the task is alternating rows, assign row numbers using a deliberate, stable ordering and test those row numbers instead. The ordering should include a unique tie breaker when equal sort keys are possible.

Use a small fixture containing negative, positive, zero, and NULL values when checking a report. If identifiers are imported from text, validate their conversion before loading or classify import failures separately. Finally, inspect the real query plan before adding an index for parity: the usefulness of such an index depends on the query and the distribution of the data.

A reporting label should describe the stored value, and an import-error label should describe the failed conversion. Keeping them separate makes anomalous records easier to investigate.