Search PostgreSQL documentation

Procedural LanguagesManual ChaptersPG18H.3. Procedural Languages PostgreSQL includes several procedural languages with the base distribution: PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python. In addition, there are a number of procedural…Procedural Languages Procedural LanguagesManual ChaptersPG18Chapter 40. Procedural Languages PostgreSQL allows user-defined functions to be written in other languages besides SQL and C. These other languages are generically called procedural language…Procedural Languages Process TitleManual ChaptersPG1819.8.6. Process Title These settings control how process titles of server processes are modified. Process titles are typically viewed using programs like ps or, on Windows, Process Explorer.…Process Title Processing and Creating JSON DataManual ChaptersPG189.16.1. Processing and Creating JSON Data Table 9.47 shows the operators that are available for use with JSON data types (see Section 8.14). In addition, the usual comparison operators shown…Processing and Creating JSON Data Processing Embedded SQL ProgramsManual ChaptersPG1834.10. Processing Embedded SQL Programs Now that you have an idea how to form embedded SQL C programs, you probably want to know how to compile them. Before compiling you run the file throug…Processing Embedded SQL Programs Processing Objects Dropped by a DDL CommandManual ChaptersPG189.30.2. Processing Objects Dropped by a DDL Command pg_event_trigger_dropped_objects () → setof record pg_event_trigger_dropped_objects returns a list of all objects dropped by the command i…Processing Objects Dropped by a DDL Command Processing ResultsManual ChaptersPG1832.5.1.2. Processing Results To process the result of one query in a pipeline, the application calls PQgetResult repeatedly and handles each result until PQgetResult returns null. The result…Processing Results Processing XMLManual ChaptersPG189.15.3. Processing XML To process values of data type xml, PostgreSQL offers the functions xpath and xpath_exists, which evaluate XPath 1.0 expressions, and the XMLTABLE table function.Processing XML Producing XML ContentManual ChaptersPG189.15.1. Producing XML Content A set of functions and function-like expressions is available for producing XML content from SQL data. As such, they are particularly suitable for formatting qu…Producing XML Content Progress ReportingManual ChaptersPG1827.4. Progress Reporting PostgreSQL has the ability to report the progress of certain commands during command execution. Currently, the only commands which support progress reporting are ANA…Progress Reporting PromptingManual ChaptersPG18Prompting The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appea…Prompting Protection Provided in Different ModesManual ChaptersPG1832.19.3. Protection Provided in Different Modes The different values for the sslmode parameter provide different levels of protection. SSL can provide protection against three types of attac…Protection Provided in Different Modes Protocol VersionsManual ChaptersPG1854.1.4. Protocol Versions The current, latest version of the protocol is version 3.2. However, for backwards compatibility with old server versions and middleware that don't support the vers…Protocol Versions Pseudo-TypesManual ChaptersPG188.21. Pseudo-Types The PostgreSQL type system contains a number of special-purpose entries that are collectively called pseudo-types. A pseudo-type cannot be used as a column data type, but …Pseudo-Types Pseudo-TypesManual ChaptersPG1836.2.4. Pseudo-Types There are a few “pseudo-types” for special purposes. Pseudo-types cannot appear as columns of tables or components of container types, but they can be used to declare th…Pseudo-Types psqlManual ChaptersPG18psql psql — PostgreSQL interactive terminal Synopsis psql [option...] [dbname [username]]psql psqlManual ChaptersPG18E.6.3.6. psql Allow psql to parse, bind, and close named prepared statements (Anthonin Bonnefoy, Michael Paquier) § § This is accomplished with new commands \parse, \bind_named, and \close_p…psql psql SupportManual ChaptersPG1812.10. psql Support Information about text search configuration objects can be obtained in psql using a set of commands: \dF{d,p,t}[+] [PATTERN] An optional + produces more details. The opti…psql Support PublicationManual ChaptersPG1829.1. Publication A publication can be defined on any physical replication primary. The node where a publication is defined is referred to as publisher. A publication is a set of changes gen…Publication PublishersManual ChaptersPG1829.12.1. Publishers wal_level must be set to logical. max_replication_slots must be set to at least the number of subscriptions expected to connect, plus some reserve for table synchronizati…Publishers Python 2 vs. Python 3Manual ChaptersPG1844.10. Python 2 vs. Python 3 PL/Python supports only Python 3. Past versions of PostgreSQL supported Python 2, using the plpythonu and plpython2u language names.Python 2 vs. Python 3 QueriesManual ChaptersPG18Chapter 7. Queries The previous chapters explained how to create tables, how to fill them with data, and how to manipulate that data. Now we finally discuss how to retrieve the data from the…Queries Queries Are Restricted to XPath 1.0Manual ChaptersPG18D.3.1. Queries Are Restricted to XPath 1.0 The PostgreSQL-specific functions xpath() and xpath_exists() query XML documents using the XPath language. PostgreSQL also provides XPath-only vari…Queries Are Restricted to XPath 1.0 Query Handling as a Complex Optimization ProblemManual ChaptersPG1861.1. Query Handling as a Complex Optimization Problem Among all relational operators the most difficult one to process and optimize is the join. The number of possible query plans grows exp…Query Handling as a Complex Optimization Problem Query Language (SQL) FunctionsManual ChaptersPG1836.5. Query Language (SQL) Functions SQL functions execute an arbitrary list of SQL statements, returning the result of the last query in the list. In the simple (non-set) case, the first ro…Query Language (SQL) Functions Query PlanningManual ChaptersPG1819.7. Query PlanningQuery Planning Query RewritingManual ChaptersPG1812.4.2.1. Query Rewriting The ts_rewrite family of functions search a given tsquery for occurrences of a target subquery, and replace each occurrence with a substitute subquery. In essence t…Query Rewriting Querying a TableManual ChaptersPG182.5. Querying a Table To retrieve data from a table, the table is queried. An SQL SELECT statement is used to do this. The statement is divided into a select list (the part that lists the co…Querying a Table Querying and Manipulating Replication SlotsManual ChaptersPG1826.2.6.1. Querying and Manipulating Replication Slots Each replication slot has a name, which can contain lower-case letters, numbers, and the underscore character. Existing replication slot…Querying and Manipulating Replication Slots Quick SetupManual ChaptersPG1829.14. Quick Setup First set the configuration options in postgresql.conf: wal_level = logical The other required settings have default values that are sufficient for a basic setup. pg_hba.c…Quick Setup RADIUS AuthenticationManual ChaptersPG1820.11. RADIUS Authentication This authentication method operates similarly to password except that it uses RADIUS as the password verification method. RADIUS is used only to validate the use…RADIUS Authentication Random-Data FunctionsManual ChaptersPG18F.26.5. Random-Data Functions gen_random_bytes(count integer) returns bytea Returns count cryptographically strong random bytes. At most 1024 bytes can be extracted at a time. This is to avo…Random-Data Functions Range Input/OutputManual ChaptersPG188.17.5. Range Input/Output The input for a range value must follow one of the following patterns: (lower-bound,upper-bound) (lower-bound,upper-bound] [lower-bound,upper-bound) [lower-bound,u…Range Input/Output Range TypesManual ChaptersPG188.17. Range Types Range types are data types representing a range of values of some element type (called the range's subtype). For instance, ranges of timestamp might be used to represent th…Range Types Range TypesManual ChaptersPG18Range Types The third form of CREATE TYPE creates a new range type, as described in Section 8.17. The range type's subtype can be any type with an associated b-tree operator class (to determ…Range Types Range/Multirange Functions and OperatorsManual ChaptersPG189.20. Range/Multirange Functions and Operators See Section 8.17 for an overview of range types. Table 9.58 shows the specialized operators available for range types. Table 9.59 shows the spe…Range/Multirange Functions and Operators Ranking Search ResultsManual ChaptersPG1812.3.3. Ranking Search Results Ranking attempts to measure how relevant documents are to a particular query, so that when there are many matches the most relevant ones can be shown first. Po…Ranking Search Results RationaleManual ChaptersPG18F.9.1. Rationale The standard approach to doing case-insensitive matches in PostgreSQL has been to use the lower function when comparing values, for example SELECT * FROM tab WHERE lower(col…Rationale RationaleManual ChaptersPG18F.21.1. Rationale One of the problems with the JDBC driver (and this affects the ODBC driver also), is that the specification assumes that references to BLOBs (Binary Large OBjects) are stor…Rationale RationaleManual ChaptersPG18F.39.1. Rationale The geometry of measurements is usually more complex than that of a point in a numeric continuum. A measurement is usually a segment of that continuum with somewhat fuzzy l…Rationale