summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/identity.out
blob: 5f03d8e14fb4bd1cb779a1ad721a009e4ea69d18 (plain)
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
-- sanity check of system catalog
SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd');
 attrelid | attname | attidentity 
----------+---------+-------------
(0 rows)

CREATE TABLE itest1 (a int generated by default as identity, b text);
CREATE TABLE itest2 (a bigint generated always as identity, b text);
CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text);
ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error
ERROR:  column "a" of relation "itest3" is already an identity column
SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2;
 table_name | column_name | column_default | is_nullable | is_identity | identity_generation | identity_start | identity_increment |  identity_maximum   | identity_minimum | identity_cycle 
------------+-------------+----------------+-------------+-------------+---------------------+----------------+--------------------+---------------------+------------------+----------------
 itest1     | a           |                | NO          | YES         | BY DEFAULT          | 1              | 1                  | 2147483647          | 1                | NO
 itest1     | b           |                | YES         | NO          |                     |                |                    |                     |                  | NO
 itest2     | a           |                | NO          | YES         | ALWAYS              | 1              | 1                  | 9223372036854775807 | 1                | NO
 itest2     | b           |                | YES         | NO          |                     |                |                    |                     |                  | NO
 itest3     | a           |                | NO          | YES         | BY DEFAULT          | 7              | 5                  | 32767               | 1                | NO
 itest3     | b           |                | YES         | NO          |                     |                |                    |                     |                  | NO
(6 rows)

-- internal sequences should not be shown here
SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';
 sequence_name 
---------------
(0 rows)

SELECT pg_get_serial_sequence('itest1', 'a');
 pg_get_serial_sequence 
------------------------
 public.itest1_a_seq
(1 row)

\d itest1_a_seq
                    Sequence "public.itest1_a_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Sequence for identity column: public.itest1.a

CREATE TABLE itest4 (a int, b text);
ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, requires NOT NULL
ERROR:  column "a" of relation "itest4" must be declared NOT NULL before identity can be added
ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL;
ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- ok
ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;  -- error, disallowed
ERROR:  column "a" of relation "itest4" is an identity column
ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;  -- error, already set
ERROR:  column "a" of relation "itest4" is already an identity column
ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY;  -- error, wrong data type
ERROR:  identity column type must be smallint, integer, or bigint
-- for later
ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT '';
-- invalid column type
CREATE TABLE itest_err_1 (a text generated by default as identity);
ERROR:  identity column type must be smallint, integer, or bigint
-- duplicate identity
CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity);
ERROR:  multiple identity specifications for column "a" of table "itest_err_2"
LINE 1: ...E itest_err_2 (a int generated always as identity generated ...
                                                             ^
-- cannot have default and identity
CREATE TABLE itest_err_3 (a int default 5 generated by default as identity);
ERROR:  both default and identity specified for column "a" of table "itest_err_3"
LINE 1: CREATE TABLE itest_err_3 (a int default 5 generated by defau...
                                                  ^
-- cannot combine serial and identity
CREATE TABLE itest_err_4 (a serial generated by default as identity);
ERROR:  both default and identity specified for column "a" of table "itest_err_4"
INSERT INTO itest1 DEFAULT VALUES;
INSERT INTO itest1 DEFAULT VALUES;
INSERT INTO itest2 DEFAULT VALUES;
INSERT INTO itest2 DEFAULT VALUES;
INSERT INTO itest3 DEFAULT VALUES;
INSERT INTO itest3 DEFAULT VALUES;
INSERT INTO itest4 DEFAULT VALUES;
INSERT INTO itest4 DEFAULT VALUES;
SELECT * FROM itest1;
 a | b 
---+---
 1 | 
 2 | 
(2 rows)

SELECT * FROM itest2;
 a | b 
---+---
 1 | 
 2 | 
(2 rows)

SELECT * FROM itest3;
 a  | b 
----+---
  7 | 
 12 | 
(2 rows)

SELECT * FROM itest4;
 a | b 
---+---
 1 | 
 2 | 
(2 rows)

-- VALUES RTEs
CREATE TABLE itest5 (a int generated always as identity, b text);
INSERT INTO itest5 VALUES (1, 'a');  -- error
ERROR:  cannot insert a non-DEFAULT value into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO itest5 VALUES (DEFAULT, 'a');  -- ok
INSERT INTO itest5 VALUES (2, 'b'), (3, 'c');  -- error
ERROR:  cannot insert a non-DEFAULT value into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO itest5 VALUES (DEFAULT, 'b'), (3, 'c');  -- error
ERROR:  cannot insert a non-DEFAULT value into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO itest5 VALUES (2, 'b'), (DEFAULT, 'c');  -- error
ERROR:  cannot insert a non-DEFAULT value into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');  -- ok
INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa');
INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc');
INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee');
INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg');
INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii');
INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa');
INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc');
INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee');
INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg');
INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii');
SELECT * FROM itest5;
 a  |  b  
----+-----
  1 | a
  2 | b
  3 | c
 -1 | aa
 -2 | bb
 -3 | cc
  4 | dd
 -4 | ee
 -5 | ff
  5 | gg
  6 | hh
  7 | ii
  8 | aaa
  9 | bbb
 10 | ccc
 11 | ddd
 12 | eee
 13 | fff
 14 | ggg
 15 | hhh
 16 | iii
(21 rows)

DROP TABLE itest5;
INSERT INTO itest3 VALUES (DEFAULT, 'a');
INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
SELECT * FROM itest3;
 a  | b 
----+---
  7 | 
 12 | 
 17 | a
 22 | b
 27 | c
(5 rows)

-- OVERRIDING tests
-- GENERATED BY DEFAULT
-- This inserts the row as presented:
INSERT INTO itest1 VALUES (10, 'xyz');
-- With GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE is not allowed
-- by the standard, but we allow it as a no-op, since it is of use if
-- there are multiple identity columns in a table, which is also an
-- extension.
INSERT INTO itest1 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
-- This ignores the 30 and uses the sequence value instead:
INSERT INTO itest1 OVERRIDING USER VALUE VALUES (30, 'xyz');
SELECT * FROM itest1;
 a  |  b  
----+-----
  1 | 
  2 | 
 10 | xyz
 20 | xyz
  3 | xyz
(5 rows)

-- GENERATED ALWAYS
-- This is an error:
INSERT INTO itest2 VALUES (10, 'xyz');
ERROR:  cannot insert a non-DEFAULT value into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
-- This inserts the row as presented:
INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (20, 'xyz');
-- This ignores the 30 and uses the sequence value instead:
INSERT INTO itest2 OVERRIDING USER VALUE VALUES (30, 'xyz');
SELECT * FROM itest2;
 a  |  b  
----+-----
  1 | 
  2 | 
 20 | xyz
  3 | xyz
(4 rows)

-- UPDATE tests
-- GENERATED BY DEFAULT is not restricted.
UPDATE itest1 SET a = 101 WHERE a = 1;
UPDATE itest1 SET a = DEFAULT WHERE a = 2;
SELECT * FROM itest1;
  a  |  b  
-----+-----
  10 | xyz
  20 | xyz
   3 | xyz
 101 | 
   4 | 
(5 rows)

-- GENERATED ALWAYS allows only DEFAULT.
UPDATE itest2 SET a = 101 WHERE a = 1;  -- error
ERROR:  column "a" can only be updated to DEFAULT
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
UPDATE itest2 SET a = DEFAULT WHERE a = 2;  -- ok
SELECT * FROM itest2;
 a  |  b  
----+-----
  1 | 
 20 | xyz
  3 | xyz
  4 | 
(4 rows)

-- COPY tests
CREATE TABLE itest9 (a int GENERATED ALWAYS AS IDENTITY, b text, c bigint);
COPY itest9 FROM stdin;
COPY itest9 (b, c) FROM stdin;
SELECT * FROM itest9 ORDER BY c;
  a  |  b   |  c  
-----+------+-----
 100 | foo  | 200
 101 | bar  | 201
   1 | foo2 | 202
   2 | bar2 | 203
(4 rows)

-- DROP IDENTITY tests
ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;
ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;  -- error
ERROR:  column "a" of relation "itest4" is not an identity column
ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS;  -- noop
NOTICE:  column "a" of relation "itest4" is not an identity column, skipping
INSERT INTO itest4 DEFAULT VALUES;  -- fails because NOT NULL is not dropped
ERROR:  null value in column "a" of relation "itest4" violates not-null constraint
DETAIL:  Failing row contains (null, ).
ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;
INSERT INTO itest4 DEFAULT VALUES;
SELECT * FROM itest4;
 a | b 
---+---
 1 | 
 2 | 
   | 
(3 rows)

-- check that sequence is removed
SELECT sequence_name FROM itest4_a_seq;
ERROR:  relation "itest4_a_seq" does not exist
LINE 1: SELECT sequence_name FROM itest4_a_seq;
                                  ^
-- test views
CREATE TABLE itest10 (a int generated by default as identity, b text);
CREATE TABLE itest11 (a int generated always as identity, b text);
CREATE VIEW itestv10 AS SELECT * FROM itest10;
CREATE VIEW itestv11 AS SELECT * FROM itest11;
INSERT INTO itestv10 DEFAULT VALUES;
INSERT INTO itestv10 DEFAULT VALUES;
INSERT INTO itestv11 DEFAULT VALUES;
INSERT INTO itestv11 DEFAULT VALUES;
SELECT * FROM itestv10;
 a | b 
---+---
 1 | 
 2 | 
(2 rows)

SELECT * FROM itestv11;
 a | b 
---+---
 1 | 
 2 | 
(2 rows)

INSERT INTO itestv10 VALUES (10, 'xyz');
INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz');
SELECT * FROM itestv10;
 a  |  b  
----+-----
  1 | 
  2 | 
 10 | xyz
  3 | xyz
(4 rows)

INSERT INTO itestv11 VALUES (10, 'xyz');
ERROR:  cannot insert a non-DEFAULT value into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');
SELECT * FROM itestv11;
 a  |  b  
----+-----
  1 | 
  2 | 
 11 | xyz
(3 rows)

DROP VIEW itestv10, itestv11;
-- ADD COLUMN
CREATE TABLE itest13 (a int);
-- add column to empty table
ALTER TABLE itest13 ADD COLUMN b int GENERATED BY DEFAULT AS IDENTITY;
INSERT INTO itest13 VALUES (1), (2), (3);
-- add column to populated table
ALTER TABLE itest13 ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY;
SELECT * FROM itest13;
 a | b | c 
---+---+---
 1 | 1 | 1
 2 | 2 | 2
 3 | 3 | 3
(3 rows)

-- various ALTER COLUMN tests
-- fail, not allowed for identity columns
ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;
ERROR:  column "a" of relation "itest1" is an identity column
-- fail, not allowed, already has a default
CREATE TABLE itest5 (a serial, b text);
ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
ERROR:  column "a" of relation "itest5" already has a default value
ALTER TABLE itest3 ALTER COLUMN a TYPE int;
SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass;
 seqtypid 
----------
 integer
(1 row)

\d itest3
                           Table "public.itest3"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 a      | integer |           | not null | generated by default as identity
 b      | text    |           |          | 

ALTER TABLE itest3 ALTER COLUMN a TYPE text;  -- error
ERROR:  identity column type must be smallint, integer, or bigint
-- kinda silly to change property in the same command, but it should work
ALTER TABLE itest3
  ADD COLUMN c int GENERATED BY DEFAULT AS IDENTITY,
  ALTER COLUMN c SET GENERATED ALWAYS;
\d itest3
                           Table "public.itest3"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 a      | integer |           | not null | generated by default as identity
 b      | text    |           |          | 
 c      | integer |           | not null | generated always as identity

-- ALTER COLUMN ... SET
CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);
INSERT INTO itest6 DEFAULT VALUES;
ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;
INSERT INTO itest6 DEFAULT VALUES;
INSERT INTO itest6 DEFAULT VALUES;
SELECT * FROM itest6;
  a  | b 
-----+---
   1 | 
 100 | 
 102 | 
(3 rows)

SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6' ORDER BY 1, 2;
 table_name | column_name | is_identity | identity_generation 
------------+-------------+-------------+---------------------
 itest6     | a           | YES         | BY DEFAULT
 itest6     | b           | NO          | 
(2 rows)

ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2;  -- fail, not identity
ERROR:  column "b" of relation "itest6" is not an identity column
-- prohibited direct modification of sequence
ALTER SEQUENCE itest6_a_seq OWNED BY NONE;
ERROR:  cannot change ownership of identity sequence
DETAIL:  Sequence "itest6_a_seq" is linked to table "itest6".
-- inheritance
CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);
INSERT INTO itest7 DEFAULT VALUES;
SELECT * FROM itest7;
 a 
---
 1
(1 row)

-- identity property is not inherited
CREATE TABLE itest7a (b text) INHERITS (itest7);
-- make column identity in child table
CREATE TABLE itest7b (a int);
CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b);
NOTICE:  merging column "a" with inherited definition
INSERT INTO itest7c DEFAULT VALUES;
SELECT * FROM itest7c;
 a 
---
 1
(1 row)

CREATE TABLE itest7d (a int not null);
CREATE TABLE itest7e () INHERITS (itest7d);
ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY;  -- error
ERROR:  cannot recursively add identity column to table that has child tables
SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2;
 table_name | column_name | is_nullable | is_identity | identity_generation 
------------+-------------+-------------+-------------+---------------------
 itest7     | a           | NO          | YES         | ALWAYS
 itest7a    | a           | NO          | NO          | 
 itest7a    | b           | YES         | NO          | 
 itest7b    | a           | YES         | NO          | 
 itest7c    | a           | NO          | YES         | ALWAYS
 itest7d    | a           | NO          | YES         | ALWAYS
 itest7e    | a           | NO          | NO          | 
(7 rows)

-- These ALTER TABLE variants will not recurse.
ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT;
ALTER TABLE itest7 ALTER COLUMN a RESTART;
ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY;
-- privileges
CREATE USER regress_identity_user1;
CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text);
GRANT SELECT, INSERT ON itest8 TO regress_identity_user1;
SET ROLE regress_identity_user1;
INSERT INTO itest8 DEFAULT VALUES;
SELECT * FROM itest8;
 a | b 
---+---
 1 | 
(1 row)

RESET ROLE;
DROP TABLE itest8;
DROP USER regress_identity_user1;
-- multiple steps in ALTER TABLE
CREATE TABLE itest8 (f1 int);
ALTER TABLE itest8
  ADD COLUMN f2 int NOT NULL,
  ALTER COLUMN f2 ADD GENERATED ALWAYS AS IDENTITY;
ALTER TABLE itest8
  ADD COLUMN f3 int NOT NULL,
  ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY,
  ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT 10;
ALTER TABLE itest8
  ADD COLUMN f4 int;
ALTER TABLE itest8
  ALTER COLUMN f4 SET NOT NULL,
  ALTER COLUMN f4 ADD GENERATED ALWAYS AS IDENTITY,
  ALTER COLUMN f4 SET DATA TYPE bigint;
ALTER TABLE itest8
  ADD COLUMN f5 int GENERATED ALWAYS AS IDENTITY;
ALTER TABLE itest8
  ALTER COLUMN f5 DROP IDENTITY,
  ALTER COLUMN f5 DROP NOT NULL,
  ALTER COLUMN f5 SET DATA TYPE bigint;
INSERT INTO itest8 VALUES(0), (1);
-- This does not work when the table isn't empty.  That's intentional,
-- since ADD GENERATED should only affect later insertions:
ALTER TABLE itest8
  ADD COLUMN f22 int NOT NULL,
  ALTER COLUMN f22 ADD GENERATED ALWAYS AS IDENTITY;
