The following bug has been logged online:
Bug reference: 3598
Logged by: Luiz K. Matsumura
Email address: luiz@planit.com.br
PostgreSQL version: 8.2.4
Operating system: Fedora Core 3
Description: Strange behaviour of character columns in select with
views
Details:
Scenario:
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::character(3) AS type1,
table3.type2
FROM table1
JOIN table3 ON table3.fk_table1 = table1.id;
When we do:
SELECT * from view1;
OR
SELECT id,col1,type1,type2 FROM view1;
column type1 return as bpchar
But if we do:
SELECT type1 FROM view1;
or
SELECT id,col1,type2,type1 FROM view1;
Now, type1 return as character(3) as expected.