← Back to branch summary

~ubuntu-branches/ubuntu/lucid/postgresql-8.4/lucid-proposed

« back to all changes in this revision

Viewing changes to src/test/regress/output/constraints.source

  • Committer: Bazaar Package Importer
  • Author(s): Martin Pitt
  • Date: 2009-03-20 12:00:13 UTC
  • Revision ID: james.westby@ubuntu.com-20090320120013-hogj7egc5mjncc5g
Tags: upstream-8.4~0cvs20090328
Import upstream version 8.4~0cvs20090328

Show diffs side-by-side

added added

removed removed

Lines of Context:
src/test/regress/output/constraints.source
 
1
--
 
2
-- CONSTRAINTS
 
3
-- Constraints can be specified with:
 
4
--  - DEFAULT clause
 
5
--  - CHECK clauses
 
6
--  - PRIMARY KEY clauses
 
7
--  - UNIQUE clauses
 
8
--
 
9
--
 
10
-- DEFAULT syntax
 
11
--
 
12
CREATE TABLE DEFAULT_TBL (i int DEFAULT 100,
 
13
        x text DEFAULT 'vadim', f float8 DEFAULT 123.456);
 
14
INSERT INTO DEFAULT_TBL VALUES (1, 'thomas', 57.0613);
 
15
INSERT INTO DEFAULT_TBL VALUES (1, 'bruce');
 
16
INSERT INTO DEFAULT_TBL (i, f) VALUES (2, 987.654);
 
17
INSERT INTO DEFAULT_TBL (x) VALUES ('marc');
 
18
INSERT INTO DEFAULT_TBL VALUES (3, null, 1.0);
 
19
SELECT '' AS five, * FROM DEFAULT_TBL;
 
20
 five |  i  |   x    |    f    
 
21
------+-----+--------+---------
 
22
      |   1 | thomas | 57.0613
 
23
      |   1 | bruce  | 123.456
 
24
      |   2 | vadim  | 987.654
 
25
      | 100 | marc   | 123.456
 
26
      |   3 |        |       1
 
27
(5 rows)
 
28
 
 
29
CREATE SEQUENCE DEFAULT_SEQ;
 
30
CREATE TABLE DEFAULTEXPR_TBL (i1 int DEFAULT 100 + (200-199) * 2,
 
31
        i2 int DEFAULT nextval('default_seq'));
 
32
INSERT INTO DEFAULTEXPR_TBL VALUES (-1, -2);
 
33
INSERT INTO DEFAULTEXPR_TBL (i1) VALUES (-3);
 
34
INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (-4);
 
35
INSERT INTO DEFAULTEXPR_TBL (i2) VALUES (NULL);
 
36
SELECT '' AS four, * FROM DEFAULTEXPR_TBL;
 
37
 four | i1  | i2 
 
38
------+-----+----
 
39
      |  -1 | -2
 
40
      |  -3 |  1
 
41
      | 102 | -4
 
42
      | 102 |   
 
43
(4 rows)
 
44
 
 
45
-- syntax errors
 
46
--  test for extraneous comma
 
47
CREATE TABLE error_tbl (i int DEFAULT (100, ));
 
48
ERROR:  syntax error at or near ")"
 
49
LINE 1: CREATE TABLE error_tbl (i int DEFAULT (100, ));
 
50
                                                    ^
 
51
--  this will fail because gram.y uses b_expr not a_expr for defaults,
 
52
--  to avoid a shift/reduce conflict that arises from NOT NULL being
 
53
--  part of the column definition syntax:
 
54
CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
 
55
ERROR:  syntax error at or near "IN"
 
56
LINE 1: CREATE TABLE error_tbl (b1 bool DEFAULT 1 IN (1, 2));
 
57
                                                  ^
 
58
--  this should work, however:
 
59
CREATE TABLE error_tbl (b1 bool DEFAULT (1 IN (1, 2)));
 
60
DROP TABLE error_tbl;
 
61
--
 
62
-- CHECK syntax
 
63
--
 
64
CREATE TABLE CHECK_TBL (x int,
 
65
        CONSTRAINT CHECK_CON CHECK (x > 3));
 
66
INSERT INTO CHECK_TBL VALUES (5);
 
67
INSERT INTO CHECK_TBL VALUES (4);
 
68
INSERT INTO CHECK_TBL VALUES (3);
 
69
ERROR:  new row for relation "check_tbl" violates check constraint "check_con"
 
