Search PostgreSQL documentation

CompatibilityManual ChaptersPG18Compatibility PREPARE TRANSACTION is a PostgreSQL extension. It is intended for use by external transaction management systems, some of which are covered by standards (such as X/Open XA), bu…Compatibility CompatibilityManual ChaptersPG18Compatibility The SQL standard includes a PREPARE statement, but it is only for use in embedded SQL. This version of the PREPARE statement also uses a somewhat different syntax.Compatibility CompatibilityManual ChaptersPG18Compatibility The REASSIGN OWNED command is a PostgreSQL extension.Compatibility CompatibilityManual ChaptersPG18Compatibility REFRESH MATERIALIZED VIEW is a PostgreSQL extension.Compatibility CompatibilityManual ChaptersPG18Compatibility There is no REINDEX command in the SQL standard.Compatibility CompatibilityManual ChaptersPG18Compatibility This command conforms to the SQL standard. The standard specifies that the key word SAVEPOINT is mandatory, but PostgreSQL allows it to be omitted.Compatibility CompatibilityManual ChaptersPG18Compatibility RESET is a PostgreSQL extension.Compatibility CompatibilityManual ChaptersPG18Compatibility The compatibility notes of the GRANT command apply analogously to REVOKE. The keyword RESTRICT or CASCADE is required according to the standard, but PostgreSQL assumes RESTRICT…Compatibility CompatibilityManual ChaptersPG18Compatibility ROLLBACK PREPARED is a PostgreSQL extension. It is intended for use by external transaction management systems, some of which are covered by standards (such as X/Open XA), but …Compatibility CompatibilityManual ChaptersPG18Compatibility The SQL standard specifies that the key word SAVEPOINT is mandatory, but PostgreSQL and Oracle allow it to be omitted. SQL allows only WORK, not TRANSACTION, as a noise word af…Compatibility CompatibilityManual ChaptersPG18Compatibility The command ROLLBACK conforms to the SQL standard. The form ROLLBACK TRANSACTION is a PostgreSQL extension.Compatibility CompatibilityManual ChaptersPG18Compatibility SQL requires a savepoint to be destroyed automatically when another savepoint with the same name is established. In PostgreSQL, the old savepoint is kept, though only the more …Compatibility CompatibilityManual ChaptersPG18Compatibility There is no SECURITY LABEL command in the SQL standard.Compatibility CompatibilityManual ChaptersPG18Compatibility Of course, the SELECT statement is compatible with the SQL standard. But there are some extensions and some missing features.Compatibility CompatibilityManual ChaptersPG18Compatibility The SQL standard uses SELECT INTO to represent selecting values into scalar variables of a host program, rather than creating a new table. This indeed is the usage found in ECP…Compatibility CompatibilityManual ChaptersPG18Compatibility This command complies with the behavior defined in the SQL standard, except for the limitation that, in PostgreSQL, it does not apply to NOT NULL and CHECK constraints. Also, P…Compatibility CompatibilityManual ChaptersPG18Compatibility PostgreSQL allows identifier syntax ("rolename"), while the SQL standard requires the role name to be written as a string literal. SQL does not allow this command during a tran…Compatibility CompatibilityManual ChaptersPG18Compatibility The SQL standard allows some other expressions to appear in place of the literal user_name, but these options are not important in practice. PostgreSQL allows identifier syntax…Compatibility CompatibilityManual ChaptersPG18Compatibility These commands are defined in the SQL standard, except for the DEFERRABLE transaction mode and the SET TRANSACTION SNAPSHOT form, which are PostgreSQL extensions. SERIALIZABLE …Compatibility CompatibilityManual ChaptersPG18Compatibility SET TIME ZONE extends syntax defined in the SQL standard. The standard allows only numeric time zone offsets while PostgreSQL allows more flexible time-zone specifications. All…Compatibility CompatibilityManual ChaptersPG18Compatibility The SHOW command is a PostgreSQL extension.Compatibility CompatibilityManual ChaptersPG18Compatibility In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. PostgreSQL's behavior can be seen as im…Compatibility CompatibilityManual ChaptersPG18Compatibility The SQL:2008 standard includes a TRUNCATE command with the syntax TRUNCATE TABLE tablename. The clauses CONTINUE IDENTITY/RESTART IDENTITY also appear in that standard, but hav…Compatibility CompatibilityManual ChaptersPG18Compatibility There is no UNLISTEN command in the SQL standard.Compatibility CompatibilityManual ChaptersPG18Compatibility This command conforms to the SQL standard, except that the FROM and RETURNING clauses are PostgreSQL extensions, as is the ability to use WITH with UPDATE. Some other database …Compatibility CompatibilityManual ChaptersPG18Compatibility There is no VACUUM statement in the SQL standard. The following syntax was used before PostgreSQL version 9.0 and is still supported: VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ A…Compatibility CompatibilityManual ChaptersPG18Compatibility VALUES conforms to the SQL standard. LIMIT and OFFSET are PostgreSQL extensions; see also under SELECT.Compatibility Compiling and Linking Dynamically-Loaded FunctionsManual ChaptersPG1836.10.5. Compiling and Linking Dynamically-Loaded Functions Before you are able to use your PostgreSQL extension functions written in C, they must be compiled and linked in a special way to …Compiling and Linking Dynamically-Loaded Functions Compiling for Dynamic TracingManual ChaptersPG1827.5.1. Compiling for Dynamic Tracing By default, probes are not available, so you will need to explicitly tell the configure script to make the probes available in PostgreSQL. To include DT…Compiling for Dynamic Tracing Compiling for Optimal PerformanceManual ChaptersPG1817.7.4.3. Compiling for Optimal Performance On the SPARC architecture, Sun Studio is strongly recommended for compilation. Try using the -xO5 optimization flag to generate significantly fast…Compiling for Optimal Performance Composite Type ComparisonManual ChaptersPG189.25.6. Composite Type Comparison record operator record The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a …Composite Type Comparison Composite Type Input and Output SyntaxManual ChaptersPG188.16.6. Composite Type Input and Output Syntax The external text representation of a composite value consists of items that are interpreted according to the I/O conversion rules for the indi…Composite Type Input and Output Syntax Composite TypesManual ChaptersPG1834.4.5.2. Composite Types Composite types are not directly supported in ECPG, but an easy workaround is possible. The available workarounds are similar to the ones described for arrays above…Composite Types Composite TypesManual ChaptersPG1844.2.4. Composite Types Composite-type arguments are passed to the function as Python mappings. The element names of the mapping are the attribute names of the composite type. If an attribut…Composite Types Composite TypesManual ChaptersPG188.16. Composite Types A composite type represents the structure of a row or record; it is essentially just a list of field names and their data types. PostgreSQL allows composite types to be…Composite Types Composite TypesManual ChaptersPG18Composite Types The first form of CREATE TYPE creates a composite type. The composite type is specified by a list of attribute names and data types. An attribute's collation can be specified…Composite Types Composite-Type ArgumentsManual ChaptersPG1836.10.7. Composite-Type Arguments Composite types do not have a fixed layout like C structures. Instances of a composite type can contain null fields. In addition, composite types that are p…Composite-Type Arguments compress-algoManual ChaptersPG18F.26.3.8.2. compress-algo Which compression algorithm to use. Only available if PostgreSQL was built with zlib. Values: 0 - no compression 1 - ZIP compression 2 - ZLIB compression (= ZIP plu…compress-algo compress-levelManual ChaptersPG18F.26.3.8.3. compress-level How much to compress. Higher levels compress smaller but are slower. 0 disables compression. Values: 0, 1-9 Default: 6 Applies to: pgp_sym_encrypt, pgp_pub_encryptcompress-level Compressed Archive LogsManual ChaptersPG1825.3.7.2. Compressed Archive Logs If archive storage size is a concern, you can use gzip to compress the archive files: archive_command = 'gzip < %p > /mnt/server/archivedir/%f.gz' You will …Compressed Archive Logs