19.1.1. Parameter Names and Values All parameter names are case-insensitive. Every parameter takes a value of one of five types: boolean, string, integer, floating point, or enumerated (enum). The type determines the syntax for setting the parameter: Boolean: Values can be written as on, off, true, false, yes, no, 1, 0 (all case-insensitive) or any unambiguous prefix of one of these. String: In general, enclose the value in single quotes, doubling any single quotes within the value. Quotes

Before anyone can access the database, you must start the database server. The database server program is called postgres. The postgres program must know where to find the data it is supposed to use. This is done with the -D option. Thus, the simplest way to start the server is: $ postgres -D /usr/local/pgsql/data which will leave the server running in the foreground. This must be done while logged into the PostgreSQL user account. Without -D, the server will try to use the data directory nam

The catalog pg_index contains part of the information about indexes. The rest is mostly in pg_class. Table 50-26. pg_index Columns Name Type References Description indexrelid oid pg_class.oid The OID of the pg_class entry for this index indrelid oid pg_class.oid The OID of the pg_class entry for the table this index is for indnatts int2 The number of columns in the index (duplicates pg_class.relnatts) indisunique bool If true, this is a unique index indisprimary bool If true, this index

Table 8-4. Character Types Name Description character varying(n), varchar(n) variable-length with limit character(n), char(n) fixed-length, blank padded text variable unlimited length Table 8-4 shows the general-purpose character types available in PostgreSQL. SQL defines two primary character types: character varying(n) and character(n), where n is a positive integer. Both of these types can store strings up to n characters (not bytes) in length. An attempt to store a longer string into

Databases are destroyed with the command DROP DATABASE: DROP DATABASE name; Only the owner of the database, or a superuser, can drop a database. Dropping a database removes all objects that were contained within the database. The destruction of a database cannot be undone. You cannot execute the DROP DATABASE command while connected to the victim database. You can, however, be connected to any other database, including the template1 database. template1 would be the only option for dropping th

This section provides an overview of TOAST (The Oversized-Attribute Storage Technique). PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. This happens transparently to the user, with only small impact on most of the backend code. The technique is affectionately know

The auto_explain module provides a means for logging execution plans of slow statements automatically, without having to run EXPLAIN by hand. This is especially helpful for tracking down un-optimized queries in large applications. The module provides no SQL-accessible functions. To use it, simply load it into the server. You can load it into an individual session: LOAD 'auto_explain'; (You must be superuser to do that.) More typical usage is to preload it into some or all sessions by includin

The view pg_policies provides access to useful information about each row-level security policy in the database. Table 50-68. pg_policies Columns Name Type References Description schemaname name pg_namespace.nspname Name of schema containing table policy is on tablename name pg_class.relname Name of table policy is on policyname name pg_policy.polname Name of policy roles name[] The roles to which this policy applies cmd text The command type to which the policy is applied qual text The

Namecreateuser -- define a new PostgreSQL user account Synopsis createuser [connection-option...] [option...] [username] Description createuser creates a new PostgreSQL user (or more precisely, a role). Only superusers and users with CREATEROLE privilege can create new users, so createuser must be invoked by someone who can connect as a superuser or a user with CREATEROLE privilege. If you wish to create a new superuser, you must connect as a superuser, not merely with CREATEROLE privil

The catalog pg_attrdef stores column default values. The main information about columns is stored in pg_attribute (see below). Only columns that explicitly specify a default value (when the table is created or the column is added) will have an entry here. Table 50-6. pg_attrdef Columns Name Type References Description oid oid Row identifier (hidden attribute; must be explicitly selected) adrelid oid pg_class.oid The table this column belongs to adnum int2 pg_attribute.attnum The number of t