[SQL] multi column foreign key for implicitly unique columns
Hi, PostgreSQL doesn't allow the creation of a foreign key to a combination of fields that has got no dedicated unique key but is unique nonetheless because a subset of the combination of fields has a unique constraint. Example: CREATE TABLE p ( name TEXT PRIMARY KEY, "type" TEXT ); CREATE TABLE f ( name TEXT, "type" TEXT, FOREIGN KEY(name, "type") REFERENCES p(name, "type") ); ERROR: there is no unique constraint matching given keys for referenced table "p" Is this on purpose? I think the foreign key should be allowed. Creating an extra unique key only has a negative impact on performance, right? Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: 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
Re: [SQL] multi column foreign key for implicitly unique columns
On Tue, 2004-08-17 at 10:25, Markus Bertheau wrote: > Hi, > > PostgreSQL doesn't allow the creation of a foreign key to a combination > of fields that has got no dedicated unique key but is unique nonetheless > because a subset of the combination of fields has a unique constraint. > Example: > > CREATE TABLE p ( > name TEXT PRIMARY KEY, > "type" TEXT > ); > > CREATE TABLE f ( > name TEXT, > "type" TEXT, > FOREIGN KEY(name, "type") REFERENCES p(name, "type") > ); > ERROR: there is no unique constraint matching given keys for referenced table "p" What's the point of this? p.name is the primary key and is therefore unique in p, so your foreign key should simply reference p.name. Having f.type as a repetition of p.type violates normalisation principles, since name is completely derivable by a join of f to p on name. > Is this on purpose? I think the foreign key should be allowed. Creating > an extra unique key only has a negative impact on performance, right? If there is no unique key, how does the foreign key trigger find the referenced row except by doing a sequential scan? Bad news! And when one of the duplicate referenced rows changes, what should happen with ON UPDATE or ON DELETE? -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "If ye abide in me, and my words abide in you, ye shall ask what ye will, and it shall be done unto you." John 15:7 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CROSS-TAB query help? I have read it cant be done in on
Theo Galanakis wrote: Thanks Rickard Max may not work as not all the data is numerical. However I will give the contrib/cross-tab a go! It will work as long as you only have one non-null value per grouped field (which you did in your example). | symbol | linktype ---+--+--+---+---+ ---+--+--+---+---+ ---+--+--+---+---+-- 100473 | 93 | | | | 100473 | | 77 | | | 100473 | | | text1| | -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] multi column foreign key for implicitly unique columns
Ð ÐÑÑ, 17.08.2004, Ð 11:39, Oliver Elphick ÐÐÑÐÑ: > What's the point of this? p.name is the primary key and is therefore > unique in p, so your foreign key should simply reference p.name. Having > f.type as a repetition of p.type violates normalisation principles, > since name is completely derivable by a join of f to p on name. The real situation is a little more complicated: CREATE TABLE classes ( name TEXT PRIMARY KEY ); CREATE TABLE class_fields ( class_name TEXT REFERENCES classes(name), field_name TEXT, PRIMARY KEY(class_name, field_name) ); CREATE TABLE objects ( name TEXT PRIMARY KEY, class_name TEXT REFERENCES classes(name) ); CREATE TABLE object_versions ( object_name TEXT REFERENCES objects(name), object_version DATE, PRIMARY KEY(object_name, object_version) ); CREATE TABLE object_version_property_values ( object_name TEXT REFERENCES objects(name), object_version DATE, class_name TEXT, field_name TEXT, value TEXT, FOREIGN KEY(object_name, object_version) REFERENCES object_versions(object_name, object_version), -- this fk is needed to make sure that the the object in -- question really is of the class that field_name is a field of FOREIGN KEY(object_name, class_name) REFERENCES objects(name, class_name), FOREIGN KEY(class_name, field_name) REFERENCES class_fields(class_name, field_name) ); ERROR: there is no unique constraint matching given keys for referenced table "objects" I need the fk on the columns. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] multi column foreign key for implicitly unique columns
Markus Bertheau wrote: Hi, PostgreSQL doesn't allow the creation of a foreign key to a combination of fields that has got no dedicated unique key but is unique nonetheless because a subset of the combination of fields has a unique constraint. [snip example] Is this on purpose? I think the foreign key should be allowed. Creating an extra unique key only has a negative impact on performance, right? As you say, the uniqueness is guaranteed so there's no good reason why it couldn't be made to work. It's probably more of an implementation issue. Unique constraints are implemented with an index, so I'm guessing the FK code assumes there is an index there to check against. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
Hi, why is the following query not allowed: SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = 'foo' FOR UPDATE OF classes) AS foo It's clear which rows should be locked here, I think. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] only last records in subgroups
O kyrios Dino Vliet egrapse stis Aug 17, 2004 : > Hi there, > > I'm having hard times with the following query: > I want to select only the last records from a subgroup > in a table. But because the subgroup contains > different number of records for every id, I don't know > how to specify that. For example, check the following > table, > id, day > x,300 > x,250, > x,0 > y,250 > y,4 > > I only want the records > x,0 and y,4 but how do I manage this in sql? I was > hoping for a keyword LAST or so, where I can specify > that when I've ordered my results with order by, I > could only get the last of the subgroups (the first > one is easy because I could use limit 1) > Try, SELECT min(day),id from reg group by id; min | id -+ 4 | y 0 | x (2 rows) > Thanks in advance > > > > > __ > Do you Yahoo!? > New and Improved Yahoo! Mail - 100MB free storage! > http://promotions.yahoo.com/new_mail > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Inheriting text[] field
10x I suppose you are right:) Regard Kaloyan Iliev Tom Lane wrote: Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: I am useing PostgreSQL 7.2.3. test_libvar=# create table temp_a( test_libvar(# name text[] test_libvar(# ); CREATE test_libvar=# create table temp( name text[] ) inherits (temp_a); NOTICE: CREATE TABLE: merging attribute "name" with inherited definition ERROR: CREATE TABLE: attribute "name" type conflict (_text and text) Works fine for me in 7.3 and later. Time to upgrade ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Verifying data type
Thanks, but the number may be a float, like '1.23,00', '12.323,00', '12,34' :( The regex works fine if it just an integer... Have some regex to "compare"? Tom Lane wrote: =?ISO-8859-1?Q?Elieser_Le=E3o?= <[EMAIL PROTECTED]> writes: I need to verify if the data in p_valor is just number or it is a string. What is the best way to do this? In PG 8.0 you can just do it exactly the way your Oracle original does, viz try to cram it into a numeric variable and catch the exception if any. In earlier versions, my thoughts would run to some kind of string matching test using a regular _expression_. The regexp method is probably significantly faster though, so maybe you want to do it anyway, especially if you don't need the full generality of possible floating-point formats. You might get away with something as simple as "p_valor ~ '^[0-9]+$'" if you only care about unsigned integer inputs. regards, tom lane
Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
On Tue, Aug 17, 2004 at 13:07:43 +0200, Markus Bertheau <[EMAIL PROTECTED]> wrote: > Hi, > > why is the following query not allowed: > > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = > 'foo' FOR UPDATE OF classes) AS foo > > It's clear which rows should be locked here, I think. Even if it was allowed, it probably wouldn't be good enough because it won't protect against newly inserted records. You really want to lock the table against concurrent updates when doing this if concurrent updates can cause a problem. You really want predicate locking, to lock any old or new rows with name = 'foo', but postgres doesn't have that capability. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
Ð ÐÑÑ, 17.08.2004, Ð 16:12, Bruno Wolff III ÐÐÑÐÑ: > > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = > > 'foo' FOR UPDATE OF classes) AS foo > > > > It's clear which rows should be locked here, I think. > > Even if it was allowed, it probably wouldn't be good enough because it won't > protect against newly inserted records. Can you detail an example where this wouldn't be good enough? In a PL/pgSQL function I'm doing PERFORM position FROM class_fields WHERE class = arg_class_name; INSERT INTO class_fields (class, field, position) VALUES (arg_class_name, arg_field_name, (SELECT MAX(position) FROM class_fields WHERE class = arg_class_name)); Is this unsafe? The question initially arose because I wanted to do something similar to SELECT INTO var_new_position MAX(position) FROM class_fields WHERE class = arg_class_name FOR UPDATE OF class_fields; which didn't work. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: 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
Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
This query does not make sense to me. Why would you create an updatable subquery just to get the highest value? Maybe you are trying to achieve something other than what the query suggest. You wou care to put in words what you want to do? JLL Markus Bertheau wrote: Hi, why is the following query not allowed: SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = 'foo' FOR UPDATE OF classes) AS foo It's clear which rows should be locked here, I think. Thanks ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] multi column foreign key for implicitly unique columns
On Tue, 17 Aug 2004, Richard Huxton wrote: > Markus Bertheau wrote: > > Hi, > > > > PostgreSQL doesn't allow the creation of a foreign key to a combination > > of fields that has got no dedicated unique key but is unique nonetheless > > because a subset of the combination of fields has a unique constraint. > [snip example] > > Is this on purpose? I think the foreign key should be allowed. Creating > > an extra unique key only has a negative impact on performance, right? > > As you say, the uniqueness is guaranteed so there's no good reason why > it couldn't be made to work. It's probably more of an implementation > issue. Unique constraints are implemented with an index, so I'm guessing No, actually, it's that the SQL92 (at least) spec says explicitly that there must be a unique constraint across all of the columns specified, not merely across a subset. "then the set of column names of that shall be equal to the set of column names in the unique columns of a unique constraint of the referenced table." ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] multi column foreign key for implicitly unique columns
Stephan Szabo <[EMAIL PROTECTED]> writes: > No, actually, it's that the SQL92 (at least) spec says explicitly that > there must be a unique constraint across all of the columns specified, not > merely across a subset. > "then the set of column names of that shall be > equal to the set of column names in the unique columns of a unique > constraint of the referenced table." SQL99 says the same. 11.8 syntax rule 3a: a) If the specifies a , then the set of s contained in that shall be equal to the set of s contained in the of a unique constraint of the referenced table. I think one reason for this is that otherwise it's not clear which unique constraint the FK constraint depends on. Consider create table a (f1 int unique, f2 int unique); create table b (f1 int, f2 int, foreign key (f1,f2) references a(f1,f2)); How would you decide which constraint to make the FK depend on? It'd be purely arbitrary. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
Ð ÐÑÑ, 17.08.2004, Ð 16:26, Jean-Luc Lachance ÐÐÑÐÑ: > This query does not make sense to me. > Why would you create an updatable subquery just to get the highest value? To make sure that the highest value will be the highest value (or then second-highest) after I commit the transaction. See my other answer. -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] multi column foreign key for implicitly unique columns
Ð ÐÑÑ, 17.08.2004, Ð 16:46, Tom Lane ÐÐÑÐÑ: > I think one reason for this is that otherwise it's not clear which > unique constraint the FK constraint depends on. Consider > > create table a (f1 int unique, f2 int unique); > > create table b (f1 int, f2 int, > foreign key (f1,f2) references a(f1,f2)); > > How would you decide which constraint to make the FK depend on? Either way, the semantics are the same, right? -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] multi column foreign key for implicitly unique columns
On Tue, 17 Aug 2004, Markus Bertheau wrote: > Ð ÐÑÑ, 17.08.2004, Ð 16:46, Tom Lane ÐÐÑÐÑ: > > > I think one reason for this is that otherwise it's not clear which > > unique constraint the FK constraint depends on. Consider > > > > create table a (f1 int unique, f2 int unique); > > > > create table b (f1 int, f2 int, > > foreign key (f1,f2) references a(f1,f2)); > > > > How would you decide which constraint to make the FK depend on? > > Either way, the semantics are the same, right? Unfortunately, not in the case of dropping the chosen constraint. Theoretically in that case, you'd probably have to extend the spec there as well to say that you check any dependent objects again to see if they would still be valid rather than dropping them (on cascade) or erroring (on restrict). ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
On Tue, 17 Aug 2004, Markus Bertheau wrote:
> В Втр, 17.08.2004, в 16:12, Bruno Wolff III пишет:
> > > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name =
> > > 'foo' FOR UPDATE OF classes) AS foo
> > >
> > > It's clear which rows should be locked here, I think.
> >
> > Even if it was allowed, it probably wouldn't be good enough because it won't
> > protect against newly inserted records.
>
> Can you detail an example where this wouldn't be good enough?
Another transaction can come along and insert a row with name='foo' into
classes with a higher position value after you've done the above but
before you commit.
T1: begin;
T2: begin;
T1: select max(position) from (select position from classes where
name='foo' for update of classes) as foo;
-- say this gets 5
T2: insert into classes (name, position) values ('foo', 10);
-- This wouldn't be blocked by the for update lock.
T2: commit;
-- now if you were to do the T1 select above, you'd get a different
-- answer in read committed.
If we had predicate locking, I think you could probably manage these cases
in serializable mode, but for now I'm not sure anything less than a table
lock would do.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] multi column foreign key for implicitly unique columns
Ð ÐÑÑ, 17.08.2004, Ð 17:06, Stephan Szabo ÐÐÑÐÑ: > On Tue, 17 Aug 2004, Markus Bertheau wrote: > > > Ð ÐÑÑ, 17.08.2004, Ð 16:46, Tom Lane ÐÐÑÐÑ: > > > > > I think one reason for this is that otherwise it's not clear which > > > unique constraint the FK constraint depends on. Consider > > > > > > create table a (f1 int unique, f2 int unique); > > > > > > create table b (f1 int, f2 int, > > > foreign key (f1,f2) references a(f1,f2)); > > > > > > How would you decide which constraint to make the FK depend on? > > > > Either way, the semantics are the same, right? > > Unfortunately, not in the case of dropping the chosen constraint. Can't you choose at fk check time rather than fk creation time? > Theoretically in that case, you'd probably have to extend the spec there > as well to say that you check any dependent objects again to see if they > would still be valid rather than dropping them (on cascade) or erroring > (on restrict). That also makes sense and is more efficient as I see it. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] multi column foreign key for implicitly unique columns
On Tue, 17 Aug 2004, Markus Bertheau wrote: > В Втр, 17.08.2004, в 17:06, Stephan Szabo пишет: > > On Tue, 17 Aug 2004, Markus Bertheau wrote: > > > > > В Втр, 17.08.2004, в 16:46, Tom Lane пишет: > > > > > > > I think one reason for this is that otherwise it's not clear which > > > > unique constraint the FK constraint depends on. Consider > > > > > > > > create table a (f1 int unique, f2 int unique); > > > > > > > > create table b (f1 int, f2 int, > > > > foreign key (f1,f2) references a(f1,f2)); > > > > > > > > How would you decide which constraint to make the FK depend on? > > > > > > Either way, the semantics are the same, right? > > > > Unfortunately, not in the case of dropping the chosen constraint. > > Can't you choose at fk check time rather than fk creation time? > > > Theoretically in that case, you'd probably have to extend the spec there > > as well to say that you check any dependent objects again to see if they > > would still be valid rather than dropping them (on cascade) or erroring > > (on restrict). > > That also makes sense and is more efficient as I see it. I'm not seeing what you're seeing then. Right now, at creation, we can say object A depends on object B. When you go to drop object B, we can easily lookup up which objects (A) depend on it. When you go to drop object C, we can easily lookup up which objects () depend on it. If instead you put it off to drop time, when you drop object B, you need to figure out which objects might potentially depend on be (lets say (A,C)) and then determine which objects those do depend on and see if B is among those sets. If we do the in-between one, we could say that object A partially depends on B (because something else can fufill the requirement as well potentially). When you go to drop object B, we can see that A partially depends on B and then check only A's dependencies to see whether any other thing that might fufill the requirement still exists. In general, such a system would need to be able to make sure that it worked properly with multiple concurrent drops of objects that an object partially dependended on (even though the constraint case is probably safe.) It sounds like it'd be a pain at best. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] multi column foreign key for implicitly unique columns
Stephan Szabo <[EMAIL PROTECTED]> writes: > ... It sounds like it'd be a pain at best. Also, that would directly violate the SQL spec's model of dependencies, with possibly unpleasant consequences. The current implementation does exactly what SQL says to do. I cite from SQL99 11.99 DROP CONSTRAINT: 3) If TC is a unique constraint and RC is a referential constraint whose referenced table is T and whose referenced columns are the unique columns of TC, then RC is said to be dependent on TC. ... 6) If RESTRICT is specified, then: a) No table constraint shall be dependent on TC. NOTE 195 - If CASCADE is specified, then any such dependent object will be dropped by the effective execution of the specified in the General Rules of this Subclause. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
If your intent is to insert a new record with position incremented by 1, you should use a trigger. Look at the autoincrement thread from few days ago. Markus Bertheau wrote: Ð ÐÑÑ, 17.08.2004, Ð 16:12, Bruno Wolff III ÐÐÑÐÑ: SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = 'foo' FOR UPDATE OF classes) AS foo It's clear which rows should be locked here, I think. Even if it was allowed, it probably wouldn't be good enough because it won't protect against newly inserted records. Can you detail an example where this wouldn't be good enough? In a PL/pgSQL function I'm doing PERFORM position FROM class_fields WHERE class = arg_class_name; INSERT INTO class_fields (class, field, position) VALUES (arg_class_name, arg_field_name, (SELECT MAX(position) FROM class_fields WHERE class = arg_class_name)); Is this unsafe? The question initially arose because I wanted to do something similar to SELECT INTO var_new_position MAX(position) FROM class_fields WHERE class = arg_class_name FOR UPDATE OF class_fields; which didn't work. Thanks ---(end of broadcast)--- TIP 3: 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
Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo
On Tue, Aug 17, 2004 at 16:51:21 +0200, Markus Bertheau <[EMAIL PROTECTED]> wrote: > ?? ??, 17.08.2004, ?? 16:26, Jean-Luc Lachance ??: > > This query does not make sense to me. > > Why would you create an updatable subquery just to get the highest value? > > To make sure that the highest value will be the highest value (or then > second-highest) after I commit the transaction. See my other answer. If you just need ordering that isn't too sensitive to overlapping transactions, then using a sequence (using a serial type is a convenient way to do this) is probably your best bet. You need to make sure the value won't roll over. But at worst a bigserial should be needed to do this. There will potentially be gaps in the numbers allocated accross the table. There will be gaps in the numbers allocated for any value of name. However, if all you need is ordering that shouldn't matter. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] multi column foreign key for implicitly unique columns
Markus, Hey, I see you figured out a workaround to writing a trigger for this. Let's see if we can make it work. ERROR: there is no unique constraint matching given keys for referenced table "objects" The reason for this is that CASCADE behavior gets quite odd when there is an FK reference to a non-unique column. We used to allow it, in 7.1, and I was responsible for a number of bug reports that led to us disallowing it. It should be theoretically implementable and relationally sound but will require a *lot* of troubleshooting to make work. So far, nobody's really interested enough. However, you have an easy way out: ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name); This will add the unique constraint that Postgres wants without changing your data at all. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] SQL Challenge: Arbitrary Cross-tab
Folks, I have a wierd business case. Annoyingly it has to be written in *portable* SQL92, which means no arrays or custom aggregates. I think it may be impossible to do in SQL which is why I thought I'd give the people on this list a crack at it. Solver gets a free drink/lunch on me if we ever meet at a convention. The Problem: for each "case" there are from zero to eight "timekeepers" authorized to work on the "case", out of a pool of 150 "timekeepers". This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447 | 047 132113 | 021 132113 | 115 132113 | 106 etc. But, a client's e-billing application wants to see these timekeepers displayed in the following horizontal format: case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 213447 | 047 | | | | | | | | 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | etc. Order does not matter for timekeepers 1-8. This is a daunting problem because traditional crosstab solutions do not work; timekeepers 1-8 are coming out of a pool of 150. Can it be done? Or are we going to build this with a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL "advanced feature") -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
I would use my report writer, but in any case you'd want at least 2 separate queries, maybe three to keep it simple and readable. If you are allowed to use stored procedures you can build up the output by using simple concats instead of text aggregation (which is a procedure of simple concats). Using loops and subqueries you should be to construct the heading (count distinct timekeeper_id) and then select the data row by row concatenating results before you send it out. This is a non-solution which effectively hides the aggregation in a function. Or write it in a client perl app if you must. You can't really do it w/o loops or aggregates. (I wish (hope?) I were wrong about this.) --elein On Tue, Aug 17, 2004 at 07:55:11PM -0700, Josh Berkus wrote: > Folks, > > I have a wierd business case. Annoyingly it has to be written in *portable* > SQL92, which means no arrays or custom aggregates. I think it may be > impossible to do in SQL which is why I thought I'd give the people on this > list a crack at it. Solver gets a free drink/lunch on me if we ever meet at > a convention. > > The Problem: for each "case" there are from zero to eight "timekeepers" > authorized to work on the "case", out of a pool of 150 "timekeepers". This > data is stored vertically: > > authorized_timekeepers: > case_id | timekeeper_id > 213447| 047 > 132113| 021 > 132113| 115 > 132113| 106 > etc. > > But, a client's e-billing application wants to see these timekeepers displayed > in the following horizontal format: > > case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 > 213447| 047 | | | | | | | | > 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | > etc. > > Order does not matter for timekeepers 1-8. > > This is a daunting problem because traditional crosstab solutions do not work; > timekeepers 1-8 are coming out of a pool of 150. > > Can it be done? Or are we going to build this with a row-by-row procedural > loop? (to reiterate: I'm not allowed to use a custom aggregate or other > PostgreSQL "advanced feature") > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
On Tue, 17 Aug 2004, Josh Berkus wrote: > I have a wierd business case. Annoyingly it has to be written in *portable* > SQL92, which means no arrays or custom aggregates. I think it may be > impossible to do in SQL which is why I thought I'd give the people on this > list a crack at it. Solver gets a free drink/lunch on me if we ever meet at > a convention. > > The Problem: for each "case" there are from zero to eight "timekeepers" > authorized to work on the "case", out of a pool of 150 "timekeepers". This > data is stored vertically: > > authorized_timekeepers: > case_id | timekeeper_id > 213447| 047 > 132113| 021 > 132113| 115 > 132113| 106 > etc. > > But, a client's e-billing application wants to see these timekeepers displayed > in the following horizontal format: > > case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 > 213447| 047 | | | | | | | | > 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | > etc. > > Order does not matter for timekeepers 1-8. > > This is a daunting problem because traditional crosstab solutions do not work; > timekeepers 1-8 are coming out of a pool of 150. > > Can it be done? Or are we going to build this with a row-by-row procedural If you know it's max 8, I think it may be possible, but I can't think of a way that'd be better than just writing code yourself. Just maybe something like the following would give you three timekeepers: select foo.case_id, foo.v1, foo.v2, (select min(timekeeper_id) from authorized_timekeepers where authorized_timekeepers.case_id=foo.case_id and timekeeper_id > foo.v2) as v3 from ( select foo.case_id, foo.v1, (select min(timekeeper_id) from authorized_timekeepers where authorized_timekeepers.case_id=foo.case_id and timekeeper_id > foo.v1) as v2 from (select foo.case_id, foo.v1 from (select foo.case_id, (select min(timekeeper_id) from authorized_timekeepers where authorized_timekeepers.case_id=foo.case_id) as v1 from (select distinct case_id from authorized_timekeepers) foo ) foo) foo) foo; If that works for 3 (and I think that's standard behavior), then you should be able to extend it to any fixed number using the pattern. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Names shortened to spare the line lengths: SELECT bob.cid, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1) AS tk1, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 2) AS tk3, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 3) AS tk4, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 4) AS tk5, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 5) AS tk6, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 6) AS tk7, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 7) AS tk8, FROM (SELECT DISTINCT cid FROM ats) AS bob; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200408172335 -BEGIN PGP SIGNATURE- iD8DBQFBIs7AvJuQZxSWSsgRAkglAJ9mNEmOYlLPynygMmelvzlqkYoHlwCeJqTb g5gyh9LztONPCZj32aOEuGI= =Yy7m -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Greg, Stephan, > (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2, bz! Thanks for playing. LIMIT and OFFSET, sadly, are not SQL standard. They're only portable to MySQL. This has to port to SQL Server and Oracle. > If that works for 3 (and I think that's standard behavior), then you > should be able to extend it to any fixed number using the pattern. Hmmm... that might work. I'll have to test that the nesting doesn't kill SQL Server (a serious danger) but it's worth a try. Performance will really suck but fortunately we only run this bill once a month. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Josh Berkus wrote: The Problem: for each "case" there are from zero to eight "timekeepers" authorized to work on the "case", out of a pool of 150 "timekeepers". This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447 | 047 132113 | 021 132113 | 115 132113 | 106 etc. But, a client's e-billing application wants to see these timekeepers displayed in the following horizontal format: case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 213447 | 047 | | | | | | | | 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | etc. Order does not matter for timekeepers 1-8. This is a daunting problem because traditional crosstab solutions do not work; timekeepers 1-8 are coming out of a pool of 150. Can it be done? Or are we going to build this with a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL "advanced feature") This is pretty much exactly how contrib/tablefunc's crosstab (non-hashed version; crosstab(sourcesql, ncols)) works. If you really need it to be portable, though, application layer procedural code is likely to be the easiest and fastest way to go. crosstab just wraps the procedural code in an SRF for you. Joe ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] multi column foreign key for implicitly unique columns
Ð ÐÑÐ, 18.08.2004, Ð 04:45, Josh Berkus ÐÐÑÐÑ: > Markus, Hi Josh, > Hey, I see you figured out a workaround to writing a trigger for this. Let's > see if we can make it work. > > ERROR: there is no unique constraint matching given keys > for referenced table "objects" > > The reason for this is that CASCADE behavior gets quite odd when there is an > FK reference to a non-unique column. We used to allow it, in 7.1, and I was > responsible for a number of bug reports that led to us disallowing it. It > should be theoretically implementable and relationally sound but will require > a *lot* of troubleshooting to make work. So far, nobody's really interested > enough. > > However, you have an easy way out: > > ALTER TABLE objects ADD CONSTRAINT obj_unq_2 UNIQUE (name, class_name); I was worried about the performance hit because (name, class_name) will always be unique, yet they will be checked for uniqueness. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL]
Im running/playing with PG 8.0 locally and want to install the contrib/dblink and contrib/crosstab. Can this be done on Windows, is there a GMAKE.exe for Windows?? Someone enlighten me! __This email, including attachments, is intended only for the addresseeand may be confidential, privileged and subject to copyright. If youhave received this email in error, please advise the sender and deleteit. If you are not the intended recipient of this email, you must notuse, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner.
