{"Entry":{"collection":"sql","key":"create-publication","name":"CREATE PUBLICATION","aliases":["createpublication"],"metadata":{"aliases":["createpublication"],"changed_in":["15","19"],"changes":[{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"12","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"13"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ FOR ALL TABLES","| FOR publication_object [, ... ] ]","TABLE table_and_columns [, ... ]","TABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]","[ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]"],"removed":["[ FOR TABLE [ ONLY ] table_name [ * ] [, ...]","| FOR ALL TABLES ]"]},"to":"15"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["[ FOR { publication_object [, ... ] | publication_all_object [, ... ] } ]","ALL TABLES [ EXCEPT ( except_table_object [, ... ] ) ]","ALL SEQUENCES","table_object [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]","TABLE table_object [, ... ]","[ ONLY ] table_name [ * ]"],"removed":["[ FOR ALL TABLES","| FOR publication_object [, ... ] ]","[ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]"]},"to":"19"},{"from":"19","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"20"}],"content_hash":"10d66e15a72088118311be6f7c3130e307e178d5342b4d5f2856320010113401","editorial":{},"first_version":"10","group":"replication","imported_at":"2026-09-30T17:43:38.87876+08:00","last_version":"20","name":"CREATE PUBLICATION","object":"PUBLICATION","position":10000,"present_in":["10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new publication","purpose_zh":"","related":["alter-publication","drop-publication","create-subscription","alter-subscription"],"slug":"create-publication","source_rev":"a709ab85","synopsis":"CREATE PUBLICATION name\n[ FOR { publication_object [, ... ] | publication_all_object [, ... ] } ]\n[ WITH ( publication_parameter [= value] [, ... ] ) ]\n\nwhere publication_object is one of:\n\nTABLE table_and_columns [, ... ]\nTABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]\n\nand publication_all_object is one of:\n\nALL TABLES [ EXCEPT ( except_table_object [, ... ] ) ]\nALL SEQUENCES\n\nand table_and_columns is:\ntable_object [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]\n\nand except_table_object is:\n\nTABLE table_object [, ... ]\n\nand table_object is:\n\n[ ONLY ] table_name [ * ]","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-publication","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-publication","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATEPUBLICATION","file":"sql-createpublication.html","lang":"en","name":"CREATE PUBLICATION","purpose":"define a new publication","purpose_zh":"","related":["alter-publication","drop-publication","create-subscription","alter-subscription"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE PUBLICATION\u003c/code\u003e adds a new publication into the current database. The publication name must be distinct from the name of any existing publication in the current database.\u003c/p\u003e\u003cp\u003eA publication is essentially a group of tables whose data changes are intended to be replicated through logical replication. See \u003ca href=\"/docs/18/logical-replication-publication.html\" title=\"29.1. Publication\"\u003eSection 29.1\u003c/a\u003e for details about how publications fit into the logical replication setup.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the new publication.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFOR TABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies a list of tables to add to the publication. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is specified before the table name, only that table is added to the publication. If \u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e is not specified, the table and all its descendant tables (if any) are added. Optionally, \u003ccode class=\"literal\"\u003e*\u003c/code\u003e can be specified after the table name to explicitly indicate that descendant tables are included. This does not apply to a partitioned table, however. The partitions of a partitioned table are always implicitly considered part of the publication, so they are never explicitly added to the publication.\u003c/p\u003e\u003cp\u003eIf the optional \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause is specified, it defines a \u003cem class=\"firstterm\"\u003erow filter\u003c/em\u003e expression. Rows for which the \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e evaluates to false or null will not be published. Note that parentheses are required around the expression. It has no effect on \u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e commands.\u003c/p\u003e\u003cp\u003eWhen a column list is specified, only the named columns are replicated. The column list can contain stored generated columns as well. If the column list is omitted, the publication will replicate all non-generated columns (including any added in the future) by default. Stored generated columns can also be replicated if \u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e is set to \u003ccode class=\"literal\"\u003estored\u003c/code\u003e. Specifying a column list has no effect on \u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e commands. See \u003ca href=\"/docs/18/logical-replication-col-lists.html\" title=\"29.5. Column Lists\"\u003eSection 29.5\u003c/a\u003e for details about column lists.\u003c/p\u003e\u003cp\u003eOnly persistent base tables and partitioned tables can be part of a publication. Temporary tables, unlogged tables, foreign tables, materialized views, and regular views cannot be part of a publication.\u003c/p\u003e\u003cp\u003eSpecifying a column list when the publication also publishes \u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e is not supported.\u003c/p\u003e\u003cp\u003eWhen a partitioned table is added to a publication, all of its existing and future partitions are implicitly considered to be part of the publication. So, even operations that are performed directly on a partition are also published via publications that its ancestors are part of.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFOR ALL TABLES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eMarks the publication as one that replicates changes for all tables in the database, including tables created in the future.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eMarks the publication as one that replicates changes for all tables in the specified list of schemas, including tables created in the future.\u003c/p\u003e\u003cp\u003eSpecifying a schema when the publication also publishes a table with a column list is not supported.\u003c/p\u003e\u003cp\u003eOnly persistent base tables and partitioned tables present in the schema will be included as part of the publication. Temporary tables, unlogged tables, foreign tables, materialized views, and regular views from the schema will not be part of the publication.\u003c/p\u003e\u003cp\u003eWhen a partitioned table is published via a schema-level publication, all of its existing and future partitions are implicitly considered to be part of the publication, regardless of whether they are from the publication schema or not. So, even operations that are performed directly on a partition are also published via publications that its ancestors are part of.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause specifies optional parameters for a publication. The following parameters are supported:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003epublish\u003c/code\u003e (\u003ccode class=\"type\"\u003estring\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis parameter determines which DML operations will be published by the new publication to the subscribers. The value is a comma-separated list of operations. The allowed operations are \u003ccode class=\"literal\"\u003einsert\u003c/code\u003e, \u003ccode class=\"literal\"\u003eupdate\u003c/code\u003e, \u003ccode class=\"literal\"\u003edelete\u003c/code\u003e, and \u003ccode class=\"literal\"\u003etruncate\u003c/code\u003e. The default is to publish all actions, and so the default value for this option is \u003ccode class=\"literal\"\u003e'insert, update, delete, truncate'\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eThis parameter only affects DML operations. In particular, the initial data synchronization (see \u003ca href=\"/docs/18/logical-replication-architecture.html#LOGICAL-REPLICATION-SNAPSHOT\" title=\"29.9.1. Initial Snapshot\"\u003eSection 29.9.1\u003c/a\u003e) for logical replication does not take this parameter into account when copying existing table data.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e (\u003ccode class=\"type\"\u003eenum\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies whether the generated columns present in the tables associated with the publication should be replicated. Possible values are \u003ccode class=\"literal\"\u003enone\u003c/code\u003e and \u003ccode class=\"literal\"\u003estored\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eThe default is \u003ccode class=\"literal\"\u003enone\u003c/code\u003e meaning the generated columns present in the tables associated with publication will not be replicated.\u003c/p\u003e\u003cp\u003eIf set to \u003ccode class=\"literal\"\u003estored\u003c/code\u003e, the stored generated columns present in the tables associated with publication will be replicated.\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003eNote\u003c/h3\u003e\u003cp\u003eIf the subscriber is from a release prior to 18, then initial table synchronization won't copy generated columns even if the parameter \u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e is \u003ccode class=\"literal\"\u003estored\u003c/code\u003e in the publisher.\u003c/p\u003e\u003c/div\u003e\u003cp\u003eSee \u003ca href=\"/docs/18/logical-replication-gencols.html\" title=\"29.6. Generated Column Replication\"\u003eSection 29.6\u003c/a\u003e for more details about logical replication of generated columns.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis parameter controls how changes to a partitioned table (or any of its partitions) are published. When set to \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e, changes are published using the identity and schema of the root partitioned table. When set to \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e (the default), changes are published using the identity and schema of the individual partitions where the changes actually occurred. Enabling this option allows the changes to be replicated into a non-partitioned table or into a partitioned table whose partition structure differs from that of the publisher.\u003c/p\u003e\u003cp\u003eThere can be a case where a subscription combines multiple publications. If a partitioned table is published by any subscribed publications which set \u003ccode class=\"literal\"\u003epublish_via_partition_root = true\u003c/code\u003e, changes on this partitioned table (or on its partitions) will be published using the identity and schema of this partitioned table rather than that of the individual partitions.\u003c/p\u003e\u003cp\u003eThis parameter also affects how row filters and column lists are chosen for partitions; see below for details.\u003c/p\u003e\u003cp\u003eIf this is enabled, \u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e operations performed directly on partitions are not replicated.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eWhen specifying a parameter of type \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e, the \u003ccode class=\"literal\"\u003e=\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e part can be omitted, which is equivalent to specifying \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e.\u003c/p\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eIf \u003ccode class=\"literal\"\u003eFOR TABLE\u003c/code\u003e, \u003ccode class=\"literal\"\u003eFOR ALL TABLES\u003c/code\u003e or \u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e are not specified, then the publication starts out with an empty set of tables. That is useful if tables or schemas are to be added later.\u003c/p\u003e\u003cp\u003eThe creation of a publication does not start replication. It only defines a grouping and filtering logic for future subscribers.\u003c/p\u003e\u003cp\u003eTo create a publication, the invoking user must have the \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e privilege for the current database. (Of course, superusers bypass this check.)\u003c/p\u003e\u003cp\u003eTo add a table to a publication, the invoking user must have ownership rights on the table. The \u003ccode class=\"command\"\u003eFOR ALL TABLES\u003c/code\u003e and \u003ccode class=\"command\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e clauses require the invoking user to be a superuser.\u003c/p\u003e\u003cp\u003eThe tables added to a publication that publishes \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e and/or \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e operations must have \u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e defined. Otherwise those operations will be disallowed on those tables.\u003c/p\u003e\u003cp\u003eAny column list must include the \u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e columns in order for \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e or \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e operations to be published. There are no column list restrictions if the publication publishes only \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e operations.\u003c/p\u003e\u003cp\u003eA row filter expression (i.e., the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause) must contain only columns that are covered by the \u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e, in order for \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e and \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e operations to be published. For publication of \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e operations, any column may be used in the \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e expression. The row filter allows simple expressions that don't have user-defined functions, user-defined operators, user-defined types, user-defined collations, non-immutable built-in functions, or references to system columns.\u003c/p\u003e\u003cp\u003eThe generated columns that are part of \u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e must be published explicitly either by listing them in the column list or by enabling the \u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e option, in order for \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e and \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e operations to be published.\u003c/p\u003e\u003cp\u003eThe row filter on a table becomes redundant if \u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e is specified and the table belongs to the referred schema.\u003c/p\u003e\u003cp\u003eFor published partitioned tables, the row filter for each partition is taken from the published partitioned table if the publication parameter \u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e is true, or from the partition itself if it is false (the default). See \u003ca href=\"/docs/18/logical-replication-row-filter.html\" title=\"29.4. Row Filters\"\u003eSection 29.4\u003c/a\u003e for details about row filters. Similarly, for published partitioned tables, the column list for each partition is taken from the published partitioned table if the publication parameter \u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e is true, or from the partition itself if it is false.\u003c/p\u003e\u003cp\u003eFor an \u003ccode class=\"command\"\u003eINSERT ... ON CONFLICT\u003c/code\u003e command, the publication will publish the operation that results from the command. Depending on the outcome, it may be published as either \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e or \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, or it may not be published at all.\u003c/p\u003e\u003cp\u003eFor a \u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e command, the publication will publish an \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e, \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e, or \u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e for each row inserted, updated, or deleted.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eATTACH\u003c/code\u003eing a table into a partition tree whose root is published using a publication with \u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e set to \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e does not result in the table's existing contents being replicated.\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCOPY ... FROM\u003c/code\u003e commands are published as \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e operations.\u003c/p\u003e\u003cp\u003e\u003cacronym\u003eDDL\u003c/acronym\u003e operations are not published.\u003c/p\u003e\u003cp\u003eThe \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause expression is executed with the role used for the replication connection.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eCreate a publication that publishes all changes in two tables:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION mypublication FOR TABLE users, departments;\n\u003c/pre\u003e\u003cp\u003eCreate a publication that publishes all changes from active departments:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);\n\u003c/pre\u003e\u003cp\u003eCreate a publication that publishes all changes in all tables:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION alltables FOR ALL TABLES;\n\u003c/pre\u003e\u003cp\u003eCreate a publication that only publishes \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e operations in one table:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION insert_only FOR TABLE mydata\n    WITH (publish = 'insert');\n\u003c/pre\u003e\u003cp\u003eCreate a publication that publishes all changes for tables \u003ccode class=\"structname\"\u003eusers\u003c/code\u003e, \u003ccode class=\"structname\"\u003edepartments\u003c/code\u003e and all changes for all the tables present in the schema \u003ccode class=\"structname\"\u003eproduction\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;\n\u003c/pre\u003e\u003cp\u003eCreate a publication that publishes all changes for all the tables present in the schemas \u003ccode class=\"structname\"\u003emarketing\u003c/code\u003e and \u003ccode class=\"structname\"\u003esales\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;\n\u003c/pre\u003e\u003cp\u003eCreate a publication that publishes all changes for table \u003ccode class=\"structname\"\u003eusers\u003c/code\u003e, but replicates only columns \u003ccode class=\"structname\"\u003euser_id\u003c/code\u003e and \u003ccode class=\"structname\"\u003efirstname\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE PUBLICATION\u003c/code\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-publication/?v=18\" title=\"ALTER PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-publication/?v=18\" title=\"DROP PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-subscription/?v=18\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/alter-subscription/?v=18\" title=\"ALTER SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    [ FOR ALL TABLES\n      | FOR \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_object\u003c/code\u003e\u003c/em\u003e [, ... ] ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\n\n\u003cspan class=\"phrase\"\u003ewhere \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_object\u003c/code\u003e\u003c/em\u003e is one of:\u003c/span\u003e\n\n    TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e [, ... ]\n    TABLES IN SCHEMA { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema_name\u003c/code\u003e\u003c/em\u003e | CURRENT_SCHEMA } [, ... ]\n\n\u003cspan class=\"phrase\"\u003eand \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e is:\u003c/span\u003e\n\n    [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ... ] ) ] [ WHERE ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ) ]","synopsis_text":"CREATE PUBLICATION name\n[ FOR ALL TABLES\n| FOR publication_object [, ... ] ]\n[ WITH ( publication_parameter [= value] [, ... ] ) ]\n\nwhere publication_object is one of:\n\nTABLE table_and_columns [, ... ]\nTABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]\n\nand table_and_columns is:\n\n[ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-publication","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE PUBLICATION","Summary":"定义一个新的发布","BodyHTML":"\u003cpre\u003eCREATE PUBLICATION name\n[ FOR ALL TABLES\n| FOR publication_object [, ... ] ]\n[ WITH ( publication_parameter [= value] [, ... ] ) ]\n\n其中publication_object可以是：\n\nTABLE table_and_columns [, ... ]\nTABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]\n\n其中table_and_columns为：\n\n[ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE PUBLICATION\u003c/code\u003e向当前数据库中添加一个新发布。该发布的名称必须不同于当前数据库中任何现有发布的名称。\u003c/p\u003e\u003cp\u003e发布本质上是一组表，其数据更改会通过逻辑复制进行复制。有关发布在逻辑复制配置中如何工作的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-publication.html\" rel=\"nofollow\"\u003e第 29.1 节\u003c/a\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新发布的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFOR TABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定要加入该发布的表列表。如果在表名前指定了\u003ccode\u003eONLY\u003c/code\u003e，则只有该表会被加入发布。如果未指定\u003ccode\u003eONLY\u003c/code\u003e，则该表及其所有后代表（如果有）都会被加入。也可以在表名后指定 \u003ccode\u003e*\u003c/code\u003e，以显式表明包含后代表。不过，这不适用于分区表。分区表的各个分区始终会被隐式视为发布的一部分，因此永远不会被显式加入发布。\u003c/p\u003e\u003cp\u003e如果指定了可选的\u003ccode\u003eWHERE\u003c/code\u003e子句，它会定义一个\u003cem\u003e行过滤器\u003c/em\u003e表达式。对于 \u003cem\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e求值为 \u003ccode\u003efalse\u003c/code\u003e或\u003ccode\u003eNULL\u003c/code\u003e的行，不会被发布。注意，表达式外必须加括号。它对\u003ccode\u003eTRUNCATE\u003c/code\u003e命令没有影响。\u003c/p\u003e\u003cp\u003e指定列列表时，只复制其中列出的列。列列表也可以包含存储生成列。如果省略列列表，则该发布默认复制所有非生成列（包括今后新增的列）。如果将\u003ccode\u003epublish_generated_columns\u003c/code\u003e设为 \u003ccode\u003estored\u003c/code\u003e，也可以复制存储生成列。指定列列表对 \u003ccode\u003eTRUNCATE\u003c/code\u003e命令没有影响。有关列列表的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-col-lists.html\" rel=\"nofollow\"\u003e第 29.5 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e只有持久基表和分区表才能成为发布的一部分。临时表、不记录 WAL 的表、外部表、物化视图和常规视图都不能成为发布的一部分。\u003c/p\u003e\u003cp\u003e如果该发布还包含\u003ccode\u003eFOR TABLES IN SCHEMA\u003c/code\u003e，则不支持指定列列表。\u003c/p\u003e\u003cp\u003e将分区表加入发布时，其所有现有分区和未来分区都会被隐式视为发布的一部分。因此，即使直接在某个分区上执行的操作，也会通过其祖先表所在的发布进行发布。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFOR ALL TABLES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将该发布标记为复制数据库中所有表的更改，包括未来创建的表。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eFOR TABLES IN SCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将该发布标记为复制指定模式列表中所有表的更改，包括未来创建的表。\u003c/p\u003e\u003cp\u003e如果该发布还发布带有列列表的表，则不支持指定模式。\u003c/p\u003e\u003cp\u003e只有该模式中的持久基表和分区表会被纳入发布。该模式中的临时表、不记录 WAL 的表、外部表、物化视图和常规视图都不会成为发布的一部分。\u003c/p\u003e\u003cp\u003e当通过模式级发布来发布分区表时，其所有现有分区和未来分区都会被隐式视为发布的一部分，无论这些分区是否位于该发布模式中。因此，即使直接在某个分区上执行的操作，也会通过其祖先表所在的发布进行发布。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eWITH ( \u003cem\u003e\u003ccode\u003epublication_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句指定发布的可选参数。支持以下参数：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003epublish\u003c/code\u003e (\u003ccode\u003estring\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该参数决定新发布会向订阅者发布哪些 DML 操作。其值是一个以逗号分隔的操作列表。允许的操作有\u003ccode\u003einsert\u003c/code\u003e、\u003ccode\u003eupdate\u003c/code\u003e、\u003ccode\u003edelete\u003c/code\u003e和\u003ccode\u003etruncate\u003c/code\u003e。默认会发布所有操作，因此该选项的默认值为 \u003ccode\u003e\u0026#39;insert, update, delete, truncate\u0026#39;\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e该参数只影响 DML 操作。特别是，逻辑复制的初始数据同步（参见\u003ca href=\"/docs/18/logical-replication-architecture.html#LOGICAL-REPLICATION-SNAPSHOT\" rel=\"nofollow\"\u003e第 29.9.1 节\u003c/a\u003e）在复制现有表数据时不会考虑该参数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003epublish_generated_columns\u003c/code\u003e (\u003ccode\u003eenum\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定是否复制与该发布关联的表中存在的生成列。可选值为 \u003ccode\u003enone\u003c/code\u003e和\u003ccode\u003estored\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e默认值为\u003ccode\u003enone\u003c/code\u003e，表示不会复制与该发布关联的表中的生成列。\u003c/p\u003e\u003cp\u003e如果设为\u003ccode\u003estored\u003c/code\u003e，则会复制与该发布关联的表中的存储生成列。\u003c/p\u003e\u003cdiv\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e如果订阅端版本早于 18，那么即使发布端的 \u003ccode\u003epublish_generated_columns\u003c/code\u003e参数为 \u003ccode\u003estored\u003c/code\u003e，初始表同步时也不会复制生成列。\u003c/p\u003e\u003c/div\u003e\u003cp\u003e有关生成列逻辑复制的更多细节，请参见\u003ca href=\"/docs/18/logical-replication-gencols.html\" rel=\"nofollow\"\u003e第 29.6 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003epublish_via_partition_root\u003c/code\u003e (\u003ccode\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该参数控制如何发布分区表（或其任一分区）上的更改。设为 \u003ccode\u003etrue\u003c/code\u003e时，更改会使用根分区表的标识和模式来发布。设为\u003ccode\u003efalse\u003c/code\u003e（默认值）时，更改会使用实际发生更改的各个分区的标识和模式来发布。启用该选项后，可以将这些更改复制到非分区表，或者复制到分区结构与发布端不同的分区表。\u003c/p\u003e\u003cp\u003e订阅可能会组合多个发布。如果任一已订阅发布以 \u003ccode\u003epublish_via_partition_root = true\u003c/code\u003e发布某个分区表，那么该分区表（或其分区）上的更改将使用该分区表本身的标识和模式来发布，而不是使用各个分区的标识和模式。\u003c/p\u003e\u003cp\u003e该参数还会影响为分区选择行过滤器和列列表的方式；详见下文。\u003c/p\u003e\u003cp\u003e如果启用该选项，直接对分区执行的\u003ccode\u003eTRUNCATE\u003c/code\u003e操作不会被复制。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e当指定\u003ccode\u003eboolean\u003c/code\u003e类型的参数时，可以省略 \u003ccode\u003e=\u003c/code\u003e \u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e 部分，这等价于指定\u003ccode\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e如果未指定\u003ccode\u003eFOR TABLE\u003c/code\u003e、\u003ccode\u003eFOR ALL TABLES\u003c/code\u003e或 \u003ccode\u003eFOR TABLES IN SCHEMA\u003c/code\u003e，则该发布开始时不包含任何表。这在稍后还要添加表或模式时很有用。\u003c/p\u003e\u003cp\u003e创建发布不会启动复制。它只为未来的订阅者定义分组和过滤逻辑。\u003c/p\u003e\u003cp\u003e要创建发布，调用用户必须具有当前数据库的\u003ccode\u003eCREATE\u003c/code\u003e权限。（当然，超级用户会绕过这一检查。）\u003c/p\u003e\u003cp\u003e要向发布中添加表，调用用户必须拥有该表。使用\u003ccode\u003eFOR ALL TABLES\u003c/code\u003e 和\u003ccode\u003eFOR TABLES IN SCHEMA\u003c/code\u003e子句要求调用用户是超级用户。\u003c/p\u003e\u003cp\u003e如果某个发布发布\u003ccode\u003eUPDATE\u003c/code\u003e和/或\u003ccode\u003eDELETE\u003c/code\u003e 操作，则加入该发布的表必须已经定义\u003ccode\u003eREPLICA IDENTITY\u003c/code\u003e。否则，在这些表上将不允许执行这些操作。\u003c/p\u003e\u003cp\u003e任何列列表都必须包含\u003ccode\u003eREPLICA IDENTITY\u003c/code\u003e列，这样 \u003ccode\u003eUPDATE\u003c/code\u003e或\u003ccode\u003eDELETE\u003c/code\u003e操作才能被发布。如果该发布只发布\u003ccode\u003eINSERT\u003c/code\u003e操作，则列列表没有限制。\u003c/p\u003e\u003cp\u003e行过滤表达式（即\u003ccode\u003eWHERE\u003c/code\u003e子句）必须只包含 \u003ccode\u003eREPLICA IDENTITY\u003c/code\u003e所覆盖的列，这样 \u003ccode\u003eUPDATE\u003c/code\u003e和\u003ccode\u003eDELETE\u003c/code\u003e操作才能被发布。对于发布\u003ccode\u003eINSERT\u003c/code\u003e操作，可以在\u003ccode\u003eWHERE\u003c/code\u003e 表达式中使用任何列。行过滤器只允许简单表达式。它不能包含用户定义的函数、操作符、类型和排序规则、系统列引用，或非不可变内置函数。\u003c/p\u003e\u003cp\u003e属于\u003ccode\u003eREPLICA IDENTITY\u003c/code\u003e的生成列必须被显式发布，即要么将其列入列列表，要么启用\u003ccode\u003epublish_generated_columns\u003c/code\u003e 选项，这样\u003ccode\u003eUPDATE\u003c/code\u003e和\u003ccode\u003eDELETE\u003c/code\u003e 操作才能被发布。\u003c/p\u003e\u003cp\u003e如果指定了\u003ccode\u003eFOR TABLES IN SCHEMA\u003c/code\u003e，并且该表属于所引用的模式，那么该表上的行过滤器就是多余的。\u003c/p\u003e\u003cp\u003e对于已发布的分区表，如果发布参数\u003ccode\u003epublish_via_partition_root\u003c/code\u003e 为\u003ccode\u003etrue\u003c/code\u003e，则每个分区的行过滤器取自已发布的分区表；如果为\u003ccode\u003efalse\u003c/code\u003e（默认值），则取自分区本身。有关行过滤器的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-row-filter.html\" rel=\"nofollow\"\u003e第 29.4 节\u003c/a\u003e。类似地，对于已发布的分区表，如果发布参数\u003ccode\u003epublish_via_partition_root\u003c/code\u003e为\u003ccode\u003etrue\u003c/code\u003e，则每个分区的列列表取自已发布的分区表；如果为\u003ccode\u003efalse\u003c/code\u003e，则取自分区本身。\u003c/p\u003e\u003cp\u003e对于\u003ccode\u003eINSERT ... ON CONFLICT\u003c/code\u003e命令，发布会发布该命令实际产生的操作。根据执行结果，它可能被发布为\u003ccode\u003eINSERT\u003c/code\u003e或 \u003ccode\u003eUPDATE\u003c/code\u003e，也可能根本不被发布。\u003c/p\u003e\u003cp\u003e对于\u003ccode\u003eMERGE\u003c/code\u003e命令，发布会为每一行被插入、更新或删除的记录发布一个 \u003ccode\u003eINSERT\u003c/code\u003e、\u003ccode\u003eUPDATE\u003c/code\u003e或\u003ccode\u003eDELETE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e将表\u003ccode\u003eATTACH\u003c/code\u003e到某个分区树中，而该分区树的根通过 \u003ccode\u003epublish_via_partition_root\u003c/code\u003e设为\u003ccode\u003etrue\u003c/code\u003e 的发布进行发布时，不会复制该表的现有内容。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eCOPY ... FROM\u003c/code\u003e命令会作为\u003ccode\u003eINSERT\u003c/code\u003e操作发布。\u003c/p\u003e\u003cp\u003e\u003cacronym\u003eDDL\u003c/acronym\u003e操作不会被发布。\u003c/p\u003e\u003cp\u003e\u003ccode\u003eWHERE\u003c/code\u003e子句表达式会使用复制连接所用的同一角色来执行。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e创建一个发布，发布两个表中的所有更改：\u003c/p\u003e\u003cpre\u003eCREATE PUBLICATION mypublication FOR TABLE users, departments;\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布活跃部门中的所有更改：\u003c/p\u003e\u003cpre\u003eCREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布所有表的所有更改：\u003c/p\u003e\u003cpre\u003eCREATE PUBLICATION alltables FOR ALL TABLES;\n\u003c/pre\u003e\u003cp\u003e创建一个只发布\u003ccode\u003eINSERT\u003c/code\u003e操作、且只针对一个表的发布：\u003c/p\u003e\u003cpre\u003eCREATE PUBLICATION insert_only FOR TABLE mydata\n    WITH (publish = \u0026#39;insert\u0026#39;);\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布表\u003ccode\u003eusers\u003c/code\u003e、\u003ccode\u003edepartments\u003c/code\u003e 的所有更改，以及模式\u003ccode\u003eproduction\u003c/code\u003e中所有表的所有更改：\u003c/p\u003e\u003cpre\u003eCREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布模式\u003ccode\u003emarketing\u003c/code\u003e和 \u003ccode\u003esales\u003c/code\u003e中所有表的所有更改：\u003c/p\u003e\u003cpre\u003eCREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布表\u003ccode\u003eusers\u003c/code\u003e的所有更改，但只复制 \u003ccode\u003euser_id\u003c/code\u003e和\u003ccode\u003efirstname\u003c/code\u003e两列：\u003c/p\u003e\u003cpre\u003eCREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE PUBLICATION\u003c/code\u003e是\u003cspan\u003ePostgreSQL\u003c/span\u003e 的扩展。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-publication/?v=18\" title=\"ALTER PUBLICATION\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-publication/?v=18\" title=\"DROP PUBLICATION\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-subscription/?v=18\" title=\"CREATE SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/alter-subscription/?v=18\" title=\"ALTER SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"caa9b439accb4c8fb5783a7a85ba406ffbb96d5d6f12206efd9e1578a5d2415f","Payload":{"purpose_zh":"定义一个新的发布","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE PUBLICATION\u003c/code\u003e向当前数据库中添加一个新发布。该发布的名称必须不同于当前数据库中任何现有发布的名称。\u003c/p\u003e\u003cp\u003e发布本质上是一组表，其数据更改会通过逻辑复制进行复制。有关发布在逻辑复制配置中如何工作的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-publication.html\" title=\"29.1. 发布\"\u003e第 29.1 节\u003c/a\u003e。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e新发布的名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFOR TABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定要加入该发布的表列表。如果在表名前指定了\u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，则只有该表会被加入发布。如果未指定\u003ccode class=\"literal\"\u003eONLY\u003c/code\u003e，则该表及其所有后代表（如果有）都会被加入。也可以在表名后指定 \u003ccode class=\"literal\"\u003e*\u003c/code\u003e，以显式表明包含后代表。不过，这不适用于分区表。分区表的各个分区始终会被隐式视为发布的一部分，因此永远不会被显式加入发布。\u003c/p\u003e\u003cp\u003e如果指定了可选的\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句，它会定义一个\u003cem class=\"firstterm\"\u003e行过滤器\u003c/em\u003e表达式。对于 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e求值为 \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e或\u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e的行，不会被发布。注意，表达式外必须加括号。它对\u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e命令没有影响。\u003c/p\u003e\u003cp\u003e指定列列表时，只复制其中列出的列。列列表也可以包含存储生成列。如果省略列列表，则该发布默认复制所有非生成列（包括今后新增的列）。如果将\u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e设为 \u003ccode class=\"literal\"\u003estored\u003c/code\u003e，也可以复制存储生成列。指定列列表对 \u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e命令没有影响。有关列列表的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-col-lists.html\" title=\"29.5. 列列表\"\u003e第 29.5 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e只有持久基表和分区表才能成为发布的一部分。临时表、不记录 WAL 的表、外部表、物化视图和常规视图都不能成为发布的一部分。\u003c/p\u003e\u003cp\u003e如果该发布还包含\u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e，则不支持指定列列表。\u003c/p\u003e\u003cp\u003e将分区表加入发布时，其所有现有分区和未来分区都会被隐式视为发布的一部分。因此，即使直接在某个分区上执行的操作，也会通过其祖先表所在的发布进行发布。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFOR ALL TABLES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将该发布标记为复制数据库中所有表的更改，包括未来创建的表。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e将该发布标记为复制指定模式列表中所有表的更改，包括未来创建的表。\u003c/p\u003e\u003cp\u003e如果该发布还发布带有列列表的表，则不支持指定模式。\u003c/p\u003e\u003cp\u003e只有该模式中的持久基表和分区表会被纳入发布。该模式中的临时表、不记录 WAL 的表、外部表、物化视图和常规视图都不会成为发布的一部分。\u003c/p\u003e\u003cp\u003e当通过模式级发布来发布分区表时，其所有现有分区和未来分区都会被隐式视为发布的一部分，无论这些分区是否位于该发布模式中。因此，即使直接在某个分区上执行的操作，也会通过其祖先表所在的发布进行发布。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eWITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句指定发布的可选参数。支持以下参数：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003epublish\u003c/code\u003e (\u003ccode class=\"type\"\u003estring\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该参数决定新发布会向订阅者发布哪些 DML 操作。其值是一个以逗号分隔的操作列表。允许的操作有\u003ccode class=\"literal\"\u003einsert\u003c/code\u003e、\u003ccode class=\"literal\"\u003eupdate\u003c/code\u003e、\u003ccode class=\"literal\"\u003edelete\u003c/code\u003e和\u003ccode class=\"literal\"\u003etruncate\u003c/code\u003e。默认会发布所有操作，因此该选项的默认值为 \u003ccode class=\"literal\"\u003e'insert, update, delete, truncate'\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e该参数只影响 DML 操作。特别是，逻辑复制的初始数据同步（参见\u003ca href=\"/docs/18/logical-replication-architecture.html#LOGICAL-REPLICATION-SNAPSHOT\" title=\"29.9.1. 初始快照\"\u003e第 29.9.1 节\u003c/a\u003e）在复制现有表数据时不会考虑该参数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e (\u003ccode class=\"type\"\u003eenum\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定是否复制与该发布关联的表中存在的生成列。可选值为 \u003ccode class=\"literal\"\u003enone\u003c/code\u003e和\u003ccode class=\"literal\"\u003estored\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e默认值为\u003ccode class=\"literal\"\u003enone\u003c/code\u003e，表示不会复制与该发布关联的表中的生成列。\u003c/p\u003e\u003cp\u003e如果设为\u003ccode class=\"literal\"\u003estored\u003c/code\u003e，则会复制与该发布关联的表中的存储生成列。\u003c/p\u003e\u003cdiv class=\"note\"\u003e\u003ch3\u003e注意\u003c/h3\u003e\u003cp\u003e如果订阅端版本早于 18，那么即使发布端的 \u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e参数为 \u003ccode class=\"literal\"\u003estored\u003c/code\u003e，初始表同步时也不会复制生成列。\u003c/p\u003e\u003c/div\u003e\u003cp\u003e有关生成列逻辑复制的更多细节，请参见\u003ca href=\"/docs/18/logical-replication-gencols.html\" title=\"29.6. 生成列复制\"\u003e第 29.6 节\u003c/a\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该参数控制如何发布分区表（或其任一分区）上的更改。设为 \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e时，更改会使用根分区表的标识和模式来发布。设为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e（默认值）时，更改会使用实际发生更改的各个分区的标识和模式来发布。启用该选项后，可以将这些更改复制到非分区表，或者复制到分区结构与发布端不同的分区表。\u003c/p\u003e\u003cp\u003e订阅可能会组合多个发布。如果任一已订阅发布以 \u003ccode class=\"literal\"\u003epublish_via_partition_root = true\u003c/code\u003e发布某个分区表，那么该分区表（或其分区）上的更改将使用该分区表本身的标识和模式来发布，而不是使用各个分区的标识和模式。\u003c/p\u003e\u003cp\u003e该参数还会影响为分区选择行过滤器和列列表的方式；详见下文。\u003c/p\u003e\u003cp\u003e如果启用该选项，直接对分区执行的\u003ccode class=\"literal\"\u003eTRUNCATE\u003c/code\u003e操作不会被复制。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e当指定\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e类型的参数时，可以省略 \u003ccode class=\"literal\"\u003e=\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e 部分，这等价于指定\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e。\u003c/p\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e如果未指定\u003ccode class=\"literal\"\u003eFOR TABLE\u003c/code\u003e、\u003ccode class=\"literal\"\u003eFOR ALL TABLES\u003c/code\u003e或 \u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e，则该发布开始时不包含任何表。这在稍后还要添加表或模式时很有用。\u003c/p\u003e\u003cp\u003e创建发布不会启动复制。它只为未来的订阅者定义分组和过滤逻辑。\u003c/p\u003e\u003cp\u003e要创建发布，调用用户必须具有当前数据库的\u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e权限。（当然，超级用户会绕过这一检查。）\u003c/p\u003e\u003cp\u003e要向发布中添加表，调用用户必须拥有该表。使用\u003ccode class=\"command\"\u003eFOR ALL TABLES\u003c/code\u003e 和\u003ccode class=\"command\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e子句要求调用用户是超级用户。\u003c/p\u003e\u003cp\u003e如果某个发布发布\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和/或\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 操作，则加入该发布的表必须已经定义\u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e。否则，在这些表上将不允许执行这些操作。\u003c/p\u003e\u003cp\u003e任何列列表都必须包含\u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e列，这样 \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e或\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e操作才能被发布。如果该发布只发布\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e操作，则列列表没有限制。\u003c/p\u003e\u003cp\u003e行过滤表达式（即\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句）必须只包含 \u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e所覆盖的列，这样 \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e操作才能被发布。对于发布\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e操作，可以在\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e 表达式中使用任何列。行过滤器只允许简单表达式。它不能包含用户定义的函数、操作符、类型和排序规则、系统列引用，或非不可变内置函数。\u003c/p\u003e\u003cp\u003e属于\u003ccode class=\"literal\"\u003eREPLICA IDENTITY\u003c/code\u003e的生成列必须被显式发布，即要么将其列入列列表，要么启用\u003ccode class=\"literal\"\u003epublish_generated_columns\u003c/code\u003e 选项，这样\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e和\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e 操作才能被发布。\u003c/p\u003e\u003cp\u003e如果指定了\u003ccode class=\"literal\"\u003eFOR TABLES IN SCHEMA\u003c/code\u003e，并且该表属于所引用的模式，那么该表上的行过滤器就是多余的。\u003c/p\u003e\u003cp\u003e对于已发布的分区表，如果发布参数\u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e 为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e，则每个分区的行过滤器取自已发布的分区表；如果为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e（默认值），则取自分区本身。有关行过滤器的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-row-filter.html\" title=\"29.4. 行过滤器\"\u003e第 29.4 节\u003c/a\u003e。类似地，对于已发布的分区表，如果发布参数\u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e，则每个分区的列列表取自已发布的分区表；如果为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e，则取自分区本身。\u003c/p\u003e\u003cp\u003e对于\u003ccode class=\"command\"\u003eINSERT ... ON CONFLICT\u003c/code\u003e命令，发布会发布该命令实际产生的操作。根据执行结果，它可能被发布为\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e或 \u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e，也可能根本不被发布。\u003c/p\u003e\u003cp\u003e对于\u003ccode class=\"command\"\u003eMERGE\u003c/code\u003e命令，发布会为每一行被插入、更新或删除的记录发布一个 \u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e、\u003ccode class=\"command\"\u003eUPDATE\u003c/code\u003e或\u003ccode class=\"command\"\u003eDELETE\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e将表\u003ccode class=\"command\"\u003eATTACH\u003c/code\u003e到某个分区树中，而该分区树的根通过 \u003ccode class=\"literal\"\u003epublish_via_partition_root\u003c/code\u003e设为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e 的发布进行发布时，不会复制该表的现有内容。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"command\"\u003eCOPY ... FROM\u003c/code\u003e命令会作为\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e操作发布。\u003c/p\u003e\u003cp\u003e\u003cacronym\u003eDDL\u003c/acronym\u003e操作不会被发布。\u003c/p\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句表达式会使用复制连接所用的同一角色来执行。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e创建一个发布，发布两个表中的所有更改：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION mypublication FOR TABLE users, departments;\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布活跃部门中的所有更改：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION active_departments FOR TABLE departments WHERE (active IS TRUE);\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布所有表的所有更改：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION alltables FOR ALL TABLES;\n\u003c/pre\u003e\u003cp\u003e创建一个只发布\u003ccode class=\"command\"\u003eINSERT\u003c/code\u003e操作、且只针对一个表的发布：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION insert_only FOR TABLE mydata\n    WITH (publish = 'insert');\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布表\u003ccode class=\"structname\"\u003eusers\u003c/code\u003e、\u003ccode class=\"structname\"\u003edepartments\u003c/code\u003e 的所有更改，以及模式\u003ccode class=\"structname\"\u003eproduction\u003c/code\u003e中所有表的所有更改：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION production_publication FOR TABLE users, departments, TABLES IN SCHEMA production;\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布模式\u003ccode class=\"structname\"\u003emarketing\u003c/code\u003e和 \u003ccode class=\"structname\"\u003esales\u003c/code\u003e中所有表的所有更改：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION sales_publication FOR TABLES IN SCHEMA marketing, sales;\n\u003c/pre\u003e\u003cp\u003e创建一个发布，发布表\u003ccode class=\"structname\"\u003eusers\u003c/code\u003e的所有更改，但只复制 \u003ccode class=\"structname\"\u003euser_id\u003c/code\u003e和\u003ccode class=\"structname\"\u003efirstname\u003c/code\u003e两列：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE PUBLICATION users_filtered FOR TABLE users (user_id, firstname);\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE PUBLICATION\u003c/code\u003e是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 的扩展。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-publication/?v=18\" title=\"ALTER PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-publication/?v=18\" title=\"DROP PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-subscription/?v=18\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/alter-subscription/?v=18\" title=\"ALTER SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\n    [ FOR ALL TABLES\n      | FOR \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_object\u003c/code\u003e\u003c/em\u003e [, ... ] ]\n    [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\n\n\u003cspan class=\"phrase\"\u003e其中\u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_object\u003c/code\u003e\u003c/em\u003e可以是：\u003c/span\u003e\n\n    TABLE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e [, ... ]\n    TABLES IN SCHEMA { \u003cem class=\"replaceable\"\u003e\u003ccode\u003eschema_name\u003c/code\u003e\u003c/em\u003e | CURRENT_SCHEMA } [, ... ]\n\n\u003cspan class=\"phrase\"\u003e其中\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_and_columns\u003c/code\u003e\u003c/em\u003e为：\u003c/span\u003e\n\n    [ ONLY ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e [ * ] [ ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e [, ... ] ) ] [ WHERE ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eexpression\u003c/code\u003e\u003c/em\u003e ) ]","synopsis_text":"CREATE PUBLICATION name\n[ FOR ALL TABLES\n| FOR publication_object [, ... ] ]\n[ WITH ( publication_parameter [= value] [, ... ] ) ]\n\n其中publication_object可以是：\n\nTABLE table_and_columns [, ... ]\nTABLES IN SCHEMA { schema_name | CURRENT_SCHEMA } [, ... ]\n\n其中table_and_columns为：\n\n[ ONLY ] table_name [ * ] [ ( column_name [, ... ] ) ] [ WHERE ( expression ) ]"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}