Postgres Pro
Downloads
Home   >   mailing lists

Thread: Redefining a column within a view

Redefining a column within a view

From
"Schade, Jeffrey"
Date:
<div class="WordSection1"><p class="MsoNormal">We have a table which contains a 3 byte column with datatype CHAR(3)
whichwe want to redefine within the view as a CHAR(1) column and a CHAR(2) column. When I code the SUBSTR function the
resultingcolumn 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:<p class="MsoNormal"> <p class="MsoNormal">CREATE OR REPLACE VIEW schema.jeff_view AS
<pclass="MsoNormal"> SELECT col1 ,<p class="MsoNormal">                col2,<p class="MsoNormal">               
Substr(col3,1,1)as col3_part1,<p class="MsoNormal">                Substr(col3,2)     as col3_part2,<p
class="MsoNormal">               col4<p class="MsoNormal">  FROM schema.jeff_table;<p class="MsoNormal"> <p
class="MsoNormal">Anysuggestions would be appreciated.<p class="MsoNormal"> <p class="MsoNormal"><i><span
style="font-size:13.5pt;font-family:"Arial","sans-serif";color:blue">Jeff</span></i><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif";color:#1F497D"></span><p class="MsoNormal"><i><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:blue">_________________________________________<br
/></span></i><b><i><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif";color:black">Jeffrey Schade
</span></i></b><spanstyle="font-size:12.0pt;font-family:"Times New Roman","serif";color:#1F497D"></span><p
class="MsoNormal"><b><i><spanstyle="font-size:10.0pt;font-family:"Arial","sans-serif";color:black">Systems Consultant,
TechnologyEngineering</span></i></b><span style="font-size:12.0pt;font-family:"Times New
Roman","serif";color:#1F497D"></span><pclass="MsoNormal"><b><span style="font-size:12.0pt;font-family:"Times New
Roman","serif";color:blue"><br/></span></b><b><i><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:maroon">ISO</span></i></b><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif";color:blue"><br /></span><i><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:maroon">545Washington Boulevard<br />Jersey City, NJ
07310<br/>Voice: (201) 469-3738</span></i><span style="font-size:12.0pt;font-family:"Times New
Roman","serif";color:blue"></span><pclass="MsoNormal"><i><span
style="font-size:10.0pt;font-family:"Arial","sans-serif";color:maroon"> FAX: (201) 748-1500  <br /></span></i><span
style="font-size:12.0pt;font-family:"TimesNew Roman","serif";color:blue"><a href="mailto:jschade@iso.com"><i><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">jschade@iso.com</span></i></a></span><span
style="color:#1F497D"></span><pclass="MsoNormal"> </div><br /><br /><table bgcolor="white"
style="color:black"><tr><td>Thisemail is intended for the recipient only. If you are not the intended recipient please
disregard,and do not use the information for any purpose.</td></tr></table>

Re: Redefining a column within a view

From
"Schade, Jeffrey"
Date:

Thanks to Laurenz for providing the answer to my problem:

 

Try:

[...]

                 Substr(col3,1,1)::char(1) as col3_part1,

                 Substr(col3,2)::char(2)   as col3_part2,

[...]

 

Yours,

Laurenz Albe

 

 

Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

  FAX: (201) 748-1500  
jschade@iso.com

 

From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Schade, Jeffrey
Sent: Friday, February 22, 2013 8:09 AM
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Redefining a column within a view

 

We have a table which contains a 3 byte column with datatype CHAR(3) which 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:

 

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;

 

Any suggestions would be appreciated.

 

Jeff

_________________________________________
Jeffrey Schade

Systems Consultant, Technology Engineering


ISO
545 Washington Boulevard
Jersey City, NJ 07310
Voice: (201) 469-3738

  FAX: (201) 748-1500  
jschade@iso.com

 

 

This email is intended for the recipient only. If you are not the intended recipient please disregard, and do not use the information for any purpose.

 



This email is intended for the recipient only. If you are not the intended recipient please disregard, and do not use the information for any purpose.