Search PostgreSQL documentation

NOT INManual ChaptersPG18Subquery Expressionsexpression NOT IN (subquery) The right-hand side is a parenthesized subquery, which must return exactly one column. The left-hand expression is evaluated and compared to each row of the subq… Not-Null ConstraintsManual ChaptersPG18ConstraintsA not-null constraint simply specifies that a column must not assume the null value. A syntax example: CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric … NotesManual ChaptersPG18ecpgWhen compiling the preprocessed C code files, the compiler needs to be able to find the ECPG header files in the PostgreSQL include directory. Therefore, you might have to use the -I option … NotesManual ChaptersPG18initdbinitdb can also be invoked via pg_ctl initdb. NotesManual ChaptersPG18pg_dumpallSince pg_dumpall calls pg_dump internally, some diagnostic messages will refer to pg_dump. The --clean option can be useful even when your intention is to restore the dump script into a fres… NotesManual ChaptersPG18pg_isreadyIt is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connec… NotesManual ChaptersPG18pg_amcheckpg_amcheck is designed to work with PostgreSQL 14.0 and later. NotesManual ChaptersPG18pg_basebackupAt the beginning of the backup, a checkpoint needs to be performed on the source server. This can take some time (especially if the option --checkpoint=fast is not used), during which pg_bas… NotesManual ChaptersPG18pg_checksumsEnabling checksums in a large cluster can potentially take a long time. During this operation, the cluster or other programs that write to the data directory must not be started or else data… NotesManual ChaptersPG18pg_configThe options --docdir, --pkgincludedir, --localedir, --mandir, --sharedir, --sysconfdir, --cc, --cppflags, --cflags, --cflags_sl, --ldflags, --ldflags_sl, and --libs were added in PostgreSQL … NotesManual ChaptersPG18pg_createsubscriber NotesManual ChaptersPG18pg_dumpIf your database cluster has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors du… NotesManual ChaptersPG18pg_receivewalWhen using pg_receivewal instead of archive_command or archive_library as the main WAL backup method, it is strongly recommended to use replication slots. Otherwise, the server is free to re… NotesManual ChaptersPG18pg_recvlogicalpg_recvlogical will preserve group permissions on the output files if group permissions are enabled on the source cluster. NotesManual ChaptersPG18pg_resetwalThis command must not be used when the server is running. pg_resetwal will refuse to start up if it finds a server lock file in the data directory. If the server crashed then a lock file mig… NotesManual ChaptersPG18pg_restoreIf your installation has any local additions to the template1 database, be careful to load the output of pg_restore into a truly empty database; otherwise you are likely to get errors due to… NotesManual ChaptersPG18pg_rewindWhen executing pg_rewind using an online cluster as source, a role having sufficient permissions to execute the functions used by pg_rewind on the source cluster can be used instead of a sup… NotesManual ChaptersPG18postgresThe utility command pg_ctl can be used to start and shut down the postgres server safely and comfortably. If at all possible, do not use SIGKILL to kill the main postgres server. Doing so wi… NotesManual ChaptersPG18psqlpsql works best with servers of the same or an older major version. Backslash commands are particularly likely to fail if the server is of a newer version than psql itself. However, backslas… NotesManual ChaptersPG18basic_archive — an example WAL archive moduleServer crashes may leave temporary files with the prefix archtemp in the archive directory. It is recommended to delete such files before restarting the server after a crash. It is safe to r… NotesManual ChaptersPG18dblink_build_sql_deleteAs of PostgreSQL 9.0, the attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the column's position in SELECT * FROM relname. Previous versio… NotesManual ChaptersPG18dblink_build_sql_insertAs of PostgreSQL 9.0, the attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the column's position in SELECT * FROM relname. Previous versio… NotesManual ChaptersPG18dblink_build_sql_updateAs of PostgreSQL 9.0, the attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the column's position in SELECT * FROM relname. Previous versio… NotesManual ChaptersPG18dblink_closeIf dblink_open started an explicit transaction block, and this is the last remaining open cursor in this connection, dblink_close will issue the matching COMMIT. NotesManual ChaptersPG18dblink_connectIf untrusted users have access to a database that has not adopted a secure schema usage pattern, begin each session by removing publicly-writable schemas from search_path. One could, for exa… NotesManual ChaptersPG18dblink_error_messageWhen asynchronous queries are initiated by dblink_send_query, the error message associated with the connection might not get updated until the server's response message is consumed. This typ… NotesManual ChaptersPG18dblink_fetchOn a mismatch between the number of return columns specified in the FROM clause, and the actual number of columns returned by the remote cursor, an error will be thrown. In this event, the r… NotesManual ChaptersPG18dblinkA convenient way to use dblink with predetermined queries is to create a view. This allows the column type information to be buried in the view, instead of having to spell it out in every qu… NotesManual ChaptersPG18dblink_get_resultThis function must be called if dblink_send_query returned 1. It must be called once for each query sent, and one additional time to obtain an empty set result, before the connection can be … NotesManual ChaptersPG18dblink_openSince a cursor can only persist within a transaction, dblink_open starts an explicit transaction block (BEGIN) on the remote side, if the remote side was not already within a transaction. Th… NotesManual ChaptersPG18cube — a multi-dimensional cube data typeFor examples of usage, see the regression test sql/cube.sql. To make it harder for people to break things, there is a limit of 100 on the number of dimensions of cubes. This is set in cubeda… NotesManual ChaptersPG18DECLARE STATEMENTThis association is valid only if the declaration is physically placed on top of a dynamic statement. NotesManual ChaptersPG18EXECUTE IMMEDIATEIn typical usage, the string is a host variable reference to a string containing a dynamically-constructed SQL statement. The case of a literal string is not very useful; you might as well j… NotesManual ChaptersPG18PREPAREIn typical usage, the string is a host variable reference to a string containing a dynamically-constructed SQL statement. The case of a literal string is not very useful; you might as well j… NotesManual ChaptersPG18oid2nameoid2name requires a running database server with non-corrupt system catalogs. It is therefore of only limited use for recovering from catastrophic database corruption situations. NotesManual ChaptersPG18pg_archivecleanuppg_archivecleanup is designed to work with PostgreSQL 8.0 and later when used as a standalone utility, or with PostgreSQL 9.0 and later when used as an archive cleanup command. pg_archivecle… NotesManual ChaptersPG18pgbench NotesManual ChaptersPG18pgcrypto — cryptographic functions NotesManual ChaptersPG18pg_upgradepg_upgrade creates various working files, such as schema dumps, stored within pg_upgrade_output.d in the directory of the new cluster. Each run creates a new subdirectory named with a timest… NotesManual ChaptersPG18pg_waldumpCan give wrong results when the server is running. Only the specified timeline is displayed (or the default, if none is specified). Records in other timelines are ignored. pg_waldump cannot …