Running queries on inherited tables

From: Michael Richards <miker(at)scifair(dot)acadiau(dot)ca>
To: pgsql-sql(at)postgreSQL(dot)org
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Running queries on inherited tables
Date: 1999-09-12 16:41:52
Message-ID: Pine.BSF.4.10.9909121328100.61970-100000@scifair.acadiau.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-sql

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 1999-09-12 17:07:37 Re: [BUGS] Running queries on inherited tables
Previous Message .QQVS 1999-09-11 23:55:27 WWW.CDBUTIKEN.COM - Nyhetsbrev vecka 37

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 1999-09-12 17:07:37 Re: [BUGS] Running queries on inherited tables
Previous Message elca7107 1999-09-12 04:42:20 20