Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cast on character columns in views

Cast on character columns in views

From
"Luiz K. Matsumura"
Date:
Hello,

I have a scenario like this:

CREATE TABLE table1
( id serial NOT NULL, col1 character varying(30), CONSTRAINT pk_table1 PRIMARY KEY (id)
);

CREATE TABLE table2
( fk_table1 integer, type1 character(3), id serial NOT NULL, CONSTRAINT pk_table2 PRIMARY KEY (id)
);

CREATE TABLE table3
( id serial NOT NULL, type2 integer, fk_table1 integer, CONSTRAINT pk_table3 PRIMARY KEY (id)
);

CREATE VIEW view1 AS
SELECT table1.id,      table1.col1,      table2.type1,      NULL AS type2
FROM   table1
JOIN   table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,      table1.col1,      NULL AS type1,      table3.type2
FROM   table1
JOIN   table3 ON table3.fk_table1 = table1.id;

It's all ok except by the fact that when I retrieve data from view1 the 
column type1 become bpchar instead of character(3)
There are some manner to make the view return type1 as character(3) when 
I do a command like "select * from view1" ?
It's strangeous because type2 return as integer.

I try to do a

CREATE VIEW view1 AS
SELECT table1.id,      table1.col1,      table2.type1,      NULL AS type2
FROM   table1
JOIN   table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,      table1.col1,      NULL::character(3) AS type1,      table3.type2
FROM   table1
JOIN   table3 ON table3.fk_table1 = table1.id;

But i got an error:

ERROR: cannot change data type of view column "type1"

I try this too but got the same error:

CREATE VIEW view1 AS
SELECT table1.id,      table1.col1,      table2.type1,      NULL AS type2
FROM   table1
JOIN   table2 ON table2.fk_table1 = table1.id
UNION ALL
SELECT table1.id,      table1.col1,      ''::character(3) AS type1,      table3.type2
FROM   table1
JOIN   table3 ON table3.fk_table1 = table1.id;

I'm doing a cast on a query on view1 like select type1::character(3) 
from view1 , but if exists a manner of do this without this cast it will 
much appreciated.

TIA

-- 
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.

Re: Cast on character columns in views

From
Richard Broersma Jr
Date:
--- "Luiz K. Matsumura" <luiz@planit.com.br> wrote:
> CREATE VIEW view1( id, col1, type1, type2) AS
> SELECT table1.id,
>        table1.col1,
>        CAST( table2.type1 AS CHARACTER( 3 )),
>        NULL
> FROM   table1
> JOIN   table2 ON table2.fk_table1 = table1.id
> UNION ALL
> SELECT table1.id,
>        table1.col1,
>        CAST( NULL AS CHARACTER( 3 )),
>        table3.type2
> FROM   table1
> JOIN   table3 ON table3.fk_table1 = table1.id;

Would the above changes work?

Regards,
Richard Broersma Jr.

Re: Cast on character columns in views

From
"Luiz K. Matsumura"
Date:
Richard Broersma Jr wrote:
> --- "Luiz K. Matsumura" <luiz@planit.com.br> wrote:
>   
>> CREATE VIEW view1( id, col1, type1, type2) AS
>> SELECT table1.id,
>>        table1.col1,
>>        CAST( table2.type1 AS CHARACTER( 3 )),
>>        NULL
>> FROM   table1
>> JOIN   table2 ON table2.fk_table1 = table1.id
>> UNION ALL
>> SELECT table1.id,
>>        table1.col1,
>>        CAST( NULL AS CHARACTER( 3 )),
>>        table3.type2
>> FROM   table1
>> JOIN   table3 ON table3.fk_table1 = table1.id;
>>     
>
> Would the above changes work?
>
> Regards,
> Richard Broersma Jr.
>
>
>   
Hi Richard,
Your changes works ! But now I know what mistake I did:

The error is occurring because I'm doing a CREATE OR REPLACE VIEW command.
The command with null:character(3) works too.
The error is because I create a view then try to change the definition 
with the CREATE OR REPLACE VIEW command
When I drop the view first , and then create again the view (in a 
separated transaction), now the command works! (this is a bug?)
I'm using a postgres 8.2.4 on Linux.

Thanks a lot!

-- 
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.

Re: Cast on character columns in views

