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.

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);

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;

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)

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