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      |
56(1 row)
57
58SELECT '' AS six, c.*
59   FROM VARCHAR_TBL c
60   WHERE c.f1 <= 'a';
61 six | f1
62-----+----
63     | a
64     |
65(2 rows)
66
67SELECT '' AS one, c.*
68   FROM VARCHAR_TBL c
69   WHERE c.f1 > 'a';
70 one | f1
71-----+----
72     | A
73     | 1
74     | 2
75     | 3
76     | c
77(5 rows)
78
79SELECT '' AS two, c.*
80   FROM VARCHAR_TBL c
81   WHERE c.f1 >= 'a';
82 two | f1
83-----+----
84     | a
85     | A
86     | 1
87     | 2
88     | 3
89     | c
90(6 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