> For the complete documentation index, see [llms.txt](https://upsolver.gitbook.io/content/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/create-job/ingestion/postgresql.md). # PostgreSQL ## Syntax ```sql CREATE JOB [{ job_options }] AS COPY FROM POSTGRES [{ source_options }] INTO [ WITH EXPECTATION EXPECT ON VIOLATION { DROP | WARN } ]; ``` #### Jump to * [Job options](#job-options) * [Source options](#source-options) ## Job options {% code overflow="wrap" %} ```sql [ COLUMN_TRANSFORMATIONS = ( = , ...) ] [ COMMENT = '' ] [ COMPUTE_CLUSTER = ] [ END_AT = { NOW | } ] [ EXCLUDE_COLUMNS = ( , ...) ] [ HEARTBEAT_TABLE = '' ] [ PARSE_JSON_COLUMNS = { TRUE | FALSE } ] [ PUBLICATION_NAME = ('regexFilter1', 'regexFilter2') ] [ SKIP_SNAPSHOTS = { TRUE | FALSE } ] [ SNAPSHOT_PARALLELISM = ] ``` {% endcode %} #### Jump to PostgreSQL job options: * [`HEARTBEAT_TABLE`](#heartbeat_table) * [`PARSE_JSON_COLUMNS`](#parse_json_columns) * [`PUBLICATION_NAME`](#publication_name) * [`SKIP_SNAPSHOTS`](#skip_snapshots) * [`SNAPSHOT_PARALLELISM`](#snapshot_parallelism) General job options: * [`COLUMN_TRANSFORMATIONS`](/content/reference-1/sql-commands/jobs/create-job/ingestion.md#column_transformations) * [`COMMENT`](/content/reference-1/sql-commands/jobs/create-job/ingestion.md#comment-editable) * [`COMPUTE_CLUSTER`](/content/reference-1/sql-commands/jobs/create-job/ingestion.md#compute_cluster-editable) * [`END_AT`](/content/reference-1/sql-commands/jobs/create-job/ingestion.md#end_at-editable) * [`EXCLUDE_COLUMNS`](/content/reference-1/sql-commands/jobs/create-job/ingestion.md#exclude_columns) See also: * [`WITH EXPECTATION`](broken://spaces/j7NDvdvbkHS8wtX8aREE/pages/Y2QSVaw0n1NMmbDpVePG) #### **`HEARTBEAT_TABLE`** Type: `string` The name of the heartbeat table to use as described in [Setting up a Heartbeat Table](/content/how-to-guides-1/connectors/enable-cdc/postgresql.md#setting-up-a-heartbeat-table). (Optional) If it is not set, no heartbeat table is used. Using a heartbeat table is recommended to avoid the replication slot growing indefinitely when no CDC events are captured for the subscribed tables. #### `PARSE_JSON_COLUMNS` Type: `Boolean` Default: `false` If enabled, Upsolver will parse JSON columns into a struct matching the JSON value. #### `PUBLICATION_NAME` Type: `text` Adds a new publication to the current database. The publication name must be distinct from the name of any existing publication in the current database. DDL will be filtered. #### `SKIP_SNAPSHOTS` Type: `Boolean` Default: `false` (Optional) By default, snapshots are enabled for new tables. This means that Upsolver will take a full snapshot of the table(s) and ingest it into the staging table before it continues to listen for change events. When `True`, Upsolver will not take an initial snapshot and only process change events starting from the time the ingestion job is created. In the majority of cases, when you connect to your source tables, you want to take a full snapshot and ingest it as the baseline of your table. This creates a full copy of the source table in your data lake before you begin to stream the most recent change events. If you skip taking a snapshot, you will not have the historical data in the target table, only the newly added or changed rows. Skipping a snapshot is useful in scenarios where your primary database instance crashed or became unreachable, failing over to the secondary. In this case, you will need to re-establish the CDC connection but would not want to take a full snapshot because you already have all of the history in your table. In this case, you would want to restart processing from the moment you left off when the connection to the primary database went down. #### `SNAPSHOT_PARALLELISM` Type: `int` Default: `1` (Optional) Configures how many snapshots are performed concurrently. The more snapshots performed concurrently, the quicker the tables are streaming. However, doing more snapshots in parallel increases the load on the source database. ## Source Options ``` [ TABLE_INCLUDE_LIST = ('regexFilter1', 'regexFilter2') ] [ COLUMN_EXCLUDE_LIST = ('regexFilter1', 'regexFilter2') ] ``` #### `TABLE_INCLUDE_LIST` — editable Type: `text` Default: `''` (Optional) Comma-separated list of regular expressions that match fully-qualified table identifiers of tables whose changes you want to capture. Tables not included in this list will not be loaded. If the list is left empty all tables will be loaded. This maps to the [table.include.list](https://debezium.io/documentation/reference/2.0/connectors/mysql.html#mysql-property-table-include-list) property in Debezium. By default, the connector captures changes in every non-system table in all databases. To match the name of a table, Upsolver applies the regular expression that you specify as an *anchored* regular expression. That is, the specified expression is matched against the entire name string of the table. It does not match substrings that might be present in a table name. Each RegEx pattern matches against the full string `databaseName.tableName`, for example:
RegEx PatternResults
db_name.*Select all tables from the db_name database.
db_name.users, db_name.itemsSelect the users and items tables from the db_name database.
db1.items_.*Select all tables from db1 that start with items_.
#### `COLUMN_EXCLUDE_LIST` — editable Type: `array[string]` Default: `''` (Optional) Comma-separated list of regular expressions that match the fully-qualified names of columns to exclude from change event record values. This maps to the Debezium [column.exclude.list](https://debezium.io/documentation/reference/2.0/connectors/mysql.html#mysql-property-column-exclude-list) property. By default, the connector matches all columns of the tables listed in `TABLE_INCLUDE_LIST`. To match the name of a column, Upsolver applies the regular expression that you specify as an *anchored* regular expression. That is, the specified expression is matched against the entire name string of the column; it does not match substrings that might be present in a column name. Each RegEx pattern matches against the full string `databaseName.tableName.columnName`, for example:
RegEx PatternResults
db.users.address_.*Select all columns starting with address_ from the users table in the db database.
db.*.(.*_pii)Select all columns ending in _pii across all tables in the db database.
## Examples ### Ingest data into the data lake The following example creates a job to ingest data from PostgreSQL into a table in the data lake. The `PUBLICATION_NAME` option specifies that a new publication named **sample** is added. ```sql CREATE JOB load_orders_raw_data_from_postgres PUBLICATION_NAME = 'sample' AS COPY FROM POSTGRES upsolver_postgres_samples INTO default_glue_catalog.upsolver_samples.orders_raw_data; ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://upsolver.gitbook.io/content/reference-1/sql-commands/jobs/create-job/ingestion/postgresql.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.