| blob | 0d037d48ca01f43397c5e7d2dd84f36af0e0bf8b |
1 -- sanity check of system catalog
2 SELECT attrelid, attname, attgenerated FROM pg_attribute WHERE attgenerated NOT IN ('', 's');
3 attrelid | attname | attgenerated
4 ----------+---------+--------------
5 (0 rows)
7 CREATE SCHEMA generated_stored_tests;
8 GRANT USAGE ON SCHEMA generated_stored_tests TO PUBLIC;
9 SET search_path = generated_stored_tests;
10 CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55) STORED);
11 CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
12 SELECT table_name, column_name, column_default, is_nullable, is_generated, generation_expression FROM information_schema.columns WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2;
13 table_name | column_name | column_default | is_nullable | is_generated | generation_expression
14 ------------+-------------+----------------+-------------+--------------+-----------------------
15 gtest0 | a | | NO | NEVER |
16 gtest0 | b | | YES | ALWAYS | 55
17 gtest1 | a | | NO | NEVER |
18 gtest1 | b | | YES | ALWAYS | (a * 2)
19 (4 rows)
21 SELECT table_name, column_name, dependent_column FROM information_schema.column_column_usage WHERE table_schema = 'generated_stored_tests' ORDER BY 1, 2, 3;
22 table_name | column_name | dependent_column
23 ------------+-------------+------------------
24 gtest1 | a | b
25 (1 row)
27 \d gtest1
28 Table "generated_stored_tests.gtest1"
29 Column | Type | Collation | Nullable | Default
30 --------+---------+-----------+----------+------------------------------------
31 a | integer | | not null |
32 b | integer | | | generated always as (a * 2) stored
33 Indexes:
34 "gtest1_pkey" PRIMARY KEY, btree (a)
36 -- duplicate generated
37 CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED GENERATED ALWAYS AS (a * 3) STORED);
38 ERROR: multiple generation clauses specified for column "b" of table "gtest_err_1"
39 LINE 1: ...ARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED GENERATED ...
40 ^
41 -- references to other generated columns, including self-references
42 CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) STORED);
43 ERROR: cannot use generated column "b" in column generation expression
44 LINE 1: ...2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b * 2) STO...
45 ^
46 DETAIL: A generated column cannot reference another generated column.
47 CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STORED);
48 ERROR: cannot use generated column "b" in column generation expression
49 LINE 1: ...AYS AS (a * 2) STORED, c int GENERATED ALWAYS AS (b * 3) STO...
50 ^
51 DETAIL: A generated column cannot reference another generated column.
52 -- a whole-row var is a self-reference on steroids, so disallow that too
53 CREATE TABLE gtest_err_2c (a int PRIMARY KEY,
54 b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STORED);
55 ERROR: cannot use whole-row variable in column generation expression
56 LINE 2: b int GENERATED ALWAYS AS (num_nulls(gtest_err_2c)) STOR...
57 ^
58 DETAIL: This would cause the generated column to depend on its own value.
59 -- invalid reference
60 CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STORED);
61 ERROR: column "c" does not exist
62 LINE 1: ..._3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c * 2) STO...
63 ^
64 -- generation expression must be immutable
65 CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED ALWAYS AS (random()) STORED);
66 ERROR: generation expression is not immutable
67 -- ... but be sure that the immutability test is accurate
68 CREATE TABLE gtest2 (a int, b text GENERATED ALWAYS AS (a || ' sec') STORED);
69 DROP TABLE gtest2;
70 -- cannot have default/identity and generated
71 CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS AS (a * 2) STORED);
72 ERROR: both default and generation expression specified for column "b" of table "gtest_err_5a"
73 LINE 1: ... gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ...
74 ^
75 CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ALWAYS AS (a * 2) STORED);
76 ERROR: both identity and generation expression specified for column "b" of table "gtest_err_5b"
77 LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ...
78 ^
79 -- reference to system column not allowed in generated column
80 -- (except tableoid, which we test below)
81 CREATE TABLE gtest_err_6a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37) STORED);
82 ERROR: cannot use system column "xmin" in column generation expression
83 LINE 1: ...a (a int PRIMARY KEY, b bool GENERATED ALWAYS AS (xmin <> 37...
84 ^
85 -- various prohibited constructs
86 CREATE TABLE gtest_err_7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) STORED);
87 ERROR: aggregate functions are not allowed in column generation expressions
88 LINE 1: ...7a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (avg(a)) ST...
89 ^
90 CREATE TABLE gtest_err_7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number() OVER (ORDER BY a)) STORED);
91 ERROR: window functions are not allowed in column generation expressions
92 LINE 1: ...7b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (row_number...
93 ^
94 CREATE TABLE gtest_err_7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)) STORED);
95 ERROR: cannot use subquery in column generation expression
96 LINE 1: ...7c (a int PRIMARY KEY, b int GENERATED ALWAYS AS ((SELECT a)...
97 ^
98 CREATE TABLE gtest_err_7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_series(1, a)) STORED);
99 ERROR: set-returning functions are not allowed in column generation expressions
100 LINE 1: ...7d (a int PRIMARY KEY, b int GENERATED ALWAYS AS (generate_s...
101 ^
102 -- GENERATED BY DEFAULT not allowed
103 CREATE TABLE gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT AS (a * 2) STORED);
104 ERROR: for a generated column, GENERATED ALWAYS must be specified
105 LINE 1: ...E gtest_err_8 (a int PRIMARY KEY, b int GENERATED BY DEFAULT...
106 ^
107 INSERT INTO gtest1 VALUES (1);
108 INSERT INTO gtest1 VALUES (2, DEFAULT); -- ok
109 INSERT INTO gtest1 VALUES (3, 33); -- error
110 ERROR: cannot insert a non-DEFAULT value into column "b"
111 DETAIL: Column "b" is a generated column.
112 INSERT INTO gtest1 VALUES (3, 33), (4, 44); -- error
113 ERROR: cannot insert a non-DEFAULT value into column "b"
114 DETAIL: Column "b" is a generated column.
115 INSERT INTO gtest1 VALUES (3, DEFAULT), (4, 44); -- error
116 ERROR: cannot insert a non-DEFAULT value into column "b"
117 DETAIL: Column "b" is a generated column.
118 INSERT INTO gtest1 VALUES (3, 33), (4, DEFAULT); -- error
119 ERROR: cannot insert a non-DEFAULT value into column "b"
120 DETAIL: Column "b" is a generated column.
121 INSERT INTO gtest1 VALUES (3, DEFAULT), (4, DEFAULT); -- ok
122 SELECT * FROM gtest1 ORDER BY a;
123 a | b
124 ---+---
125 1 | 2
126 2 | 4
127 3 | 6
128 4 | 8
129 (4 rows)
131 DELETE FROM gtest1 WHERE a >= 3;
132 UPDATE gtest1 SET b = DEFAULT WHERE a = 1;
133 UPDATE gtest1 SET b = 11 WHERE a = 1; -- error
134 ERROR: column "b" can only be updated to DEFAULT
135 DETAIL: Column "b" is a generated column.
136 SELECT * FROM gtest1 ORDER BY a;
137 a | b
138 ---+---
139 1 | 2
140 2 | 4
141 (2 rows)
143 SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
144 a | b | b2
145 ---+---+----
146 1 | 2 | 4
147 2 | 4 | 8
148 (2 rows)
150 SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
151 a | b
152 ---+---
153 2 | 4
154 (1 row)
156 -- test that overflow error happens on write
157 INSERT INTO gtest1 VALUES (2000000000);
158 ERROR: integer out of range
159 SELECT * FROM gtest1;
160 a | b
161 ---+---
162 2 | 4
163 1 | 2
164 (2 rows)
166 DELETE FROM gtest1 WHERE a = 2000000000;
167 -- test with joins
168 CREATE TABLE gtestx (x int, y int);
169 INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3);
170 SELECT * FROM gtestx, gtest1 WHERE gtestx.y = gtest1.a;
171 x | y | a | b
172 ----+---+---+---
173 11 | 1 | 1 | 2
174 22 | 2 | 2 | 4
175 (2 rows)
177 DROP TABLE gtestx;
178 -- test UPDATE/DELETE quals
179 SELECT * FROM gtest1 ORDER BY a;
180 a | b
181 ---+---
182 1 | 2
183 2 | 4
184 (2 rows)
186 UPDATE gtest1 SET a = 3 WHERE b = 4;
187 SELECT * FROM gtest1 ORDER BY a;
188 a | b
189 ---+---
190 1 | 2
191 3 | 6
192 (2 rows)
194 DELETE FROM gtest1 WHERE b = 2;
195 SELECT * FROM gtest1 ORDER BY a;
196 a | b
197 ---+---
198 3 | 6
199 (1 row)
201 -- test MERGE
202 CREATE TABLE gtestm (
203 id int PRIMARY KEY,
204 f1 int,
205 f2 int,
206 f3 int GENERATED ALWAYS AS (f1 * 2) STORED,
207 f4 int GENERATED ALWAYS AS (f2 * 2) STORED
208 );
209 INSERT INTO gtestm VALUES (1, 5, 100);
210 MERGE INTO gtestm t USING (VALUES (1, 10), (2, 20)) v(id, f1) ON t.id = v.id
211 WHEN MATCHED THEN UPDATE SET f1 = v.f1
212 WHEN NOT MATCHED THEN INSERT VALUES (v.id, v.f1, 200);
213 SELECT * FROM gtestm ORDER BY id;
214 id | f1 | f2 | f3 | f4
215 ----+----+-----+----+-----
216 1 | 10 | 100 | 20 | 200
217 2 | 20 | 200 | 40 | 400
218 (2 rows)
220 DROP TABLE gtestm;
221 -- views
222 CREATE VIEW gtest1v AS SELECT * FROM gtest1;
223 SELECT * FROM gtest1v;
224 a | b
225 ---+---
226 3 | 6
227 (1 row)
229 INSERT INTO gtest1v VALUES (4, 8); -- error
230 ERROR: cannot insert a non-DEFAULT value into column "b"
231 DETAIL: Column "b" is a generated column.
232 INSERT INTO gtest1v VALUES (5, DEFAULT); -- ok
233 INSERT INTO gtest1v VALUES (6, 66), (7, 77); -- error
234 ERROR: cannot insert a non-DEFAULT value into column "b"
235 DETAIL: Column "b" is a generated column.
236 INSERT INTO gtest1v VALUES (6, DEFAULT), (7, 77); -- error
237 ERROR: cannot insert a non-DEFAULT value into column "b"
238 DETAIL: Column "b" is a generated column.
239 INSERT INTO gtest1v VALUES (6, 66), (7, DEFAULT); -- error
240 ERROR: cannot insert a non-DEFAULT value into column "b"
241 DETAIL: Column "b" is a generated column.
242 INSERT INTO gtest1v VALUES (6, DEFAULT), (7, DEFAULT); -- ok
243 ALTER VIEW gtest1v ALTER COLUMN b SET DEFAULT 100;
244 INSERT INTO gtest1v VALUES (8, DEFAULT); -- error
245 ERROR: cannot insert a non-DEFAULT value into column "b"
246 DETAIL: Column "b" is a generated column.
247 INSERT INTO gtest1v VALUES (8, DEFAULT), (9, DEFAULT); -- error
248 ERROR: cannot insert a non-DEFAULT value into column "b"
249 DETAIL: Column "b" is a generated column.
250 SELECT * FROM gtest1v;
251 a | b
252 ---+----
253 3 | 6
254 5 | 10
255 6 | 12
256 7 | 14
257 (4 rows)
259 DELETE FROM gtest1v WHERE a >= 5;
260 DROP VIEW gtest1v;
261 -- CTEs
262 WITH foo AS (SELECT * FROM gtest1) SELECT * FROM foo;
263 a | b
264 ---+---
265 3 | 6
266 (1 row)
268 -- inheritance
269 CREATE TABLE gtest1_1 () INHERITS (gtest1);
270 SELECT * FROM gtest1_1;
271 a | b
272 ---+---
273 (0 rows)
275 \d gtest1_1
276 Table "generated_stored_tests.gtest1_1"
277 Column | Type | Collation | Nullable | Default
278 --------+---------+-----------+----------+------------------------------------
279 a | integer | | not null |
280 b | integer | | | generated always as (a * 2) stored
281 Inherits: gtest1
283 INSERT INTO gtest1_1 VALUES (4);
284 SELECT * FROM gtest1_1;
285 a | b
286 ---+---
287 4 | 8
288 (1 row)
290 SELECT * FROM gtest1;
291 a | b
292 ---+---
293 3 | 6
294 4 | 8
295 (2 rows)
297 -- can't have generated column that is a child of normal column
298 CREATE TABLE gtest_normal (a int, b int);
299 CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED) INHERITS (gtest_normal); -- error
300 NOTICE: merging column "a" with inherited definition
301 NOTICE: merging column "b" with inherited definition
302 ERROR: child column "b" specifies generation expression
303 HINT: A child table column cannot be generated unless its parent column is.
304 CREATE TABLE gtest_normal_child (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
305 ALTER TABLE gtest_normal_child INHERIT gtest_normal; -- error
306 ERROR: column "b" in child table must not be a generated column
307 DROP TABLE gtest_normal, gtest_normal_child;
308 -- test inheritance mismatches between parent and child
309 CREATE TABLE gtestx (x int, b int DEFAULT 10) INHERITS (gtest1); -- error
310 NOTICE: merging column "b" with inherited definition
311 ERROR: column "b" inherits from generated column but specifies default
312 CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS IDENTITY) INHERITS (gtest1); -- error
313 NOTICE: merging column "b" with inherited definition
314 ERROR: column "b" inherits from generated column but specifies identity
315 CREATE TABLE gtestx (x int, b int GENERATED ALWAYS AS (a * 22) STORED) INHERITS (gtest1); -- ok, overrides parent
316 NOTICE: merging column "b" with inherited definition
317 \d+ gtestx
318 Table "generated_stored_tests.gtestx"
319 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
320 --------+---------+-----------+----------+-------------------------------------+---------+--------------+-------------
321 a | integer | | not null | | plain | |
322 b | integer | | | generated always as (a * 22) stored | plain | |
323 x | integer | | | | plain | |
324 Not-null constraints:
325 "gtest1_a_not_null" NOT NULL "a" (inherited)
326 Inherits: gtest1
328 CREATE TABLE gtestxx_1 (a int NOT NULL, b int);
329 ALTER TABLE gtestxx_1 INHERIT gtest1; -- error
330 ERROR: column "b" in child table must be a generated column
331 CREATE TABLE gtestxx_3 (a int NOT NULL, b int GENERATED ALWAYS AS (a * 2) STORED);
332 ALTER TABLE gtestxx_3 INHERIT gtest1; -- ok
333 CREATE TABLE gtestxx_4 (b int GENERATED ALWAYS AS (a * 2) STORED, a int NOT NULL);
334 ALTER TABLE gtestxx_4 INHERIT gtest1; -- ok
335 -- test multiple inheritance mismatches
336 CREATE TABLE gtesty (x int, b int DEFAULT 55);
337 CREATE TABLE gtest1_y () INHERITS (gtest0, gtesty); -- error
338 NOTICE: merging multiple inherited definitions of column "b"
339 ERROR: inherited column "b" has a generation conflict
340 DROP TABLE gtesty;
341 CREATE TABLE gtesty (x int, b int);
342 CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
343 NOTICE: merging multiple inherited definitions of column "b"
344 ERROR: inherited column "b" has a generation conflict
345 DROP TABLE gtesty;
346 CREATE TABLE gtesty (x int, b int GENERATED ALWAYS AS (x * 22) STORED);
347 CREATE TABLE gtest1_y () INHERITS (gtest1, gtesty); -- error
348 NOTICE: merging multiple inherited definitions of column "b"
349 ERROR: column "b" inherits conflicting generation expressions
350 HINT: To resolve the conflict, specify a generation expression explicitly.
351 CREATE TABLE gtest1_y (b int GENERATED ALWAYS AS (x + 1) STORED) INHERITS (gtest1, gtesty); -- ok
352 NOTICE: merging multiple inherited definitions of column "b"
353 NOTICE: moving and merging column "b" with inherited definition
354 DETAIL: User-specified column moved to the position of the inherited column.
355 \d gtest1_y
356 Table "generated_stored_tests.gtest1_y"
357 Column | Type | Collation | Nullable | Default
358 --------+---------+-----------+----------+------------------------------------
359 a | integer | | not null |
360 b | integer | | | generated always as (x + 1) stored
361 x | integer | | |
362 Inherits: gtest1,
363 gtesty
365 -- test correct handling of GENERATED column that's only in child
366 CREATE TABLE gtestp (f1 int);
367 CREATE TABLE gtestc (f2 int GENERATED ALWAYS AS (f1+1) STORED) INHERITS(gtestp);
368 INSERT INTO gtestc values(42);
369 TABLE gtestc;
370 f1 | f2
371 ----+----
372 42 | 43
373 (1 row)
375 UPDATE gtestp SET f1 = f1 * 10;
376 TABLE gtestc;
377 f1 | f2
378 -----+-----
379 420 | 421
380 (1 row)
382 DROP TABLE gtestp CASCADE;
383 NOTICE: drop cascades to table gtestc
384 -- test stored update
385 CREATE TABLE gtest3 (a int, b int GENERATED ALWAYS AS (a * 3) STORED);
386 INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
387 SELECT * FROM gtest3 ORDER BY a;
388 a | b
389 ---+---
390 1 | 3
391 2 | 6
392 3 | 9
393 |
394 (4 rows)
396 UPDATE gtest3 SET a = 22 WHERE a = 2;
397 SELECT * FROM gtest3 ORDER BY a;
398 a | b
399 ----+----
400 1 | 3
401 3 | 9
402 22 | 66
403 |
404 (4 rows)
406 CREATE TABLE gtest3a (a text, b text GENERATED ALWAYS AS (a || '+' || a) STORED);
407 INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
408 SELECT * FROM gtest3a ORDER BY a;
409 a | b
410 ---+-----
411 a | a+a
412 b | b+b
413 c | c+c
414 |
415 (4 rows)
417 UPDATE gtest3a SET a = 'bb' WHERE a = 'b';
418 SELECT * FROM gtest3a ORDER BY a;
419 a | b
420 ----+-------
421 a | a+a
422 bb | bb+bb
423 c | c+c
424 |
425 (4 rows)
427 -- COPY
428 TRUNCATE gtest1;
429 INSERT INTO gtest1 (a) VALUES (1), (2);
430 COPY gtest1 TO stdout;
431 1
432 2
433 COPY gtest1 (a, b) TO stdout;
434 ERROR: column "b" is a generated column
435 DETAIL: Generated columns cannot be used in COPY.
436 COPY gtest1 FROM stdin;
437 COPY gtest1 (a, b) FROM stdin;
438 ERROR: column "b" is a generated column
439 DETAIL: Generated columns cannot be used in COPY.
440 SELECT * FROM gtest1 ORDER BY a;
441 a | b
442 ---+---
443 1 | 2
444 2 | 4
445 3 | 6
446 4 | 8
447 (4 rows)
449 TRUNCATE gtest3;
450 INSERT INTO gtest3 (a) VALUES (1), (2);
451 COPY gtest3 TO stdout;
452 1
453 2
454 COPY gtest3 (a, b) TO stdout;
455 ERROR: column "b" is a generated column
456 DETAIL: Generated columns cannot be used in COPY.
457 COPY gtest3 FROM stdin;
458 COPY gtest3 (a, b) FROM stdin;
459 ERROR: column "b" is a generated column
460 DETAIL: Generated columns cannot be used in COPY.
461 SELECT * FROM gtest3 ORDER BY a;
462 a | b
463 ---+----
464 1 | 3
465 2 | 6
466 3 | 9
467 4 | 12
468 (4 rows)
470 -- null values
471 CREATE TABLE gtest2 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (NULL) STORED);
472 INSERT INTO gtest2 VALUES (1);
473 SELECT * FROM gtest2;
474 a | b
475 ---+---
476 1 |
477 (1 row)
479 -- simple column reference for varlena types
480 CREATE TABLE gtest_varlena (a varchar, b varchar GENERATED ALWAYS AS (a) STORED);
481 INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
482 INSERT INTO gtest_varlena (a) VALUES(NULL);
483 SELECT * FROM gtest_varlena ORDER BY a;
484 a | b
485 ----------------------+----------------------
486 01234567890123456789 | 01234567890123456789
487 |
488 (2 rows)
490 DROP TABLE gtest_varlena;
491 -- composite types
492 CREATE TYPE double_int as (a int, b int);
493 CREATE TABLE gtest4 (
494 a int,
495 b double_int GENERATED ALWAYS AS ((a * 2, a * 3)) STORED
496 );
497 INSERT INTO gtest4 VALUES (1), (6);
498 SELECT * FROM gtest4;
499 a | b
500 ---+---------
501 1 | (2,3)
502 6 | (12,18)
503 (2 rows)
505 DROP TABLE gtest4;
506 DROP TYPE double_int;
507 -- using tableoid is allowed
508 CREATE TABLE gtest_tableoid (
509 a int PRIMARY KEY,
510 b bool GENERATED ALWAYS AS (tableoid = 'gtest_tableoid'::regclass) STORED
511 );
512 INSERT INTO gtest_tableoid VALUES (1), (2);
513 ALTER TABLE gtest_tableoid ADD COLUMN
514 c regclass GENERATED ALWAYS AS (tableoid) STORED;
515 SELECT * FROM gtest_tableoid;
516 a | b | c
517 ---+---+----------------
518 1 | t | gtest_tableoid
519 2 | t | gtest_tableoid
520 (2 rows)
522 -- drop column behavior
523 CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
524 ALTER TABLE gtest10 DROP COLUMN b; -- fails
525 ERROR: cannot drop column b of table gtest10 because other objects depend on it
526 DETAIL: column c of table gtest10 depends on column b of table gtest10
527 HINT: Use DROP ... CASCADE to drop the dependent objects too.
528 ALTER TABLE gtest10 DROP COLUMN b CASCADE; -- drops c too
529 NOTICE: drop cascades to column c of table gtest10
530 \d gtest10
531 Table "generated_stored_tests.gtest10"
532 Column | Type | Collation | Nullable | Default
533 --------+---------+-----------+----------+---------
534 a | integer | | not null |
535 Indexes:
536 "gtest10_pkey" PRIMARY KEY, btree (a)
538 CREATE TABLE gtest10a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
539 ALTER TABLE gtest10a DROP COLUMN b;
540 INSERT INTO gtest10a (a) VALUES (1);
541 -- privileges
542 CREATE USER regress_user11;
543 CREATE TABLE gtest11s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b * 2) STORED);
544 INSERT INTO gtest11s VALUES (1, 10), (2, 20);
545 GRANT SELECT (a, c) ON gtest11s TO regress_user11;
546 CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE LANGUAGE SQL;
547 REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
548 CREATE TABLE gtest12s (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (gf1(b)) STORED);
549 INSERT INTO gtest12s VALUES (1, 10), (2, 20);
550 GRANT SELECT (a, c) ON gtest12s TO regress_user11;
551 SET ROLE regress_user11;
552 SELECT a, b FROM gtest11s; -- not allowed
553 ERROR: permission denied for table gtest11s
554 SELECT a, c FROM gtest11s; -- allowed
555 a | c
556 ---+----
557 1 | 20
558 2 | 40
559 (2 rows)
561 SELECT gf1(10); -- not allowed
562 ERROR: permission denied for function gf1
563 SELECT a, c FROM gtest12s; -- allowed
564 a | c
565 ---+----
566 1 | 30
567 2 | 60
568 (2 rows)
570 RESET ROLE;
571 DROP FUNCTION gf1(int); -- fail
572 ERROR: cannot drop function gf1(integer) because other objects depend on it
573 DETAIL: column c of table gtest12s depends on function gf1(integer)
574 HINT: Use DROP ... CASCADE to drop the dependent objects too.
575 DROP TABLE gtest11s, gtest12s;
576 DROP FUNCTION gf1(int);
577 DROP USER regress_user11;
578 -- check constraints
579 CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED CHECK (b < 50));
580 INSERT INTO gtest20 (a) VALUES (10); -- ok
581 INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
582 ERROR: new row for relation "gtest20" violates check constraint "gtest20_b_check"
583 DETAIL: Failing row contains (30, 60).
584 ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 100); -- violates constraint
585 ERROR: check constraint "gtest20_b_check" of relation "gtest20" is violated by some row
586 ALTER TABLE gtest20 ALTER COLUMN b SET EXPRESSION AS (a * 3); -- ok
587 CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
588 INSERT INTO gtest20a (a) VALUES (10);
589 INSERT INTO gtest20a (a) VALUES (30);
590 ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
591 ERROR: check constraint "gtest20a_b_check" of relation "gtest20a" is violated by some row
592 CREATE TABLE gtest20b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED);
593 INSERT INTO gtest20b (a) VALUES (10);
594 INSERT INTO gtest20b (a) VALUES (30);
595 ALTER TABLE gtest20b ADD CONSTRAINT chk CHECK (b < 50) NOT VALID;
596 ALTER TABLE gtest20b VALIDATE CONSTRAINT chk; -- fails on existing row
597 ERROR: check constraint "chk" of relation "gtest20b" is violated by some row
598 -- not-null constraints
599 CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED NOT NULL);
600 INSERT INTO gtest21a (a) VALUES (1); -- ok
601 INSERT INTO gtest21a (a) VALUES (0); -- violates constraint
602 ERROR: null value in column "b" of relation "gtest21a" violates not-null constraint
603 DETAIL: Failing row contains (0, null).
604 CREATE TABLE gtest21b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a, 0)) STORED);
605 ALTER TABLE gtest21b ALTER COLUMN b SET NOT NULL;
606 INSERT INTO gtest21b (a) VALUES (1); -- ok
607 INSERT INTO gtest21b (a) VALUES (0); -- violates constraint
608 ERROR: null value in column "b" of relation "gtest21b" violates not-null constraint
609 DETAIL: Failing row contains (0, null).
610 ALTER TABLE gtest21b ALTER COLUMN b DROP NOT NULL;
611 INSERT INTO gtest21b (a) VALUES (0); -- ok now
612 -- index constraints
613 CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) STORED UNIQUE);
614 INSERT INTO gtest22a VALUES (2);
615 INSERT INTO gtest22a VALUES (3);
616 ERROR: duplicate key value violates unique constraint "gtest22a_b_key"
617 DETAIL: Key (b)=(1) already exists.
618 INSERT INTO gtest22a VALUES (4);
619 CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) STORED, PRIMARY KEY (a, b));
620 INSERT INTO gtest22b VALUES (2);
621 INSERT INTO gtest22b VALUES (2);
622 ERROR: duplicate key value violates unique constraint "gtest22b_pkey"
623 DETAIL: Key (a, b)=(2, 1) already exists.
624 -- indexes
625 CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) STORED);
626 CREATE INDEX gtest22c_b_idx ON gtest22c (b);
627 CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
628 CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
629 \d gtest22c
630 Table "generated_stored_tests.gtest22c"
631 Column | Type | Collation | Nullable | Default
632 --------+---------+-----------+----------+------------------------------------
633 a | integer | | |
634 b | integer | | | generated always as (a * 2) stored
635 Indexes:
636 "gtest22c_b_idx" btree (b)
637 "gtest22c_expr_idx" btree ((b * 3))
638 "gtest22c_pred_idx" btree (a) WHERE b > 0
640 INSERT INTO gtest22c VALUES (1), (2), (3);
641 SET enable_seqscan TO off;
642 SET enable_bitmapscan TO off;
643 EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4;
644 QUERY PLAN
645 ---------------------------------------------
646 Index Scan using gtest22c_b_idx on gtest22c
647 Index Cond: (b = 4)
648 (2 rows)
650 SELECT * FROM gtest22c WHERE b = 4;
651 a | b
652 ---+---
653 2 | 4
654 (1 row)
656 EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6;
657 QUERY PLAN
658 ------------------------------------------------
659 Index Scan using gtest22c_expr_idx on gtest22c
660 Index Cond: ((b * 3) = 6)
661 (2 rows)
663 SELECT * FROM gtest22c WHERE b * 3 = 6;
664 a | b
665 ---+---
666 1 | 2
667 (1 row)
669 EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
670 QUERY PLAN
671 ------------------------------------------------
672 Index Scan using gtest22c_pred_idx on gtest22c
673 Index Cond: (a = 1)
674 (2 rows)
676 SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
677 a | b
678 ---+---
679 1 | 2
680 (1 row)
682 ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4);
683 ANALYZE gtest22c;
684 EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8;
685 QUERY PLAN
686 ---------------------------------------------
687 Index Scan using gtest22c_b_idx on gtest22c
688 Index Cond: (b = 8)
689 (2 rows)
691 SELECT * FROM gtest22c WHERE b = 8;
692 a | b
693 ---+---
694 2 | 8
695 (1 row)
697 EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12;
698 QUERY PLAN
699 ------------------------------------------------
700 Index Scan using gtest22c_expr_idx on gtest22c
701 Index Cond: ((b * 3) = 12)
702 (2 rows)
704 SELECT * FROM gtest22c WHERE b * 3 = 12;
705 a | b
706 ---+---
707 1 | 4
708 (1 row)
710 EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
711 QUERY PLAN
712 ------------------------------------------------
713 Index Scan using gtest22c_pred_idx on gtest22c
714 Index Cond: (a = 1)
715 (2 rows)
717 SELECT * FROM gtest22c WHERE a = 1 AND b > 0;
718 a | b
719 ---+---
720 1 | 4
721 (1 row)
723 RESET enable_seqscan;
724 RESET enable_bitmapscan;
725 -- foreign keys
726 CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
727 INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33);
728 CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON UPDATE CASCADE); -- error
729 ERROR: invalid ON UPDATE action for foreign key constraint containing generated column
730 CREATE TABLE gtest23x (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x) ON DELETE SET NULL); -- error
731 ERROR: invalid ON DELETE action for foreign key constraint containing generated column
732 CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED REFERENCES gtest23a (x));
733 \d gtest23b
734 Table "generated_stored_tests.gtest23b"
735 Column | Type | Collation | Nullable | Default
736 --------+---------+-----------+----------+------------------------------------
737 a | integer | | not null |
738 b | integer | | | generated always as (a * 2) stored
739 Indexes:
740 "gtest23b_pkey" PRIMARY KEY, btree (a)
741 Foreign-key constraints:
742 "gtest23b_b_fkey" FOREIGN KEY (b) REFERENCES gtest23a(x)
744 INSERT INTO gtest23b VALUES (1); -- ok
745 INSERT INTO gtest23b VALUES (5); -- error
746 ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
747 DETAIL: Key (b)=(10) is not present in table "gtest23a".
748 ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 5); -- error
749 ERROR: insert or update on table "gtest23b" violates foreign key constraint "gtest23b_b_fkey"
750 DETAIL: Key (b)=(5) is not present in table "gtest23a".
751 ALTER TABLE gtest23b ALTER COLUMN b SET EXPRESSION AS (a * 1); -- ok
752 DROP TABLE gtest23b;
753 DROP TABLE gtest23a;
754 CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) STORED, PRIMARY KEY (y));
755 INSERT INTO gtest23p VALUES (1), (2), (3);
756 CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y));
757 INSERT INTO gtest23q VALUES (1, 2); -- ok
758 INSERT INTO gtest23q VALUES (2, 5); -- error
759 ERROR: insert or update on table "gtest23q" violates foreign key constraint "gtest23q_b_fkey"
760 DETAIL: Key (b)=(5) is not present in table "gtest23p".
761 -- domains
762 CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
763 CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a * 2) STORED);
764 INSERT INTO gtest24 (a) VALUES (4); -- ok
765 INSERT INTO gtest24 (a) VALUES (6); -- error
766 ERROR: value for domain gtestdomain1 violates check constraint "gtestdomain1_check"
767 -- typed tables (currently not supported)
768 CREATE TYPE gtest_type AS (f1 integer, f2 text, f3 bigint);
769 CREATE TABLE gtest28 OF gtest_type (f1 WITH OPTIONS GENERATED ALWAYS AS (f2 *2) STORED);
770 ERROR: generated columns are not supported on typed tables
771 DROP TYPE gtest_type CASCADE;
772 -- partitioning cases
773 CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint) PARTITION BY RANGE (f1);
774 CREATE TABLE gtest_child PARTITION OF gtest_parent (
775 f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 2) STORED
776 ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
777 ERROR: child column "f3" specifies generation expression
778 HINT: A child table column cannot be generated unless its parent column is.
779 CREATE TABLE gtest_child (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED);
780 ALTER TABLE gtest_parent ATTACH PARTITION gtest_child FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
781 ERROR: column "f3" in child table must not be a generated column
782 DROP TABLE gtest_parent, gtest_child;
783 CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
784 CREATE TABLE gtest_child PARTITION OF gtest_parent
785 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
786 CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
787 f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) STORED -- overrides gen expr
788 ) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
789 CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
790 f3 DEFAULT 42 -- error
791 ) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
792 ERROR: column "f3" inherits from generated column but specifies default
793 CREATE TABLE gtest_child3 PARTITION OF gtest_parent (
794 f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY -- error
795 ) FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
796 ERROR: identity columns are not supported on partitions
797 CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint);
798 ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
799 ERROR: column "f3" in child table must be a generated column
800 DROP TABLE gtest_child3;
801 CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint DEFAULT 42);
802 ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
803 ERROR: column "f3" in child table must be a generated column
804 DROP TABLE gtest_child3;
805 CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS IDENTITY);
806 ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error
807 ERROR: table "gtest_child3" being attached contains an identity column "f3"
808 DETAIL: The new partition may not contain an identity column.
809 DROP TABLE gtest_child3;
810 CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED);
811 ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
812 \d gtest_child
813 Table "generated_stored_tests.gtest_child"
814 Column | Type | Collation | Nullable | Default
815 --------+--------+-----------+----------+-------------------------------------
816 f1 | date | | not null |
817 f2 | bigint | | |
818 f3 | bigint | | | generated always as (f2 * 2) stored
819 Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
821 \d gtest_child2
822 Table "generated_stored_tests.gtest_child2"
823 Column | Type | Collation | Nullable | Default
824 --------+--------+-----------+----------+--------------------------------------
825 f1 | date | | not null |
826 f2 | bigint | | |
827 f3 | bigint | | | generated always as (f2 * 22) stored
828 Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
830 \d gtest_child3
831 Table "generated_stored_tests.gtest_child3"
832 Column | Type | Collation | Nullable | Default
833 --------+--------+-----------+----------+--------------------------------------
834 f1 | date | | not null |
835 f2 | bigint | | |
836 f3 | bigint | | | generated always as (f2 * 33) stored
837 Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
839 INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
840 INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
841 INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
842 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
843 tableoid | f1 | f2 | f3
844 --------------+------------+----+----
845 gtest_child | 07-15-2016 | 1 | 2
846 gtest_child | 07-15-2016 | 2 | 4
847 gtest_child2 | 08-15-2016 | 3 | 66
848 (3 rows)
850 UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
851 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
852 tableoid | f1 | f2 | f3
853 --------------+------------+----+----
854 gtest_child | 07-15-2016 | 2 | 4
855 gtest_child2 | 08-15-2016 | 3 | 66
856 gtest_child3 | 09-13-2016 | 1 | 33
857 (3 rows)
859 -- alter only parent's and one child's generation expression
860 ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 4);
861 ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION AS (f2 * 10);
862 \d gtest_parent
863 Partitioned table "generated_stored_tests.gtest_parent"
864 Column | Type | Collation | Nullable | Default
865 --------+--------+-----------+----------+-------------------------------------
866 f1 | date | | not null |
867 f2 | bigint | | |
868 f3 | bigint | | | generated always as (f2 * 4) stored
869 Partition key: RANGE (f1)
870 Number of partitions: 3 (Use \d+ to list them.)
872 \d gtest_child
873 Table "generated_stored_tests.gtest_child"
874 Column | Type | Collation | Nullable | Default
875 --------+--------+-----------+----------+--------------------------------------
876 f1 | date | | not null |
877 f2 | bigint | | |
878 f3 | bigint | | | generated always as (f2 * 10) stored
879 Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
881 \d gtest_child2
882 Table "generated_stored_tests.gtest_child2"
883 Column | Type | Collation | Nullable | Default
884 --------+--------+-----------+----------+--------------------------------------
885 f1 | date | | not null |
886 f2 | bigint | | |
887 f3 | bigint | | | generated always as (f2 * 22) stored
888 Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
890 \d gtest_child3
891 Table "generated_stored_tests.gtest_child3"
892 Column | Type | Collation | Nullable | Default
893 --------+--------+-----------+----------+--------------------------------------
894 f1 | date | | not null |
895 f2 | bigint | | |
896 f3 | bigint | | | generated always as (f2 * 33) stored
897 Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
899 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
900 tableoid | f1 | f2 | f3
901 --------------+------------+----+----
902 gtest_child | 07-15-2016 | 2 | 20
903 gtest_child2 | 08-15-2016 | 3 | 66
904 gtest_child3 | 09-13-2016 | 1 | 33
905 (3 rows)
907 -- alter generation expression of parent and all its children altogether
908 ALTER TABLE gtest_parent ALTER COLUMN f3 SET EXPRESSION AS (f2 * 2);
909 \d gtest_parent
910 Partitioned table "generated_stored_tests.gtest_parent"
911 Column | Type | Collation | Nullable | Default
912 --------+--------+-----------+----------+-------------------------------------
913 f1 | date | | not null |
914 f2 | bigint | | |
915 f3 | bigint | | | generated always as (f2 * 2) stored
916 Partition key: RANGE (f1)
917 Number of partitions: 3 (Use \d+ to list them.)
919 \d gtest_child
920 Table "generated_stored_tests.gtest_child"
921 Column | Type | Collation | Nullable | Default
922 --------+--------+-----------+----------+-------------------------------------
923 f1 | date | | not null |
924 f2 | bigint | | |
925 f3 | bigint | | | generated always as (f2 * 2) stored
926 Partition of: gtest_parent FOR VALUES FROM ('07-01-2016') TO ('08-01-2016')
928 \d gtest_child2
929 Table "generated_stored_tests.gtest_child2"
930 Column | Type | Collation | Nullable | Default
931 --------+--------+-----------+----------+-------------------------------------
932 f1 | date | | not null |
933 f2 | bigint | | |
934 f3 | bigint | | | generated always as (f2 * 2) stored
935 Partition of: gtest_parent FOR VALUES FROM ('08-01-2016') TO ('09-01-2016')
937 \d gtest_child3
938 Table "generated_stored_tests.gtest_child3"
939 Column | Type | Collation | Nullable | Default
940 --------+--------+-----------+----------+-------------------------------------
941 f1 | date | | not null |
942 f2 | bigint | | |
943 f3 | bigint | | | generated always as (f2 * 2) stored
944 Partition of: gtest_parent FOR VALUES FROM ('09-01-2016') TO ('10-01-2016')
946 SELECT tableoid::regclass, * FROM gtest_parent ORDER BY 1, 2, 3;
947 tableoid | f1 | f2 | f3
948 --------------+------------+----+----
949 gtest_child | 07-15-2016 | 2 | 4
950 gtest_child2 | 08-15-2016 | 3 | 6
951 gtest_child3 | 09-13-2016 | 1 | 2
952 (3 rows)
954 -- we leave these tables around for purposes of testing dump/reload/upgrade
955 -- generated columns in partition key (not allowed)
956 CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
957 ERROR: cannot use generated column in partition key
958 LINE 1: ...ENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f3);
959 ^
960 DETAIL: Column "f3" is a generated column.
961 CREATE TABLE gtest_part_key (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
962 ERROR: cannot use generated column in partition key
963 LINE 1: ...ED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE ((f3 * 3));
964 ^
965 DETAIL: Column "f3" is a generated column.
966 -- ALTER TABLE ... ADD COLUMN
967 CREATE TABLE gtest25 (a int PRIMARY KEY);
968 INSERT INTO gtest25 VALUES (3), (4);
969 ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 2) STORED, ALTER COLUMN b SET EXPRESSION AS (a * 3);
970 SELECT * FROM gtest25 ORDER BY a;
971 a | b
972 ---+----
973 3 | 9
974 4 | 12
975 (2 rows)
977 ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4) STORED; -- error
978 ERROR: cannot use generated column "b" in column generation expression
979 DETAIL: A generated column cannot reference another generated column.
980 ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4) STORED; -- error
981 ERROR: column "z" does not exist
982 ALTER TABLE gtest25 ADD COLUMN c int DEFAULT 42,
983 ADD COLUMN x int GENERATED ALWAYS AS (c * 4) STORED;
984 ALTER TABLE gtest25 ADD COLUMN d int DEFAULT 101;
985 ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
986 ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED;
987 SELECT * FROM gtest25 ORDER BY a;
988 a | b | c | x | d | y
989 ---+----+----+-----+-----+-----
990 3 | 9 | 42 | 168 | 101 | 404
991 4 | 12 | 42 | 168 | 101 | 404
992 (2 rows)
994 \d gtest25
995 Table "generated_stored_tests.gtest25"
996 Column | Type | Collation | Nullable | Default
997 --------+------------------+-----------+----------+------------------------------------------------------
998 a | integer | | not null |
999 b | integer | | | generated always as (a * 3) stored
1000 c | integer | | | 42
1001 x | integer | | | generated always as (c * 4) stored
1002 d | double precision | | | 101
1003 y | double precision | | | generated always as (d * 4::double precision) stored
1004 Indexes:
1005 "gtest25_pkey" PRIMARY KEY, btree (a)
1007 -- ALTER TABLE ... ALTER COLUMN
1008 CREATE TABLE gtest27 (
1009 a int,
1010 b int,
1011 x int GENERATED ALWAYS AS ((a + b) * 2) STORED
1012 );
1013 INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
1014 ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
1015 ERROR: cannot alter type of a column used by a generated column
1016 DETAIL: Column "a" is used by generated column "x".
1017 ALTER TABLE gtest27 ALTER COLUMN x TYPE numeric;
1018 \d gtest27
1019 Table "generated_stored_tests.gtest27"
1020 Column | Type | Collation | Nullable | Default
1021 --------+---------+-----------+----------+--------------------------------------------
1022 a | integer | | |
1023 b | integer | | |
1024 x | numeric | | | generated always as (((a + b) * 2)) stored
1026 SELECT * FROM gtest27;
1027 a | b | x
1028 ---+----+----
1029 3 | 7 | 20
1030 4 | 11 | 30
1031 (2 rows)
1033 ALTER TABLE gtest27 ALTER COLUMN x TYPE boolean USING x <> 0; -- error
1034 ERROR: cannot specify USING when altering type of generated column
1035 DETAIL: Column "x" is a generated column.
1036 ALTER TABLE gtest27 ALTER COLUMN x DROP DEFAULT; -- error
1037 ERROR: column "x" of relation "gtest27" is a generated column
1038 HINT: Use ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION instead.
1039 -- It's possible to alter the column types this way:
1040 ALTER TABLE gtest27
1041 DROP COLUMN x,
1042 ALTER COLUMN a TYPE bigint,
1043 ALTER COLUMN b TYPE bigint,
1044 ADD COLUMN x bigint GENERATED ALWAYS AS ((a + b) * 2) STORED;
1045 \d gtest27
1046 Table "generated_stored_tests.gtest27"
1047 Column | Type | Collation | Nullable | Default
1048 --------+--------+-----------+----------+------------------------------------------
1049 a | bigint | | |
1050 b | bigint | | |
1051 x | bigint | | | generated always as ((a + b) * 2) stored
1053 -- Ideally you could just do this, but not today (and should x change type?):
1054 ALTER TABLE gtest27
1055 ALTER COLUMN a TYPE float8,
1056 ALTER COLUMN b TYPE float8; -- error
1057 ERROR: cannot alter type of a column used by a generated column
1058 DETAIL: Column "a" is used by generated column "x".
1059 \d gtest27
1060 Table "generated_stored_tests.gtest27"
1061 Column | Type | Collation | Nullable | Default
1062 --------+--------+-----------+----------+------------------------------------------
1063 a | bigint | | |
1064 b | bigint | | |
1065 x | bigint | | | generated always as ((a + b) * 2) stored
1067 SELECT * FROM gtest27;
1068 a | b | x
1069 ---+----+----
1070 3 | 7 | 20
1071 4 | 11 | 30
1072 (2 rows)
1074 -- ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION
1075 CREATE TABLE gtest29 (
1076 a int,
1077 b int GENERATED ALWAYS AS (a * 2) STORED
1078 );
1079 INSERT INTO gtest29 (a) VALUES (3), (4);
1080 SELECT * FROM gtest29;
1081 a | b
1082 ---+---
1083 3 | 6
1084 4 | 8
1085 (2 rows)
1087 \d gtest29
1088 Table "generated_stored_tests.gtest29"
1089 Column | Type | Collation | Nullable | Default
1090 --------+---------+-----------+----------+------------------------------------
1091 a | integer | | |
1092 b | integer | | | generated always as (a * 2) stored
1094 ALTER TABLE gtest29 ALTER COLUMN a SET EXPRESSION AS (a * 3); -- error
1095 ERROR: column "a" of relation "gtest29" is not a generated column
1096 ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION; -- error
1097 ERROR: column "a" of relation "gtest29" is not a stored generated column
1098 ALTER TABLE gtest29 ALTER COLUMN a DROP EXPRESSION IF EXISTS; -- notice
1099 NOTICE: column "a" of relation "gtest29" is not a stored generated column, skipping
1100 -- Change the expression
1101 ALTER TABLE gtest29 ALTER COLUMN b SET EXPRESSION AS (a * 3);
1102 SELECT * FROM gtest29;
1103 a | b
1104 ---+----
1105 3 | 9
1106 4 | 12
1107 (2 rows)
1109 \d gtest29
1110 Table "generated_stored_tests.gtest29"
1111 Column | Type | Collation | Nullable | Default
1112 --------+---------+-----------+----------+------------------------------------
1113 a | integer | | |
1114 b | integer | | | generated always as (a * 3) stored
1116 ALTER TABLE gtest29 ALTER COLUMN b DROP EXPRESSION;
1117 INSERT INTO gtest29 (a) VALUES (5);
1118 INSERT INTO gtest29 (a, b) VALUES (6, 66);
1119 SELECT * FROM gtest29;
1120 a | b
1121 ---+----
1122 3 | 9
1123 4 | 12
1124 5 |
1125 6 | 66
1126 (4 rows)
1128 \d gtest29
1129 Table "generated_stored_tests.gtest29"
1130 Column | Type | Collation | Nullable | Default
1131 --------+---------+-----------+----------+---------
1132 a | integer | | |
1133 b | integer | | |
1135 -- check that dependencies between columns have also been removed
1136 ALTER TABLE gtest29 DROP COLUMN a; -- should not drop b
1137 \d gtest29
1138 Table "generated_stored_tests.gtest29"
1139 Column | Type | Collation | Nullable | Default
1140 --------+---------+-----------+----------+---------
1141 b | integer | | |
1143 -- with inheritance
1144 CREATE TABLE gtest30 (
1145 a int,
1146 b int GENERATED ALWAYS AS (a * 2) STORED
1147 );
1148 CREATE TABLE gtest30_1 () INHERITS (gtest30);
1149 ALTER TABLE gtest30 ALTER COLUMN b DROP EXPRESSION;
1150 \d gtest30
1151 Table "generated_stored_tests.gtest30"
1152 Column | Type | Collation | Nullable | Default
1153 --------+---------+-----------+----------+---------
1154 a | integer | | |
1155 b | integer | | |
1156 Number of child tables: 1 (Use \d+ to list them.)
1158 \d gtest30_1
1159 Table "generated_stored_tests.gtest30_1"
1160 Column | Type | Collation | Nullable | Default
1161 --------+---------+-----------+----------+---------
1162 a | integer | | |
1163 b | integer | | |
1164 Inherits: gtest30
1166 DROP TABLE gtest30 CASCADE;
1167 NOTICE: drop cascades to table gtest30_1
1168 CREATE TABLE gtest30 (
1169 a int,
1170 b int GENERATED ALWAYS AS (a * 2) STORED
1171 );
1172 CREATE TABLE gtest30_1 () INHERITS (gtest30);
1173 ALTER TABLE ONLY gtest30 ALTER COLUMN b DROP EXPRESSION; -- error
1174 ERROR: ALTER TABLE / DROP EXPRESSION must be applied to child tables too
1175 \d gtest30
1176 Table "generated_stored_tests.gtest30"
1177 Column | Type | Collation | Nullable | Default
1178 --------+---------+-----------+----------+------------------------------------
1179 a | integer | | |
1180 b | integer | | | generated always as (a * 2) stored
1181 Number of child tables: 1 (Use \d+ to list them.)
1183 \d gtest30_1
1184 Table "generated_stored_tests.gtest30_1"
1185 Column | Type | Collation | Nullable | Default
1186 --------+---------+-----------+----------+------------------------------------
1187 a | integer | | |
1188 b | integer | | | generated always as (a * 2) stored
1189 Inherits: gtest30
1191 ALTER TABLE gtest30_1 ALTER COLUMN b DROP EXPRESSION; -- error
1192 ERROR: cannot drop generation expression from inherited column
1193 -- triggers
1194 CREATE TABLE gtest26 (
1195 a int PRIMARY KEY,
1196 b int GENERATED ALWAYS AS (a * 2) STORED
1197 );
1198 CREATE FUNCTION gtest_trigger_func() RETURNS trigger
1199 LANGUAGE plpgsql
1200 AS $$
1201 BEGIN
1202 IF tg_op IN ('DELETE', 'UPDATE') THEN
1203 RAISE INFO '%: %: old = %', TG_NAME, TG_WHEN, OLD;
1204 END IF;
1205 IF tg_op IN ('INSERT', 'UPDATE') THEN
1206 RAISE INFO '%: %: new = %', TG_NAME, TG_WHEN, NEW;
1207 END IF;
1208 IF tg_op = 'DELETE' THEN
1209 RETURN OLD;
1210 ELSE
1211 RETURN NEW;
1212 END IF;
1213 END
1214 $$;
1215 CREATE TRIGGER gtest1 BEFORE DELETE OR UPDATE ON gtest26
1216 FOR EACH ROW
1217 WHEN (OLD.b < 0) -- ok
1218 EXECUTE PROCEDURE gtest_trigger_func();
1219 CREATE TRIGGER gtest2a BEFORE INSERT OR UPDATE ON gtest26
1220 FOR EACH ROW
1221 WHEN (NEW.b < 0) -- error
1222 EXECUTE PROCEDURE gtest_trigger_func();
1223 ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated columns
1224 LINE 3: WHEN (NEW.b < 0) -- error
1225 ^
1226 DETAIL: Column "b" is a generated column.
1227 CREATE TRIGGER gtest2b BEFORE INSERT OR UPDATE ON gtest26
1228 FOR EACH ROW
1229 WHEN (NEW.* IS NOT NULL) -- error
1230 EXECUTE PROCEDURE gtest_trigger_func();
1231 ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated columns
1232 LINE 3: WHEN (NEW.* IS NOT NULL) -- error
1233 ^
1234 DETAIL: A whole-row reference is used and the table contains generated columns.
1235 CREATE TRIGGER gtest2 BEFORE INSERT ON gtest26
1236 FOR EACH ROW
1237 WHEN (NEW.a < 0)
1238 EXECUTE PROCEDURE gtest_trigger_func();
1239 CREATE TRIGGER gtest3 AFTER DELETE OR UPDATE ON gtest26
1240 FOR EACH ROW
1241 WHEN (OLD.b < 0) -- ok
1242 EXECUTE PROCEDURE gtest_trigger_func();
1243 CREATE TRIGGER gtest4 AFTER INSERT OR UPDATE ON gtest26
1244 FOR EACH ROW
1245 WHEN (NEW.b < 0) -- ok
1246 EXECUTE PROCEDURE gtest_trigger_func();
1247 INSERT INTO gtest26 (a) VALUES (-2), (0), (3);
1248 INFO: gtest2: BEFORE: new = (-2,)
1249 INFO: gtest4: AFTER: new = (-2,-4)
1250 SELECT * FROM gtest26 ORDER BY a;
1251 a | b
1252 ----+----
1253 -2 | -4
1254 0 | 0
1255 3 | 6
1256 (3 rows)
1258 UPDATE gtest26 SET a = a * -2;
1259 INFO: gtest1: BEFORE: old = (-2,-4)
1260 INFO: gtest1: BEFORE: new = (4,)
1261 INFO: gtest3: AFTER: old = (-2,-4)
1262 INFO: gtest3: AFTER: new = (4,8)
1263 INFO: gtest4: AFTER: old = (3,6)
1264 INFO: gtest4: AFTER: new = (-6,-12)
1265 SELECT * FROM gtest26 ORDER BY a;
1266 a | b
1267 ----+-----
1268 -6 | -12
1269 0 | 0
1270 4 | 8
1271 (3 rows)
1273 DELETE FROM gtest26 WHERE a = -6;
1274 INFO: gtest1: BEFORE: old = (-6,-12)
1275 INFO: gtest3: AFTER: old = (-6,-12)
1276 SELECT * FROM gtest26 ORDER BY a;
1277 a | b
1278 ---+---
1279 0 | 0
1280 4 | 8
1281 (2 rows)
1283 DROP TRIGGER gtest1 ON gtest26;
1284 DROP TRIGGER gtest2 ON gtest26;
1285 DROP TRIGGER gtest3 ON gtest26;
1286 -- Check that an UPDATE of "a" fires the trigger for UPDATE OF b, per
1287 -- SQL standard.
1288 CREATE FUNCTION gtest_trigger_func3() RETURNS trigger
1289 LANGUAGE plpgsql
1290 AS $$
1291 BEGIN
1292 RAISE NOTICE 'OK';
1293 RETURN NEW;
1294 END
1295 $$;
1296 CREATE TRIGGER gtest11 BEFORE UPDATE OF b ON gtest26
1297 FOR EACH ROW
1298 EXECUTE PROCEDURE gtest_trigger_func3();
1299 UPDATE gtest26 SET a = 1 WHERE a = 0;
1300 NOTICE: OK
1301 DROP TRIGGER gtest11 ON gtest26;
1302 TRUNCATE gtest26;
1303 -- check that modifications of stored generated columns in triggers do
1304 -- not get propagated
1305 CREATE FUNCTION gtest_trigger_func4() RETURNS trigger
1306 LANGUAGE plpgsql
1307 AS $$
1308 BEGIN
1309 NEW.a = 10;
1310 NEW.b = 300;
1311 RETURN NEW;
1312 END;
1313 $$;
1314 CREATE TRIGGER gtest12_01 BEFORE UPDATE ON gtest26
1315 FOR EACH ROW
1316 EXECUTE PROCEDURE gtest_trigger_func();
1317 CREATE TRIGGER gtest12_02 BEFORE UPDATE ON gtest26
1318 FOR EACH ROW
1319 EXECUTE PROCEDURE gtest_trigger_func4();
1320 CREATE TRIGGER gtest12_03 BEFORE UPDATE ON gtest26
1321 FOR EACH ROW
1322 EXECUTE PROCEDURE gtest_trigger_func();
1323 INSERT INTO gtest26 (a) VALUES (1);
1324 UPDATE gtest26 SET a = 11 WHERE a = 1;
1325 INFO: gtest12_01: BEFORE: old = (1,2)
1326 INFO: gtest12_01: BEFORE: new = (11,)
1327 INFO: gtest12_03: BEFORE: old = (1,2)
1328 INFO: gtest12_03: BEFORE: new = (10,)
1329 SELECT * FROM gtest26 ORDER BY a;
1330 a | b
1331 ----+----
1332 10 | 20
1333 (1 row)
1335 -- LIKE INCLUDING GENERATED and dropped column handling
1336 CREATE TABLE gtest28a (
1337 a int,
1338 b int,
1339 c int,
1340 x int GENERATED ALWAYS AS (b * 2) STORED
1341 );
1342 ALTER TABLE gtest28a DROP COLUMN a;
1343 CREATE TABLE gtest28b (LIKE gtest28a INCLUDING GENERATED);
1344 \d gtest28*
1345 Table "generated_stored_tests.gtest28a"
1346 Column | Type | Collation | Nullable | Default
1347 --------+---------+-----------+----------+------------------------------------
1348 b | integer | | |
1349 c | integer | | |
1350 x | integer | | | generated always as (b * 2) stored
1352 Table "generated_stored_tests.gtest28b"
1353 Column | Type | Collation | Nullable | Default
1354 --------+---------+-----------+----------+------------------------------------
1355 b | integer | | |
1356 c | integer | | |
1357 x | integer | | | generated always as (b * 2) stored