~vcs-imports/mammoth-replicator/trunk

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

AS <replaceable>query</replaceable>

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>CREATE TABLE AS</command> creates a table and fills it

with data computed by a <command>SELECT</command> command or an

<command>EXECUTE</command> that runs a prepared

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

<title>Parameters</title>

<term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term>

<para>

Ignored for compatibility. Refer to <xref

linkend="sql-createtable" endterm="sql-createtable-title"> for

details.

</para>

</listitem>

</varlistentry>

</variablelist>

<term><literal>TEMPORARY</> or <literal>TEMP</></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>table_name</replaceable></term>

<para>

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

</para>

</listitem>

</varlistentry>

<term><replaceable>column_name</replaceable></term>

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

100

101

102

103

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

104

105

<para>

106

This optional clause specifies whether the table created by

107

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

108

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

109

<xref linkend="guc-default-with-oids"> configuration parameter is

110

used.

111

</para>

112

</listitem>

113

</varlistentry>

114

115

116

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

117

118

<para>

119

A query statement (that is, a <command>SELECT</command> command

120

or an <command>EXECUTE</command> command that runs a prepared

121

<command>SELECT</command> command). Refer to <xref

122

linkend="sql-select" endterm="sql-select-title"> or <xref

123

linkend="sql-execute" endterm="sql-execute-title">,

124

respectively, for a description of the allowed syntax.

125

</para>

126

</listitem>

127

</varlistentry>

128

</variablelist>

129

</refsect1>

130

131

132

<title>Notes</title>

133

134

<para>

135

This command is functionally similar to <xref

136

linkend="sql-selectinto" endterm="sql-selectinto-title">, but it is

137

preferred since it is less likely to be confused with other uses of

138

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

139

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

140

by <command>SELECT INTO</command>.

141

</para>

142

143

<para>

144

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

145

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

146

produced. As of <productname>PostgresSQL</productname> 8.0,

147

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

148

explicitly specify whether OIDs should be included. If the

149

presence of OIDs is not explicitly specified,

150

the <xref linkend="guc-default-with-oids"> configuration variable is

151

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

152

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

153

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

154

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

155

OIDS</literal> to ensure compatibility with future versions

156

of <productname>PostgreSQL</productname>.

157

</para>

158

</refsect1>

159

160

161

<title>Examples</title>

162

163

<para>

164

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

165

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

166

167

168

CREATE TABLE films_recent AS

169

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

170

</programlisting>

171

</para>

172

</refsect1>

173

174

175

<title>Compatibility</title>

176

177

<para>

178

<command>CREATE TABLE AS</command> is specified by the SQL:2003

179

standard. There are some small differences between the definition

180

of the command in SQL:2003 and its implementation in

181

<productname>PostgreSQL</>:

182

183

184

185

<para>

186

The standard requires parentheses around the subquery clause; in

187

<productname>PostgreSQL</productname>, these parentheses are

188

optional.

189

</para>

190

</listitem>

191

192

193

<para>

194

The standard defines an <literal>ON COMMIT</literal> clause;

195

this is not currently implemented by <productname>PostgreSQL</>.

196

</para>

197

</listitem>

198

199

200

<para>

201

The standard defines a <literal>WITH DATA</literal> clause;

202

this is not currently implemented by <productname>PostgreSQL</>.

203

</para>

204

</listitem>

205

</itemizedlist>

206

</para>

207

</refsect1>

208

209

210

211

212

213

214

215

216

217

</simplelist>

218

</refsect1>

219

220

</refentry>

221

222

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

223

Local variables:

224

mode: sgml

225

sgml-omittag:nil

226

sgml-shorttag:t

227

sgml-minimize-attributes:nil

228

sgml-always-quote-attributes:t

229

sgml-indent-step:1

230

sgml-indent-data:t

231

sgml-parent-document:nil

232

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

233

sgml-exposed-tags:nil

234

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

235

sgml-local-ecat-files:nil

236

End:

237

-->

Older »