Skip site navigation (1) Skip section navigation (2)

BUG #3598: Strange behaviour of character columns in select with views

From: "Luiz K(dot) Matsumura" <luiz(at)planit(dot)com(dot)br>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3598: Strange behaviour of character columns in select with views
Date: 2007-09-04 08:03:29
Message-ID: 200709040803.l8483TIn042430@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3598
Logged by:          Luiz K. Matsumura
Email address:      luiz(at)planit(dot)com(dot)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.

Responses

pgsql-bugs by date

Next:From: Luiz K. MatsumuraDate: 2007-09-04 08:07:32
Subject: Re: BUG #3597: CREATE OR REPLACE VIEW
Previous:From: Heikki LinnakangasDate: 2007-09-04 07:49:43
Subject: Re: BUG #3597: CREATE OR REPLACE VIEW

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group