[SQL] sql problem with join
Hi all, I've got a problem tryng to define a view with a few joins, I'll appreciate if someone could drive me in the definition of such query. I've got a table roleSkill that contains a row for each skill belonging to a defined role and with the desired competence level for such skill in such role: roleSkill = (id_role, id_skill, desired_level) PRIMARY KEY(id_role,id_skill) Then I've got a table peopleSkill with a row for each evaluated skill for a person (a skill in this case could not belong to a defined role): peopleSkill = (id_person,id_skill, evaluated_level) PRIMARY KEY(id_person,id_skill) Finally I've got an association between a person and a role: peopleRole = (id_person,id_role) PRIMARY KEY(id_person,id_role) Now I'd like to build a view that shows a row for each skill a person has (i.e. it has been evaluated) and should have (i.e. it is listed in the role). Initially I tried with something like: select p.*,r.* from roleSkill r JOIN peopleRole p on p.id_role=r.id_role /* this gives me all the roles a person has and all her skills */ LEFT JOIN peopleSkill s on s.id_skill = r.id_skill /* this should keep all the role skills and show the one evaluated */ So the first join should give me all the role played from a person, with its skills, the second join should take the evaluated skills and should keep the not evaluated (i.e., present only in roleSkill) rows. But this is not working, I see a lot of rows with the same role for the same person and I cannot find the error. Any clues? Thanks, Luca ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] sql problem with join
> Hi all, > I've got a problem tryng to define a view with a few joins, I'll appreciate > if > someone could drive me in the definition of such query. > I've got a table roleSkill that contains a row for each skill belonging to a > defined role and with the desired competence level for such skill in such > role: > roleSkill = (id_role, id_skill, desired_level) PRIMARY KEY(id_role,id_skill) > > Then I've got a table peopleSkill with a row for each evaluated skill for a > person (a skill in this case could not belong to a defined role): > peopleSkill = (id_person,id_skill, evaluated_level) PRIMARY > KEY(id_person,id_skill) > > Finally I've got an association between a person and a role: > peopleRole = (id_person,id_role) PRIMARY KEY(id_person,id_role) > > Now I'd like to build a view that shows a row for each skill a person has > (i.e. it has been evaluated) and should have (i.e. it is listed in the role). > Initially I tried with something like: > select p.*,r.* > from roleSkill r > JOIN peopleRole p on p.id_role=r.id_role /* this gives me all the roles a > person has and all her skills */ > LEFT JOIN peopleSkill s on s.id_skill = r.id_skill/* this should > keep all > the role skills and show the one evaluated */ > > So the first join should give me all the role played from a person, with its > skills, the second join should take the evaluated skills and should keep the > not evaluated (i.e., present only in roleSkill) rows. But this is not > working, I see a lot of rows with the same role for the same person and I > cannot find the error. > Any clues? May be you could show the results you are getting and then make up some results that you would really like to get. I am not entirely clear what you are trying to achieve. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sql problem with join
On Wednesday 15 November 2006 16:20 your cat, walking on the keyboard, wrote: > May be you could show the results you are getting and then make up some > results that you would really like to get. I am not entirely clear what > you are trying to achieve. Lastly I found the solution, for the moment, that should be the following: select * from peopleRole pr left join roleSkill rs on rs.id_role = pr.id_role left join peopleSkill ps on ps.id_skill = rs.id_skill I think it should go, don't you think? Thanks, Luca ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] recursive SQL and with clause
I was just wondering when we could reasonably expect recursive SQL to be added to Postgres? I saw some posts from 2004 that made it sound like it was imminent, but I guess something went wrong? I believe the WITH clause is a pre-requisite for recursive SQL, however I do find the WITH clause also very useful in decomposing very complex SQL into understandable chunks. -- View this message in context: http://www.nabble.com/recursive-SQL-and-with-clause-tf2638088.html#a7363779 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Aggregate function to build 2-d array
I would like to aggregate several rows of a query, maintaining the
relative order. Is there an other way to achive the same result? I
have an alternative construction, but I am not convinced it will work
in all cases.
For example, with the setup below:
-- Concatenate elements of type t into array of type t[]
CREATE AGGREGATE aconcat (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
-- Sample table
CREATE TABLE a (
id INT PRIMARY KEY,
k TEXT NOT NULL,
v1 TEXT NOT NULL,
v2 TEXT NOT NULL);
-- Initialize data
COPY a(id,k,v1,v2) FROM STDIN DELIMITER '|';
1|Alice|A|a
2|Bob|B|b
3|Charlie|C|c
4|Alice|A|a
5|Charlie|C|c
\.
This query is what I would like to run:
SELECT aconcat(ARRAY[v1, v2]), k FROM a GROUP BY k;
Which gives the result
"ERROR: could not find array type for data type text[]"
I would have expected:
aconcat |k
---+-
{{C,c},C,c}} | Charlie
{{A,a},{A,a} | Alice
{{B,b},{B,b}} | Bob
The problem I am hitting appears to be that the array_append()
function does not accept 2-d arrays[1].
SELECT array_append(ARRAY[ARRAY[1,2],ARRAY[2,3]], ARRAY[3,4]);
ERROR: function array_append(integer[], integer[]) does not exist
The operator "||" does but I don't know how to use this to make a
custom aggregate. Is there some way to do so, or achive the same
result?
SELECT ARRAY[ARRAY[1,2],ARRAY[2,3]] || ARRAY[3,4];
?column?
-
{{1,2},{2,3},{3,4}}
An alternative works in my test case:
SELECT aconcat(v1), aconcat(v2), k FROM a GROUP BY k;
aconcat | aconcat |k
-+-+-
{C,C} | {c,c} | Charlie
{A,A} | {a,a} | Alice
{B} | {b} | Bob
However I can't find any assurance that the order that each aggregate
is formed will be the same in each column. Is this currently the case,
and is it likely to remain so?
Thanks in advance,
Steven.
[1] http://www.postgresql.org/docs/8.2/interactive/arrays.html
--
w: http://www.cl.cam.ac.uk/users/sjm217/
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Aggregate function to build 2-d array
Hi Steven,
I believe I saw something about a fix to array_append in the release notes
for V8.2. Not sure if this helps.
Steven Murdoch-2 wrote:
>
> I would like to aggregate several rows of a query, maintaining the
> relative order. Is there an other way to achive the same result? I
> have an alternative construction, but I am not convinced it will work
> in all cases.
>
> For example, with the setup below:
>
> -- Concatenate elements of type t into array of type t[]
> CREATE AGGREGATE aconcat (
> BASETYPE = anyelement,
> SFUNC = array_append,
> STYPE = anyarray,
> INITCOND = '{}'
> );
>
> -- Sample table
> CREATE TABLE a (
> id INT PRIMARY KEY,
> k TEXT NOT NULL,
> v1 TEXT NOT NULL,
> v2 TEXT NOT NULL);
>
> -- Initialize data
> COPY a(id,k,v1,v2) FROM STDIN DELIMITER '|';
> 1|Alice|A|a
> 2|Bob|B|b
> 3|Charlie|C|c
> 4|Alice|A|a
> 5|Charlie|C|c
> \.
>
> This query is what I would like to run:
> SELECT aconcat(ARRAY[v1, v2]), k FROM a GROUP BY k;
> Which gives the result
> "ERROR: could not find array type for data type text[]"
>
> I would have expected:
> aconcat |k
> ---+-
> {{C,c},C,c}} | Charlie
> {{A,a},{A,a} | Alice
> {{B,b},{B,b}} | Bob
>
> The problem I am hitting appears to be that the array_append()
> function does not accept 2-d arrays[1].
>
> SELECT array_append(ARRAY[ARRAY[1,2],ARRAY[2,3]], ARRAY[3,4]);
> ERROR: function array_append(integer[], integer[]) does not exist
>
> The operator "||" does but I don't know how to use this to make a
> custom aggregate. Is there some way to do so, or achive the same
> result?
>
> SELECT ARRAY[ARRAY[1,2],ARRAY[2,3]] || ARRAY[3,4];
>?column?
> -
> {{1,2},{2,3},{3,4}}
>
> An alternative works in my test case:
> SELECT aconcat(v1), aconcat(v2), k FROM a GROUP BY k;
> aconcat | aconcat |k
> -+-+-
> {C,C} | {c,c} | Charlie
> {A,A} | {a,a} | Alice
> {B} | {b} | Bob
>
> However I can't find any assurance that the order that each aggregate
> is formed will be the same in each column. Is this currently the case,
> and is it likely to remain so?
>
> Thanks in advance,
> Steven.
>
> [1] http://www.postgresql.org/docs/8.2/interactive/arrays.html
>
> --
> w: http://www.cl.cam.ac.uk/users/sjm217/
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>
--
View this message in context:
http://www.nabble.com/Aggregate-function-to-build-2-d-array-tf2638930.html#a7369425
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] recursive SQL and with clause
After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (chrisj) belched out: > I was just wondering when we could reasonably expect recursive SQL to be > added to Postgres? > > I saw some posts from 2004 that made it sound like it was imminent, > but I guess something went wrong? I believe the WITH clause is a > pre-requisite for recursive SQL, however I do find the WITH clause > also very useful in decomposing very complex SQL into understandable > chunks. If I recall correctly, Jim Nasby was considering it; he hadn't heard much interest back, and got to other things in the 8.2 cycle. There has been some discussion on strategy for an approach to it for the 8.3 cycle; you can expect that not to progress much 'til 8.2.0 gets released. Bringing it back up on pgsql.hackers, some time soon thereafter, would be not the worst idea of all time :-). -- (format nil "[EMAIL PROTECTED]" "cbbrowne" "gmail.com") http://linuxdatabases.info/info/wp.html Programming is one of the most difficult branches of applied mathematics; the poorer mathematicians hadbetter remain pure mathematicians. -- Edsger W. Dijkstra ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [SQL] Case Preservation disregarding case
On Nov 14, 2006, at 2:42 PM, Simon Riggs wrote: On Thu, 2006-11-02 at 10:51 -0500, Tom Lane wrote: "Simon Riggs" <[EMAIL PROTECTED]> writes: We have namespaces to differentiate between two sources of object names, so anybody who creates a schema where MyColumn is not the same thing as myColumn is not following sensible rules for conceptual distance. I'd agree that that is not a good design practice, but the fact remains that they *are* different per spec. Would be better to make this behaviour a userset switchable between the exactly compliant and the more intuitive. That's certainly not happening --- if you make any changes in the semantics of equality of type name, it would have to be frozen no later than initdb time, for exactly the same reasons we freeze locale then (hint: index ordering). [Re-read all of this after Bruce's post got me thinking.] My summary of the thread, with TODO items noted: 1. PostgreSQL doesn't follow the spec, but almost does, with regard to comparison of unquoted and quoted identifiers. DB2 does this per spec. 2. TODO: We could follow the spec, but it would need an initdb option; some non-SQL:2003 standard PostgreSQL programs would not work as they do now. This is considered a minor, low priority item, though. 3. TODO: We could set column headers better if we wanted to (rather than ?column? we could use e.g. Sum_ColumnName etc) Did the idea of preserving the original case and using that for output column names, /d, etc. get shot down? I thought it would be a useful addition... -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
