NameCREATE AGGREGATE -- define a new aggregate function Synopsis CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) ( SFUNC = sfunc, STYPE = state_data_type [ , SSPACE = state_data_size ] [ , FINALFUNC = ffunc ] [ , FINALFUNC_EXTRA ] [ , COMBINEFUNC = combinefunc ] [ , SERIALFUNC = serialfunc ] [ , DESERIALFUNC = deserialfunc ] [ , INITCOND = initial_condition ] [ , MSFUNC = msfunc ] [ , MINVFUNC = minvfunc ] [ , MSTYP

NameCREATE ACCESS METHOD -- define a new access method Synopsis CREATE ACCESS METHOD name TYPE access_method_type HANDLER handler_function Description CREATE ACCESS METHOD creates a new access method. The access method name must be unique within the database. Only superusers can define new access methods. Parameters name The name of the access method to be created. access_method_type This clause specifies the type of access method to define. Only INDEX is supported at p

NameCOPY -- copy data between a file and a table Synopsis COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ] where option can be one of: FORMAT format_name OIDS [ boolean ] FREEZE [ boolean ] DELIMITER 'delimiter_character' NULL 'null_string'

At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory. The log records every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by "replaying" the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-

Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should be only one

This section describes the SQL-compliant conditional expressions available in PostgreSQL. Tip: If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a stored procedure in a more expressive programming language. 9.17.1. CASE The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages: CASE WHEN condition THEN result [WHEN ...] [ELSE result] END CASE clauses can

PostgreSQL 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 Control, MVCC). This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data row

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 used in many of the same ways that simple types can be used. For example, a column of a table can be declared to be of a composite type. 8.16.1. Declaration of Composite Types Here are two simple examples of defining composite types: CREATE TYPE complex AS ( r double precision, i double precision ); CREATE

The usual comparison operators are available, as shown in Table 9-1. Table 9-1. Comparison Operators Operator Description < less than > greater than <= less than or equal to >= greater than or equal to = equal <> or != not equal Note: The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things. Comparison operators are available for all relevant data types. All comparison operators are

Shared Disk Failover Shared disk failover avoids synchronization overhead by having only one copy of the database. It uses a single disk array that is shared by multiple servers. If the main database server fails, the standby server is able to mount and start the database as though it were recovering from a database crash. This allows rapid failover with no data loss. Shared hardware functionality is common in network storage devices. Using a network file system is also possible, though care