Thread: Redefining a column within a view

We have a table which contains a 3 byte column with datatype CHAR(3)=0D=0Aw=
hich we want to redefine within the view as a CHAR(1) column and a=0D=0ACHA=
R(2) column. When I code the SUBSTR function the resulting column=0D=0Adata=
type is TEXT. I would like to see the CHAR datatype, is there=0D=0Aanything=
 I can do to set the proper datatype. The sample view is below:=0D=0A=0D=0A=
=20=0D=0A=0D=0ACREATE OR REPLACE VIEW schema.jeff=5Fview AS=20=0D=0A=0D=0A =
SELECT col1 ,=0D=0A=0D=0A                col2,=0D=0A=0D=0A                S=
ubstr(col3,1,1) as col3=5Fpart1,=0D=0A=0D=0A                Substr(col3,2) =
    as col3=5Fpart2,=0D=0A=0D=0A                col4=0D=0A=0D=0A  FROM sche=
ma.jeff=5Ftable;=0D=0A=0D=0A=20=0D=0A=0D=0AAny suggestions would be appreci=
ated.=0D=0A=0D=0A=20=0D=0A=0D=0AJeff=0D=0A=0D=0A=5F=5F=5F=5F=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5F=0D=0AJeffrey Schade=20=0D=0A=0D=0ASystems Consultant,=
 Technology Engineering=0D=0A=0D=0A=0D=0AISO=0D=0A545 Washington Boulevard=0D=
=0AJersey City, NJ 07310=0D=0AVoice: (201) 469-3738=0D=0A=0D=0A  FAX: (201)=
 748-1500 =20=0D=0Ajschade@iso.com <mailto:jschade@iso.com>=20=0D=0A=0D=0A =0D=
=0A=0D=0AThis email is intended for the recipient only.  If you are not the=
 intended recipient please disregard, and do not use the information for an=
y purpose.=0D=0A

Jeffrey Schade wrote:
> We have a table which contains a 3 byte column with datatype CHAR(3) whic=
h we want to redefine within
> the view as a CHAR(1) column and a CHAR(2) column. When I code the SUBSTR=
 function the resulting
> column datatype is TEXT. I would like to see the CHAR datatype, is there =
anything I can do to set the
> proper datatype. The sample view is below:
>=20
> CREATE OR REPLACE VIEW schema.jeff_view AS
>  SELECT col1 ,
>                 col2,
>                 Substr(col3,1,1) as col3_part1,
>                 Substr(col3,2)     as col3_part2,
>                 col4
>   FROM schema.jeff_table;

Try:
[...]
                 Substr(col3,1,1)::char(1) as col3_part1,
                 Substr(col3,2)::char(2)   as col3_part2,
[...]

Yours,
Laurenz Albe

Good Morning Laurenz,=0D=0A=0D=0AThat worked perfectly.=0D=0A=0D=0AThank Yo=
u=0D=0A=0D=0AJeff=0D=0A=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=
=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=0D=0A=
Jeffrey Schade=20=0D=0ASystems Consultant, Technology Engineering=0D=0A=0D=0A=
ISO=0D=0A545 Washington Boulevard=0D=0AJersey City, NJ 07310=0D=0AVoice: (2=
01) 469-3738=0D=0A=A0 FAX: (201) 748-1500=A0=A0=0D=0Ajschade@iso.com=0D=0A=0D=
=0A-----Original Message-----=0D=0AFrom: Albe Laurenz [mailto:laurenz.albe@=
wien.gv.at]=20=0D=0ASent: Friday, February 22, 2013 8:31 AM=0D=0ATo: Schade=
, Jeffrey; pgsql-general@postgresql.org=0D=0ASubject: RE: Redefining a colu=
mn within a view=0D=0A=0D=0AJeffrey Schade wrote:=0D=0A> We have a table wh=
ich contains a 3 byte column with datatype CHAR(3)=20=0D=0A> which we want =
to redefine within the view as a CHAR(1) column and a=20=0D=0A> CHAR(2) col=
umn. When I code the SUBSTR function the resulting column=20=0D=0A> datatyp=
e is TEXT. I would like to see the CHAR datatype, is there anything I can d=
o to set the proper datatype. The sample view is below:=0D=0A>=20=0D=0A> CR=
EATE OR REPLACE VIEW schema.jeff=5Fview AS  SELECT col1 ,=0D=0A>           =
      col2,=0D=0A>                 Substr(col3,1,1) as col3=5Fpart1,=0D=0A>=
                 Substr(col3,2)     as col3=5Fpart2,=0D=0A>                =
 col4=0D=0A>   FROM schema.jeff=5Ftable;=0D=0A=0D=0ATry:=0D=0A[...]=0D=0A  =
               Substr(col3,1,1)::char(1) as col3=5Fpart1,=0D=0A            =
     Substr(col3,2)::char(2)   as col3=5Fpart2,=0D=0A[...]=0D=0A=0D=0AYours=
,=0D=0ALaurenz Albe=0D=0A=0D=0AThis email is intended for the recipient onl=
y.  If you are not the intended recipient please disregard, and do not use =
the information for any purpose.=0D=0A