<!--

$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.19 2003/12/13 23:59:07 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/ref/create_table_as.sgml,v 1.20 2004/01/10 23:28:44 neilc Exp $

PostgreSQL documentation

-->

<refsynopsisdiv>

<synopsis>

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <replaceable>table_name</replaceable> [ (<replaceable>column_name</replaceable> [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]

AS <replaceable>query</replaceable>

</synopsis>

</refsynopsisdiv>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>WITH OIDS</literal></term>

<term><literal>WITHOUT OIDS</literal></term>

<listitem>

<para>

This optional clause specifies whether the table created by

<command>CREATE TABLE AS</command> should include OIDs. If

neither form of this clause if specified, the value of the

<varname>default_with_oids</varname> configuration parameter is

used.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><replaceable>query</replaceable></term>

<listitem>

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</command> syntax.

the <command>SELECT INTO</> syntax. Furthermore, <command>CREATE

TABLE AS</command> offers a superset of the functionality offerred

by <command>SELECT INTO</command>.

</para>

<para>

Prior to <productname>PostgreSQL</> 7.5, <command>CREATE TABLE

AS</command> always included OIDs in the table it

Prior to <productname>PostgreSQL</productname> 7.5, <command>CREATE

TABLEAS</command> always included OIDs in the table it

produced. Furthermore, these OIDs were newly generated: they were

distinct from the OIDs of any of the rows in the source tables of

the <command>SELECT</command> or <command>EXECUTE</command>

statement. Therefore, if <command>CREATE TABLE AS</command> was

frequently executed, the OID counter would be rapidly

incremented. As of <productname>PostgreSQL</> 7.5, the inclusion of

OIDs in the table generated by <command>CREATE TABLE AS</command>

is controlled by the <varname>default_with_oids</varname>

configuration variable. This variable currently defaults to true,

but will likely default to false in a future release of

<productname>PostgreSQL</>.

incremented. As of <productname>PostgresSQL</productname> 7.5,

the <command>CREATE TABLE AS</command> command allows the user to

explicitely specify whether OIDs should be included. If the

presence of OIDs is not explicitely specified,

the <varname>default_with_oids</varname> configuration variable is

used. While this variable currently defaults to true, the default

value may be changed in the future. Therefore, applications that

require OIDs in the table created by <command>CREATE TABLE

AS</command> should explicitely specify <literal>WITH

OIDS</literal> to ensure compatibility with future versions

of <productname>PostgreSQL</productname>.

</para>

</refsect1>

<!--

$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.229 2004/01/06 17:26:22 neilc Exp $

$PostgreSQL: pgsql/doc/src/sgml/runtime.sgml,v 1.230 2004/01/10 23:28:43 neilc Exp $

-->

<Chapter Id="runtime">

<term><varname>default_with_oids</varname> (<type>boolean</type>)</term>

<listitem>

<para>

This controls whether <command>CREATE TABLE</command> will

OIDS</literal> or<literal>WITHOUT OIDS</literal> have been

<command>CREATE TABLE AS</command>. In

This controls whether <command>CREATE TABLE</command>

newly-created tables, if neither<literal>WITH OIDS</literal>

created by<command>SELECT INTO</command>. In

<productname>PostgreSQL</productname> &version;

<varname>default_with_oids</varname> defaults to true. This is

also the behavior of previous versions of

<productname>PostgreSQL</productname>. However, assuming that

tables will contain OIDs by default is not

<varname>default_with_oids</varname> defaults to

true. This isalso the behavior of previous versions

of<productname>PostgreSQL</productname>. However, assuming

thattables will contain OIDs by default is not

encouraged. Therefore, this option will default to false in a

future release of <productname>PostgreSQL</productname>.

</para>

stmt->relation=seq->sequence;

stmt->inhRelations=NIL;

stmt->constraints=NIL;

stmt->hasoids=false;

stmt->hasoids=MUST_NOT_HAVE_OIDS;

stmt->oncommit=ONCOMMIT_NOOP;

seqoid=DefineRelation(stmt,RELKIND_SEQUENCE);

descriptor=BuildDescForRelation(schema);

descriptor->tdhasoid= (stmt->hasoids||parentHasOids);

if (parentHasOids)

descriptor->tdhasoid= true;

{

switch (stmt->hasoids)

{

caseMUST_HAVE_OIDS:

descriptor->tdhasoid= true;

break;

caseMUST_NOT_HAVE_OIDS:

descriptor->tdhasoid= false;

break;

caseDEFAULT_OIDS:

descriptor->tdhasoid=default_with_oids;

break;

}

}

if (old_constraints!=NIL)

{

createStmt->tableElts=coldeflist;

createStmt->inhRelations=NIL;

createStmt->constraints=NIL;

createStmt->hasoids=false;

createStmt->hasoids=MUST_NOT_HAVE_OIDS;

createStmt->oncommit=ONCOMMIT_NOOP;

createStmt->tableElts=attrList;

createStmt->inhRelations=NIL;

createStmt->constraints=NIL;

createStmt->hasoids=false;

createStmt->hasoids=MUST_NOT_HAVE_OIDS;

createStmt->oncommit=ONCOMMIT_NOOP;

if (operation==CMD_SELECT&&parseTree->into!=NULL)

{

do_select_into= true;

estate->es_force_oids=default_with_oids;

estate->es_force_oids=parseTree->intoHasOids;

}

COPY_NODE_FIELD(utilityStmt);

COPY_SCALAR_FIELD(resultRelation);

COPY_NODE_FIELD(into);

COPY_SCALAR_FIELD(intoHasOids);

COPY_SCALAR_FIELD(hasAggs);

COPY_SCALAR_FIELD(hasSubLinks);

COPY_NODE_FIELD(rtable);

COPY_NODE_FIELD(distinctClause);

COPY_NODE_FIELD(into);

COPY_NODE_FIELD(intoColNames);

COPY_SCALAR_FIELD(intoHasOids);

COPY_NODE_FIELD(targetList);

COPY_NODE_FIELD(fromClause);

COPY_NODE_FIELD(whereClause);

COMPARE_NODE_FIELD(utilityStmt);

COMPARE_SCALAR_FIELD(resultRelation);

COMPARE_NODE_FIELD(into);

COMPARE_SCALAR_FIELD(intoHasOids);

COMPARE_SCALAR_FIELD(hasAggs);

COMPARE_SCALAR_FIELD(hasSubLinks);

COMPARE_NODE_FIELD(rtable);

COMPARE_NODE_FIELD(distinctClause);

COMPARE_NODE_FIELD(into);

COMPARE_NODE_FIELD(intoColNames);

COMPARE_SCALAR_FIELD(intoHasOids);

COMPARE_NODE_FIELD(targetList);

COMPARE_NODE_FIELD(fromClause);

COMPARE_NODE_FIELD(whereClause);

if (stmt->intoColNames)

applyColumnNames(qry->targetList,stmt->intoColNames);

switch (stmt->intoHasOids)

{

caseMUST_HAVE_OIDS:

qry->intoHasOids= true;

break;

caseMUST_NOT_HAVE_OIDS:

qry->intoHasOids= false;

break;

caseDEFAULT_OIDS:

qry->intoHasOids=default_with_oids;

break;

}

markTargetListOrigins(pstate,qry->targetList);