From
"Luiz K. Matsumura"
Date:

Luiz K. Matsumura wrote:
>
> Richard Broersma Jr wrote:
>> --- "Luiz K. Matsumura" <luiz@planit.com.br> wrote:
>>  
>>> CREATE VIEW view1( id, col1, type1, type2) AS
>>> SELECT table1.id,
>>>        table1.col1,
>>>        CAST( table2.type1 AS CHARACTER( 3 )),
>>>        NULL
>>> FROM   table1
>>> JOIN   table2 ON table2.fk_table1 = table1.id
>>> UNION ALL
>>> SELECT table1.id,
>>>        table1.col1,
>>>        CAST( NULL AS CHARACTER( 3 )),
>>>        table3.type2
>>> FROM   table1
>>> JOIN   table3 ON table3.fk_table1 = table1.id;
>>>     
>>
>> Would the above changes work?
>>
>> Regards,
>> Richard Broersma Jr.
>>
>>
>>   
> Hi Richard,
> Your changes works ! But now I know what mistake I did:
>
> The error is occurring because I'm doing a CREATE OR REPLACE VIEW 
> command.
> The command with null:character(3) works too.
> The error is because I create a view then try to change the definition 
> with the CREATE OR REPLACE VIEW command
> When I drop the view first , and then create again the view (in a 
> separated transaction), now the command works! (this is a bug?)
> I'm using a postgres 8.2.4 on Linux.
>
> Thanks a lot!
>
Ugh, I'm totally crazy with this views
I'm using pgadmin with postgres, when I  clink on "view the data of 
selected object" button all works fine.
But when I open a query tool window and do:

SELECT * FROM view1;

Now, again type1 column returns as bpchar.
But if I do:

SELECT type1 FROM view1;

Now, type1 column returns as character(3)

If I do

select id, col1, type1,type2  from view1;

Again type1 returns as bpchar. But if I do

select id, col1,type2, type1 from view1;

Now type1 return as character(3).



-- 
Luiz K. Matsumura
Plan IT Tecnologia Informática Ltda.

Re: Cast on character columns in views

From
Richard Broersma Jr
Date:
--- "Luiz K. Matsumura" <luiz@planit.com.br> wrote:
> When I drop the view first , and then create again the view (in a 
> separated transaction), now the command works! (this is a bug?)

Well according to the manual, it is working as it is intended to work:
http://www.postgresql.org/docs/8.2/interactive/sql-createview.html

I've created large SQL scripts that employ:

DROP VIEW IF EXITS viewname;

CREATE VIEW ...

Regards,
Richard Broersma Jr.

Re: Cast on character columns in views

From
Richard Broersma Jr
Date:
--- "Luiz K. Matsumura" <luiz@planit.com.br> wrote:

> Ugh, I'm totally crazy with this views
> I'm using pgadmin with postgres, when I  clink on "view the data of 
> selected object" button all works fine.
> But when I open a query tool window and do:
> 
> SELECT * FROM view1;
> Now, again type1 column returns as bpchar.
> But if I do:

This might be a good question to ask on the PGAdmin mailing list or even try using the latest
version of PGAdmin.  Perhaps this is something that PGAdmin is doing rather than postgresql.

If you open psql -U your_db_user -d your_database_name.

and type:

\d schema_name.view_name

was does it show as the definition of the column.

Regards,
Richard Broersma Jr.

Re: Cast on character columns in views

From
Tom Lane
Date:
Richard Broersma Jr <rabroersma@yahoo.com> writes:
> --- "Luiz K. Matsumura" <luiz@planit.com.br> wrote:
>> But when I open a query tool window and do:
>> SELECT * FROM view1;
>> Now, again type1 column returns as bpchar.

> This might be a good question to ask on the PGAdmin mailing list or even try using the latest
> version of PGAdmin.  Perhaps this is something that PGAdmin is doing rather than postgresql.

The seeming ordering dependency certainly sounds like it might be a
client-side bug --- something failing to keep straight which typmod goes
with which column, maybe?

There are some backend-side issues with simply not being able to tell
the difference between null::bpchar and null::char(3) ... but for
any given view definition, it's pretty hard to see how the order of
selecting the columns would matter.  A client-side bug seems a bit
more likely.
        regards, tom lane