Running queries on inherited tables
Michael Richards <miker@scifair.acadiau.ca>
From: Michael Richards <miker@scifair.acadiau.ca>
To: pgsql-sql@postgreSQL.org
Cc: pgsql-bugs@postgreSQL.org
Date: 1999-09-12T16:41:52Z
Lists: pgsql-bugs
Hi.
I was fooling with inheritance today. From the page at:
http://www.postgresql.org/docs/user/inherit.htm
It says:
Here the * after cities indicates that the query should be run over cities
and all classes below cities in the inheritance hierarchy. Many of the
commands that we have
already discussed -- SELECT, UPDATE and DELETE -- support this * notation,
as do others, like ALTER TABLE.
So here's what I tried using the example data...
miker=> CREATE TABLE cities (
miker-> name text,
miker-> population float,
miker-> altitude int -- (in ft)
miker-> );
CREATE
miker=>
miker=> CREATE TABLE capitals (
miker-> state char(2)
miker-> ) INHERITS (cities);
CREATE
miker=> insert into cities (name,altitude) VALUES ('Wolfville',69);
INSERT 160729 1
miker=> insert into capitals (name,altitude,state) VALUES
('Halifax',455,'NS');
INSERT 160730 1
miker=> select * from cities*;
name |population|altitude
---------+----------+--------
Wolfville| | 69
Halifax | | 455
(2 rows)
miker=> update cities* set population=222;
ERROR: parser: parse error at or near "*"
I've tried a number of variations on the cities* thing but can only make
it for for select. Is this a bug?
More playing followed... If I alter table on cities and add a column, is
it not expected that the additional col should appear in the tables which
inherit from cities?
miker=> alter table cities add column niceplace bool;
ADD
miker=> select * from cities;
name |population|altitude|niceplace
---------+----------+--------+---------
Wolfville| | 69|
(1 row)
miker=> select * from cities*;
name |population|altitude|niceplace
---------+----------+--------+---------
Wolfville| | 69|
Halifax | | 455|t
(2 rows)
miker=> select niceplace from capitals;
ERROR: attribute 'niceplace' not found
miker=> \d capitals;
Table = capitals
+-----------------------------+----------------------------------+-------+
| Field | Type | Length|
+-----------------------------+----------------------------------+-------+
| name | text | var |
| population | float8 | 8 |
| altitude | int4 | 4 |
| state | char() | 2 |
+-----------------------------+----------------------------------+-------+
Something is positively b0rked here.... Halifax is showing up as having
niceplace=true, yet according to the next select, it doesn't have a column
of that name...
I'm running 6.5.1. If this is not an error on my part, any people can't
reproduce it, I'll submit a bug report...
-Michael