Replicating the SQL server SQL_VARIANT data type v7
SQL_VARIANT data type defines a column so that the individual values in that column can have different data types. For example, the same
SQL_VARIANT column can store values that were explicitly cast as character, integer, numeric, and date/time.
However, if a table containing a
SQL_VARIANT column is to be replicated to a Postgres database, the usage of the column in Postgres is restricted to a single data type to which all the values in the
SQL_VARIANT column are implicitly convertible (that is, without the use of explicit casting). For example, an integer value is implicitly convertible to a
FLOAT data type, but a floating point value is not implicitly convertible to an
INTEGER data type.
The following restrictions apply to using replication on tables with the
SQL_VARIANT data type:
- The values stored in the
SQL_VARIANTcolumns of the table to be replicated must be implicitly convertible to the same data type in Postgres.
- If there's more than one table with
SQL_VARIANTcolumns to be replicated to the same Postgres database, then all such
SQL_VARIANTcolumns must contain values that are implicitly convertible to the same data type in Postgres.
In the Postgres subscription database, you define a domain named
sql_variant that maps to an underlying data type to which all values in the
SQL_VARIANT columns are implicitly convertible.
The following example shows how to set up replication for a table containing a
SQL_VARIANT data type used to store numeric values, but of different data types. The SQL Server table definition is the following:
The following query uses a function named
SQL_VARIANT_PROPERTY to show the values stored in column
f2 and their data types.
In the Postgres subscription database, create a domain named
sql_variant with an underlying data type compatible with the values that are stored in the SQL Server
After replication occurs, the subscription table is created using the
sql_variant domain in place of the
SQL_VARIANT data type of the publication table.