"Adam Tomjack" <[EMAIL PROTECTED]> writes: > -- This will succeed: > SELECT * FROM v_a_b_c; > > -- But, this will fail with > -- ERROR: common column name "aid" appears more than once in left table > -- SQL state: 42702 > SELECT * > FROM b > JOIN c USING (bid) > JOIN a USING (aid) > > -- It is now possible to make a backup with pg_dump that cannot be fully > restored. > -- When restoring, this will fail: > CREATE OR REPLACE VIEW v_a_b_c AS > SELECT * > FROM b > JOIN c USING (bid) > JOIN a USING (aid) > ;
That does really suck. But I'm not sure what we can do about it. There's no SQL which is entirely equivalent to the resulting view. I think the closest you could get would be something like SELECT * FROM (SELECT bid,cid FROM b) AS b JOIN c USING (bid) JOIN a USING (aid) But it's not clear to me that we could generate that easily. AFAIK the information about which columns were in the table at the time the view was defined isn't readily available. And checking for conflicts might be hard since they could happen much higher up in the join tree. We can't just output the USING as an ON clause which would let pg_dump specify precisely which column to join against because ON doesn't merge the two columns. The resulting records would have two bid columns. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
