I want to extract portions of a TEXT column into a CHAR(8) column:
> create table one (descr text);
> create view two as select substr(descr, 2, 4) as d from one;
> \d two
View = two
Query = SELECT "substr"("one"."descr", '2'::"int4", '4'::"int4") AS
"d" FROM "one";
+-----------------------+------------------------------+-------+
| Field | Type | Length|
+-----------------------+------------------------------+-------+
| d | text | var |
+-----------------------+------------------------------+-------+
> create view three as select CAST(substr(descr, 2, 4) as char(8)) as
d from one;
> \d three
View = three
Query = SELECT "substr"("one"."descr", '2'::"int4", '4'::"int4") AS
"d" FROM "one";
+-----------------------+------------------------------+-------+
| Field | Type | Length|
+-----------------------+------------------------------+-------+
| d | text | var |
+-----------------------+------------------------------+-------+
SELECT INTO behaves the same way. It seems impossible to cast a TEXT into
a CHAR.
The char() function works neither:
> create view four as select char(substr(descr, 2, 4)) as d from one;
ERROR: parser: parse error at or near "substr"
Any ideas?
This is 6.5.2 (RH Linux 6.1)
Thanks in advance
peter