Replicating DDL changes v7

Once a replication system is created and in operation, there might be times when you need to change the publication table definitions. These data definition language (DDL) changes can include the following:

• Adding new columns to a table
• Renaming existing columns
• Modifying a column data type
• Modifying a column constraint
• Removing columns

Table definition changes are generally implemented using the SQL ALTER TABLE statement, which you issue in an SQL command line utility program such as PSQL.

The DDL change replication feature accepts one or more ALTER TABLE statements. You can provide the statements in a text file or by entering them into the Alter Publication Table dialog box. The DDL change replication feature then performs the following actions:

• Applies the ALTER TABLE statements to the appropriate target table in the publication and subscription databases of a single-master replication system or in all primary nodes (including the primary definition node) of a multi-master replication system
• For the trigger-based method of synchronization replication, modifies the insert/update/delete triggers that add data into the shadow table whenever a transaction occurs on the target table
• For the trigger-based method of synchronization replication, modifies the shadow table to accommodate the target table changes

The DDL change replication feature is supported for Oracle and SQL Server subscription databases as well as Postgres subscription databases. However, the publication database must always be a Postgres database.

The syntax of the ALTER TABLE statement accepted by the DDL change replication features is as follows:

ALTER TABLE schema.table_name <action>

<action> can be any of the following:

Rename an existing column:

RENAME [ COLUMN ] column_name TO  new_column_name

Add a column to the table:

ADD [ COLUMN ] column_name data_type
[ DEFAULT dflt_expr ]
[ column_constraint_1 [ column_constraint_2 ] ...]

Drop a column from the table:

DROP [ COLUMN ] column_name [ RESTRICT ]

Change the data type of a column:

ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type
[ COLLATE "collation" ]
[ USING data_type_expr ]

Set the DEFAULT value of a column:

ALTER [ COLUMN ] column_name SET DEFAULT dflt_expr

Drop the DEFAULT value of a column:

ALTER [ COLUMN ] column_name DROP DEFAULT

Set the column to reject null values:

ALTER [ COLUMN ] column_name SET NOT NULL

Allow the column to accept null values:

ALTER [ COLUMN ] column_name DROP NOT NULL

The following restrictions apply to the manner in which you specify the ALTER TABLE statements. These restrictions apply whether you're entering the statements in a text file or in the dialog box.

• You must terminate each ALTER TABLE statement with a semicolon and begin each statement on a separate line.
• Although the Postgres ALTER TABLE statement allows multiple actions per statement, the Replication Server DDL change replication feature permits only one action per ALTER TABLE statement.
• The target table of all ALTER TABLE statements must be the same.
• You can't specify the DROP COLUMN action for a column that's part of the table’s primary key.

Parameters

schema

The name of the schema containing table_name. This value is case sensitive.

table_name

The name of the table containing the column to add, modify, or drop. This value is case sensitive.

column_name

The name of the column to add, modify, or drop.

new_column_name

The new name of the column specified in the RENAME COLUMN clause.

data_type

The data type of the column.

dflt_expr

An expression for the default value of the column.

column_constraint_n

A column constraint such as a UNIQUE or CHECK constraint. For more information on column constraints see the CREATE TABLE SQL command in the PostgreSQL Core Documentation.

RESTRICT

In the DROP COLUMN clause, don't drop the column if objects depend on it. This is the default.

collation

Collation assigned to the column. If omitted, the column data type’s default collation is used. Examples of collation are default, C, POSIX, en_US, en_GB, or de_DE.

data_type_expr

An expression specifying how to convert the column value with the new data type from the column value with the old data type. This expression can reference other columns in the same table. If omitted, the default conversion is an assignment cast from the old data type to the new data type.

Examples of ALTER TABLE statements

The following set of ALTER TABLE statements adds columns to the edb.emp table.

ALTER TABLE edb.emp ADD COLUMN gender CHAR(1) CHECK(gender IN ('M','F'));
ALTER TABLE edb.emp ADD COLUMN gradelevel VARCHAR2(4);
ALTER TABLE edb.emp ADD COLUMN title VARCHAR2(10);

The following ALTER TABLE statement changes the data type length of the title column and sets its values with the USING data_type_expr clause.

ALTER TABLE edb.emp
  ALTER COLUMN title SET DATA TYPE VARCHAR(25) USING
    CASE job
      WHEN 'CLERK' THEN 'ADMINISTRATIVE ASSISTANT'
      WHEN 'ANALYST' THEN 'R & D SPECIALIST'
      WHEN 'SALESMAN' THEN 'MARKETING REPRESENTATIVE'
      WHEN 'MANAGER' THEN 'SUPERVISOR'
      WHEN 'PRESIDENT' THEN 'CHIEF EXECUTIVE OFFICER'
    END;

The following query shows the values assigned to the title column after the DDL change replication feature applies the preceding ALTER TABLE statement to the edb.emp table. This change to the title column and assignment of values occurs in all the subscription databases of a single-master replication system or in all the primary nodes of a multi-master replication system.

edb=# SELECT empno, ename, job, title FROM emp;

 empno | ename  |    job    |          title
-------+--------+-----------+--------------------------
  7369 | SMITH  | CLERK     | ADMINISTRATIVE ASSISTANT
  7499 | ALLEN  | SALESMAN  | MARKETING REPRESENTATIVE
  7521 | WARD   | SALESMAN  | MARKETING REPRESENTATIVE
  7566 | JONES  | MANAGER   | SUPERVISOR
  7654 | MARTIN | SALESMAN  | MARKETING REPRESENTATIVE
  7698 | BLAKE  | MANAGER   | SUPERVISOR
  7782 | CLARK  | MANAGER   | SUPERVISOR
  7788 | SCOTT  | ANALYST   | R & D SPECIALIST
  7839 | KING   | PRESIDENT | CHIEF EXECUTIVE OFFICER
  7844 | TURNER | SALESMAN  | MARKETING REPRESENTATIVE
  7876 | ADAMS  | CLERK     | ADMINISTRATIVE ASSISTANT
  7900 | JAMES  | CLERK     | ADMINISTRATIVE ASSISTANT
  7902 | FORD   | ANALYST   | R & D SPECIALIST
  7934 | MILLER | CLERK     | ADMINISTRATIVE ASSISTANT
(14 rows)

The following set of ALTER TABLE statements drops the columns that were added in the first example.

ALTER TABLE edb.emp DROP COLUMN gender;
ALTER TABLE edb.emp DROP COLUMN gradelevel;
ALTER TABLE edb.emp DROP COLUMN title;

• On this page
• Parameters
• Examples of ALTER TABLE statements