70
INSERT INTO CHECK_TBL VALUES (2);
 
71
ERROR:  new row for relation "check_tbl" violates check constraint "check_con"
 
72
INSERT INTO CHECK_TBL VALUES (6);
 
73
INSERT INTO CHECK_TBL VALUES (1);
 
74
ERROR:  new row for relation "check_tbl" violates check constraint "check_con"
 
75
SELECT '' AS three, * FROM CHECK_TBL;
 
76
 three | x 
 
77
-------+---
 
78
       | 5
 
79
       | 4
 
80
       | 6
 
81
(3 rows)
 
82
 
 
83
CREATE SEQUENCE CHECK_SEQ;
 
84
CREATE TABLE CHECK2_TBL (x int, y text, z int,
 
85
        CONSTRAINT SEQUENCE_CON
 
86
        CHECK (x > 3 and y <> 'check failed' and z < 8));
 
87
INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2);
 
88
INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2);
 
89
ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
 
90
INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10);
 
91
ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
 
92
INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2);
 
93
ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
 
94
INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11);
 
95
ERROR:  new row for relation "check2_tbl" violates check constraint "sequence_con"
 
96
INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7);
 
97
SELECT '' AS two, * from CHECK2_TBL;
 
98
 two | x |    y     | z  
 
99
-----+---+----------+----
 
100
     | 4 | check ok | -2
 
101
     | 7 | check ok |  7
 
102
(2 rows)
 
103
 
 
104
--
 
105
-- Check constraints on INSERT
 
106
--
 
107
CREATE SEQUENCE INSERT_SEQ;
 
108
CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
 
109
        y TEXT DEFAULT '-NULL-',
 
110
        z INT DEFAULT -1 * currval('insert_seq'),
 
111
        CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
 
112
        CHECK (x + z = 0));
 
113
INSERT INTO INSERT_TBL(x,z) VALUES (2, -2);
 
114
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 
115
SELECT '' AS zero, * FROM INSERT_TBL;
 
116
 zero | x | y | z 
 
117
------+---+---+---
 
118
(0 rows)
 
119
 
 
120
SELECT 'one' AS one, nextval('insert_seq');
 
121
 one | nextval 
 
122
-----+---------
 
123
 one |       1
 
124
(1 row)
 
125
 
 
126
INSERT INTO INSERT_TBL(y) VALUES ('Y');
 
127
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 
128
INSERT INTO INSERT_TBL(y) VALUES ('Y');
 
129
INSERT INTO INSERT_TBL(x,z) VALUES (1, -2);
 
130
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
 
131
INSERT INTO INSERT_TBL(z,x) VALUES (-7,  7);
 
132
INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5);
 
133
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 
134
INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7);
 
135
INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 
136
SELECT '' AS four, * FROM INSERT_TBL;
 
137
 four | x |       y       | z  
 
138
------+---+---------------+----
 
139
      | 3 | Y             | -3
 
140
      | 7 | -NULL-        | -7
 
141
      | 7 | !check failed | -7
 
142
      | 4 | -!NULL-       | -4
 
143
(4 rows)
 
144
 
 
145
INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4);
 
146
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_tbl_check"
 
147
INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed');
 
148
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 
149
INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed');
 
150
INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-');
 
151
SELECT '' AS six, * FROM INSERT_TBL;
 
152
 six | x |       y       | z  
 
153
-----+---+---------------+----
 
154
     | 3 | Y             | -3
 
155
     | 7 | -NULL-        | -7
 
156
     | 7 | !check failed | -7
 
157
     | 4 | -!NULL-       | -4
 
158
     | 5 | !check failed | -5
 
159
     | 6 | -!NULL-       | -6
 
160
(6 rows)
 
161
 
 
162
SELECT 'seven' AS one, nextval('insert_seq');
 
163
  one  | nextval 
 
164
-------+---------
 
165
 seven |       7
 
166
(1 row)
 
167
 
 
168
INSERT INTO INSERT_TBL(y) VALUES ('Y');
 
169
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 
170
SELECT 'eight' AS one, currval('insert_seq');
 
171
  one  | currval 
 
172
-------+---------
 
173
 eight |       8
 
174
(1 row)
 
175
 
 
176
-- According to SQL92, it is OK to insert a record that gives rise to NULL
 
177
-- constraint-condition results.  Postgres used to reject this, but it
 
