Thread

Running queries on inherited tables

Michael Richards <miker@scifair.acadiau.ca> — 1999-09-12T16:41:52Z

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