Tables

Tables are a core database object. The CREATE TABLE statement is very rich. It can be used for several purposes, such as cloning a table, which is handy for database refactoring to create an uninstall script to roll back changes. Also, it can be used to materialize the result of the SELECT statement to boost performance, or for temporarily storing the intermediate data and results. 

The PostgreSQL tables are used internally to model views and sequences. In PostgreSQL, tables can be of different types:

• Ordinary table: The table lifespan starts with table creation and ends with table dropping.
• Temporary table: The table lifespan is the user session. This is often used with procedural languages to model business logic.
• Unlogged table: Unlogged tables are faster than ordinary tables, because data isn't written into the WAL files. Unlogged tables aren't crash-safe. Also, since streaming replication is based on shipping the WAL files, unlogged tables can't be replicated to the standby node.
• Child table: A child table is an ordinary table that inherits one or more tables. The inheritance is often used with constraint exclusion to physically partition the data on the hard disk and to improve performance by retrieving a subset of data that has a certain value.

The CREATE TABLE syntax is quite long; it's full syntax can be found at http://www.postgresql.org/docs/current/static/sql-createtable.html. The CREATE TABLE SQL command normally requires the following input:

• Table name of the created table.
• The table type.
• The table's storage parameters. These parameters are used to control the table's storage allocation and several other administrative tasks.
• The table columns, including the data type, default values, and constraint.
• The cloned table name and the options to clone the table.

To create a table, you can write a query as simple as the following:

CREATE TEMP TABLE temp  AS SELECT 1 AS one;

The preceding SQL command creates a temporary table with one column and one tuple. The column name and type are 1 and integer, respectively.