178
-- was wrong:
 
179
INSERT INTO INSERT_TBL VALUES (null, null, null);
 
180
SELECT '' AS nine, * FROM INSERT_TBL;
 
181
 nine | x |       y       | z  
 
182
------+---+---------------+----
 
183
      | 3 | Y             | -3
 
184
      | 7 | -NULL-        | -7
 
185
      | 7 | !check failed | -7
 
186
      | 4 | -!NULL-       | -4
 
187
      | 5 | !check failed | -5
 
188
      | 6 | -!NULL-       | -6
 
189
      |   |               |   
 
190
(7 rows)
 
191
 
 
192
--
 
193
-- Check inheritance of defaults and constraints
 
194
--
 
195
CREATE TABLE INSERT_CHILD (cx INT default 42,
 
196
        cy INT CHECK (cy > x))
 
197
        INHERITS (INSERT_TBL);
 
198
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11);
 
199
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6);
 
200
ERROR:  new row for relation "insert_child" violates check constraint "insert_child_check"
 
201
INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7);
 
202
ERROR:  new row for relation "insert_child" violates check constraint "insert_tbl_check"
 
203
INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7);
 
204
ERROR:  new row for relation "insert_child" violates check constraint "insert_con"
 
205
SELECT * FROM INSERT_CHILD;
 
206
 x |   y    | z  | cx | cy 
 
207
---+--------+----+----+----
 
208
 7 | -NULL- | -7 | 42 | 11
 
209
(1 row)
 
210
 
 
211
DROP TABLE INSERT_CHILD;
 
212
--
 
213
-- Check constraints on INSERT INTO
 
214
--
 
215
DELETE FROM INSERT_TBL;
 
216
ALTER SEQUENCE INSERT_SEQ RESTART WITH 4;
 
217
CREATE TABLE tmp (xd INT, yd TEXT, zd INT);
 
218
INSERT INTO tmp VALUES (null, 'Y', null);
 
219
INSERT INTO tmp VALUES (5, '!check failed', null);
 
220
INSERT INTO tmp VALUES (null, 'try again', null);
 
221
INSERT INTO INSERT_TBL(y) select yd from tmp;
 
222
SELECT '' AS three, * FROM INSERT_TBL;
 
223
 three | x |       y       | z  
 
224
-------+---+---------------+----
 
225
       | 4 | Y             | -4
 
226
       | 5 | !check failed | -5
 
227
       | 6 | try again     | -6
 
228
(3 rows)
 
229
 
 
230
INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again';
 
231
INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again';
 
232
INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again';
 
233
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 
234
SELECT '' AS four, * FROM INSERT_TBL;
 
235
 four | x |       y       | z  
 
236
------+---+---------------+----
 
237
      | 4 | Y             | -4
 
238
      | 5 | !check failed | -5
 
239
      | 6 | try again     | -6
 
240
      |   | try again     |   
 
241
      | 7 | try again     | -7
 
242
(5 rows)
 
243
 
 
244
DROP TABLE tmp;
 
245
--
 
246
-- Check constraints on UPDATE
 
247
--
 
248
UPDATE INSERT_TBL SET x = NULL WHERE x = 5;
 
249
UPDATE INSERT_TBL SET x = 6 WHERE x = 6;
 
250
UPDATE INSERT_TBL SET x = -z, z = -x;
 
251
UPDATE INSERT_TBL SET x = z, z = x;
 
252
ERROR:  new row for relation "insert_tbl" violates check constraint "insert_con"
 
253
SELECT * FROM INSERT_TBL;
 
254
 x |       y       | z  
 
255
---+---------------+----
 
256
 4 | Y             | -4
 
257
   | try again     |   
 
258
 7 | try again     | -7
 
259
 5 | !check failed |   
 
260
 6 | try again     | -6
 
261
(5 rows)
 
262
 
 
263
-- DROP TABLE INSERT_TBL;
 
264
--
 
265
-- Check constraints on COPY FROM
 
266
--
 
267
CREATE TABLE COPY_TBL (x INT, y TEXT, z INT,
 
268
        CONSTRAINT COPY_CON
 
269
        CHECK (x > 3 AND y <> 'check failed' AND x < 7 ));
 
270
COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data';
 
271
SELECT '' AS two, * FROM COPY_TBL;
 
272
 two | x |       y       | z 
 
273
-----+---+---------------+---
 
