You can create a new table by specifying the table name, along with all column names and their types: CREATE TABLE weather ( city varchar(80), temp_lo int, -- low temperature temp_hi int, -- high temperature prcp real, -- precipitation date date ); You can enter this into psql with the line breaks. psql will recognize that the command is not terminated until the semicolon. White space (i.e., spac

The view pg_views provides access to useful information about each view in the database. Table 50-84. pg_views Columns Name Type References Description schemaname name pg_namespace.nspname Name of schema containing view viewname name pg_class.relname Name of view viewowner name pg_authid.rolname Name of view's owner definition text View definition (a reconstructed SELECT query) Prev Next pg_user_mappings Up Frontend/Backend Protocol

NameSET CONSTRAINTS -- set constraint check timing for the current transaction Synopsis SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE } Description SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode. Upon creation, a constraint is given one of three cha

The character set support in PostgreSQL allows you to store text in a variety of character sets (also called encodings), including single-byte character sets such as the ISO 8859 series and multiple-byte character sets such as EUC (Extended Unix Code), UTF-8, and Mule internal code. All supported character sets can be used transparently by clients, but a few are not supported for use within the server (that is, as a server-side encoding). The default character set is selected while initializing

So far we have explained how to add data to tables and how to change data. What remains is to discuss how to remove data that is no longer needed. Just as adding data is only possible in whole rows, you can only remove entire rows from a table. In the previous section we explained that SQL does not provide a way to directly address individual rows. Therefore, removing rows can only be done by specifying conditions that the rows to be removed have to match. If you have a primary key in the table

NameDROP RULE -- remove a rewrite rule Synopsis DROP RULE [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ] Description DROP RULE drops a rewrite rule. Parameters IF EXISTS Do not throw an error if the rule does not exist. A notice is issued in this case. name The name of the rule to drop. table_name The name (optionally schema-qualified) of the table or view that the rule applies to. CASCADE Automatically drop objects that depend on the rule, and in turn all objects

PostgreSQL supports basic table partitioning. This section describes why and how to implement partitioning as part of your database design. 5.10.1. Overview Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits: Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. The pa

NameCREATE MATERIALIZED VIEW -- define a new materialized view Synopsis CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ] Description CREATE MATERIALIZED VIEW defines a materialized view of a query. The query is executed and used to populate the view at the time the command is issued (unless WITH NO DATA is used) and may be r

In addition to the postgresql.conf file already mentioned, PostgreSQL uses two other manually-edited configuration files, which control client authentication (their use is discussed in Chapter 20). By default, all three configuration files are stored in the database cluster's data directory. The parameters described in this section allow the configuration files to be placed elsewhere. (Doing so can ease administration. In particular it is often easier to ensure that the configuration files are

Nameclusterdb -- cluster a PostgreSQL database Synopsis clusterdb [connection-option...] [--verbose | -v] [ --table | -t table ] ... [dbname] clusterdb [connection-option...] [--verbose | -v] --all | -a Description clusterdb is a utility for reclustering tables in a PostgreSQL database. It finds tables that have previously been clustered, and clusters them again on the same index that was last used. Tables that have never been clustered are not affected. clusterdb is a wrapper around th