1ALTER PUBLICATION(7)     PostgreSQL 15.4 Documentation    ALTER PUBLICATION(7)
2
3
4

NAME

6       ALTER_PUBLICATION - change the definition of a publication
7

SYNOPSIS

9       ALTER PUBLICATION name ADD publication_object [, ...]
10       ALTER PUBLICATION name SET publication_object [, ...]
11       ALTER PUBLICATION name DROP publication_object [, ...]
12       ALTER PUBLICATION name SET ( publication_parameter [= value] [, ... ] )
13       ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
14       ALTER PUBLICATION name RENAME TO new_name
15
16       where publication_object is one of:
17
18           TABLE [ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ] [, ... ]
19           TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]
20

DESCRIPTION

22       The command ALTER PUBLICATION can change the attributes of a
23       publication.
24
25       The first three variants change which tables/schemas are part of the
26       publication. The SET clause will replace the list of tables/schemas in
27       the publication with the specified list; the existing tables/schemas
28       that were present in the publication will be removed. The ADD and DROP
29       clauses will add and remove one or more tables/schemas from the
30       publication. Note that adding tables/schemas to a publication that is
31       already subscribed to will require an ALTER SUBSCRIPTION ... REFRESH
32       PUBLICATION action on the subscribing side in order to become
33       effective. Note also that DROP TABLES IN SCHEMA will not drop any
34       schema tables that were specified using FOR TABLE/ ADD TABLE, and the
35       combination of DROP with a WHERE clause is not allowed.
36
37       The fourth variant of this command listed in the synopsis can change
38       all of the publication properties specified in CREATE PUBLICATION
39       (CREATE_PUBLICATION(7)). Properties not mentioned in the command retain
40       their previous settings.
41
42       The remaining variants change the owner and the name of the
43       publication.
44
45       You must own the publication to use ALTER PUBLICATION. Adding a table
46       to a publication additionally requires owning that table. The ADD
47       TABLES IN SCHEMA and SET TABLES IN SCHEMA to a publication requires the
48       invoking user to be a superuser. To alter the owner, you must also be a
49       direct or indirect member of the new owning role. The new owner must
50       have CREATE privilege on the database. Also, the new owner of a FOR ALL
51       TABLES or FOR TABLES IN SCHEMA publication must be a superuser.
52       However, a superuser can change the ownership of a publication
53       regardless of these restrictions.
54
55       Adding/Setting any schema when the publication also publishes a table
56       with a column list, and vice versa is not supported.
57

PARAMETERS

59       name
60           The name of an existing publication whose definition is to be
61           altered.
62
63       table_name
64           Name of an existing table. If ONLY is specified before the table
65           name, only that table is affected. If ONLY is not specified, the
66           table and all its descendant tables (if any) are affected.
67           Optionally, * can be specified after the table name to explicitly
68           indicate that descendant tables are included.
69
70           Optionally, a column list can be specified. See CREATE PUBLICATION
71           (CREATE_PUBLICATION(7)) for details. Note that a subscription
72           having several publications in which the same table has been
73           published with different column lists is not supported. See
74           Warning: Combining Column Lists from Multiple Publications for
75           details of potential problems when altering column lists.
76
77           If the optional WHERE clause is specified, rows for which the
78           expression evaluates to false or null will not be published. Note
79           that parentheses are required around the expression. The expression
80           is evaluated with the role used for the replication connection.
81
82       schema_name
83           Name of an existing schema.
84
85       SET ( publication_parameter [= value] [, ... ] )
86           This clause alters publication parameters originally set by CREATE
87           PUBLICATION (CREATE_PUBLICATION(7)). See there for more
88           information.
89
90       new_owner
91           The user name of the new owner of the publication.
92
93       new_name
94           The new name for the publication.
95

EXAMPLES

97       Change the publication to publish only deletes and updates:
98
99           ALTER PUBLICATION noinsert SET (publish = 'update, delete');
100
101       Add some tables to the publication:
102
103           ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname), departments;
104
105       Change the set of columns published for a table:
106
107           ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname), TABLE departments;
108
109       Add schemas marketing and sales to the publication sales_publication:
110
111           ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
112
113       Add tables users, departments and schema production to the publication
114       production_publication:
115
116           ALTER PUBLICATION production_publication ADD TABLE users, departments, TABLES IN SCHEMA production;
117

COMPATIBILITY

119       ALTER PUBLICATION is a PostgreSQL extension.
120

SEE ALSO

122       CREATE PUBLICATION (CREATE_PUBLICATION(7)), DROP PUBLICATION
123       (DROP_PUBLICATION(7)), CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)),
124       ALTER SUBSCRIPTION (ALTER_SUBSCRIPTION(7))
125
126
127
128PostgreSQL 15.4                      2023                 ALTER PUBLICATION(7)