274
     | 4 | !check failed | 5
 
275
     | 6 | OK            | 4
 
276
(2 rows)
 
277
 
 
278
COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
 
279
ERROR:  new row for relation "copy_tbl" violates check constraint "copy_con"
 
280
CONTEXT:  COPY copy_tbl, line 2: "7     check failed    6"
 
281
SELECT * FROM COPY_TBL;
 
282
 x |       y       | z 
 
283
---+---------------+---
 
284
 4 | !check failed | 5
 
285
 6 | OK            | 4
 
286
(2 rows)
 
287
 
 
288
--
 
289
-- Primary keys
 
290
--
 
291
CREATE TABLE PRIMARY_TBL (i int PRIMARY KEY, t text);
 
292
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl"
 
293
INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 
294
INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 
295
INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 
296
ERROR:  duplicate key value violates unique constraint "primary_tbl_pkey"
 
297
INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 
298
INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 
299
INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 
300
ERROR:  null value in column "i" violates not-null constraint
 
301
SELECT '' AS four, * FROM PRIMARY_TBL;
 
302
 four | i |   t   
 
303
------+---+-------
 
304
      | 1 | one
 
305
      | 2 | two
 
306
      | 4 | three
 
307
      | 5 | one
 
308
(4 rows)
 
309
 
 
310
DROP TABLE PRIMARY_TBL;
 
311
CREATE TABLE PRIMARY_TBL (i int, t text,
 
312
        PRIMARY KEY(i,t));
 
313
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "primary_tbl_pkey" for table "primary_tbl"
 
314
INSERT INTO PRIMARY_TBL VALUES (1, 'one');
 
315
INSERT INTO PRIMARY_TBL VALUES (2, 'two');
 
316
INSERT INTO PRIMARY_TBL VALUES (1, 'three');
 
317
INSERT INTO PRIMARY_TBL VALUES (4, 'three');
 
318
INSERT INTO PRIMARY_TBL VALUES (5, 'one');
 
319
INSERT INTO PRIMARY_TBL (t) VALUES ('six');
 
320
ERROR:  null value in column "i" violates not-null constraint
 
321
SELECT '' AS three, * FROM PRIMARY_TBL;
 
322
 three | i |   t   
 
323
-------+---+-------
 
324
       | 1 | one
 
325
       | 2 | two
 
326
       | 1 | three
 
327
       | 4 | three
 
328
       | 5 | one
 
329
(5 rows)
 
330
 
 
331
DROP TABLE PRIMARY_TBL;
 
332
--
 
333
-- Unique keys
 
334
--
 
335
CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
 
336
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl"
 
337
INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 
338
INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 
339
INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 
340
ERROR:  duplicate key value violates unique constraint "unique_tbl_i_key"
 
341
INSERT INTO UNIQUE_TBL VALUES (4, 'four');
 
342
INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 
343
INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 
344
INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
 
345
SELECT '' AS five, * FROM UNIQUE_TBL;
 
346
 five | i |   t   
 
347
------+---+-------
 
348
      | 1 | one
 
349
      | 2 | two
 
350
      | 4 | four
 
351
      | 5 | one
 
352
      |   | six
 
353
      |   | seven
 
354
(6 rows)
 
355
 
 
356
DROP TABLE UNIQUE_TBL;
 
357
CREATE TABLE UNIQUE_TBL (i int, t text,
 
358
        UNIQUE(i,t));
 
359
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "unique_tbl_i_key" for table "unique_tbl"
 
360
INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 
361
INSERT INTO UNIQUE_TBL VALUES (2, 'two');
 
362
INSERT INTO UNIQUE_TBL VALUES (1, 'three');
 
363
INSERT INTO UNIQUE_TBL VALUES (1, 'one');
 
364
ERROR:  duplicate key value violates unique constraint "unique_tbl_i_key"
 
365
INSERT INTO UNIQUE_TBL VALUES (5, 'one');
 
366
INSERT INTO UNIQUE_TBL (t) VALUES ('six');
 
367
SELECT '' AS five, * FROM UNIQUE_TBL;
 
368
 five | i |   t   
 
369
------+---+-------
 
370
      | 1 | one
 
371
      | 2 | two
 
372
      | 1 | three
 
373
      | 5 | one
 
374
      |   | six
 
375
(5 rows)
 
376
 
 
377
DROP TABLE UNIQUE_TBL;