Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Use column selection to ingest only the table columns you need. This page covers the requirements that are specific to PostgreSQL.
Select columns to ingest
By default, the pipeline ingests all columns in the tables you select. To ingest only some columns, set include_columns or exclude_columns in the table configuration. For a description of each property and example pipeline definitions, see Select columns to ingest.
How the pipeline handles your column selection depends on the pipeline type:
- Ingestion gateway and ingestion pipeline: The pipeline ingests the columns you include, or all columns except the ones you exclude. It doesn't check whether your selection keeps the table's key columns, so keep the columns described in Required key columns selected.
- Integrated change data capture (CDC) pipeline: The pipeline also checks that your selection keeps the table's key columns. If it doesn't, the table fails to ingest. See Required key columns.
Required key columns
CDC keeps your destination table in sync by matching each change to a row using the source table's key. The connector picks the key in this order, and your column selection must keep all of that key's columns:
- Primary key: If the table has a primary key, keep all primary key columns. Unique key columns are optional.
- Unique key: If the table has no primary key, keep all columns of the first unique key whose columns are all
NOT NULL. Unique constraints come before unique indexes created withCREATE UNIQUE INDEX, and within each group, unique keys are ordered alphabetically by name. For example, if unique constraintsuk_emailanduk_phoneboth have onlyNOT NULLcolumns, the connector usesuk_email. To list a table's unique constraints and unique indexes, run\d <table>inpsql. - No usable key: If the table has neither, you can include or exclude any columns. A unique key with a nullable column doesn't count as a key, and neither do partial unique indexes or unique indexes on expressions.
To keep a column, list it in include_columns, or leave it out of exclude_columns.
In an integrated CDC pipeline, if your column selection drops a required key column, the table fails to ingest with the error INGESTION_GATEWAY_KEY_COLUMN_NOT_SELECTED. To recover, update include_columns or exclude_columns so that the key is fully selected, and then run a full refresh of the table. See Fully refresh target tables.
Change column selection for a table that is already ingesting
In an integrated CDC pipeline, you can edit include_columns or exclude_columns for a table at any time, and the change is saved to your pipeline. However, changing the column selection for a table that is already being ingested requires a full refresh of that table. If you change the selection and don't run a full refresh of the affected table in the same update, the pipeline update fails with the error CANNOT_CHANGE_COLUMN_SELECTION_WITHOUT_FULL_REFRESH.