1--
2-- VARCHAR
3--
4CREATE TABLE VARCHAR_TBL(f1 varchar(1));
5INSERT INTO VARCHAR_TBL (f1) VALUES ('a');
6INSERT INTO VARCHAR_TBL (f1) VALUES ('A');
7-- any of the following three input formats are acceptable
8INSERT INTO VARCHAR_TBL (f1) VALUES ('1');
9INSERT INTO VARCHAR_TBL (f1) VALUES (2);
10INSERT INTO VARCHAR_TBL (f1) VALUES ('3');
11-- zero-length char
12INSERT INTO VARCHAR_TBL (f1) VALUES ('');
13-- try varchar's of greater than 1 length
14INSERT INTO VARCHAR_TBL (f1) VALUES ('cd');
15ERROR:  value too long for type character varying(1)
16INSERT INTO VARCHAR_TBL (f1) VALUES ('c     ');
17SELECT '' AS seven, * FROM VARCHAR_TBL;
18 seven | f1
19-------+----
20       | a
21       | A
22       | 1
23       | 2
24       | 3
25       |
26       | c
27(7 rows)
28
29SELECT '' AS six, c.*
30   FROM VARCHAR_TBL c
31   WHERE c.f1 <> 'a';
32 six | f1
33-----+----
34     | A
35     | 1
36     | 2
37     | 3
38     |
39     | c
40(6 rows)
41
42SELECT '' AS one, c.*
43   FROM VARCHAR_TBL c
44   WHERE c.f1 = 'a';
45 one | f1
46-----+----
47     | a
48(1 row)
49
50SELECT '' AS five, c.*
51   FROM VARCHAR_TBL c
52   WHERE c.f1 < 'a';
53 five | f1
54------+----
55      | A
56      | 1
57      | 2
58      | 3
59      |
60(5 rows)
61
62SELECT '' AS six, c.*
63   FROM VARCHAR_TBL c
64   WHERE c.f1 <= 'a';
65 six | f1
66-----+----
67     | a
68     | A
69     | 1
70     | 2
71     | 3
72     |
73(6 rows)
74
75SELECT '' AS one, c.*
76   FROM VARCHAR_TBL c
77   WHERE c.f1 > 'a';
78 one | f1
79-----+----
80     | c
81(1 row)
82
83SELECT '' AS two, c.*
84   FROM VARCHAR_TBL c
85   WHERE c.f1 >= 'a';
86 two | f1
87-----+----
88     | a
89     | c
90(2 rows)
91
92DROP TABLE VARCHAR_TBL;
93--
94-- Now test longer arrays of char
95--
96CREATE TABLE VARCHAR_TBL(f1 varchar(4));
97INSERT INTO VARCHAR_TBL (f1) VALUES ('a');
98INSERT INTO VARCHAR_TBL (f1) VALUES ('ab');
99INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd');
100INSERT INTO VARCHAR_TBL (f1) VALUES ('abcde');
101ERROR:  value too long for type character varying(4)
102INSERT INTO VARCHAR_TBL (f1) VALUES ('abcd    ');
103SELECT '' AS four, * FROM VARCHAR_TBL;
104 four |  f1
105------+------
106      | a
107      | ab
108      | abcd
109      | abcd
110(4 rows)
111
112