1<!-- 2doc/src/sgml/ref/alter_publication.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="sql-alterpublication"> 7 <indexterm zone="sql-alterpublication"> 8 <primary>ALTER PUBLICATION</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle>ALTER PUBLICATION</refentrytitle> 13 <manvolnum>7</manvolnum> 14 <refmiscinfo>SQL - Language Statements</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname>ALTER PUBLICATION</refname> 19 <refpurpose>change the definition of a publication</refpurpose> 20 </refnamediv> 21 22 <refsynopsisdiv> 23<synopsis> 24ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...] 25ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...] 26ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...] 27ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) 28ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_USER | SESSION_USER } 29ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable> 30</synopsis> 31 </refsynopsisdiv> 32 33 <refsect1> 34 <title>Description</title> 35 36 <para> 37 The command <command>ALTER PUBLICATION</command> can change the attributes 38 of a publication. 39 </para> 40 41 <para> 42 The first three variants change which tables are part of the publication. 43 The <literal>SET TABLE</literal> clause will replace the list of tables in 44 the publication with the specified one. The <literal>ADD TABLE</literal> 45 and <literal>DROP TABLE</literal> clauses will add and remove one or more 46 tables from the publication. Note that adding tables to a publication that 47 is already subscribed to will require a <literal>ALTER SUBSCRIPTION 48 ... REFRESH PUBLICATION</literal> action on the subscribing side in order 49 to become effective. 50 </para> 51 52 <para> 53 The fourth variant of this command listed in the synopsis can change 54 all of the publication properties specified in 55 <xref linkend="sql-createpublication"/>. Properties not mentioned in the 56 command retain their previous settings. 57 </para> 58 59 <para> 60 The remaining variants change the owner and the name of the publication. 61 </para> 62 63 <para> 64 You must own the publication to use <command>ALTER PUBLICATION</command>. 65 Adding a table to a publication additionally requires owning that table. 66 To alter the owner, you must also be a direct or indirect member of the new 67 owning role. The new owner must have <literal>CREATE</literal> privilege on 68 the database. Also, the new owner of a <literal>FOR ALL TABLES</literal> 69 publication must be a superuser. However, a superuser can change the 70 ownership of a publication regardless of these restrictions. 71 </para> 72 </refsect1> 73 74 <refsect1> 75 <title>Parameters</title> 76 77 <variablelist> 78 <varlistentry> 79 <term><replaceable class="parameter">name</replaceable></term> 80 <listitem> 81 <para> 82 The name of an existing publication whose definition is to be altered. 83 </para> 84 </listitem> 85 </varlistentry> 86 87 <varlistentry> 88 <term><replaceable class="parameter">table_name</replaceable></term> 89 <listitem> 90 <para> 91 Name of an existing table. If <literal>ONLY</literal> is specified before the 92 table name, only that table is affected. If <literal>ONLY</literal> is not 93 specified, the table and all its descendant tables (if any) are 94 affected. Optionally, <literal>*</literal> can be specified after the table 95 name to explicitly indicate that descendant tables are included. 96 </para> 97 </listitem> 98 </varlistentry> 99 100 <varlistentry> 101 <term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term> 102 <listitem> 103 <para> 104 This clause alters publication parameters originally set by 105 <xref linkend="sql-createpublication"/>. See there for more information. 106 </para> 107 </listitem> 108 </varlistentry> 109 110 <varlistentry> 111 <term><replaceable class="parameter">new_owner</replaceable></term> 112 <listitem> 113 <para> 114 The user name of the new owner of the publication. 115 </para> 116 </listitem> 117 </varlistentry> 118 119 <varlistentry> 120 <term><replaceable class="parameter">new_name</replaceable></term> 121 <listitem> 122 <para> 123 The new name for the publication. 124 </para> 125 </listitem> 126 </varlistentry> 127 </variablelist> 128 </refsect1> 129 130 <refsect1> 131 <title>Examples</title> 132 133 <para> 134 Change the publication to publish only deletes and updates: 135<programlisting> 136ALTER PUBLICATION noinsert SET (publish = 'update, delete'); 137</programlisting> 138 </para> 139 140 <para> 141 Add some tables to the publication: 142<programlisting> 143ALTER PUBLICATION mypublication ADD TABLE users, departments; 144</programlisting></para> 145 </refsect1> 146 147 <refsect1> 148 <title>Compatibility</title> 149 150 <para> 151 <command>ALTER PUBLICATION</command> is a <productname>PostgreSQL</productname> 152 extension. 153 </para> 154 </refsect1> 155 156 <refsect1> 157 <title>See Also</title> 158 159 <simplelist type="inline"> 160 <member><xref linkend="sql-createpublication"/></member> 161 <member><xref linkend="sql-droppublication"/></member> 162 <member><xref linkend="sql-createsubscription"/></member> 163 <member><xref linkend="sql-altersubscription"/></member> 164 </simplelist> 165 </refsect1> 166</refentry> 167