Horton

Add COPY (on_error set_null) option

Peter Eisentraut <peter@eisentraut.org>

View on postgr.es
Commit: 2a525cc97e19868940c533787165bc7e7de3a80a
Author: Peter Eisentraut <peter@eisentraut.org>
Date: 2026-03-03T06:37:12Z
Releases: 19 (unreleased) 
Add COPY (on_error set_null) option

If ON_ERROR SET_NULL is specified during COPY FROM, any data type
conversion errors will result in the affected column being set to a
null value.  A column's not-null constraints are still enforced, and
attempting to set a null value in such columns will raise a constraint
violation error.  This applies to a column whose data type is a domain
with a NOT NULL constraint.

Author: Jian He <jian.universality@gmail.com>
Author: Kirill Reshke <reshkekirill@gmail.com>
Reviewed-by: Fujii Masao <masao.fujii@oss.nttdata.com>
Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
Reviewed-by: "David G. Johnston" <david.g.johnston@gmail.com>
Reviewed-by: Yugo NAGATA <nagata@sraoss.co.jp>
Reviewed-by: torikoshia <torikoshia@oss.nttdata.com>
Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com>
Reviewed-by: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Reviewed-by: Matheus Alcantara <matheusssilv97@gmail.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Discussion: https://www.postgresql.org/message-id/flat/CAKFQuwawy1e6YR4S%3Dj%2By7pXqg_Dw1WBVrgvf%3DBP3d1_aSfe_%2BQ%40mail.gmail.com

Files

PathChange+/−
doc/src/sgml/monitoring.sgml modified +2 −2
doc/src/sgml/ref/copy.sgml modified +21 −9
src/backend/commands/copy.c modified +3 −4
src/backend/commands/copyfrom.c modified +42 −12
src/backend/commands/copyfromparse.c modified +75 −13
src/bin/psql/tab-complete.in.c modified +1 −1
src/include/commands/copyfrom_internal.h modified +7 −0
src/include/commands/copy.h modified +1 −0
src/test/regress/expected/copy2.out modified +58 −0
src/test/regress/sql/copy2.sql modified +43 −0

Discussion