Обсуждение: Cast on character columns in views
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.
--- "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.
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.
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.
--- "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.
--- "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.
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