~vcs-imports/mammoth-replicator/trunk

[ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]

[ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]

[ OFFSET <replaceable class="PARAMETER">start</replaceable> ]

[ FOR UPDATE [ OF <replaceable class="PARAMETER">tablename</replaceable> [, ...] ] ]

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>SELECT INTO</command> creates a new table and fills it

with data computed by a query. The data is not returned to the

client, as it is with a normal <command>SELECT</command>. The new

table's columns have the names and data types associated with the

output columns of the <command>SELECT</command>.

</para>

</refsect1>

<title>Parameters</title>

<term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>

<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>

<term><replaceable class="PARAMETER">new_table</replaceable></term>

<para>

The name (optionally schema-qualified) of the table to be created.

</para>

</listitem>

</varlistentry>

</variablelist>

<para>

All other parameters are described in detail under <xref

linkend="sql-select" endterm="sql-select-title">.

</para>

</refsect1>

<title>Notes</title>

<para>

<xref linkend="sql-createtableas"

endterm="sql-createtableas-title"> is functionally similar to

<command>SELECT INTO</command>. <command>CREATE TABLE AS</command>

is the recommended syntax, since this form of <command>SELECT

INTO</command> is not available in <application>ECPG</application>

or <application>PL/pgSQL</application>, because they interpret the

<literal>INTO</literal> clause differently. Furthermore,

<command>CREATE TABLE AS</command> offers a superset of the

functionality provided by <command>SELECT INTO</command>.

</para>

<para>

Prior to <productname>PostgreSQL</> 8.0, the table created by

<command>SELECT INTO</command> always included OIDs.

As of <productname>PostgreSQL</> 8.0, the

100

inclusion of OIDs in the table created by <command>SELECT

101

INTO</command> is controlled by the

102

<xref linkend="guc-default-with-oids"> configuration variable. This

103

variable currently defaults to true, but will likely default to

104

false in a future release of <productname>PostgreSQL</>.

105

</para>

106

</refsect1>

107

108

109

<title>Examples</title>

110

111

<para>

112

Create a new table <literal>films_recent</literal> consisting of only

113

recent entries from the table <literal>films</literal>:

114

115

116

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

117

</programlisting>

118

</para>

119

</refsect1>

120

121

122

<title>Compatibility</title>

123

124

<para>

125

The SQL standard uses <command>SELECT INTO</command> to

126

represent selecting values into scalar variables of a host program,

127

rather than creating a new table. This indeed is the usage found

128

in <application>ECPG</application> (see <xref linkend="ecpg">) and

129

<application>PL/pgSQL</application> (see <xref linkend="plpgsql">).

130

The <productname>PostgreSQL</productname> usage of <command>SELECT

131

INTO</command> to represent table creation is historical. It is

132

best to use <command>CREATE TABLE AS</command> for this purpose in

133

new code.

134

</para>

135

</refsect1>

136

137

138

139

140

141

142

</simplelist>

143

</refsect1>

144

</refentry>

145

146

<!-- Keep this comment at the end of the file

147

Local variables:

148

mode: sgml

149

sgml-omittag:nil

150

sgml-shorttag:t

151

sgml-minimize-attributes:nil

152

sgml-always-quote-attributes:t

153

sgml-indent-step:1

154

sgml-indent-data:t

155

sgml-parent-document:nil

156

sgml-default-dtd-file:"../reference.ced"

157

sgml-exposed-tags:nil

158

sgml-local-catalogs:"/usr/lib/sgml/catalog"

159

sgml-local-ecat-files:nil

160

End:

161

-->

Older »