SQL STRICT

What is SQL STRICT in PostgreSQL?

In PostgreSQL, the STRICT modifier on a user-defined function makes the function automatically return NULL if any argument it receives is NULL, skipping execution.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL STRICT: Supported: PostgreSQL (all modern versions), Amazon Redshift (as RETURNS NULL ON NULL INPUT). Not supported: MySQL, SQL Server, Oracle, SQLite.

SQL STRICT Full Explanation

STRICT is an optional modifier in PostgreSQL's CREATE FUNCTION statement. It is a shorthand for the longer phrase RETURNS NULL ON NULL INPUT. When a function is declared STRICT, PostgreSQL first checks every argument passed at runtime. If at least one argument is NULL, the function body is not executed and the result is immediately NULL. This behaviour reduces code inside the function, avoids unnecessary computation, and enforces predictable NULL-propagation semantics. STRICT applies to any language supported by CREATE FUNCTION (SQL, PL/pgSQL, C, etc.). It only affects runtime calls; it does not change how NULLs are stored. If you supply both STRICT and RETURNS NULL ON NULL INPUT, PostgreSQL treats them as the same. STRICT is incompatible with the CALLED ON NULL INPUT attribute (the default) and will cause a syntax error if both appear. Strictness is checked per call, so set-returning functions declared STRICT still yield zero rows when any argument is NULL.

SQL STRICT Syntax

CREATE OR REPLACE FUNCTION function_name (arg1 data_type, ...)
RETURNS return_type
LANGUAGE plpgsql
STRICT    -- or: RETURNS NULL ON NULL INPUT
AS $$
BEGIN
    -- function body only runs when all args are NOT NULL
END;
$$;

SQL STRICT Parameters

Example Queries Using SQL STRICT

-- 1. Simple STRICT function
CREATE OR REPLACE FUNCTION add_two(a integer, b integer)
RETURNS integer
LANGUAGE sql
STRICT
AS $$
  SELECT a + b;
$$;

SELECT add_two(3, 4);   -- returns 7
SELECT add_two(NULL, 4);-- returns NULL

-- 2. Equivalent verbose form
CREATE FUNCTION concat_strict(x text, y text)
RETURNS text
LANGUAGE sql
RETURNS NULL ON NULL INPUT
AS $$
  SELECT x || y;
$$;

SELECT concat_strict('foo', NULL); -- NULL

Expected Output Using SQL STRICT

For calls where every argument is NOT NULL, the function executes normally and returns its computed value
When any argument is NULL, PostgreSQL skips the function body and immediately returns NULL

Use Cases with SQL STRICT

Propagate NULLs without extra code inside functions
Guarantee that expensive functions are not executed with incomplete data
Enforce business rules that disallow partial input
Simplify SQL-language functions that merely combine arguments

Common Mistakes with SQL STRICT

Mixing STRICT with CALLED ON NULL INPUT, which causes a syntax error
Forgetting that STRICT prevents the function body from running, leading to debugging confusion
Assuming STRICT changes table data persistence—it only affects execution time
Expecting STRICT to work in non-PostgreSQL systems such as MySQL or SQL Server

First Introduced In

PostgreSQL 7.3

Frequently Asked Questions

What does STRICT actually check?

It checks whether **any** incoming argument is NULL. If at least one is NULL, PostgreSQL skips the function body and returns NULL.

Is STRICT faster than handling NULLs inside the function?

Yes. Because PostgreSQL avoids running the function at all, it saves the overhead of entering the function language interpreter or executing SQL inside it.

Can I safely combine STRICT with IMMUTABLE or STABLE?

Absolutely. STRICT is orthogonal to volatility classifications. Mark a function STRICT IMMUTABLE when it both propagates NULLs and has no side effects.

How do I remove strictness later?

Use CREATE OR REPLACE FUNCTION without the STRICT keyword or with CALLED ON NULL INPUT. The new definition replaces the old one.