PostgreSQL External Routine Invocation: Null Value Not Allowed (SQLSTATE 39004)

When PostgreSQL raises ERROR: null value not allowed with SQLSTATE 39004, the condition name is null_value_not_allowed in the External Routine Invocation Exceptions error class (class 39). This error means an external routine — one implemented in a procedural language that calls out of the PostgreSQL process, such as PL/Java, PL/R, or a C-language external function — received a NULL argument under circumstances where NULL input is not permitted.

What This Error Means

SQLSTATE class 39 covers errors that occur when PostgreSQL invokes an external routine and the routine itself or the invocation contract is violated. The 39004 sub-code specifically covers the case where a NULL value was passed to an external routine that cannot or must not receive one.

This is distinct from the more common 22004 (null_value_not_allowed, class 22 — Data Exception), which covers null violations inside built-in SQL operations. The 39 class errors are reserved for issues that arise at the language boundary between PostgreSQL's SQL engine and an external language runtime.

In practice, PostgreSQL's own PL/pgSQL uses STRICT as a declarative shorthand: if any argument is NULL, execution is short-circuited and NULL is returned immediately, never raising 39004. External language handlers (PL/Java, PL/R, PL/Python in some configurations, or custom C extensions registered as LANGUAGE c) are responsible for enforcing their own null semantics, and some handlers raise 39004 explicitly when a NULL reaches a point in the external code where it cannot be handled safely.

After this error is raised, the current transaction is aborted and must be rolled back before further work can proceed on that connection.

Common Causes

1. An external-language function (PL/Java, PL/R, etc.) enforces its own STRICT semantics and raises the error explicitly when it receives a NULL argument rather than relying on PostgreSQL's built-in STRICT short-circuit.

2. A C-language extension function calls ereport(ERROR, ...) with ERRCODE ERRCODE_NULL_VALUE_NOT_ALLOWED (39004) because the underlying C logic cannot handle a NULL datum at that parameter position.

3. A PL/Python or PL/Perl function written with explicit null checks raises a custom error that maps to this SQLSTATE, intending to communicate that NULL input is invalid for that function's contract.

4. A foreign data wrapper (FDW) or procedural language handler propagates this SQLSTATE from a remote system or runtime that itself rejects NULL in a particular context.

How to Fix null_value_not_allowed

1. Filter NULLs before calling the external function. Use COALESCE or a CASE expression at the call site to substitute a safe default or skip the call entirely:

   -- Instead of calling directly with a potentially NULL value:
   SELECT my_external_func(some_column) FROM my_table;
   
   -- Guard the call:
   SELECT
     CASE
       WHEN some_column IS NOT NULL THEN my_external_func(some_column)
       ELSE NULL
     END
   FROM my_table;
   
   -- Or use COALESCE to supply a default:
   SELECT my_external_func(COALESCE(some_column, '')) FROM my_table;
   

2. Declare the function with STRICT (also written RETURNS NULL ON NULL INPUT) in the CREATE FUNCTION DDL. This tells PostgreSQL to skip execution and return NULL automatically when any argument is NULL, before the external runtime is ever invoked:

   CREATE OR REPLACE FUNCTION my_external_func(input text)
   RETURNS text
   LANGUAGE java
   STRICT
   AS 'com.example.MyClass.myMethod';
   

   If the function already exists, alter it:

   ALTER FUNCTION my_external_func(text) STRICT;
   

3. Review the external function's source code. If you own the PL/Java, PL/R, or C extension code, add a null guard at the entry point rather than letting NULL propagate into logic that cannot handle it. For PL/Java, check for null before dereferencing; for C extensions, use PG_ARGISNULL(n) guards:

   /* C extension example */
   Datum my_func(PG_FUNCTION_ARGS)
   {
       if (PG_ARGISNULL(0))
           PG_RETURN_NULL();   /* or ereport an appropriate error */
       ...
   }
   

4. Wrap the call in a PL/pgSQL block to catch the error and handle it gracefully in application logic:

   DO $$
   BEGIN
     PERFORM my_external_func(some_value);
   EXCEPTION
     WHEN null_value_not_allowed THEN
       -- log or handle gracefully
       RAISE NOTICE 'Skipping null input for my_external_func';
   END;
   $$;
   

Additional Information

• SQLSTATE class 39 (External Routine Invocation Exception) is defined by the SQL standard; PostgreSQL implements it to maintain compatibility with external language handlers that may raise standard-conforming error codes.
• The more frequently encountered sibling code is 22004 (null_value_not_allowed, class 22), which covers null violations inside SQL/data operations and is unrelated to external routine invocation.
• Other class 39 codes in PostgreSQL include 39001 (prohibited_sql_statement_attempted) and 39P01 (trigger_protocol_violated).
• PL/pgSQL functions with CALLED ON NULL INPUT (the default) will never raise 39004 — they receive NULL and handle it in user code. Only external language handlers or C functions that explicitly raise this SQLSTATE will produce it.
• Most PostgreSQL client drivers (libpq, psycopg2, JDBC, node-postgres) surface this as a generic DatabaseError or SQLException with sqlstate="39004" in the error object; check the driver's error class hierarchy to catch it specifically.

Frequently Asked Questions

Why does PostgreSQL show SQLSTATE 39004 instead of a simpler "null not allowed" message? The 39 class specifically identifies the error as originating at the boundary between PostgreSQL and an external runtime (PL/Java, C extension, etc.). A plain NOT NULL constraint violation would produce SQLSTATE 23502, and a null in a built-in expression context would produce 22004. The 39 prefix tells you the problem is inside or at the interface to an external routine.

My function is declared CALLED ON NULL INPUT — why is it still raising 39004? CALLED ON NULL INPUT (the default) means PostgreSQL will invoke the function even when arguments are NULL. However, the external runtime code itself may check for NULL and raise 39004 explicitly. The declaration controls whether PostgreSQL short-circuits the call; it does not prevent the external function body from raising the error once it is called.

Can I reproduce 39004 in pure PL/pgSQL for testing? Yes, by raising it explicitly with RAISE EXCEPTION:

DO $$
BEGIN
  RAISE EXCEPTION USING ERRCODE = '39004';
END;
$$;

This is useful for integration testing error-handling code paths without needing the actual external runtime.

Is this error transactional — do I need to rollback after catching it? Yes. Like all PostgreSQL ERROR-level conditions, 39004 aborts the current transaction. Any subsequent statements on the same connection will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until you issue a ROLLBACK (or ROLLBACK TO SAVEPOINT if you used a savepoint before the call).