Search PostgreSQL documentation

Installing Procedural LanguagesManual ChaptersPG18A procedural language must be “installed” into each database where it is to be used. But procedural languages installed in the database template1 are automatically available in all subsequen… Insufficient Stack DepthManual ChaptersPG18Test EvaluationIf the errors test results in a server crash at the select infinite_recurse() command, it means that the platform's limit on process stack size is smaller than the max_stack_depth parameter … intFunctionsPG18pgbench › Notes › Built-In Functions( number ) → integer Casts to integer. int(5.4 + 3.8) → 9 intaggExtensionsPG18intagg — integer aggregator and enumeratorinteger aggregator and enumerator The intagg module provides an integer aggregator and an enumerator. intagg is now obsolete, because there are built-in functions that provide a superset of … intagg — integer aggregator and enumeratorManual ChaptersPG18The intagg module provides an integer aggregator and an enumerator. intagg is now obsolete, because there are built-in functions that provide a superset of its capabilities. However, the mod… intarrayExtensionsPG18intarray — manipulate arrays of integersmanipulate arrays of integers The intarray module provides a number of useful functions and operators for manipulating null-free arrays of integers. There is also support for indexed searche… intarray Functions and OperatorsManual ChaptersPG18intarray — manipulate arrays of integersThe functions provided by the intarray module are shown in Table F.8, the operators in Table F.9. Table F.8. intarray Functions Function Description Example(s) icount ( integer[] ) → integer… intarray — manipulate arrays of integersManual ChaptersPG18The intarray module provides a number of useful functions and operators for manipulating null-free arrays of integers. There is also support for indexed searches using some of the operators.… Integer TypesManual ChaptersPG18Numeric TypesThe types smallint, integer, and bigint store whole numbers, that is, numbers without fractional components, of various ranges. Attempts to store values outside of the allowed range will res… interconnectExtensionsExtensionsAnalytics · ExtensionsOne advanced and mature open-source MPP (Massively Parallel Processing) database. Open source alternative to Greenplum Database. ai big-data c cloudberry data-analysis data-warehouse databas… Interface FunctionsManual ChaptersPG18 Interface Support FunctionsManual ChaptersPG18The functions described here provide an interface for extracting information from result sets returned by SPI_execute and other SPI functions. All functions described in this section can be … Interfacing Extensions to IndexesManual ChaptersPG18The procedures described thus far let you define new types, new functions, and new operators. However, we cannot yet define an index on a column of a new data type. To do this, we must defin… Interleaving Result Processing and Query DispatchManual ChaptersPG18Pipeline ModeTo avoid deadlocks on large pipelines the client should be structured around a non-blocking event loop using operating system facilities such as select, poll, WaitForMultipleObjectEx, etc. T… Internal FunctionsManual ChaptersPG18are functions written in C that have been statically linked into the PostgreSQL server. The “body” of the function definition specifies the C-language name of the function, which need not be… InternalsManual ChaptersPG18This section explains how ECPG works internally. This information can occasionally be useful to help users understand how to use ECPG. The first four lines written by ecpg to the output are … InternalsManual ChaptersPG18Part VII. Internals This part contains assorted information that might be of use to PostgreSQL developers. Interpreting ResultsManual ChaptersPG18pg_test_timingGood results will show most (>90%) individual timing calls take less than one microsecond. Average per loop overhead will be even lower, below 100 nanoseconds. This example from an Intel i7-… INTERSECTSQL CommandsPG18Combining Queries (UNION, INTERSECT, EXCEPT)7.4. Combining Queries (UNION, INTERSECT, EXCEPT) # The results of two queries can be combined using the set operations union, intersection, and difference. The syntax is query1 UNION [ALL] … INTERSECT ClauseManual ChaptersPG18SELECTThe INTERSECT clause has this general form: select_statement INTERSECT [ ALL | DISTINCT ] select_statement select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPD… intervalManual ChaptersPG18Using Host VariablesThe handling of the interval type is also similar to the timestamp and date types. It is required, however, to allocate memory for an interval type value explicitly. In other words, the memo… Interval InputManual ChaptersPG18Date/Time Typesinterval values can be written using the following verbose syntax: [@] quantity unit [quantity unit...] [direction] where quantity is a number (possibly signed); unit is microsecond, millise… Interval OutputManual ChaptersPG18Date/Time TypesAs previously explained, PostgreSQL stores interval values as months, days, and microseconds. For output, the months field is converted to years and months by dividing by 12. The days field … intpairExtensionsExtensionsData types · ExtensionsPacked int64pair data type with comparison, hash, and index operator support. data-type index pg_intpair Data types IntroductionManual ChaptersPG18BRIN IndexesBRIN stands for Block Range Index. BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table. BRIN … IntroductionManual ChaptersPG18B-Tree IndexesPostgreSQL includes an implementation of the standard btree (multi-way balanced tree) index data structure. Any data type that can be sorted into a well-defined linear order can be indexed b… IntroductionManual ChaptersPG18GIN IndexesGIN stands for Generalized Inverted Index. GIN is designed for handling cases where the items to be indexed are composite values, and the queries to be handled by the index need to search fo… IntroductionManual ChaptersPG18GiST IndexesGiST stands for Generalized Search Tree. It is a balanced, tree-structured access method, that acts as a base template in which to implement arbitrary indexing schemes. B-trees, R-trees and … IntroductionManual ChaptersPG18Suppose we have a table similar to this: CREATE TABLE test1 ( id integer, content varchar ); and the application issues many queries of the form: SELECT content FROM test1 WHERE id = constan… IntroductionManual ChaptersPG18All large objects are stored in a single system table named pg_largeobject. Each large object also has an entry in the system table pg_largeobject_metadata. Large objects can be created, mod… IntroductionManual ChaptersPG18PostgreSQL provides a rich set of tools for developers to manage concurrent access to data. Internally, data consistency is maintained by using a multiversion model (Multiversion Concurrency… IntroductionManual ChaptersPG18SP-GiST IndexesSP-GiST is an abbreviation for space-partitioned GiST. SP-GiST supports partitioned search trees, which facilitate development of a wide range of different non-balanced data structures, such… IntroductionManual ChaptersPG18Full Text Searching (or just text search) provides the capability to identify natural-language documents that satisfy a query, and optionally to sort them by relevance to the query. The most… IntroductionManual ChaptersPG18In the previous chapter we have covered the basics of using SQL to store and access your data in PostgreSQL. We will now discuss some more advanced features of SQL that simplify management a… IntroductionManual ChaptersPG18This chapter provides an overview of how to use SQL to perform simple operations. This tutorial is only intended to give you an introduction and is in no way a complete tutorial on SQL. Nume… intsetFunctionsPG18intarray — manipulate arrays of integers › intarray Functions and Operators( integer ) → integer[] Makes a single-element array. intset(42) → {42} ip4rExtensionsExtensionsData types · ExtensionsIPv4/v6 and IPv4/v6 range index type for PostgreSQL ip4r Data types ipinfoExtensionsExtensionsGeospatial · ExtensionsIP geolocation lookup functions backed by the ipinfo.io HTTP service. ip geolocation http ipinfo ipinfo Geospatial IS DOCUMENTSQL CommandsPG18XML Functions › XML Predicates › IS DOCUMENT# xml IS DOCUMENT → boolean The expression IS DOCUMENT returns true if the argument XML value is a proper XML document, false if it is not (that is, it is a content fragment), or null if the… IS DOCUMENTManual ChaptersPG18XML Functionsxml IS DOCUMENT → boolean The expression IS DOCUMENT returns true if the argument XML value is a proper XML document, false if it is not (that is, it is a content fragment), or null if the a…