1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
|
'\" t
.\" Title: ALTER PUBLICATION
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2024
.\" Manual: PostgreSQL 16.2 Documentation
.\" Source: PostgreSQL 16.2
.\" Language: English
.\"
.TH "ALTER PUBLICATION" "7" "2024" "PostgreSQL 16.2" "PostgreSQL 16.2 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
ALTER_PUBLICATION \- change the definition of a publication
.SH "SYNOPSIS"
.sp
.nf
ALTER PUBLICATION \fIname\fR ADD \fIpublication_object\fR [, \&.\&.\&.]
ALTER PUBLICATION \fIname\fR SET \fIpublication_object\fR [, \&.\&.\&.]
ALTER PUBLICATION \fIname\fR DROP \fIpublication_object\fR [, \&.\&.\&.]
ALTER PUBLICATION \fIname\fR SET ( \fIpublication_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] )
ALTER PUBLICATION \fIname\fR OWNER TO { \fInew_owner\fR | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION \fIname\fR RENAME TO \fInew_name\fR
where \fIpublication_object\fR is one of:
TABLE [ ONLY ] \fItable_name\fR [ * ] [ ( \fIcolumn_name\fR [, \&.\&.\&. ] ) ] [ WHERE ( \fIexpression\fR ) ] [, \&.\&.\&. ]
TABLES IN SCHEMA { \fIschema_name\fR | CURRENT_SCHEMA } [, \&.\&.\&. ]
.fi
.SH "DESCRIPTION"
.PP
The command
\fBALTER PUBLICATION\fR
can change the attributes of a publication\&.
.PP
The first three variants change which tables/schemas are part of the publication\&. The
SET
clause will 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\&. The
ADD
and
DROP
clauses will add and remove one or more tables/schemas from the publication\&. Note that adding tables/schemas to a publication that is already subscribed to will require an
ALTER SUBSCRIPTION \&.\&.\&. REFRESH PUBLICATION
action on the subscribing side in order to become effective\&. Note also that
DROP TABLES IN SCHEMA
will not drop any schema tables that were specified using
FOR TABLE/
ADD TABLE, and the combination of
DROP
with a
WHERE
clause is not allowed\&.
.PP
The fourth variant of this command listed in the synopsis can change all of the publication properties specified in
CREATE PUBLICATION (\fBCREATE_PUBLICATION\fR(7))\&. Properties not mentioned in the command retain their previous settings\&.
.PP
The remaining variants change the owner and the name of the publication\&.
.PP
You must own the publication to use
\fBALTER PUBLICATION\fR\&. Adding a table to a publication additionally requires owning that table\&. The
ADD TABLES IN SCHEMA
and
SET TABLES IN SCHEMA
to a publication requires the invoking user to be a superuser\&. To alter the owner, you must be able to
SET ROLE
to the new owning role, and that role must have
CREATE
privilege on the database\&. Also, the new owner of a
FOR ALL TABLES
or
FOR TABLES IN SCHEMA
publication must be a superuser\&. However, a superuser can change the ownership of a publication regardless of these restrictions\&.
.PP
Adding/Setting any schema when the publication also publishes a table with a column list, and vice versa is not supported\&.
.SH "PARAMETERS"
.PP
\fIname\fR
.RS 4
The name of an existing publication whose definition is to be altered\&.
.RE
.PP
\fItable_name\fR
.RS 4
Name of an existing table\&. If
ONLY
is specified before the table name, only that table is affected\&. If
ONLY
is not specified, the table and all its descendant tables (if any) are affected\&. Optionally,
*
can be specified after the table name to explicitly indicate that descendant tables are included\&.
.sp
Optionally, a column list can be specified\&. See
CREATE PUBLICATION (\fBCREATE_PUBLICATION\fR(7))
for details\&. Note that a subscription having several publications in which the same table has been published with different column lists is not supported\&. See
Warning: Combining Column Lists from Multiple Publications
for details of potential problems when altering column lists\&.
.sp
If the optional
WHERE
clause is specified, rows for which the
\fIexpression\fR
evaluates to false or null will not be published\&. Note that parentheses are required around the expression\&. The
\fIexpression\fR
is evaluated with the role used for the replication connection\&.
.RE
.PP
\fIschema_name\fR
.RS 4
Name of an existing schema\&.
.RE
.PP
SET ( \fIpublication_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] )
.RS 4
This clause alters publication parameters originally set by
CREATE PUBLICATION (\fBCREATE_PUBLICATION\fR(7))\&. See there for more information\&.
.RE
.PP
\fInew_owner\fR
.RS 4
The user name of the new owner of the publication\&.
.RE
.PP
\fInew_name\fR
.RS 4
The new name for the publication\&.
.RE
.SH "EXAMPLES"
.PP
Change the publication to publish only deletes and updates:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER PUBLICATION noinsert SET (publish = \*(Aqupdate, delete\*(Aq);
.fi
.if n \{\
.RE
.\}
.PP
Add some tables to the publication:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER PUBLICATION mypublication ADD TABLE users (user_id, firstname), departments;
.fi
.if n \{\
.RE
.\}
.PP
Change the set of columns published for a table:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER PUBLICATION mypublication SET TABLE users (user_id, firstname, lastname), TABLE departments;
.fi
.if n \{\
.RE
.\}
.PP
Add schemas
marketing
and
sales
to the publication
sales_publication:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER PUBLICATION sales_publication ADD TABLES IN SCHEMA marketing, sales;
.fi
.if n \{\
.RE
.\}
.PP
Add tables
users,
departments
and schema
production
to the publication
production_publication:
.sp
.if n \{\
.RS 4
.\}
.nf
ALTER PUBLICATION production_publication ADD TABLE users, departments, TABLES IN SCHEMA production;
.fi
.if n \{\
.RE
.\}
.SH "COMPATIBILITY"
.PP
\fBALTER PUBLICATION\fR
is a
PostgreSQL
extension\&.
.SH "SEE ALSO"
CREATE PUBLICATION (\fBCREATE_PUBLICATION\fR(7)), DROP PUBLICATION (\fBDROP_PUBLICATION\fR(7)), CREATE SUBSCRIPTION (\fBCREATE_SUBSCRIPTION\fR(7)), ALTER SUBSCRIPTION (\fBALTER_SUBSCRIPTION\fR(7))
|
