EC
2F002
PostgreSQLERRORNotableSQL Routine ExceptionHIGH confidence

modifying SQL data not permitted

What this means

SQLSTATE 2F002 is raised when a SQL function declared as CONTAINS SQL or READS SQL DATA attempts to modify data (INSERT, UPDATE, DELETE), which is not permitted by its declared data access category.

Why it happens
  1. 1A SQL function with CONTAINS SQL or READS SQL DATA attribute attempts to execute a DML statement that modifies data
How to reproduce

SQL function with READS SQL DATA attempting a write.

trigger — this will error
trigger — this will error
CREATE FUNCTION read_only_fn() RETURNS VOID
LANGUAGE SQL
READS SQL DATA
AS $ INSERT INTO log (msg) VALUES ('test'); $; -- modifies data

expected output

ERROR:  modifying SQL-data not permitted

Fix

Change the function to MODIFIES SQL DATA if data modification is intentional

WHEN When the function should be allowed to write data.

Change the function to MODIFIES SQL DATA if data modification is intentional
CREATE OR REPLACE FUNCTION my_fn() RETURNS VOID
LANGUAGE SQL
MODIFIES SQL DATA
AS $ INSERT INTO log (msg) VALUES ('test'); $;

Why this works

MODIFIES SQL DATA declares that the function may change data, allowing DML statements.

Sources
Official documentation ↗

Class 2F — SQL Routine Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors