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
|
--
-- insert with DEFAULT in the target_list
--
create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text default 'testing');
insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT);
ERROR: null value in column "col2" violates not-null constraint
DETAIL: Failing row contains (null, null, testing).
insert into inserttest (col2, col3) values (3, DEFAULT);
insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
insert into inserttest values (DEFAULT, 5, 'test');
insert into inserttest values (DEFAULT, 7);
select * from inserttest;
col1 | col2 | col3
------+------+---------
| 3 | testing
| 5 | testing
| 5 | test
| 7 | testing
(4 rows)
--
-- insert with similar expression / target_list values (all fail)
--
insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT);
ERROR: INSERT has more target columns than expressions
LINE 1: insert into inserttest (col1, col2, col3) values (DEFAULT, D...
^
insert into inserttest (col1, col2, col3) values (1, 2);
ERROR: INSERT has more target columns than expressions
LINE 1: insert into inserttest (col1, col2, col3) values (1, 2);
^
insert into inserttest (col1) values (1, 2);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into inserttest (col1) values (1, 2);
^
insert into inserttest (col1) values (DEFAULT, DEFAULT);
ERROR: INSERT has more expressions than target columns
LINE 1: insert into inserttest (col1) values (DEFAULT, DEFAULT);
^
select * from inserttest;
col1 | col2 | col3
------+------+---------
| 3 | testing
| 5 | testing
| 5 | test
| 7 | testing
(4 rows)
--
-- VALUES test
--
insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT),
((select 2), (select i from (values(3)) as foo (i)), 'values are fun!');
select * from inserttest;
col1 | col2 | col3
------+------+-----------------
| 3 | testing
| 5 | testing
| 5 | test
| 7 | testing
10 | 20 | 40
-1 | 2 | testing
2 | 3 | values are fun!
(7 rows)
--
-- TOASTed value test
--
insert into inserttest values(30, 50, repeat('x', 10000));
select col1, col2, char_length(col3) from inserttest;
col1 | col2 | char_length
------+------+-------------
| 3 | 7
| 5 | 7
| 5 | 4
| 7 | 7
10 | 20 | 2
-1 | 2 | 7
2 | 3 | 15
30 | 50 | 10000
(8 rows)
drop table inserttest;
|
