PostgreSQL to PolarDB for Oracle Data Replication
When you create a PostgreSQL to PolarDB for Oracle replication task, the console provides schema, full, and incremental replication options. This page describes the current schema-plus-full replication flow.
Before you begin
Add the PostgreSQL source data source and the PolarDB for Oracle target data source to NineData, and make sure that the target data source is available. For instructions, see Add Data Source.
When you create the replication task, select PolarDB (compatible with Oracle) as the target data source type.
The replication task must be able to access both data sources. The required account privileges are determined by the precheck results when you create the task.
Procedure
Sign in to the NineData Console.
In the left navigation pane, click Replication > Data Replication.
On the Replication page, click Create Replication.
On the Source & Target tab, configure the fields in the table, and click Next.
Parameter Description Name Enter a name for the data synchronization task. To make the task easier to find and manage later, use a meaningful name. Up to 64 characters are supported. Source The data source that contains the objects to synchronize. Target The data source that receives the synchronized objects. Target DB Select the target database to which data is synchronized. Target Object Name Select the case conversion rule for object names after they are migrated from the source to the target. - Convert all to Lowercase: Regardless of the naming rule on the source, all target names are lowercase.
- Consistent with Source: Keep the naming rule of the source.
- Convert all to Uppercase: Regardless of the naming rule on the source, all target names are uppercase.
Type Select the replication type. - Schema: Synchronize only the database and table schemas of the source data source, without synchronizing data.
- Full: Synchronize all objects and data from the source data source, namely full data replication. The switch on the right enables periodic full replication. For more information, see Periodic Full Replication.
- Incremental: After full synchronization completes, perform incremental synchronization based on the logs of the source data source.The
icon is used to configure incremental operation types. Clear the operation types to exclude. Cleared operation types will be ignored during incremental synchronization.
Incremental Started Required only when Type is Incremental. - From Started: Use the current replication task start time as the baseline for incremental replication.
- Customized Time: Select the point in time from which incremental replication starts. Select a time zone based on the region of your business. If the configured time point is earlier than the current replication task start time and DDL operations occurred during that period, the replication task will fail.
Spec The specification of the replication task. A larger specification provides a higher replication rate. Hover over the icon to view the rate and configuration information of each specification. If you configure the data replication task before purchasing resources, you can select the required specification here. If you purchase resources before configuring the task, NineData selects the specification specified during purchase, and you cannot change it in the task configuration.
If target table already exists (Required when Schema is selected) - Pre-Check Error and Stop Task: Stop the task when a table with the same name is detected during the precheck stage.
- Skip and Continue Task: When a table with the same name is detected during the precheck stage, display a message and continue the task. During schema replication, ignore the table with the same name. If you also perform data replication, data is appended to the table with the same name and existing data is not overwritten.
- Delete Objects and Rewrite: When a table with the same name is detected during the precheck stage, display a message and continue the task. During schema replication, delete the table with the same name in the target database and replicate the table schema again based on the source database. If you also perform data replication, data is written after schema replication completes.
Target Table Exists Data (Required when Full is selected) - Pre-Check Error and Stop Task: Stop the task when data is detected in the target table during the precheck stage.
- Ignore existing target data and append to it.: When data is detected in the target table during the precheck stage, ignore that data and append other data.
- Clear target existing data before write: When data is detected in the target table during the precheck stage, delete that data and write it again.
Incremental data conflict handling strategy for target table (Required when Incremental is selected) - Runtime error: During incremental replication, report an error when target data already exists and wait for manual intervention.
- Do not update target data: During incremental replication, do not write data when target data already exists, and continue subsequent tasks.
- Update target data: During incremental replication, overwrite the target data when target data already exists.
On the Objects tab, configure the parameters in the table, and click Next.
Parameter Description Replication Objects Select the content to replicate. Select All Objects to replicate all content in the source database, or select Customized Object, select the content to replicate in the Source Object list, and click > to add it to the Target Object list on the right. To create multiple replication tasks with the same replication objects, import a configuration file. Click Import Config, click Download Template to download the template, edit the file, and then click Upload to upload it and import the objects in bulk. For field descriptions and examples, see Import Replication Object Templates. The configuration file uses these fields:
Parameter Description Example source_table_nameThe exact source table name of the object to synchronize. example_tbl_nametarget_table_nameThe target table name that receives the synchronized object. example_tbl_namesource_database_nameThe source database name of the object to synchronize. test_dbtarget_database_nameThe target database name that receives the synchronized object. test_db_bakcolumn_listThe list of columns to synchronize. ["col1", "clo2", "col3"]extra_configurationA JSON string containing additional configuration. Use column_rulesfor column mappings and value rules:column_nameis the source column,target_column_nameis the target column,target_column_typeis the target column type, andcolumn_valueis the column value. Usefilter_conditionfor row-level filtering.{"extra_config": {"column_rules": [...]}}tipThe following JSON represents the two example rows in the downloaded Excel template. When uploading the file, keep the Excel column names and enter the
extra_configurationvalue as a JSON string. This code block is not a JSON file that you can upload directly.[
{
"source_table_name": "example_tbl_name",
"target_table_name": "example_tbl_name",
"source_database_name": "test_db",
"target_database_name": "test_db_bak",
"column_list": ["col1", "clo2", "col3"],
"extra_configuration": {
"extra_config": {
"column_rules": [
{
"column_name": "example_column_name",
"target_column_name": "example_target_column_name",
"target_column_type": "int",
"column_value": "current_timestamp()"
},
{
"column_name": "example_column_name",
"target_column_name": "example_target_column_name",
"target_column_type": "int"
}
],
"filter_condition": "example_condition_expression"
}
}
},
{
"source_table_name": "example_order_tbl",
"target_table_name": "example_order_tbl",
"source_database_name": "test_db",
"target_database_name": "test_db_bak",
"column_list": ["col1", "clo2", "col3"],
"extra_configuration": {
"extra_config": {
"column_rules": [
{
"column_name": "example_column_name",
"target_column_name": "example_target_column_name",
"target_column_type": "int",
"column_value": "current_timestamp()"
},
{
"column_name": "example_column_name",
"target_column_name": "example_target_column_name",
"target_column_type": "int"
}
],
"filter_condition": "example_condition_expression"
}
}
}
]On the Mapping tab, configure the mapping that matches the selected replication type, then click Save and Pre-Check. If source or target metadata changes while you configure mappings, click Refresh Metadata to refresh the metadata.
Includes Schema: Configure the table name after synchronization to the target data source.
Does not include Schema: NineData selects the database with the same name in the target data source by default. If no such database exists, select the target database manually. The table names and column names in the target database must match the synchronization objects. If they do not match, map the table names and column names manually.
Other available actions:
- Click Mapping & Filtering to customize the column names after synchronization to the target data source.
- On the Mapping & Filtering page, enter a comparison expression in the text box below Data Filter as the filtering condition. Only data that meets the filtering conditions is synchronized to the target data source. For example, if the filtering condition is set to
emp_no>=10005, data whose emp_no column value is less than 10005 is not synchronized to the target data source. - Click the
icon to the right of "Target Table" to search for a table name and replace it with the target name.
- Enter a table name in the Search Table text box to quickly locate the target table.
- Click Batch Configuration to define common rules in batches, such as table name and column name case conversion, prefix or suffix addition, and replacement. Use this option to apply mapping configuration to many tables and columns at the same time.
- Click the drop-down menu to the right of Object Owner to specify the owner of the object. The available options and default value depend on what the current page displays.
- Click Set Tablespace on the page to specify the Tablespace for tables and indexes in the target data source. This controls the physical storage location of objects in the target database. If you do not configure it, the default Tablespace of the target database user is used.
On the Pre-check tab, wait for the precheck to finish. After it passes, open the saved task's details from the replication task list and start the task.
Select Enable data consistency comparison. Based on the selected Type, the system automatically starts a data consistency comparison based on the source data source at the corresponding time. The comparison checks whether the source and target data are consistent. The start time is as follows:
- Schema: Starts after schema replication completes.
- Schema+Full: Starts after full replication completes.
- Full: Starts after full replication completes.
- Schema+Full+Incremental, Incremental: Starts when incremental data is consistent with the source data source for the first time and Delay is 0 seconds. Click View Details to view synchronization delay on the Details page.

