Postgres Pro
Downloads
Home   >   mailing lists

Thread: lowercase on columnname using view

lowercase on columnname using view

From
Chrishelring
Date:
Hi all,

had some help the other day, but now I´m kinda stuck again. :/

I have a table ("virksomhedsdata") with the following columns:

"MI_STYLE" character varying(254),
 "MI_PRINX" integer NOT NULL DEFAULT
nextval('rk_ois."virksomhedsdata_MI_PRINX_seq"'::regclass),
 "SP_GEOMETRY" geometry,

I would like to make a view so that the columnnames are presented in
lowercase. I thought that the following would work:

CREATE OR REPLACE VIEW rk_ois.virksomhedsdata AS
 SELECT  virksomhedsdata.MI_STYLE AS mi_style, virksomhedsdata.MI_PRINX as
mi_prinx, virksomhedsdata.SP_GEOMETRY AS sp_geometry
   FROM rk_ois.virksomhedsdata;

But it fails saying that column virksomhedsdata.mi_style does not exist.

What am I doing wrong here?

thanks!

Christian

--
View this message in context: http://postgresql.1045698.n5.nabble.com/lowercase-on-columnname-using-view-tp5693220.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: lowercase on columnname using view

From
Szymon Guz
Date:
On 8 May 2012 13:00, Chrishelring <christianhelring@gmail.com> wrote:
Hi all,

had some help the other day, but now I´m kinda stuck again. :/

I have a table ("virksomhedsdata") with the following columns:

"MI_STYLE" character varying(254),
 "MI_PRINX" integer NOT NULL DEFAULT
nextval('rk_ois."virksomhedsdata_MI_PRINX_seq"'::regclass),
 "SP_GEOMETRY" geometry,

I would like to make a view so that the columnnames are presented in
lowercase. I thought that the following would work:

CREATE OR REPLACE VIEW rk_ois.virksomhedsdata AS
 SELECT  virksomhedsdata.MI_STYLE AS mi_style, virksomhedsdata.MI_PRINX as
mi_prinx, virksomhedsdata.SP_GEOMETRY AS sp_geometry
  FROM rk_ois.virksomhedsdata;

But it fails saying that column virksomhedsdata.mi_style does not exist.

What am I doing wrong here?

thanks!

Christian


If you created the columns like "MI_PRINX", then you need to call them using "MI_PRINX", not MI_PRINX, because it will be change to lowercase in the query.

So the proper query should look like this:

CREATE OR REPLACE VIEW rk_ois.virksomhedsdata AS
 SELECT  
    virksomhedsdata."MI_STYLE" AS mi_style, 
    virksomhedsdata."MI_PRINX" as mi_prinx, 
    virksomhedsdata."SP_GEOMETRY" AS sp_geometry
 FROM rk_ois.virksomhedsdata;


- szymon

Re: lowercase on columnname using view

From
Guillaume Lelarge
Date:
On Tue, 2012-05-08 at 04:00 -0700, Chrishelring wrote:
> Hi all,
>
> had some help the other day, but now I´m kinda stuck again. :/
>
> I have a table ("virksomhedsdata") with the following columns:
>
> "MI_STYLE" character varying(254),
>  "MI_PRINX" integer NOT NULL DEFAULT
> nextval('rk_ois."virksomhedsdata_MI_PRINX_seq"'::regclass),
>  "SP_GEOMETRY" geometry,
>
> I would like to make a view so that the columnnames are presented in
> lowercase. I thought that the following would work:
>
> CREATE OR REPLACE VIEW rk_ois.virksomhedsdata AS
>  SELECT  virksomhedsdata.MI_STYLE AS mi_style, virksomhedsdata.MI_PRINX as
> mi_prinx, virksomhedsdata.SP_GEOMETRY AS sp_geometry
>    FROM rk_ois.virksomhedsdata;
>
> But it fails saying that column virksomhedsdata.mi_style does not exist.
>
> What am I doing wrong here?
>

You should double-quote the columns of the table. Something like this:

CREATE OR REPLACE VIEW rk_ois.virksomhedsdata AS
  SELECT  virksomhedsdata."MI_STYLE" AS mi_style, ...


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com