1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>CREATE TABLE</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-createsequence.html" title="CREATE SEQUENCE">
<link rel="next" href="sql-createtableas.html" title="CREATE TABLE AS">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-createtable"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>CREATE TABLE — define a new table</p>
</div>
<a name="id762182"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE <em class="replaceable"><code>table_name</code></em> ( [
{ <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>data_type</code></em> [ DEFAULT <em class="replaceable"><code>default_expr</code></em> ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]
| <em class="replaceable"><code>table_constraint</code></em>
| LIKE <em class="replaceable"><code>parent_table</code></em> [ { INCLUDING | EXCLUDING } DEFAULTS ] }
[, ... ]
] )
[ INHERITS ( <em class="replaceable"><code>parent_table</code></em> [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE <em class="replaceable"><code>tablespace</code></em> ]
where <em class="replaceable"><code>column_constraint</code></em> is:
[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
{ NOT NULL |
NULL |
UNIQUE [ USING INDEX TABLESPACE <em class="replaceable"><code>tablespace</code></em> ] |
PRIMARY KEY [ USING INDEX TABLESPACE <em class="replaceable"><code>tablespace</code></em> ] |
CHECK (<em class="replaceable"><code>expression</code></em>) |
REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <em class="replaceable"><code>action</code></em> ] [ ON UPDATE <em class="replaceable"><code>action</code></em> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and <em class="replaceable"><code>table_constraint</code></em> is:
[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
{ UNIQUE ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) [ USING INDEX TABLESPACE <em class="replaceable"><code>tablespace</code></em> ] |
PRIMARY KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) [ USING INDEX TABLESPACE <em class="replaceable"><code>tablespace</code></em> ] |
CHECK ( <em class="replaceable"><code>expression</code></em> ) |
FOREIGN KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] ) REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <em class="replaceable"><code>action</code></em> ] [ ON UPDATE <em class="replaceable"><code>action</code></em> ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]</pre>
</div>
<div class="refsect1" lang="en">
<a name="sql-createtable-description"></a><h2>Description</h2>
<p> <code class="command">CREATE TABLE</code> will create a new, initially empty table
in the current database. The table will be owned by the user issuing the
command.
</p>
<p> If a schema name is given (for example, <code class="literal">CREATE TABLE
myschema.mytable ...</code>) then the table is created in the specified
schema. Otherwise it is created in the current schema. Temporary
tables exist in a special schema, so a schema name may not be given
when creating a temporary table. The name of the table must be
distinct from the name of any other table, sequence, index, or view
in the same schema.
</p>
<p> <code class="command">CREATE TABLE</code> also automatically creates a data
type that represents the composite type corresponding
to one row of the table. Therefore, tables cannot have the same
name as any existing data type in the same schema.
</p>
<p> The optional constraint clauses specify constraints (tests) that
new or updated rows must satisfy for an insert or update operation
to succeed. A constraint is an SQL object that helps define the
set of valid values in the table in various ways.
</p>
<p> There are two ways to define constraints: table constraints and
column constraints. A column constraint is defined as part of a
column definition. A table constraint definition is not tied to a
particular column, and it can encompass more than one column.
Every column constraint can also be written as a table constraint;
a column constraint is only a notational convenience for use when the
constraint only affects one column.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id762473"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code></span></dt>
<dd>
<p> If specified, the table is created as a temporary table.
Temporary tables are automatically dropped at the end of a
session, or optionally at the end of the current transaction
(see <code class="literal">ON COMMIT</code> below). Existing permanent
tables with the same name are not visible to the current session
while the temporary table exists, unless they are referenced
with schema-qualified names. Any indexes created on a temporary
table are automatically temporary as well.
</p>
<p> Optionally, <code class="literal">GLOBAL</code> or <code class="literal">LOCAL</code>
can be written before <code class="literal">TEMPORARY</code> or <code class="literal">TEMP</code>.
This makes no difference in <span class="productname">PostgreSQL</span>, but see
<a href="sql-createtable.html#sql-createtable-compatibility">Compatibility</a>.
</p>
</dd>
<dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of the table to be created.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt>
<dd><p> The name of a column to be created in the new table.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt>
<dd><p> The data type of the column. This may include array
specifiers. For more information on the data types supported by
<span class="productname">PostgreSQL</span>, refer to <a href="datatype.html" title="Chapter8.Data Types">Chapter8, <i>Data Types</i></a>.
</p></dd>
<dt><span class="term"><code class="literal">DEFAULT
<em class="replaceable"><code>default_expr</code></em></code></span></dt>
<dd>
<p> The <code class="literal">DEFAULT</code> clause assigns a default data value for
the column whose column definition it appears within. The value
is any variable-free expression (subqueries and cross-references
to other columns in the current table are not allowed). The
data type of the default expression must match the data type of the
column.
</p>
<p> The default expression will be used in any insert operation that
does not specify a value for the column. If there is no default
for a column, then the default is null.
</p>
</dd>
<dt><span class="term"><code class="literal">INHERITS ( <em class="replaceable"><code>parent_table</code></em> [, ... ] )</code></span></dt>
<dd>
<p> The optional <code class="literal">INHERITS</code> clause specifies a list of
tables from which the new table automatically inherits all
columns.
</p>
<p> Use of <code class="literal">INHERITS</code> creates a persistent relationship
between the new child table and its parent table(s). Schema
modifications to the parent(s) normally propagate to children
as well, and by default the data of the child table is included in
scans of the parent(s).
</p>
<p> If the same column name exists in more than one parent
table, an error is reported unless the data types of the columns
match in each of the parent tables. If there is no conflict,
then the duplicate columns are merged to form a single column in
the new table. If the column name list of the new table
contains a column name that is also inherited, the data type must
likewise match the inherited column(s), and the column
definitions are merged into one. However, inherited and new
column declarations of the same name need not specify identical
constraints: all constraints provided from any declaration are
merged together and all are applied to the new table. If the
new table explicitly specifies a default value for the column,
this default overrides any defaults from inherited declarations
of the column. Otherwise, any parents that specify default
values for the column must all specify the same default, or an
error will be reported.
</p>
</dd>
<dt><span class="term"><code class="literal">LIKE <em class="replaceable"><code>parent_table</code></em> [ { INCLUDING | EXCLUDING } DEFAULTS ]</code></span></dt>
<dd>
<p> The <code class="literal">LIKE</code> clause specifies a table from which
the new table automatically copies all column names, their data types,
and their not-null constraints.
</p>
<p> Unlike <code class="literal">INHERITS</code>, the new table and original table
are completely decoupled after creation is complete. Changes to the
original table will not be applied to the new table, and it is not
possible to include data of the new table in scans of the original
table.
</p>
<p> Default expressions for the copied column definitions will only be
copied if <code class="literal">INCLUDING DEFAULTS</code> is specified. The
default behavior is to exclude default expressions, resulting in
all columns of the new table having null defaults.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">WITH OIDS</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">WITHOUT OIDS</code></span>
</dt>
<dd>
<p> This optional clause specifies whether rows of the new table
should have OIDs (object identifiers) assigned to them. If
neither <code class="literal">WITH OIDS</code> nor <code class="literal">WITHOUT
OIDS</code> is specified, the default value depends upon the
<a href="runtime-config-compatible.html#guc-default-with-oids">default_with_oids</a> configuration parameter. (If
the new table inherits from any tables that have OIDs, then
<code class="literal">WITH OIDS</code> is forced even if the command says
<code class="literal">WITHOUT OIDS</code>.)
</p>
<p> If <code class="literal">WITHOUT OIDS</code> is specified or implied, the new
table does not store OIDs and no OID will be assigned for a row inserted
into it. This is generally considered worthwhile, since it
will reduce OID consumption and thereby postpone the wraparound
of the 32-bit OID counter. Once the counter wraps around, OIDs
can no longer be assumed to be unique, which makes them
considerably less useful. In addition, excluding OIDs from a
table reduces the space required to store the table on disk by
4 bytes per row (on most machines), slightly improving performance.
</p>
<p> To remove OIDs from a table after it has been created, use <a href="sql-altertable.html">ALTER TABLE</a>.
</p>
</dd>
<dt><span class="term"><code class="literal">CONSTRAINT <em class="replaceable"><code>constraint_name</code></em></code></span></dt>
<dd><p> An optional name for a column or table constraint. If not specified,
the system generates a name.
</p></dd>
<dt><span class="term"><code class="literal">NOT NULL</code></span></dt>
<dd><p> The column is not allowed to contain null values.
</p></dd>
<dt><span class="term"><code class="literal">NULL</code></span></dt>
<dd>
<p> The column is allowed to contain null values. This is the default.
</p>
<p> This clause is only provided for compatibility with
non-standard SQL databases. Its use is discouraged in new
applications.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">UNIQUE</code> (column constraint)</span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">UNIQUE ( <em class="replaceable"><code>column_name</code></em> [, ... ] )</code> (table constraint)</span>
</dt>
<dd>
<p> The <code class="literal">UNIQUE</code> constraint specifies that a
group of one or more columns of a table may contain
only unique values. The behavior of the unique table constraint
is the same as that for column constraints, with the additional
capability to span multiple columns.
</p>
<p> For the purpose of a unique constraint, null values are not
considered equal.
</p>
<p> Each unique table constraint must name a set of columns that is
different from the set of columns named by any other unique or
primary key constraint defined for the table. (Otherwise it
would just be the same constraint listed twice.)
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">PRIMARY KEY</code> (column constraint)</span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">PRIMARY KEY ( <em class="replaceable"><code>column_name</code></em> [, ... ] )</code> (table constraint)</span>
</dt>
<dd>
<p> The primary key constraint specifies that a column or columns of a table
may contain only unique (non-duplicate), nonnull values.
Technically, <code class="literal">PRIMARY KEY</code> is merely a
combination of <code class="literal">UNIQUE</code> and <code class="literal">NOT NULL</code>, but
identifying a set of columns as primary key also provides
metadata about the design of the schema, as a primary key
implies that other tables
may rely on this set of columns as a unique identifier for rows.
</p>
<p> Only one primary key can be specified for a table, whether as a
column constraint or a table constraint.
</p>
<p> The primary key constraint should name a set of columns that is
different from other sets of columns named by any unique
constraint defined for the same table.
</p>
</dd>
<dt><span class="term"><code class="literal">CHECK (<em class="replaceable"><code>expression</code></em>)</code></span></dt>
<dd>
<p> The <code class="literal">CHECK</code> clause specifies an expression producing a
Boolean result which new or updated rows must satisfy for an
insert or update operation to succeed. Expressions evaluating
to TRUE or UNKNOWN succeed. Should any row of an insert or
update operation produce a FALSE result an error exception is
raised and the insert or update does not alter the database. A
check constraint specified as a column constraint should
reference that column's value only, while an expression
appearing in a table constraint may reference multiple columns.
</p>
<p> Currently, <code class="literal">CHECK</code> expressions cannot contain
subqueries nor refer to variables other than columns of the
current row.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> ) ] [ MATCH <em class="replaceable"><code>matchtype</code></em> ] [ ON DELETE <em class="replaceable"><code>action</code></em> ] [ ON UPDATE <em class="replaceable"><code>action</code></em> ]</code> (column constraint)</span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">FOREIGN KEY ( <em class="replaceable"><code>column</code></em> [, ... ] )
REFERENCES <em class="replaceable"><code>reftable</code></em> [ ( <em class="replaceable"><code>refcolumn</code></em> [, ... ] ) ]
[ MATCH <em class="replaceable"><code>matchtype</code></em> ]
[ ON DELETE <em class="replaceable"><code>action</code></em> ]
[ ON UPDATE <em class="replaceable"><code>action</code></em> ]</code>
(table constraint)</span>
</dt>
<dd>
<p> These clauses specify a foreign key constraint, which requires
that a group of one or more columns of the new table must only
contain values that match values in the referenced
column(s) of some row of the referenced table. If <em class="replaceable"><code>refcolumn</code></em> is omitted, the
primary key of the <em class="replaceable"><code>reftable</code></em> is used. The
referenced columns must be the columns of a unique or primary
key constraint in the referenced table. Note that foreign key
constraints may not be defined between temporary tables and
permanent tables.
</p>
<p> A value inserted into the referencing column(s) is matched against the
values of the referenced table and referenced columns using the
given match type. There are three match types: <code class="literal">MATCH
FULL</code>, <code class="literal">MATCH PARTIAL</code>, and <code class="literal">MATCH
SIMPLE</code>, which is also the default. <code class="literal">MATCH
FULL</code> will not allow one column of a multicolumn foreign key
to be null unless all foreign key columns are null.
<code class="literal">MATCH SIMPLE</code> allows some foreign key columns
to be null while other parts of the foreign key are not
null. <code class="literal">MATCH PARTIAL</code> is not yet implemented.
</p>
<p> In addition, when the data in the referenced columns is changed,
certain actions are performed on the data in this table's
columns. The <code class="literal">ON DELETE</code> clause specifies the
action to perform when a referenced row in the referenced table is
being deleted. Likewise, the <code class="literal">ON UPDATE</code>
clause specifies the action to perform when a referenced column
in the referenced table is being updated to a new value. If the
row is updated, but the referenced column is not actually
changed, no action is done. Referential actions other than the
<code class="literal">NO ACTION</code> check cannot be deferred, even if
the constraint is declared deferrable. There are the following possible
actions for each clause:
</p>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">NO ACTION</code></span></dt>
<dd><p> Produce an error indicating that the deletion or update
would create a foreign key constraint violation.
If the constraint is deferred, this
error will be produced at constraint check time if there still
exist any referencing rows. This is the default action.
</p></dd>
<dt><span class="term"><code class="literal">RESTRICT</code></span></dt>
<dd><p> Produce an error indicating that the deletion or update
would create a foreign key constraint violation.
This is the same as <code class="literal">NO ACTION</code> except that
the check is not deferrable.
</p></dd>
<dt><span class="term"><code class="literal">CASCADE</code></span></dt>
<dd><p> Delete any rows referencing the deleted row, or update the
value of the referencing column to the new value of the
referenced column, respectively.
</p></dd>
<dt><span class="term"><code class="literal">SET NULL</code></span></dt>
<dd><p> Set the referencing column(s) to null.
</p></dd>
<dt><span class="term"><code class="literal">SET DEFAULT</code></span></dt>
<dd><p> Set the referencing column(s) to their default values.
</p></dd>
</dl></div>
<p>
</p>
<p> If the referenced column(s) are changed frequently, it may be wise to
add an index to the foreign key column so that referential actions
associated with the foreign key column can be performed more
efficiently.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">DEFERRABLE</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">NOT DEFERRABLE</code></span>
</dt>
<dd><p> This controls whether the constraint can be deferred. A
constraint that is not deferrable will be checked immediately
after every command. Checking of constraints that are
deferrable may be postponed until the end of the transaction
(using the <a href="sql-set-constraints.html">SET CONSTRAINTS</a> command).
<code class="literal">NOT DEFERRABLE</code> is the default. Only foreign
key constraints currently accept this clause. All other
constraint types are not deferrable.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">INITIALLY IMMEDIATE</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">INITIALLY DEFERRED</code></span>
</dt>
<dd><p> If a constraint is deferrable, this clause specifies the default
time to check the constraint. If the constraint is
<code class="literal">INITIALLY IMMEDIATE</code>, it is checked after each
statement. This is the default. If the constraint is
<code class="literal">INITIALLY DEFERRED</code>, it is checked only at the
end of the transaction. The constraint check time can be
altered with the <a href="sql-set-constraints.html">SET CONSTRAINTS</a> command.
</p></dd>
<dt><span class="term"><code class="literal">ON COMMIT</code></span></dt>
<dd>
<p> The behavior of temporary tables at the end of a transaction
block can be controlled using <code class="literal">ON COMMIT</code>.
The three options are:
</p>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">PRESERVE ROWS</code></span></dt>
<dd><p> No special action is taken at the ends of transactions.
This is the default behavior.
</p></dd>
<dt><span class="term"><code class="literal">DELETE ROWS</code></span></dt>
<dd><p> All rows in the temporary table will be deleted at the
end of each transaction block. Essentially, an automatic
<a href="sql-truncate.html" title="TRUNCATE"><span class="refentrytitle"><a name="sql-truncate-title"></a>TRUNCATE</span></a> is done at each commit.
</p></dd>
<dt><span class="term"><code class="literal">DROP</code></span></dt>
<dd><p> The temporary table will be dropped at the end of the current
transaction block.
</p></dd>
</dl></div>
<p>
</p>
</dd>
<dt><span class="term"><code class="literal">TABLESPACE <em class="replaceable"><code>tablespace</code></em></code></span></dt>
<dd><p> The <em class="replaceable"><code>tablespace</code></em> is the name
of the tablespace in which the new table is to be created.
If not specified,
<a href="runtime-config-client.html#guc-default-tablespace">default_tablespace</a> is used, or the database's
default tablespace if <code class="varname">default_tablespace</code> is an empty
string.
</p></dd>
<dt><span class="term"><code class="literal">USING INDEX TABLESPACE <em class="replaceable"><code>tablespace</code></em></code></span></dt>
<dd><p> This clause allows selection of the tablespace in which the index
associated with a <code class="literal">UNIQUE</code> or <code class="literal">PRIMARY
KEY</code> constraint will be created.
If not specified,
<a href="runtime-config-client.html#guc-default-tablespace">default_tablespace</a> is used, or the database's
default tablespace if <code class="varname">default_tablespace</code> is an empty
string.
</p></dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="sql-createtable-notes"></a><h2>Notes</h2>
<p> Using OIDs in new applications is not recommended: where
possible, using a <code class="literal">SERIAL</code> or other sequence
generator as the table's primary key is preferred. However, if
your application does make use of OIDs to identify specific
rows of a table, it is recommended to create a unique constraint
on the <code class="structfield">oid</code> column of that table, to ensure that
OIDs in the table will indeed uniquely identify rows even after
counter wraparound. Avoid assuming that OIDs are unique across
tables; if you need a database-wide unique identifier, use the
combination of <code class="structfield">tableoid</code> and row OID for the
purpose.
</p>
<div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Tip</h3>
<p> The use of <code class="literal">WITHOUT OIDS</code> is not recommended
for tables with no primary key, since without either an OID or a
unique data key, it is difficult to identify specific rows.
</p>
</div>
<p> <span class="productname">PostgreSQL</span> automatically creates an
index for each unique constraint and primary key constraint to
enforce uniqueness. Thus, it is not necessary to create an
index explicitly for primary key columns. (See <a href="sql-createindex.html">CREATE INDEX</a> for more information.)
</p>
<p> Unique constraints and primary keys are not inherited in the
current implementation. This makes the combination of
inheritance and unique constraints rather dysfunctional.
</p>
<p> A table cannot have more than 1600 columns. (In practice, the
effective limit is lower because of tuple-length constraints.)
</p>
</div>
<div class="refsect1" lang="en">
<a name="sql-createtable-examples"></a><h2>Examples</h2>
<p> Create table <code class="structname">films</code> and table
<code class="structname">distributors</code>:
</p>
<pre class="programlisting">CREATE TABLE films (
code char(5) CONSTRAINT firstkey PRIMARY KEY,
title varchar(40) NOT NULL,
did integer NOT NULL,
date_prod date,
kind varchar(10),
len interval hour to minute
);</pre>
<p>
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer PRIMARY KEY DEFAULT nextval('serial'),
name varchar(40) NOT NULL CHECK (name <> '')
);</pre>
<p>
</p>
<p> Create a table with a 2-dimensional array:
</p>
<pre class="programlisting">CREATE TABLE array_int (
vector int[][]
);</pre>
<p>
</p>
<p> Define a unique table constraint for the table
<code class="literal">films</code>. Unique table constraints can be defined
on one or more columns of the table.
</p>
<pre class="programlisting">CREATE TABLE films (
code char(5),
title varchar(40),
did integer,
date_prod date,
kind varchar(10),
len interval hour to minute,
CONSTRAINT production UNIQUE(date_prod)
);</pre>
<p>
</p>
<p> Define a check column constraint:
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer CHECK (did > 100),
name varchar(40)
);</pre>
<p>
</p>
<p> Define a check table constraint:
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer,
name varchar(40)
CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);</pre>
<p>
</p>
<p> Define a primary key table constraint for the table
<code class="structname">films</code>. Primary key table constraints can be defined
on one or more columns of the table.
</p>
<pre class="programlisting">CREATE TABLE films (
code char(5),
title varchar(40),
did integer,
date_prod date,
kind varchar(10),
len interval hour to minute,
CONSTRAINT code_title PRIMARY KEY(code,title)
);</pre>
<p>
</p>
<p> Define a primary key constraint for table
<code class="structname">distributors</code>. The following two examples are
equivalent, the first using the table constraint syntax, the second
the column constraint syntax.
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer,
name varchar(40),
PRIMARY KEY(did)
);</pre>
<p>
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer PRIMARY KEY,
name varchar(40)
);</pre>
<p>
</p>
<p> This assigns a literal constant default value for the column
<code class="literal">name</code>, arranges for the default value of column
<code class="literal">did</code> to be generated by selecting the next value
of a sequence object, and makes the default value of
<code class="literal">modtime</code> be the time at which the row is
inserted.
</p>
<pre class="programlisting">CREATE TABLE distributors (
name varchar(40) DEFAULT 'Luso Films',
did integer DEFAULT nextval('distributors_serial'),
modtime timestamp DEFAULT current_timestamp
);</pre>
<p>
</p>
<p> Define two <code class="literal">NOT NULL</code> column constraints on the table
<code class="classname">distributors</code>, one of which is explicitly
given a name:
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer CONSTRAINT no_null NOT NULL,
name varchar(40) NOT NULL
);</pre>
<p>
</p>
<p> Define a unique constraint for the <code class="literal">name</code> column:
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer,
name varchar(40) UNIQUE
);</pre>
<p>
The above is equivalent to the following specified as a table constraint:
</p>
<pre class="programlisting">CREATE TABLE distributors (
did integer,
name varchar(40),
UNIQUE(name)
);</pre>
<p>
</p>
<p> Create table <code class="structname">cinemas</code> in tablespace <code class="structname">diskvol1</code>:
</p>
<pre class="programlisting">CREATE TABLE cinemas (
id serial,
name text,
location text
) TABLESPACE diskvol1;</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="sql-createtable-compatibility"></a><h2>Compatibility</h2>
<p> The <code class="command">CREATE TABLE</code> command conforms to the
<acronym class="acronym">SQL</acronym> standard, with exceptions listed below.
</p>
<div class="refsect2" lang="en">
<a name="id763981"></a><h3>Temporary Tables</h3>
<p> Although the syntax of <code class="literal">CREATE TEMPORARY TABLE</code>
resembles that of the SQL standard, the effect is not the same. In the
standard,
temporary tables are defined just once and automatically exist (starting
with empty contents) in every session that needs them.
<span class="productname">PostgreSQL</span> instead
requires each session to issue its own <code class="literal">CREATE TEMPORARY
TABLE</code> command for each temporary table to be used. This allows
different sessions to use the same temporary table name for different
purposes, whereas the standard's approach constrains all instances of a
given temporary table name to have the same table structure.
</p>
<p> The standard's definition of the behavior of temporary tables is
widely ignored. <span class="productname">PostgreSQL</span>'s behavior
on this point is similar to that of several other SQL databases.
</p>
<p> The standard's distinction between global and local temporary tables
is not in <span class="productname">PostgreSQL</span>, since that distinction
depends on the concept of modules, which
<span class="productname">PostgreSQL</span> does not have.
For compatibility's sake, <span class="productname">PostgreSQL</span> will
accept the <code class="literal">GLOBAL</code> and <code class="literal">LOCAL</code> keywords
in a temporary table declaration, but they have no effect.
</p>
<p> The <code class="literal">ON COMMIT</code> clause for temporary tables
also resembles the SQL standard, but has some differences.
If the <code class="literal">ON COMMIT</code> clause is omitted, SQL specifies that the
default behavior is <code class="literal">ON COMMIT DELETE ROWS</code>. However, the
default behavior in <span class="productname">PostgreSQL</span> is
<code class="literal">ON COMMIT PRESERVE ROWS</code>. The <code class="literal">ON COMMIT
DROP</code> option does not exist in SQL.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id764117"></a><h3>Column Check Constraints</h3>
<p> The SQL standard says that <code class="literal">CHECK</code> column constraints
may only refer to the column they apply to; only <code class="literal">CHECK</code>
table constraints may refer to multiple columns.
<span class="productname">PostgreSQL</span> does not enforce this
restriction; it treats column and table check constraints alike.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id764147"></a><h3>
<code class="literal">NULL</code> “<span class="quote">Constraint</span>”</h3>
<p> The <code class="literal">NULL</code> “<span class="quote">constraint</span>” (actually a
non-constraint) is a <span class="productname">PostgreSQL</span>
extension to the SQL standard that is included for compatibility with some
other database systems (and for symmetry with the <code class="literal">NOT
NULL</code> constraint). Since it is the default for any
column, its presence is simply noise.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id764190"></a><h3>Inheritance</h3>
<p> Multiple inheritance via the <code class="literal">INHERITS</code> clause is
a <span class="productname">PostgreSQL</span> language extension.
SQL:1999 and later define single inheritance using a
different syntax and different semantics. SQL:1999-style
inheritance is not yet supported by
<span class="productname">PostgreSQL</span>.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id764220"></a><h3>Object IDs</h3>
<p> The <span class="productname">PostgreSQL</span> concept of OIDs is not
standard.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id764234"></a><h3>Zero-column tables</h3>
<p> <span class="productname">PostgreSQL</span> allows a table of no columns
to be created (for example, <code class="literal">CREATE TABLE foo();</code>). This
is an extension from the SQL standard, which does not allow zero-column
tables. Zero-column tables are not in themselves very useful, but
disallowing them creates odd special cases for <code class="command">ALTER TABLE
DROP COLUMN</code>, so it seems cleaner to ignore this spec restriction.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id764266"></a><h3>Tablespaces</h3>
<p> The <span class="productname">PostgreSQL</span> concept of tablespaces is not
part of the standard. Hence, the clauses <code class="literal">TABLESPACE</code>
and <code class="literal">USING INDEX TABLESPACE</code> are extensions.
</p>
</div>
</div>
<div class="refsect1" lang="en">
<a name="id764295"></a><h2>See Also</h2>
<span class="simplelist"><a href="sql-altertable.html">ALTER TABLE</a>, <a href="sql-droptable.html">DROP TABLE</a>, <a href="sql-createtablespace.html">CREATE TABLESPACE</a></span>
</div>
</div></body>
</html>
|