If the precheck fails, click Details in the Actions column for the failed check item, review the cause, fix the issue, and then click Check Again to run the precheck again until it passes.
Items with Warning in Result can be fixed or ignored if required.
After the replication task starts, you can perform the following actions:
- Click View Details to view the execution status of each stage of the synchronization task.
- Click Back to list to return to the Replication task list page.
Modify incremental synchronization positions
For a task whose replication type includes Incremental and whose task details provide position management, you can view and modify synchronization positions. The available position types and input format depend on the data source and replication link. Follow the options displayed for the current task. If a position is shown as - or no position type is available for selection, the current task has no position available for modification.
This feature is intended for specific operations and maintenance scenarios where you need to specify a new log-read position or synchronization-write position for an incremental task. The read position controls where the task continues reading from the source, while the write position controls where it continues processing synchronized writes. After you submit the change, the task pauses and restarts so that the new position can take effect.
In the replication task list, click the Task ID of the target task to open its details.
On the Incremental tab, click Synchronous site management to view the current read site and write site.
Click modify site. Under site type, select the read site or write site to adjust, and then enter the value for Adjustment point according to the position type displayed for the task.
Confirm the value and scope of impact, and click Verify and submit. The task enters a pause and relaunch process. After the task resumes running, open Synchronous site management again to confirm that the position has been updated.
Modifying the synchronization point will adjust the point of log reading or synchronization writing, which may cause data loss, so operate with caution! Modifying either position may cause data loss or inconsistency between the source and target. Perform this operation only in an authorized test environment or during a maintenance window with a backup and rollback plan. A task whose incremental replication has finished cannot have its position reset.
Data Type Mapping
The table applies to the listed source type declarations and parameter combinations from PostgreSQL 15.7 to PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode). Target types and catalog attributes correspond to the destination column metadata after replication. The table contains 124 entries: 44 core and parameterized types, 32 special types, 12 range and multirange types, 10 additional aliases and unbounded types, and 26 additional declarations and system catalog types. Non-NULL values were copied for the special-type and range batches; the other scalar mappings describe column types only and do not establish serial defaults or conversion of every value. Separate tables below list PostgreSQL 15.2 core types, additional built-in scalar and system types, built-in arrays, user-defined type examples, and manually repaired vector mappings; they are not merged with the 15.7 batch.
The additional types, system catalog types, and 17 supplemental declarations came from empty test tables. These results confirm only target column types and catalog attributes; they do not verify serial sequence/default behavior or value conversion for these empty-table batches.
The table above applies only to the listed PostgreSQL 15.7 source types; the following core-type and array tables record separate PostgreSQL 15.2 replication batches. Do not infer behavior for unlisted internal types, extensions, or user-defined types, or for other parameters.
The PostgreSQL 15 documentation states that pseudo-types cannot be used as table columns or as components of container types. Therefore, this mapping does not list replication mappings for pseudo-types. See Pseudo-Types and The PostgreSQL Type System.
The source type inventory follows the PostgreSQL 15 data types, array types, range types, object identifier types, pg_type system catalog, and the pg_index field definitions for int2vector and oidvector.
| PostgreSQL source type | PolarDB target column type | Target catalog attributes |
|---|---|---|
smallint | smallint (int2) | precision 16, scale 0 |
int2 | smallint (int2) | precision 16, scale 0 |
integer | integer (int4) | precision 32, scale 0 |
int | integer (int4) | precision 32, scale 0 |
int4 | integer (int4) | precision 32, scale 0 |
bigint | bigint (int8) | precision 64, scale 0 |
int8 | bigint (int8) | precision 64, scale 0 |
bigserial | bigint (int8) | precision 64, scale 0 |
serial8 | bigint (int8) | precision 64, scale 0 |
smallserial | smallint (int2) | precision 16, scale 0 |
serial2 | smallint (int2) | precision 16, scale 0 |
serial | integer (int4) | precision 32, scale 0 |
serial4 | integer (int4) | precision 32, scale 0 |
numeric | numeric | No precision or scale reported |
decimal | numeric | No precision or scale reported |
numeric(12,4) | numeric | precision 12, scale 4 |
decimal(12,4) | numeric | precision 12, scale 4 |
numeric(20,5) | numeric | precision 20, scale 5 |
decimal(20,5) | numeric | precision 20, scale 5 |
real | real (float4) | precision 24 |
float4 | real (float4) | precision 24 |
float(24) | real (float4) | precision 24 |
double precision | double precision (float8) | precision 53 |
float | double precision (float8) | precision 53 |
float8 | double precision (float8) | precision 53 |
float(25) | double precision (float8) | precision 53 |
boolean | boolean (bool) | — |
bool | boolean (bool) | — |
character(3) | character (bpchar) | length 3 |
char(3) | character (bpchar) | length 3 |
character(8) | character (bpchar) | length 8 |
char(8) | character (bpchar) | length 8 |
character | character (bpchar) | length 1 |
character varying(20) | character varying (varchar) | length 20 |
varchar(20) | character varying (varchar) | length 20 |
character varying(32) | character varying (varchar) | length 32 |
varchar(32) | character varying (varchar) | length 32 |
character varying | text | No maximum length reported |
bpchar | text | No maximum length reported |
text | text | — |
bytea | bytea | — |
date | date | — |
time | time without time zone (time) | datetime precision 6 |
time(3) without time zone | time without time zone (time) | datetime precision 3 |
time(3) with time zone | time with time zone (timetz) | datetime precision 3 |
timetz | time with time zone (timetz) | datetime precision 6 |
timestamp without time zone | timestamp without time zone (timestamp) | datetime precision 6 |
timestamp(3) without time zone | timestamp without time zone (timestamp) | datetime precision 3 |
timestamp(3) with time zone | timestamp with time zone (timestamptz) | datetime precision 6 (source declaration precision: 3) |
timestamptz | timestamp with time zone (timestamptz) | datetime precision 6 |
timestamptz(3) | timestamp with time zone (timestamptz) | datetime precision 6 |
interval | interval | The catalog does not report precision or scale |
interval(3) | interval | datetime precision 6 |
interval year to month | interval | The catalog reports the year to month field qualifier; no precision or scale is reported |
interval day to second(3) | interval | The catalog reports the day to second field qualifier; no precision or scale is reported |
bit(4) | bit | length 4 |
bit(8) | bit | length 8 |
bit | bit | length 1 |
bit varying(8) | bit varying (varbit) | length 8 |
varbit(8) | bit varying (varbit) | length 8 |
bit varying | bit varying (varbit) | No maximum length reported |
varbit | bit varying (varbit) | No maximum length reported |
varchar | text | No maximum length reported |
uuid | uuid | — |
json | json | — |
jsonb | jsonb | — |
jsonpath | jsonpath | — |
xml | xml | — |
money | money | — |
point | point | — |
line | line | — |
lseg | lseg | — |
box | box | — |
path | path | — |
polygon | polygon | — |
circle | circle | — |
cidr | cidr | — |
inet | inet | — |
macaddr | macaddr | — |
macaddr8 | character varying (varchar) | length 255 |
name | name | — |
"char" | character (bpchar) | length 1 |
tsquery | tsquery | — |
tsvector | tsvector | — |
pg_lsn | pg_lsn | — |
pg_snapshot | pg_snapshot | — |
txid_snapshot | txid_snapshot | — |
oid | oid | — |
regclass | regclass | — |
regcollation | regcollation | — |
regconfig | regconfig | — |
regdictionary | regdictionary | — |
regnamespace | regnamespace | — |
regoper | regoper | — |
regoperator | regoperator | — |
regproc | regproc | — |
regprocedure | regprocedure | — |
regrole | regrole | — |
regtype | regtype | — |
xid | xid | — |
xid8 | xid8 | — |
tid | tid | — |
refcursor | refcursor | — |
int4range | int4range | — |
int8range | int8range | — |
numrange | numrange | — |
tsrange | tsrange | — |
tstzrange | tstzrange | — |
daterange | daterange | — |
int4multirange | int4multirange | — |
int8multirange | int8multirange | — |
nummultirange | nummultirange | — |
tsmultirange | tsmultirange | — |
tstzmultirange | tstzmultirange | — |
datemultirange | datemultirange | — |
aclitem | aclitem (aclitem) | No length or precision reported |
cid | cid (cid) | No length or precision reported |
gtsvector | gtsvector (gtsvector) | No length or precision reported |
pg_brin_bloom_summary | pg_brin_bloom_summary (pg_brin_bloom_summary) | No length or precision reported |
pg_brin_minmax_multi_summary | pg_brin_minmax_multi_summary (pg_brin_minmax_multi_summary) | No length or precision reported |
pg_dependencies | pg_dependencies (pg_dependencies) | No length or precision reported |
pg_mcv_list | pg_mcv_list (pg_mcv_list) | No length or precision reported |
pg_ndistinct | pg_ndistinct (pg_ndistinct) | No length or precision reported |
pg_node_tree | pg_node_tree (pg_node_tree) | No length or precision reported |
PostgreSQL 15.2 Core Scalar and Parameterized Types
This table lists 52 tested source type declarations and parameter combinations from PostgreSQL 15.2 to PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode). It does not extend to other PostgreSQL versions or unlisted declarations.
For this version combination, time-zone timestamp values are displayed as UTC at the target while representing the same instants; the JSON representation of date includes midnight; interval formatting is normalized; JSONB whitespace may differ while the parsed value matches; and insignificant trailing zeros are not retained for numeric. When the XML column value is NULL, only its column type is established; conversion of non-NULL XML values is outside the scope of this table. These value-representation boundaries are separate from the column type mapping.
| PostgreSQL 15.2 source declaration | PolarDB Oracle target catalog type | Target catalog attributes |
|---|---|---|
smallint | smallint (int2) | precision 16, scale 0 |
int2 | smallint (int2) | precision 16, scale 0 |
integer | integer (int4) | precision 32, scale 0 |
int | integer (int4) | precision 32, scale 0 |
int4 | integer (int4) | precision 32, scale 0 |
bigint | bigint (int8) | precision 64, scale 0 |
int8 | bigint (int8) | precision 64, scale 0 |
numeric | numeric | No precision or scale reported |
decimal | numeric | No precision or scale reported |
numeric(12,4) | numeric | precision 12, scale 4 |
numeric(20,6) | numeric | precision 20, scale 6 |
real | real (float4) | precision 24 |
float4 | real (float4) | precision 24 |
float(24) | real (float4) | precision 24 |
double precision | double precision (float8) | precision 53 |
float | double precision (float8) | precision 53 |
float8 | double precision (float8) | precision 53 |
float(25) | double precision (float8) | precision 53 |
boolean | boolean (bool) | — |
bool | boolean (bool) | — |
character(3) | character (bpchar) | length 3 |
char(3) | character (bpchar) | length 3 |
character(8) | character (bpchar) | length 8 |
character varying(20) | character varying (varchar) | length 20 |
varchar(20) | character varying (varchar) | length 20 |
character varying(32) | character varying (varchar) | length 32 |
text | text | octet length 1073741824 |
bytea | bytea | — |
date | date | — |
time(3) without time zone | time without time zone (time) | datetime precision 3 |
time(3) with time zone | time with time zone (timetz) | datetime precision 3 |
time without time zone | time without time zone (time) | datetime precision 6 |
time with time zone | time with time zone (timetz) | datetime precision 6 |
timestamp(3) without time zone | timestamp without time zone (timestamp) | datetime precision 3 |
timestamp(3) with time zone | timestamp with time zone (timestamptz) | datetime precision 6 (source declaration precision: 3) |
timestamptz(3) | timestamp with time zone (timestamptz) | datetime precision 6 (source declaration precision: 3) |
timestamp without time zone | timestamp without time zone (timestamp) | datetime precision 6 |
timestamp with time zone | timestamp with time zone (timestamptz) | datetime precision 6 |
interval(3) | interval | datetime precision 6 |
interval year to month | interval | datetime precision 6; field qualifier not exposed by the target catalog query |
interval day to second(3) | interval | datetime precision 6; field qualifier not exposed by the target catalog query |
bit(4) | bit | length 4 |
bit(8) | bit | length 8 |
bit varying(8) | bit varying (varbit) | length 8 |
varbit(8) | bit varying (varbit) | length 8 |
bit varying(16) | bit varying (varbit) | length 16 |
uuid | uuid | — |
json | json | — |
jsonb | jsonb | — |
jsonpath | jsonpath | — |
xml | xml | — |
money | money | — |
PostgreSQL 15.2 Additional Built-In Scalar and System Types
The table lists the target column types and catalog attributes for 62 source type declarations from PostgreSQL 15.2 to PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode). An em dash means the catalog did not report a length, precision, or scale. This table describes column type mapping only; it does not establish conversion of non-NULL values or sequence/default behavior for serial types.
Independent replications for the same PostgreSQL 15.2 and PolarDB for PostgreSQL 14.18.37.0 (Oracle-compatible mode) version combination produced different target types for six geometric scalars: one task returned same-named target types for box, circle, lseg, path, point, and polygon; two other tasks returned text for all six. line was returned as line in all three tasks. The table below records the tasks that returned text. The target catalog results for six geometric arrays also differed; the cause has not been confirmed. For an actual task, check that task’s generated target DDL and destination column catalog. This mapping describes column types only, not conversion of non-NULL values.
| PostgreSQL 15.2 source declaration | PolarDB Oracle target catalog type | Target catalog attributes |
|---|---|---|
bigserial | bigint (int8) | precision 64, scale 0 |
serial8 | bigint (int8) | precision 64, scale 0 |
smallserial | smallint (int2) | precision 16, scale 0 |
serial2 | smallint (int2) | precision 16, scale 0 |
serial | integer (int4) | precision 32, scale 0 |
serial4 | integer (int4) | precision 32, scale 0 |
box | text | octet length 1073741824 |
circle | text | octet length 1073741824 |
line | line | — |
lseg | text | octet length 1073741824 |
path | text | octet length 1073741824 |
point | text | octet length 1073741824 |
polygon | text | octet length 1073741824 |
bpchar | text | character length unreported; octet length 1073741824 |
"char" | character (bpchar) | character length 1; octet length 4 |
name | name | — |
cidr | cidr | — |
inet | inet | — |
macaddr | macaddr | — |
macaddr8 | character varying (varchar) | character length 255; octet length 1020 |
tsquery | tsquery | — |
tsvector | tsvector | — |
pg_lsn | pg_lsn | — |
pg_snapshot | pg_snapshot | — |
txid_snapshot | txid_snapshot | — |
oid | oid | — |
regclass | regclass | — |
regcollation | regcollation | — |
regconfig | regconfig | — |
regdictionary | regdictionary | — |
regnamespace | regnamespace | — |
regoper | regoper | — |
regoperator | regoperator | — |
regproc | regproc | — |
regprocedure | regprocedure | — |
regrole | regrole | — |
regtype | regtype | — |
xid | xid | — |
xid8 | xid8 | — |
cid | cid | — |
tid | tid | — |
int4range | int4range | — |
int8range | int8range | — |
numrange | numrange | — |
tsrange | tsrange | — |
tstzrange | tstzrange | — |
daterange | daterange | — |
int4multirange | int4multirange | — |
int8multirange | int8multirange | — |
nummultirange | nummultirange | — |
tsmultirange | tsmultirange | — |
tstzmultirange | tstzmultirange | — |
datemultirange | datemultirange | — |
gtsvector | gtsvector | — |
refcursor | refcursor | — |
aclitem | aclitem | — |
pg_brin_bloom_summary | pg_brin_bloom_summary | — |
pg_brin_minmax_multi_summary | pg_brin_minmax_multi_summary | — |
pg_dependencies | pg_dependencies | — |
pg_mcv_list | pg_mcv_list | — |
pg_ndistinct | pg_ndistinct | — |
pg_node_tree | pg_node_tree | — |
PostgreSQL 15.2 User-Defined Types and Arrays (Manually Repaired Path)
The results below apply only to the specific type definitions listed. The automatically generated structure DDL did not complete when it used USER-DEFINED declarations for the enum, composite, and range columns. Structure and full replication completed after corresponding target type definitions were present and those three declarations were changed to reference the target types. The domain scalar column mapped to its base type, integer, and did not use the custom domain type. Custom-type arrays had target catalog type ARRAY, with udt_name identifying the corresponding target-side array type. This table records the manually repaired mapping and does not represent the default automatic DDL path.
| PostgreSQL 15.2 source type definition | PolarDB Oracle target column catalog type | Target catalog attributes |
|---|---|---|
| Enum type with two labels | USER-DEFINED | Target catalog type category: enum (typtype=e) |
| Integer domain with a positive-value check constraint | integer (int4) | Column uses the base type; the custom domain and its constraint are not applied to this target column |
| Composite type with integer and text fields | USER-DEFINED | Target catalog type category: composite (typtype=c) |
| Range type with integer subtype | USER-DEFINED | Target catalog type category: range (typtype=r) |
| Enum type array | ARRAY | udt_name identifies the corresponding target enum-array type |
| Integer domain array | ARRAY | udt_name identifies the corresponding target domain-array type |
| Composite type array | ARRAY | udt_name identifies the corresponding target composite-array type |
| Integer range type array | ARRAY | udt_name identifies the corresponding target range-array type |
PostgreSQL 15.2 Built-in Array Types
The table lists destination catalog types and udt_name for PostgreSQL 15.2 arrays copied to the same target version. In the task recorded here, box[], circle[], lseg[], path[], point[], and polygon[] all returned _text; an earlier independent task returned _box, _circle, _lseg, _path, _point, and _polygon, respectively. The cause of the difference between the two tasks has not been confirmed. Check the generated target DDL and destination column catalog for the actual task. Array values in the recorded batches were NULL, so these rows confirm column types only and do not establish conversion of non-NULL or empty arrays. Structure replication for interval[] required a correction to the generated target DDL.
| PostgreSQL 15.2 source array type | PolarDB target column type | Target catalog udt_name |
|---|---|---|
aclitem[] | ARRAY | _aclitem |
bigint[] | ARRAY | _int8 |
bit varying[] | ARRAY | _varbit |
bit(1)[] | ARRAY (bit(1)[]) | _bit |
boolean[] | ARRAY | _bool |
box[] | ARRAY | _text |
bytea[] | ARRAY | _bytea |
"char"[] | ARRAY (character(1)[]) | _bpchar |
character varying[] | ARRAY | _varchar |
character(1)[] | ARRAY (character(1)[]) | _bpchar |
cid[] | ARRAY | _cid |
cidr[] | ARRAY | _cidr |
circle[] | ARRAY | _text |
date[] | ARRAY | _date |
datemultirange[] | ARRAY | _datemultirange |
daterange[] | ARRAY | _daterange |
double precision[] | ARRAY | _float8 |
gtsvector[] | ARRAY | _gtsvector |
inet[] | ARRAY | _inet |
int2vector[] | ARRAY | _int2vector |
int4multirange[] | ARRAY | _int4multirange |
int4range[] | ARRAY | _int4range |
int8multirange[] | ARRAY | _int8multirange |
int8range[] | ARRAY | _int8range |
integer[] | ARRAY | _int4 |
interval[] | ARRAY | _interval |
json[] | ARRAY | _json |
jsonb[] | ARRAY | _jsonb |
jsonpath[] | ARRAY | _jsonpath |
line[] | ARRAY | _line |
lseg[] | ARRAY | _text |
macaddr[] | ARRAY | _macaddr |
macaddr8[] | ARRAY | _varchar |
money[] | ARRAY | _money |
name[] | ARRAY | _name |
numeric[] | ARRAY | _numeric |
nummultirange[] | ARRAY | _nummultirange |
numrange[] | ARRAY | _numrange |
oid[] | ARRAY | _oid |
oidvector[] | ARRAY | _oidvector |
path[] | ARRAY | _text |
pg_lsn[] | ARRAY | _pg_lsn |
pg_snapshot[] | ARRAY | _pg_snapshot |
point[] | ARRAY | _text |
polygon[] | ARRAY | _text |
real[] | ARRAY | _float4 |
refcursor[] | ARRAY | _refcursor |
regclass[] | ARRAY | _regclass |
regcollation[] | ARRAY | _regcollation |
regconfig[] | ARRAY | _regconfig |
regdictionary[] | ARRAY | _regdictionary |
regnamespace[] | ARRAY | _regnamespace |
regoper[] | ARRAY | _regoper |
regoperator[] | ARRAY | _regoperator |
regproc[] | ARRAY | _regproc |
regprocedure[] | ARRAY | _regprocedure |
regrole[] | ARRAY | _regrole |
regtype[] | ARRAY | _regtype |
smallint[] | ARRAY | _int2 |
text[] | ARRAY | _text |
tid[] | ARRAY | _tid |
time with time zone[] | ARRAY | _timetz |
time without time zone[] | ARRAY | _time |
timestamp with time zone[] | ARRAY | _timestamptz |
timestamp without time zone[] | ARRAY | _timestamp |
tsmultirange[] | ARRAY | _tsmultirange |
tsquery[] | ARRAY | _tsquery |
tsrange[] | ARRAY | _tsrange |
tstzmultirange[] | ARRAY | _tstzmultirange |
tstzrange[] | ARRAY | _tstzrange |
tsvector[] | ARRAY | _tsvector |
txid_snapshot[] | ARRAY | _txid_snapshot |
uuid[] | ARRAY | _uuid |
xid[] | ARRAY | _xid |
xid8[] | ARRAY | _xid8 |
xml[] | ARRAY | _xml |
PostgreSQL 15.2 Parameterized Array Columns
The following destination catalog results are from the existing PostgreSQL 15.2 to PolarDB PostgreSQL 14.18.37.0 (Oracle-compatible mode) replication task. The target data_type is ARRAY for every column; format_type shows which type modifiers were retained at the target. The target reports interval day to second(3)[] as interval[], without the source range or precision qualifier. The source table was empty and the full-copy task completed with 0 records, so this confirms destination column types only, not array-value conversion.
| PostgreSQL 15.2 source array type | Target catalog data_type | Target catalog udt_name | Target catalog format_type |
|---|---|---|---|
numeric(20,6)[] | ARRAY | _numeric | numeric(20,6)[] |
bit(8)[] | ARRAY | _bit | bit(8)[] |
character(8)[] | ARRAY | _bpchar | character(8)[] |
time(6) without time zone[] | ARRAY | _time | time(6) without time zone[] |
time(6) with time zone[] | ARRAY | _timetz | time(6) with time zone[] |
timestamp(6) without time zone[] | ARRAY | _timestamp | timestamp(6) without time zone[] |
timestamp(6) with time zone[] | ARRAY | _timestamptz | timestamp(6) with time zone[] |
interval day to second(3)[] | ARRAY | _interval | interval[] |
PostgreSQL 15.2 Vector Types (Manually Repaired DDL)
For schema and full replication of int2vector, oidvector, and their array columns, the mapping configuration displayed these source columns as ARRAY. The generated target DDL failed because the bare ARRAY type was rejected. After the four vector-related target columns were changed to VARCHAR2(4000) in the task's repair dialog, schema and full replication completed. The destination catalog reported VARCHAR with data_length 4000. The table below records the manually repaired target types, not the default generated mapping.
The scalar sample values were read back unchanged at the target. The source values for both array columns were NULL, so this confirms only their destination column types, not conversion of non-NULL or empty array values. This was a separate PostgreSQL 15.2 batch from the ARRAY catalog results in the array table above.
| PostgreSQL 15.2 source type | Source catalog udt_name | Source sample value | PolarDB target catalog type after manual repair | Target sample value |
|---|---|---|---|---|
int2vector | int2vector | 1 2 3 | VARCHAR (data_length=4000) | 1 2 3 |
oidvector | oidvector | 10 20 30 | VARCHAR (data_length=4000) | 10 20 30 |
int2vector[] | _int2vector | NULL | VARCHAR (data_length=4000) | NULL |
oidvector[] | _oidvector | NULL | VARCHAR (data_length=4000) | NULL |
Appendix: Precheck items
| Check item | What NineData checks |
|---|---|
| "Source datasource connection check" | Gateway status, instance reachability, username, and password for the source data source. |
| Target datasource connection check | Gateway status, instance reachability, username, and password for the target data source. |
| Source database privilege check | Whether the source database account has the required privileges. |
| Cyclic replication check | Whether a replication loop exists. |