| <!-- |
| |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.36 2006/09/18 19:54:01 tgl Exp $ |
| |
| PostgreSQL documentation |
| |
| --> |
| |
| |
| |
| <refentry id="SQL-CREATETABLEAS"> |
| |
| <refmeta> |
| |
| <refentrytitle id="sql-createtableas-title">CREATE TABLE AS</refentrytitle> |
| |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| |
| </refmeta> |
| |
| |
| |
| <refnamediv> |
| |
| <refname>CREATE TABLE AS</refname> |
| |
| <refpurpose>define a new table from the results of a query</refpurpose> |
| |
| </refnamediv> |
| |
| |
| |
| <indexterm zone="sql-createtableas"> |
| |
| <primary>CREATE TABLE AS</primary> |
| |
| </indexterm> |
| |
| |
| |
| <refsynopsisdiv> |
| |
| <synopsis> |
| |
| CREATE [ [GLOBAL | LOCAL] {TEMPORARY | TEMP} ] TABLE table_name |
| [(column_name [, ...] )] |
| [ WITH ( storage_parameter=value [, ... ] ) |
| [ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP}] |
| [TABLESPACE tablespace] |
| AS query |
| [DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY] |
| |
| where storage_parameter is: |
| |
| APPENDONLY={TRUE|FALSE} |
| BLOCKSIZE={8192-2097152} |
| ORIENTATION={PARQUET|ROW} |
| COMPRESSTYPE={ZLIB|SNAPPY|GZIP|NONE} |
| COMPRESSLEVEL={0-9} |
| FILLFACTOR={10-100} |
| OIDS[=TRUE|FALSE] |
| |
| |
| </synopsis> |
| |
| </refsynopsisdiv> |
| |
| |
| |
| <refsect1> |
| |
| <title>Description</title> |
| |
| |
| |
| <para> |
| |
| <command>CREATE TABLE AS</command> creates a table and fills it |
| |
| with data computed by a <command>SELECT</command> command. |
| |
| The table columns have the |
| |
| names and data types associated with the output columns of the |
| |
| <command>SELECT</command> (except that you can override the column |
| |
| names by giving an explicit list of new column names). |
| |
| </para> |
| |
| |
| |
| <para> |
| |
| <command>CREATE TABLE AS</command> bears some resemblance to |
| |
| creating a view, but it is really quite different: it creates a new |
| |
| table and evaluates the query just once to fill the new table |
| |
| initially. The new table will not track subsequent changes to the |
| |
| source tables of the query. In contrast, a view re-evaluates its |
| |
| defining <command>SELECT</command> statement whenever it is |
| |
| queried. |
| |
| </para> |
| |
| </refsect1> |
| |
| |
| |
| <refsect1> |
| |
| <title>Parameters</title> |
| |
| |
| |
| <variablelist> |
| |
| <varlistentry> |
| |
| <term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term> |
| |
| <listitem> |
| |
| <para> |
| |
| Ignored for compatibility. Refer to <xref |
| |
| linkend="sql-createtable" endterm="sql-createtable-title"> for |
| |
| details. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| </variablelist> |
| |
| |
| |
| <variablelist> |
| |
| <varlistentry> |
| |
| <term><literal>TEMPORARY</> or <literal>TEMP</></term> |
| |
| <listitem> |
| |
| <para> |
| |
| If specified, the table is created as a temporary table. |
| |
| Refer to <xref linkend="sql-createtable" endterm="sql-createtable-title"> for details. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><replaceable>table_name</replaceable></term> |
| |
| <listitem> |
| |
| <para> |
| |
| The name (optionally schema-qualified) of the table to be created. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><replaceable>column_name</replaceable></term> |
| |
| <listitem> |
| |
| <para> |
| |
| The name of a column in the new table. If column names are not |
| |
| provided, they are taken from the output column names of the |
| |
| query. If the table is created from an |
| |
| <command>EXECUTE</command> command, a column name list cannot be |
| |
| specified. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><literal>WITH ( <replaceable class="PARAMETER">storage_parameter</replaceable> [= <replaceable class="PARAMETER">value</replaceable>] [, ... ] )</literal></term> |
| |
| <listitem> |
| |
| <para> |
| |
| This clause specifies optional storage parameters for the new table; |
| |
| see <xref linkend="sql-createtable-storage-parameters" |
| |
| endterm="sql-createtable-storage-parameters-title"> for more |
| |
| information. The <literal>WITH</> clause |
| |
| can also include <literal>OIDS=TRUE</> (or just <literal>OIDS</>) |
| |
| to specify that rows of the new table |
| |
| should have OIDs (object identifiers) assigned to them, or |
| |
| <literal>OIDS=FALSE</> to specify that the rows should not have OIDs. |
| |
| See <xref linkend="sql-createtable" |
| |
| endterm="sql-createtable-title"> for more information. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><literal>WITH OIDS</></term> |
| |
| <term><literal>WITHOUT OIDS</></term> |
| |
| <listitem> |
| |
| <para> |
| |
| These are obsolescent syntaxes equivalent to <literal>WITH (OIDS)</> |
| |
| and <literal>WITH (OIDS=FALSE)</>, respectively. If you wish to give |
| |
| both an <literal>OIDS</> setting and storage parameters, you must use |
| |
| the <literal>WITH ( ... )</> syntax; see above. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><literal>ON COMMIT</literal></term> |
| |
| <listitem> |
| |
| <para> |
| |
| The behavior of temporary tables at the end of a transaction |
| |
| block can be controlled using <literal>ON COMMIT</literal>. |
| |
| The three options are: |
| |
| |
| |
| <variablelist> |
| |
| <varlistentry> |
| |
| <term><literal>PRESERVE ROWS</literal></term> |
| |
| <listitem> |
| |
| <para> |
| |
| No special action is taken at the ends of transactions. |
| |
| This is the default behavior. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><literal>DELETE ROWS</literal></term> |
| |
| <listitem> |
| |
| <para> |
| |
| All rows in the temporary table will be deleted at the end |
| |
| of each transaction block. Essentially, an automatic <xref |
| |
| linkend="sql-truncate" endterm="sql-truncate-title"> is done |
| |
| at each commit. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><literal>DROP</literal></term> |
| |
| <listitem> |
| |
| <para> |
| |
| The temporary table will be dropped at the end of the current |
| |
| transaction block. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| </variablelist> |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><literal>TABLESPACE <replaceable class="PARAMETER">tablespace</replaceable></literal></term> |
| |
| <listitem> |
| |
| <para> |
| |
| The <replaceable class="PARAMETER">tablespace</replaceable> is the name |
| |
| of the tablespace in which the new table is to be created. |
| |
| If not specified, |
| |
| <xref linkend="guc-default-tablespace"> is used, or the database's |
| |
| default tablespace if <varname>default_tablespace</> is an empty |
| |
| string. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| |
| |
| <varlistentry> |
| |
| <term><replaceable>query</replaceable></term> |
| |
| <listitem> |
| |
| <para> |
| |
| A <xref linkend="sql-select" endterm="sql-select-title"> or |
| |
| <xref linkend="sql-values" endterm="sql-values-title"> command, |
| |
| or an <xref linkend="sql-execute" endterm="sql-execute-title"> command |
| |
| that runs a prepared <command>SELECT</> or <command>VALUES</> query. |
| |
| </para> |
| |
| </listitem> |
| |
| </varlistentry> |
| |
| </variablelist> |
| |
| </refsect1> |
| |
| |
| |
| <refsect1> |
| |
| <title>Notes</title> |
| |
| |
| |
| <para> |
| |
| This command is functionally similar to <xref |
| |
| linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is |
| |
| preferred since it is less likely to be confused with other uses of |
| |
| the <command>SELECT INTO</> syntax. Furthermore, <command>CREATE |
| |
| TABLE AS</command> offers a superset of the functionality offered |
| |
| by <command>SELECT INTO</command>. |
| |
| </para> |
| |
| |
| |
| <para> |
| |
| Prior to <productname>PostgreSQL</productname> 8.0, <command>CREATE |
| |
| TABLE AS</command> always included OIDs in the table it |
| |
| created. As of <productname>PostgreSQL</productname> 8.0, |
| |
| the <command>CREATE TABLE AS</command> command allows the user to |
| |
| explicitly specify whether OIDs should be included. If the |
| |
| presence of OIDs is not explicitly specified, |
| |
| the <xref linkend="guc-default-with-oids"> configuration variable is |
| |
| used. As of <productname>PostgreSQL</productname> 8.1, |
| |
| this variable is false by default, so the default behavior is not |
| |
| identical to pre-8.0 releases. Applications that |
| |
| require OIDs in the table created by <command>CREATE TABLE |
| |
| AS</command> should explicitly specify <literal>WITH (OIDS)</literal> |
| |
| to ensure proper behavior. |
| |
| </para> |
| |
| </refsect1> |
| |
| |
| |
| <refsect1> |
| |
| <title>Examples</title> |
| |
| |
| |
| <para> |
| |
| Create a new table <literal>films_recent</literal> consisting of only |
| |
| recent entries from the table <literal>films</literal>: |
| |
| |
| |
| <programlisting> |
| |
| CREATE TABLE films_recent AS |
| |
| SELECT * FROM films WHERE date_prod >= '2002-01-01'; |
| |
| </programlisting> |
| |
| </para> |
| |
| |
| |
| <para> |
| |
| Create a new temporary table <literal>films_recent</literal>, consisting of |
| |
| only recent entries from the table <literal>films</literal>, using a |
| |
| prepared statement. The new table has OIDs and will be dropped at commit: |
| |
| |
| |
| <programlisting> |
| |
| PREPARE recentfilms(date) AS |
| |
| SELECT * FROM films WHERE date_prod > $1; |
| |
| CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS |
| |
| EXECUTE recentfilms('2002-01-01'); |
| |
| </programlisting> |
| |
| </para> |
| |
| </refsect1> |
| |
| |
| |
| <refsect1> |
| |
| <title>Compatibility</title> |
| |
| |
| |
| <para> |
| |
| <command>CREATE TABLE AS</command> conforms to the <acronym>SQL</acronym> |
| |
| standard, with the following exceptions: |
| |
| |
| |
| <itemizedlist spacing="compact"> |
| |
| <listitem> |
| |
| <para> |
| |
| The standard requires parentheses around the subquery clause; in |
| |
| <productname>PostgreSQL</productname>, these parentheses are |
| |
| optional. |
| |
| </para> |
| |
| </listitem> |
| |
| |
| |
| <listitem> |
| |
| <para> |
| |
| The standard defines a <literal>WITH [ NO ] DATA</literal> clause; |
| |
| this is not currently implemented by <productname>PostgreSQL</>. |
| |
| The behavior provided by <productname>PostgreSQL</> is equivalent |
| |
| to the standard's <literal>WITH DATA</literal> case. |
| |
| <literal>WITH NO DATA</literal> can be simulated by appending |
| |
| <literal>LIMIT 0</> to the query. |
| |
| </para> |
| |
| </listitem> |
| |
| |
| |
| <listitem> |
| |
| <para> |
| |
| <productname>PostgreSQL</> handles temporary tables in a way |
| |
| rather different from the standard; see |
| |
| <xref linkend="sql-createtable" endterm="sql-createtable-title"> |
| |
| for details. |
| |
| </para> |
| |
| </listitem> |
| |
| |
| |
| <listitem> |
| |
| <para> |
| |
| The <literal>WITH</> clause is a <productname>PostgreSQL</productname> |
| |
| extension; neither storage parameters nor OIDs are in the standard. |
| |
| </para> |
| |
| </listitem> |
| |
| |
| |
| <listitem> |
| |
| <para> |
| |
| The <productname>PostgreSQL</productname> concept of tablespaces is not |
| |
| part of the standard. Hence, the clause <literal>TABLESPACE</literal> |
| |
| is an extension. |
| |
| </para> |
| |
| </listitem> |
| |
| </itemizedlist> |
| |
| </para> |
| |
| </refsect1> |
| |
| |
| |
| <refsect1> |
| |
| <title>See Also</title> |
| |
| |
| |
| <simplelist type="inline"> |
| |
| <member><xref linkend="sql-createtable" endterm="sql-createtable-title"></member> |
| |
| <member><xref linkend="sql-execute" endterm="sql-execute-title"></member> |
| |
| <member><xref linkend="sql-select" endterm="sql-select-title"></member> |
| |
| <member><xref linkend="sql-selectinto" endterm="sql-selectinto-title"></member> |
| |
| <member><xref linkend="sql-values" endterm="sql-values-title"></member> |
| |
| </simplelist> |
| |
| </refsect1> |
| |
| |
| |
| </refentry> |
| |