ERROR:  column "f22" of relation "itest8" contains null values
TABLE itest8;
 f1 | f2 | f3 | f4 | f5 
----+----+----+----+----
  0 |  1 |  1 |  1 |   
  1 |  2 | 11 |  2 |   
(2 rows)

\d+ itest8
                                               Table "public.itest8"
 Column |  Type   | Collation | Nullable |             Default              | Storage | Stats target | Description 
--------+---------+-----------+----------+----------------------------------+---------+--------------+-------------
 f1     | integer |           |          |                                  | plain   |              | 
 f2     | integer |           | not null | generated always as identity     | plain   |              | 
 f3     | integer |           | not null | generated by default as identity | plain   |              | 
 f4     | bigint  |           | not null | generated always as identity     | plain   |              | 
 f5     | bigint  |           |          |                                  | plain   |              | 

\d itest8_f2_seq
                   Sequence "public.itest8_f2_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Sequence for identity column: public.itest8.f2

\d itest8_f3_seq
                   Sequence "public.itest8_f3_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |        10 | no      |     1
Sequence for identity column: public.itest8.f3

\d itest8_f4_seq
                       Sequence "public.itest8_f4_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Sequence for identity column: public.itest8.f4

\d itest8_f5_seq
DROP TABLE itest8;
-- typed tables (currently not supported)
CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint);
CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error
ERROR:  identity columns are not supported on typed tables
DROP TYPE itest_type CASCADE;
-- table partitions (currently not supported)
CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1);
CREATE TABLE itest_child PARTITION OF itest_parent (
    f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY
) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error
ERROR:  identity columns are not supported on partitions
DROP TABLE itest_parent;
-- test that sequence of half-dropped serial column is properly ignored
CREATE TABLE itest14 (id serial);
ALTER TABLE itest14 ALTER id DROP DEFAULT;
ALTER TABLE itest14 ALTER id ADD GENERATED BY DEFAULT AS IDENTITY;
INSERT INTO itest14 (id) VALUES (DEFAULT);
-- Identity columns must be NOT NULL (cf bug #16913)
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL); -- fail
ERROR:  conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
LINE 1: ...ABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NULL);
                                                                 ^
CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS IDENTITY); -- fail
ERROR:  conflicting NULL/NOT NULL declarations for column "id" of table "itest15"
LINE 1: CREATE TABLE itest15 (id integer NULL GENERATED ALWAYS AS ID...
                                              ^
CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL);
DROP TABLE itest15;
CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY);
DROP TABLE itest15;
-- MERGE tests
CREATE TABLE itest15 (a int GENERATED ALWAYS AS IDENTITY, b text);
CREATE TABLE itest16 (a int GENERATED BY DEFAULT AS IDENTITY, b text);
MERGE INTO itest15 t
USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
ON t.a = s.s_a
WHEN NOT MATCHED THEN
	INSERT (a, b) VALUES (s.s_a, s.s_b);
ERROR:  cannot insert a non-DEFAULT value into column "a"
DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
-- Used to fail, but now it works and ignores the user supplied value
MERGE INTO itest15 t
USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
ON t.a = s.s_a
WHEN NOT MATCHED THEN
	INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
MERGE INTO itest15 t
USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
ON t.a = s.s_a
WHEN NOT MATCHED THEN
	INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
MERGE INTO itest16 t
USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
ON t.a = s.s_a
WHEN NOT MATCHED THEN
	INSERT (a, b) VALUES (s.s_a, s.s_b);
MERGE INTO itest16 t
USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
ON t.a = s.s_a
WHEN NOT MATCHED THEN
	INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
MERGE INTO itest16 t
USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
ON t.a = s.s_a
WHEN NOT MATCHED THEN
	INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
SELECT * FROM itest15;
 a  |         b         
----+-------------------
  1 | inserted by merge
 30 | inserted by merge
(2 rows)

SELECT * FROM itest16;
 a  |         b         
----+-------------------
 10 | inserted by merge
  1 | inserted by merge
 30 | inserted by merge
(3 rows)

DROP TABLE itest15;
DROP TABLE itest16;