Thread: no self-joins in views?
Hello, is there any restriction re the use of self joins in views? I have a moderately complicated query where I scan the same table twice using aliases, and I would like to put that into a view to keep my python code clean. However, I get an error 'column xy duplicated'. Is this so by design? best, Christoph
Christoph Pingel wrote: > Hello, > > is there any restriction re the use of self joins in views? I have a > moderately complicated query where I scan the same table twice using > aliases, and I would like to put that into a view to keep my python code > clean. However, I get an error 'column xy duplicated'. Is this so by > design? Could you give an actual example? -- Richard Huxton Archonet Ltd
On Thu, Feb 10, 2005 at 05:13:51PM +0100, Christoph Pingel wrote: > Hello, > > is there any restriction re the use of self joins in views? I have a > moderately complicated query where I scan the same table twice using > aliases, and I would like to put that into a view to keep my python > code clean. However, I get an error 'column xy duplicated'. Is this > so by design? It's probably complaining that the *resultset* has two columns with the same name. Consider: CREATE VIEW test AS SELECT * FROM table, table WHERE table.a = table.b; Basically, the output of the view will have all the columnnames listed twice. You'll need to rename them to make it work... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Thu, Feb 10, 2005 at 05:13:51PM +0100, Christoph Pingel wrote:
>
> is there any restriction re the use of self joins in views? I have a
> moderately complicated query where I scan the same table twice using
> aliases, and I would like to put that into a view to keep my python
> code clean. However, I get an error 'column xy duplicated'. Is this
> so by design?
Use column aliases so the xy columns don't have the same name:
CREATE VIEW viewname AS
SELECT a.xy AS axy,
b.xy AS bxy
FROM foo AS a,
foo AS b
WHERE ...
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
>Could you give an actual example? Sure. The idea is that 'objects' (persons, books, places) from a table obj are linked with each other in a link table ool where objects from obj can appear in an 'subject' or a 'object' column. Since the relation can be any, this is a very flexible and lean design for an 'ontology-like' collection of facts. As I said, the CREATE VIEW returns an error (column obj_id duplicated), while the select statement by itself works (with an additional constraint on s.obj_id, otherwise the db would throw up hundreds of thousands of rows). CREATE VIEW relations_aspect_subject AS SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id, o.canonical_name FROM obj s, obj o, ool, rlt WHERE s.obj_id = ool.subject AND o.obj_id = ool.object AND rlt.rlt_id = ool.relation ORDER BY rlt_id Do I miss something obvious? thanks, Christoph
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You missed something obvious. Executing this query in psql you have no
problem, because there is no structure created from the result. A view
behaves like a table - try creating a table like
create table blah (
id int,
id int
);
and you'll hit the same error. You have to have different names for the
columns. BTW: that's not a postgres problem, ANY relational database will
give the same error here.
On Thursday 10 February 2005 09:20 am, Christoph Pingel wrote:
> >Could you give an actual example?
>
> Sure. The idea is that 'objects' (persons, books, places) from a
> table obj are linked with each other in a link table ool where
> objects from obj can appear in an 'subject' or a 'object' column.
> Since the relation can be any, this is a very flexible and lean
> design for an 'ontology-like' collection of facts.
>
> As I said, the CREATE VIEW returns an error (column obj_id
> duplicated), while the select statement by itself works (with an
> additional constraint on s.obj_id, otherwise the db would throw up
> hundreds of thousands of rows).
>
> CREATE VIEW relations_aspect_subject AS
> SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id,
> o.canonical_name
> FROM obj s, obj o, ool, rlt
> WHERE s.obj_id = ool.subject
> AND o.obj_id = ool.object
> AND rlt.rlt_id = ool.relation
> ORDER BY rlt_id
>
> Do I miss something obvious?
>
> thanks,
> Christoph
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
- --
UC
- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFCC5zdjqGXBvRToM4RAk6bAJ0feXiYMKN0uYHv9qM2S8tH3mAVOwCaAjWv
VwPo11ag0tGoOzeclxxFkxI=
=xvU0
-----END PGP SIGNATURE-----
On Thu, 2005-02-10 at 18:20 +0100, Christoph Pingel wrote: > >Could you give an actual example? > > Sure. The idea is that 'objects' (persons, books, places) from a > table obj are linked with each other in a link table ool where > objects from obj can appear in an 'subject' or a 'object' column. > Since the relation can be any, this is a very flexible and lean > design for an 'ontology-like' collection of facts. > > As I said, the CREATE VIEW returns an error (column obj_id > duplicated), while the select statement by itself works (with an > additional constraint on s.obj_id, otherwise the db would throw up > hundreds of thousands of rows). > > CREATE VIEW relations_aspect_subject AS > SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id, > o.canonical_name > FROM obj s, obj o, ool, rlt > WHERE s.obj_id = ool.subject > AND o.obj_id = ool.object > AND rlt.rlt_id = ool.relation > ORDER BY rlt_id > > Do I miss something obvious? > yep, because when you create a view you are creating a table with volatile contents so the DB needs to have an identifier for each column. Theconstraints are basically the same as column names in a table. In a select you doing just that selecting so the issue does not arise. > thanks, > Christoph > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend >
Christoph Pingel wrote: >> Could you give an actual example? > > > Sure. The idea is that 'objects' (persons, books, places) from a table > obj are linked with each other in a link table ool where objects from > obj can appear in an 'subject' or a 'object' column. Since the relation > can be any, this is a very flexible and lean design for an > 'ontology-like' collection of facts. > > As I said, the CREATE VIEW returns an error (column obj_id duplicated), > while the select statement by itself works (with an additional > constraint on s.obj_id, otherwise the db would throw up hundreds of > thousands of rows). > > CREATE VIEW relations_aspect_subject AS > SELECT s.obj_id, s.canonical_name, rlt.dscr, rlt.rlt_id, o.obj_id, > o.canonical_name You've got two columns that the system wants to call "obj_id" here. Try something like SELECT s.obj_id AS s_obj_id, ... -- Richard Huxton Archonet Ltd
>BTW: that's not a postgres problem, ANY relational database will >give the same error here. I didn't suspect it to be a postgres problem. I only didn't have the opportunity to make this mistake working with MySQL. :-) best, Christoph
>You've got two columns that the system wants to call "obj_id" here. >Try something like > SELECT s.obj_id AS s_obj_id, ... That would have been elegant, but returns a 'syntax error at or near "s_obj_id"'. Hm... Thanks, Christoph
>>You've got two columns that the system wants to call "obj_id" here. >>Try something like >> SELECT s.obj_id AS s_obj_id, ... > >That would have been elegant, but returns a 'syntax error at or near >"s_obj_id"'. Hm... Was just some minor syntax error. Works now - this is exactly what I wanted. Thanks! regards, Christoph