Search PostgreSQL documentation

DECLAREManual ChaptersPG18DECLARE DECLARE — define a cursor Synopsis DECLARE name [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR queryDECLARE Declare SectionsManual ChaptersPG1834.4.2. Declare Sections To pass data from the program to the database, for example as parameters in a query, or to pass data from the database back to the program, the C variables that are …Declare Sections DECLARE STATEMENTManual ChaptersPG18DECLARE STATEMENT DECLARE STATEMENT — declare SQL statement identifier Synopsis EXEC SQL [ AT connection_name ] DECLARE statement_name STATEMENTDECLARE STATEMENT Declaring Cursor VariablesManual ChaptersPG1841.7.1. Declaring Cursor Variables All access to cursors in PL/pgSQL goes through cursor variables, which are always of the special data type refcursor. One way to create a cursor variable i…Declaring Cursor Variables Declaring Function ParametersManual ChaptersPG1841.3.1. Declaring Function Parameters Parameters passed to functions are named with the identifiers $1, $2, etc. Optionally, aliases can be declared for $n parameter names for increased read…Declaring Function Parameters DeduplicationManual ChaptersPG1865.1.4.3. Deduplication A duplicate is a leaf page tuple (a tuple that points to a table row) where all indexed key columns have values that match corresponding column values from at least o…Deduplication Default BehaviorManual ChaptersPG1819.10.3. Default Behavior vacuum_truncate (boolean) # Enables or disables vacuum to try to truncate off any empty pages at the end of the table. The default value is true. If true, VACUUM an…Default Behavior Default Roles Renamed to Predefined RolesManual ChaptersPG18O.2. Default Roles Renamed to Predefined Roles PostgreSQL 13 and below used the term “Default Roles”. However, as these roles are not able to actually be changed and are installed as part of…Default Roles Renamed to Predefined Roles Default ValuesManual ChaptersPG185.2. Default Values A column can be assigned a default value. When a new row is created and no values are specified for some of the columns, those columns will be filled with their respectiv…Default Values DefaultsManual ChaptersPG18F.10.4. Defaults This union: select cube_union('(0,5,2),(2,3,1)', '0'); cube_union ------------------- (0, 0, 0),(2, 5, 2) (1 row) does not contradict common sense, neither does the intersec…Defaults Defining New ProbesManual ChaptersPG1827.5.4. Defining New Probes New probes can be defined within the code wherever the developer desires, though this will require a recompilation. Below are the steps for inserting new probes: …Defining New Probes Defining New Range TypesManual ChaptersPG188.17.8. Defining New Range Types Users can define their own range types. The most common reason to do this is to use ranges over subtypes not provided among the built-in range types. For exa…Defining New Range Types DefinitionsManual ChaptersPG18F.22.1. Definitions A label is a sequence of alphanumeric characters, underscores, and hyphens. Valid alphanumeric character ranges are dependent on the database locale. For example, in C lo…Definitions Delaying ExecutionManual ChaptersPG189.9.6. Delaying Execution The following functions are available to delay execution of the server process: pg_sleep ( double precision ) pg_sleep_for ( interval ) pg_sleep_until ( timestamp w…Delaying Execution DELETEManual ChaptersPG18DELETE DELETE — delete rows of a table Synopsis [ WITH [ RECURSIVE ] with_query [, ...] ] DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ USING from_item [, ...] ] [ WHERE condition…DELETE Deleting DataManual ChaptersPG186.3. Deleting Data So far we have explained how to add data to tables and how to change data. What remains is to discuss how to remove data that is no longer needed. Just as adding data is o…Deleting Data DeletionsManual ChaptersPG182.9. Deletions Rows can be removed from a table using the DELETE command. Suppose you are no longer interested in the weather of Hayward. Then you can do the following to delete those rows f…Deletions Dependency TrackingManual ChaptersPG185.15. Dependency Tracking When you create complex database structures involving many tables with foreign key constraints, views, triggers, functions, etc. you implicitly create a net of depe…Dependency Tracking Deprecation NoticeManual ChaptersPG18F.50.1. Deprecation Notice From PostgreSQL 8.3 on, there is XML-related functionality based on the SQL/XML standard in the core server. That functionality covers XML syntax checking and XPat…Deprecation Notice DESCRIBEManual ChaptersPG18DESCRIBE DESCRIBE — obtain information about a prepared statement or result set Synopsis DESCRIBE [ OUTPUT ] prepared_name USING [ SQL ] DESCRIPTOR descriptor_name DESCRIBE [ OUTPUT ] prepar…DESCRIBE DescriptionManual ChaptersPG18Description clusterdb is a utility for reclustering tables in a PostgreSQL database. It finds tables that have previously been clustered, and clusters them again on the same index that was l…Description DescriptionManual ChaptersPG18Description createdb creates a new PostgreSQL database. Normally, the database user who executes this command becomes the owner of the new database. However, a different owner can be specifi…Description DescriptionManual ChaptersPG18Description createuser creates a new PostgreSQL user (or more precisely, a role). Only superusers and users with CREATEROLE privilege can create new users, so createuser must be invoked by s…Description DescriptionManual ChaptersPG18Description dropdb destroys an existing PostgreSQL database. The user who executes this command must be a database superuser or the owner of the database. dropdb is a wrapper around the SQL …Description DescriptionManual ChaptersPG18Description dropuser removes an existing PostgreSQL user. Superusers can use this command to remove any role; otherwise, only non-superuser roles can be removed, and only by a user who posse…Description DescriptionManual ChaptersPG18Description ecpg is the embedded SQL preprocessor for C programs. It converts C programs with embedded SQL statements to normal C code by replacing the SQL invocations with special function …Description DescriptionManual ChaptersPG18Description initdb creates a new PostgreSQL database cluster. Creating a database cluster consists of creating the directories in which the cluster data will live, generating the shared cata…Description DescriptionManual ChaptersPG18Description pg_ctl is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL database server (postgres), or displaying the status of a run…Description DescriptionManual ChaptersPG18Description pg_dumpall is a utility for writing out (“dumping”) all PostgreSQL databases of a cluster into one script file. The script file contains SQL commands that can be used as input to…Description DescriptionManual ChaptersPG18Description pg_isready is a utility for checking the connection status of a PostgreSQL database server. The exit status specifies the result of the connection check.Description DescriptionManual ChaptersPG18Description pg_amcheck supports running amcheck's corruption checking functions against one or more databases, with options to select which schemas, tables and indexes to check, which kinds …Description DescriptionManual ChaptersPG18Description pg_basebackup is used to take a base backup of a running PostgreSQL database cluster. The backup is taken without affecting other clients of the database, and can be used both fo…Description DescriptionManual ChaptersPG18Description pg_checksums checks, enables or disables data checksums in a PostgreSQL cluster. The server must be shut down cleanly before running pg_checksums. When verifying checksums, the e…Description DescriptionManual ChaptersPG18Description pg_combinebackup is used to reconstruct a synthetic full backup from an incremental backup and the earlier backups upon which it depends. Specify all of the required backups on t…Description DescriptionManual ChaptersPG18Description The pg_config utility prints configuration parameters of the currently installed version of PostgreSQL. It is intended, for example, to be used by software packages that want to …Description DescriptionManual ChaptersPG18Description pg_controldata prints information initialized during initdb, such as the catalog version. It also shows information about write-ahead logging and checkpoint processing. This info…Description DescriptionManual ChaptersPG18Description pg_createsubscriber creates a new logical replica from a physical standby server. All tables in the specified database are included in the logical replication setup. A pair of pu…Description DescriptionManual ChaptersPG18Description pg_dump is a utility for exporting a PostgreSQL database. It makes consistent exports even if the database is being used concurrently. pg_dump does not block other users accessin…Description DescriptionManual ChaptersPG18Description pg_receivewal is used to stream the write-ahead log from a running PostgreSQL cluster. The write-ahead log is streamed using the streaming replication protocol, and is written to…Description DescriptionManual ChaptersPG18Description pg_recvlogical controls logical decoding replication slots and streams data from such replication slots. It creates a replication-mode connection, so it is subject to the same co…Description