0

I'm trying to use pg_dump --section=pre-data to suppress CHECK constraints in pg_dump's output, but I'm finding that it includes CHECK constraints anyway. Here are the steps I followed, first in psql:

CREATE DATABASE test_pre_data_check_db;
\connect test_pre_data_check_db
CREATE TABLE the_table (the_column INT CONSTRAINT the_check CHECK (the_column > 0));

And then in shell:

$ pg_dump --section=pre-data -d test_pre_data_check_db
--
-- PostgreSQL database dump
--

-- Dumped from database version 12.1
-- Dumped by pg_dump version 12.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: the_table; Type: TABLE; Schema: public; Owner: acolombi
--

CREATE TABLE public.the_table (
    the_column integer,
    CONSTRAINT the_check CHECK ((the_column > 0))
);


ALTER TABLE public.the_table OWNER TO acolombi;

--
-- PostgreSQL database dump complete
--

Is this a bug in pg_dump or am I doing it wrong?

Relevant documentation from Postgres:

--section=sectionname

Only dump the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to dump all sections.

The data section contains actual table data, large-object contents, and sequence values. Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items.

https://www.postgresql.org/docs/current/app-pgdump.html

Andrew
  • 529
  • 1
  • 3
  • 12

1 Answers1

1

Your quotation contains the answer:

--section=sectionname

The data section contains actual table data, large-object contents, and sequence values. Post-data items include definitions of indexes, triggers, rules, and constraints other than validated check constraints. Pre-data items include all other data definition items.

That says that validated check constraints are in the pre-data section, which is exactly what you observe.

This makes perfect sense: most constraints are in the post-data section because they are either faster when created after the data have been loaded (primary keys) or to avoid constraint violations while the data section is loaded (foreign keys). Check constraints don't fall in either category, since they are only allowed to depend on the table row itself.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263