v2.23 Preview Preview release - not for production v2024.1 STS Stable release with standard-term support v2.20 LTS Stable release with long-term support v2.18 STS Stable release with standard-term support v2.14 LTS Stable release with long-term support Unsupported versions

ALTER PUBLICATION

Synopsis

Use the ALTER PUBLICATION statement to change properties of an existing publication.

Syntax

alter_publication ::= ALTER PUBLICATION publication_name 
                      [ alter_publication_action [ , ... ] ]

alter_publication_action ::= ADD TABLE table_name [ , ... ]
                             | SET TABLE table_name [ , ... ]
                             | DROP TABLE table_name [ , ... ]
                             | RENAME TO publication_name
                             | OWNER TO new_owner

alter_publication

ALTERPUBLICATIONpublication_name,alter_publication_action

alter_publication_action

ADDTABLE,table_nameSETTABLE,table_nameDROPTABLE,table_nameRENAMETOpublication_nameOWNERTOnew_owner

Semantics

alter_publication_action

Specify one of the following actions.

ADD TABLE table_name { ',' table_name }

Add the specified tables to the publication.

SET TABLE table_name { ',' table_name }

Replace the list of tables/schemas in the publication with the specified list. The existing tables/schemas that were present in the publication will be removed.

DROP TABLE table_name { ',' table_name }

Remove the specified tables from the publication.

RENAME TO publication_name

Rename the publication name to the specified name.

OWNER TO new_owner

Change the owner of the publication to the new_owner.

Permissions

To alter a publication, the invoking user must own the publication. Adding a table to a publication additionally requires owning that table. To alter the owner, you must also be a direct or indirect member of the new owning role. The new owner must have CREATE privilege on the database. Also, the new owner of a FOR ALL TABLES publication must be a superuser. However, a superuser can change the ownership of a publication regardless of these restrictions.

Examples

Assume that you have created the tables users, departments, and employees.

To create a publication my_publication, use the following command:

yugabyte=# CREATE PUBLICATION my_publication FOR TABLE employees;

Add the users and departments tables to the publication my_publication:

yugabyte=# ALTER PUBLICATION my_publication ADD TABLE users, departments;

Drop the employees table from the publication my_publication:

yugabyte=# ALTER PUBLICATION my_publication DROP TABLE employees;

Set the departments table to be the only table in the publication my_publication. The table users will be removed from the publication as a result of the command.

yugabyte=# ALTER PUBLICATION my_publication SET TABLE departments;

Rename the publication my_publication to renamed_publication.

yugabyte=# ALTER PUBLICATION my_publication RENAME TO renamed_publication;

See also