Search PostgreSQL documentation

NotesManual ChaptersPG18CREATE CONVERSIONNeither the source nor the destination encoding can be SQL_ASCII, as the server's behavior for cases involving the SQL_ASCII “encoding” is hard-wired. Use DROP CONVERSION to remove user-defi… NotesManual ChaptersPG18CREATE DATABASECREATE DATABASE cannot be executed inside a transaction block. Errors along the line of “could not initialize database directory” are most likely related to insufficient permissions on the d… NotesManual ChaptersPG18CREATE DOMAINDomain constraints, particularly NOT NULL, are checked when converting a value to the domain type. It is possible for a column that is nominally of the domain type to read as null despite th… NotesManual ChaptersPG18CREATE EVENT TRIGGEROnly superusers can create event triggers. Event triggers are disabled in single-user mode (see postgres) as well as when event_triggers is set to false. If an erroneous event trigger disabl… NotesManual ChaptersPG18CREATE EXTENSIONBefore you can use CREATE EXTENSION to load an extension into a database, the extension's supporting files must be installed. Information about installing the extensions supplied with Postgr… NotesManual ChaptersPG18CREATE FOREIGN DATA WRAPPERPostgreSQL's foreign-data functionality is still under active development. Optimization of queries is primitive (and mostly left to the wrapper, too). Thus, there is considerable room for fu… NotesManual ChaptersPG18CREATE FOREIGN TABLEConstraints on foreign tables (such as CHECK or NOT NULL clauses) are not enforced by the core PostgreSQL system, and most foreign data wrappers do not attempt to enforce them either; that i… NotesManual ChaptersPG18CREATE FUNCTIONThe full SQL type syntax is allowed for declaring a function's arguments and return value. However, parenthesized type modifiers (e.g., the precision field for type numeric) are discarded by… NotesManual ChaptersPG18CREATE INDEXSee Chapter 11 for information about when indexes can be used, when they are not used, and in which particular situations they can be useful. Currently, only the B-tree, GiST, GIN, and BRIN … NotesManual ChaptersPG18CREATE LANGUAGEUse DROP LANGUAGE to drop procedural languages. The system catalog pg_language (see Section 52.29) records information about the currently installed languages. Also, the psql command \dL lis… NotesManual ChaptersPG18CREATE OPERATOR CLASSBecause the index machinery does not check access permissions on functions before using them, including a function or operator in an operator class is tantamount to granting public execute p… NotesManual ChaptersPG18CREATE OPERATORRefer to Section 36.14 and Section 36.15 for further information. When you are defining a self-commutative operator, you just do it. When you are defining a pair of commutative operators, th… NotesManual ChaptersPG18CREATE POLICYYou must be the owner of a table to create or change policies for it. While policies will be applied for explicit queries against tables in the database, they are not applied when the system… NotesManual ChaptersPG18CREATE PROCEDURESee CREATE FUNCTION for more details on function creation that also apply to procedures. Use CALL to execute a procedure. NotesManual ChaptersPG18CREATE PUBLICATIONIf FOR TABLE, FOR ALL TABLES or FOR TABLES IN SCHEMA 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… NotesManual ChaptersPG18CREATE ROLEUse ALTER ROLE to change the attributes of a role, and DROP ROLE to remove a role. All the attributes specified by CREATE ROLE can be modified by later ALTER ROLE commands. The preferred way… NotesManual ChaptersPG18CREATE RULEYou must be the owner of a table to create or change rules for it. In a rule for INSERT, UPDATE, or DELETE on a view, you can add a RETURNING clause that emits the view's columns. This claus… NotesManual ChaptersPG18CREATE SCHEMATo create a schema, the invoking user must have the CREATE privilege for the current database. (Of course, superusers bypass this check.) NotesManual ChaptersPG18CREATE SEQUENCEUse DROP SEQUENCE to remove a sequence. Sequences are based on bigint arithmetic, so the range cannot exceed the range of an eight-byte integer (-9223372036854775808 to 9223372036854775807).… NotesManual ChaptersPG18CREATE SERVERWhen using the dblink module, a foreign server's name can be used as an argument of the dblink_connect function to indicate the connection parameters. It is necessary to have the USAGE privi… NotesManual ChaptersPG18CREATE STATISTICSYou must be the owner of a table to create a statistics object reading it. Once created, however, the ownership of the statistics object is independent of the underlying table(s). Expression… NotesManual ChaptersPG18CREATE SUBSCRIPTIONSee Section 29.11 for details on how to configure access control between the subscription and the publication instance. When creating a replication slot (the default behavior), CREATE SUBSCR… NotesManual ChaptersPG18CREATE TABLEPostgreSQL automatically creates an index for each unique constraint and primary key constraint to enforce uniqueness. Thus, it is not necessary to create an index explicitly for primary key… NotesManual ChaptersPG18CREATE TABLE ASThis command is functionally similar to SELECT INTO, but it is preferred since it is less likely to be confused with other uses of the SELECT INTO syntax. Furthermore, CREATE TABLE AS offers… NotesManual ChaptersPG18CREATE TABLESPACECREATE TABLESPACE cannot be executed inside a transaction block. NotesManual ChaptersPG18CREATE TRANSFORMUse DROP TRANSFORM to remove transforms. NotesManual ChaptersPG18CREATE TRIGGERTo create or replace a trigger on a table, the user must have the TRIGGER privilege on the table. The user must also have EXECUTE privilege on the trigger function. Use DROP TRIGGER to remov… NotesManual ChaptersPG18CREATE TEXT SEARCH CONFIGURATIONThe PARSER and COPY options are mutually exclusive, because when an existing configuration is copied, its parser selection is copied too. NotesManual ChaptersPG18CREATE TYPEBecause there are no restrictions on use of a data type once it's been created, creating a base type or range type is tantamount to granting public execute permission on the functions mentio… NotesManual ChaptersPG18CREATE VIEWUse the DROP VIEW statement to drop views. Be careful that the names and types of the view's columns will be assigned the way you want. For example: CREATE VIEW vista AS SELECT 'Hello World'… NotesManual ChaptersPG18DECLARENormal cursors return data in text format, the same as a SELECT would produce. The BINARY option specifies that the cursor should return data in binary format. This reduces conversion effort… NotesManual ChaptersPG18DELETEPostgreSQL lets you reference columns of other tables in the WHERE condition by specifying the other tables in the USING clause. For example, to delete all films produced by a given producer… NotesManual ChaptersPG18DISCARDDISCARD ALL cannot be executed inside a transaction block. NotesManual ChaptersPG18DOThe procedural language to be used must already have been installed into the current database by means of CREATE EXTENSION. plpgsql is installed by default, but other languages are not. The … NotesManual ChaptersPG18DROP OWNEDDROP OWNED is often used to prepare for the removal of one or more roles. Because DROP OWNED only affects the objects in the current database, it is usually necessary to execute this command… NotesManual ChaptersPG18DROP AGGREGATEAlternative syntaxes for referencing ordered-set aggregates are described under ALTER AGGREGATE. NotesManual ChaptersPG18DROP DATABASEDROP DATABASE cannot be executed inside a transaction block. This command cannot be executed while connected to the target database. Thus, it might be more convenient to use the program drop… NotesManual ChaptersPG18DROP OPERATOR CLASSDROP OPERATOR CLASS will not drop the operator family containing the class, even if there is nothing else left in the family (in particular, in the case where the family was implicitly creat… NotesManual ChaptersPG18DROP PROCEDUREIf there is only one procedure of the given name, the argument list can be omitted. Omit the parentheses too in this case. In PostgreSQL, it's sufficient to list the input (including INOUT) … NotesManual ChaptersPG18DROP ROLEPostgreSQL includes a program dropuser that has the same functionality as this command (in fact, it calls this command) but can be run from the command shell.