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
|
# name: test/sql/catalog/dependencies/test_default_value_dependency.test
# description: Default values and dependencies
# group: [dependencies]
require skip_reload
# dependency on a sequence in a default value
statement ok con1
CREATE SEQUENCE seq
statement ok con1
CREATE TABLE integers(i INTEGER DEFAULT nextval('seq'), j INTEGER)
statement ok con1
INSERT INTO integers (j) VALUES (1), (1), (1), (1), (1)
query R con2
SELECT SUM(i) FROM integers
----
15.000000
# we can't drop the sequence: the table depends on it
statement error con1
DROP SEQUENCE seq
----
# cascade drop works
statement ok con1
DROP SEQUENCE seq CASCADE
# but it also drops the table
statement error con1
SELECT * FROM integers
----
# dependency on multiple sequences in default value
statement ok con1
CREATE SEQUENCE seq
statement ok con1
CREATE SEQUENCE seq1
statement ok con1
CREATE SEQUENCE seq2
statement error con1
CREATE TABLE integers(i INTEGER DEFAULT nextval('seq' || CAST(nextval('seq') AS VARCHAR)), j INTEGER)
----
non-constant sequences are no longer supported
statement ok con1
CREATE TABLE integers(i INTEGER DEFAULT nextval('seq1') + nextval('seq2'), j INTEGER)
# seq1 exists, so the result of the first default value is 1
statement ok con1
INSERT INTO integers (j) VALUES (1)
# we canot drop seq1 and seq2: the dependency is fixed
statement error con1
DROP SEQUENCE seq1
----
Cannot drop entry
statement error con1
DROP SEQUENCE seq2
----
Cannot drop entry
# need to do a cascading drop
statement ok con1
DROP SEQUENCE seq1 CASCADE
# now the table is gone
statement error con1
SELECT * FROM integers
----
does not exist
|
