Section 7: overview, conventions, and miscellany
CREATE DATABASE – create a new database Synopsis CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ] Description CREATE DATABASE…
CREATE CONVERSION – define a new encoding conversion Synopsis CREATE [ DEFAULT ] CONVERSION name FOR source_encoding TO dest_encoding FROM funcname Description CREATE CONVERSION defines a new conversion between character set encodings. Also, conversions that are marked DEFAULT can be used for automatic encoding conversion between client and server. For this purpose, two conversions, from…
CREATE_COLLATION – define a new collation Synopsis CREATE COLLATION name ( [ LOCALE = locale, ] [ LC_COLLATE = lc_collate, ] [ LC_CTYPE = lc_ctype ] ) CREATE COLLATION name FROM existing_collation Description CREATE COLLATION defines a new collation using the specified operating system locale settings, or by copying an existing collation. To be able…
CREATE CAST – define a new cast Synopsis CREATE CAST (sourcetype AS targettype) WITH FUNCTION funcname (argtypes) [ AS ASSIGNMENT | AS IMPLICIT ] CREATE CAST (sourcetype AS targettype) WITHOUT FUNCTION [ AS ASSIGNMENT | AS IMPLICIT ] CREATE CAST (sourcetype AS targettype) WITH INOUT [ AS ASSIGNMENT | AS IMPLICIT ] Description CREATE CAST…
CREATE AGGREGATE – define a new aggregate function Synopsis CREATE AGGREGATE name ( input_data_type [ , … ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , FINALFUNC = ffunc ] [ , INITCOND = initial_condition ] [ , SORTOP = sort_operator ] ) or the old syntax CREATE AGGREGATE name ( BASETYPE =…
COPY – copy data between a file and a table Synopsis COPY tablename [ ( column [, …] ) ] FROM { ‘filename’ | STDIN } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] ‘delimiter’ ] [ NULL [ AS ] ‘null string’ ] [ CSV [ HEADER…
COMMIT PREPARED – commit a transaction that was earlier prepared for two-phase commit Synopsis COMMIT PREPARED transaction_id Description COMMIT PREPARED commits a transaction that is in prepared state. Parameters transaction_id The transaction identifier of the transaction that is to be committed. Notes To commit a prepared transaction, you must be either the same user that…
COMMIT – commit the current transaction Synopsis COMMIT [ WORK | TRANSACTION ] Description COMMIT commits the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs. Parameters WORK TRANSACTION Optional key words. They have no effect. Notes Use ROLLBACK [rollback(7)] to abort…
COMMENT – define or change the comment of an object Synopsis COMMENT ON { TABLE object_name | COLUMN table_name.column_name | AGGREGATE agg_name (agg_type [, …] ) | CAST (sourcetype AS targettype) | CONSTRAINT constraint_name ON table_name | CONVERSION object_name | DATABASE object_name | DOMAIN object_name | FUNCTION func_name ( [ [ argmode ] [ argname…
CLUSTER – cluster a table according to an index Synopsis CLUSTER [VERBOSE] tablename [ USING indexname ] CLUSTER [VERBOSE] Description CLUSTER instructs PostgreSQL to cluster the table specified by tablename based on the index specified by indexname. The index must already have been defined on tablename. When a table is clustered, it is physically reordered…
CLOSE – close a cursor Synopsis CLOSE { name | ALL } Description CLOSE frees the resources associated with an open cursor. After the cursor is closed, no subsequent operations are allowed on it. A cursor should be closed when it is no longer needed. Every non-holdable open cursor is implicitly closed when a transaction…
CHECKPOINT – force a transaction log checkpoint Synopsis CHECKPOINT Description Write-Ahead Logging (WAL) puts a checkpoint in the transaction log every so often. (To adjust the automatic checkpoint interval, see the run-time configuration options checkpoint_segments and checkpoint_timeout.) The CHECKPOINT command forces an immediate checkpoint when the command is issued, without waiting for a scheduled checkpoint….
BEGIN – start a transaction block Synopsis BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, …] ] where transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY Description BEGIN initiates a transaction block, that is, all statements after a BEGIN…
ANALYZE – collect statistics about a database Synopsis ANALYZE [ VERBOSE ] [ table [ ( column [, …] ) ] ] Description ANALYZE collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most…
ALTER VIEW – change the definition of a view Synopsis ALTER VIEW name ALTER [ COLUMN ] column SET DEFAULT expression ALTER VIEW name ALTER [ COLUMN ] column DROP DEFAULT ALTER VIEW name OWNER TO new_owner ALTER VIEW name RENAME TO new_name ALTER VIEW name SET SCHEMA new_schema Description ALTER VIEW changes various auxiliary…
ALTER USER MAPPING – change the definition of a user mapping Synopsis ALTER USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC } SERVER servername OPTIONS ( [ ADD | SET | DROP ] option [‘value’] [, … ] ) Description ALTER USER MAPPING changes the definition of a user mapping. The owner…
ALTER USER – change a database role Synopsis ALTER USER name [ [ WITH ] option [ … ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED |…
ALTER TYPE – change the definition of a type Synopsis ALTER TYPE name RENAME TO new_name ALTER TYPE name OWNER TO new_owner ALTER TYPE name SET SCHEMA new_schema Description ALTER TYPE changes the definition of an existing type. You must own the type to use ALTER TYPE. To change the schema of a type, you…
ALTER TRIGGER – change the definition of a trigger Synopsis ALTER TRIGGER name ON table RENAME TO newname Description ALTER TRIGGER changes properties of an existing trigger. The RENAME clause changes the name of the given trigger without otherwise changing the trigger definition. You must own the table on which the trigger acts to be…
ALTER TEXT SEARCH TEMPLATE – change the definition of a text search template Synopsis ALTER TEXT SEARCH TEMPLATE name RENAME TO newname Description ALTER TEXT SEARCH TEMPLATE changes the definition of a text search template. Currently, the only supported functionality is to change the template’s name. You must be a superuser to use ALTER TEXT…