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
|
# name: test/sql/alter/test_alter_if_exists.test
# description: Test ALTER SEQUENCE IF EXISTS
# group: [alter]
# sequence does not exist
statement ok
ALTER SEQUENCE IF EXISTS seq OWNED BY x;
# Add column to non-existing table
statement ok
ALTER TABLE IF EXISTS t0 ADD COLUMN c0 INT;
# Make sure the column was not actually created
statement error
INSERT INTO t0 VALUES (42);
----
# Add column if not exists to non-existing table
statement ok
ALTER TABLE IF EXISTS t0 ADD COLUMN IF NOT EXISTS c0 int;
statement error
INSERT INTO t0 VALUES (42);
----
statement ok
CREATE TABLE t0 (c0 INT);
# Alter existing table add column if not exist
statement ok
ALTER TABLE t0 ADD COLUMN IF NOT EXISTS c0 int;
# Ensure the column was added
statement ok
INSERT INTO t0 VALUES (42);
# Alter existing table with existing column
statement error
ALTER TABLE t0 ADD COLUMN c0 int;
----
# Alter existing table with new column
statement ok
ALTER TABLE t0 ADD COLUMN c1 int;
# Ensure the column was added
statement ok
INSERT INTO t0 VALUES (42, 43);
# Alter existing table with new column if not exists
statement ok
ALTER TABLE t0 ADD COLUMN IF NOT EXISTS c2 int;
# Ensure the column was added
statement ok
INSERT INTO t0 VALUES (42, 43, 44);
# Drop existing column if not exists
statement ok
ALTER TABLE IF EXISTS t1 DROP COLUMN IF EXISTS c3;
statement ok
ALTER TABLE IF EXISTS t0 DROP COLUMN if EXISTS c3;
statement error
ALTER TABLE t1 DROP COLUMN IF EXISTS c3;
----
statement error
ALTER TABLE t1 DROP COLUMN c3;
----
statement error
ALTER TABLE t0 DROP COLUMN c3;
----
statement ok
ALTER TABLE t0 DROP COLUMN IF EXISTS c3
statement error
ALTER TABLE IF EXISTS t0 DROP COLUMN c3
----
# Change type of column if exists
statement ok
ALTER TABLE IF EXISTS t1 ALTER COLUMN c0 TYPE varchar;
# Not supported for ALTER COLUMN
statement error
ALTER TABLE IF EXISTS t1 ALTER COLUMN IF EXISTS c0 TYPE varchar;
----
Parser Error: syntax error at or near "EXISTS"
# 'rowid' pseudo-column
statement ok
CREATE TABLE unit (price INTEGER, amount_sold INTEGER);
statement ok
ALTER TABLE unit DROP COLUMN IF EXISTS rowid;
query I
SELECT rowid FROM unit;
----
statement ok
INSERT INTO unit VALUES (10, 5);
query I
SELECT rowid FROM unit;
----
0
statement ok
ALTER TABLE unit ADD COLUMN rowid INTEGER;
# Ensure the column was added
statement ok
INSERT INTO unit VALUES (20, 10, 1337);
statement ok
ALTER TABLE unit DROP COLUMN IF EXISTS rowid;
# Ensure the column was removed
statement ok
INSERT INTO unit VALUES (30, 20);
statement ok
ALTER TABLE unit ADD COLUMN IF NOT EXISTS rowid INTEGER;
# Ensure the column was added
statement ok
INSERT INTO unit VALUES (40, 30, 1337);
statement ok
ALTER TABLE unit ADD COLUMN IF NOT EXISTS rowid INTEGER;
statement ok
ALTER TABLE unit DROP COLUMN rowid;
# We should get back the values of the original rowid pseudo column
query I
SELECT rowid FROM unit;
----
0
1
2
3
|
