[SQL] special integrity constraints
hi, i have two tables create table a (x integer); create table b (y real); i have the special constraint that the sum of the number of rows in table a plus the number of rows in table b must be even. so there is a posibility of adding one element to a and one element to b, and again the constraint is met. but this of course does not work, since between adding the element to a and adding the element to b, the constraint is not met. so i cannot use a trigger. what i need is the execution of a check procedure at commit time. is that somehow possible? my database has much more tables than just a and b, and most time the other tables are modified and not a or b, so it would be nice to execute the constraint checking procedure only if a or b was modified. yes :) this question i asked in my former mail too, but I think it looked like there was a workaround and my problem was wrong. so i created this new kind of problem here to make things clearer. cu Erik -- Erik Thiele ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] special integrity constraints
O kyrios Erik Thiele egrapse stis Mar 22, 2004 : Did you check out the DEFERRABLE option on the constraint? > hi, > > i have two tables > > create table a (x integer); > create table b (y real); > > i have the special constraint that the sum of the number of rows in > table a plus the number of rows in table b must be even. > > so there is a posibility of adding one element to a and one element to > b, and again the constraint is met. > > but this of course does not work, since between adding the element to a > and adding the element to b, the constraint is not met. > > so i cannot use a trigger. > > what i need is the execution of a check procedure at commit time. is > that somehow possible? > > my database has much more tables than just a and b, and most time the other > tables are modified and not a or b, so it would be nice to execute the > constraint checking procedure only if a or b was modified. > > yes :) this question i asked in my former mail too, but I think it > looked like there was a workaround and my problem was wrong. so i > created this new kind of problem here to make things clearer. > > > cu > Erik > > > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 12:13:29 +0200 (EET) Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > Did you check out the DEFERRABLE option on the constraint? > DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. my constraint is not a foreign key constraint cya! erik > > hi, > > > > i have two tables > > > > create table a (x integer); > > create table b (y real); > > > > i have the special constraint that the sum of the number of rows in > > table a plus the number of rows in table b must be even. > > > > so there is a posibility of adding one element to a and one element > > to b, and again the constraint is met. > > > > but this of course does not work, since between adding the element > > to a and adding the element to b, the constraint is not met. > > > > so i cannot use a trigger. > > > > what i need is the execution of a check procedure at commit time. is > > that somehow possible? > > > > my database has much more tables than just a and b, and most time > > the other tables are modified and not a or b, so it would be nice to > > execute the constraint checking procedure only if a or b was > > modified. > > > > yes :) this question i asked in my former mail too, but I think it > > looked like there was a workaround and my problem was wrong. so i > > created this new kind of problem here to make things clearer. > > > > > > cu > > Erik > > > > > > > > -- > -Achilleus > -- Erik Thiele Horst Thiele Maschinenbau-Hydraulische Geräte GmbH Im Kampfrad 2 - 74196 Neuenstadt Tel.: 07139/4801-19 Fax.: 07139/4801-29 email: [EMAIL PROTECTED] Internet: http://www.thiele-hydraulik.de/ ---(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] special integrity constraints
On Mon, Mar 22, 2004 at 10:20:31 +0100, Erik Thiele <[EMAIL PROTECTED]> wrote: > hi, > > i have two tables > > create table a (x integer); > create table b (y real); > > i have the special constraint that the sum of the number of rows in > table a plus the number of rows in table b must be even. Does the above mean that the number of rows in a is equal to the number of rows in b or that their sum is divisible by 2? > so there is a posibility of adding one element to a and one element to > b, and again the constraint is met. > > but this of course does not work, since between adding the element to a > and adding the element to b, the constraint is not met. > > so i cannot use a trigger. > > what i need is the execution of a check procedure at commit time. is > that somehow possible? Do the real tables have candidate keys? If so you can use foreign key references to pair a row in a to a row b (though I am not sure this is what your real constraint is). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] special integrity constraints
O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > On Mon, 22 Mar 2004 12:13:29 +0200 (EET) > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > > > Did you check out the DEFERRABLE option on the constraint? > > > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. > Ooops . Then i guess you have to convert your problem to use FK constraints. Add 2 additional 1 row tables "abnum" and "even" and write 2 triggers (on a,b) that keep up to date the value of the single row of abnum. (thatis num(a)+num(b) % 2). Then INSERT INTO even(num) VALUES(0); Then make 1<-->1 relationship between abnum,even using DEFERRABLE FK constraints. At the end of each xaction the num(a)+num(b) % 2 must equal to 0 (i.e. an even number). Whats your results? > my constraint is not a foreign key constraint > > cya! > erik > > > > hi, > > > > > > i have two tables > > > > > > create table a (x integer); > > > create table b (y real); > > > > > > i have the special constraint that the sum of the number of rows in > > > table a plus the number of rows in table b must be even. > > > > > > so there is a posibility of adding one element to a and one element > > > to b, and again the constraint is met. > > > > > > but this of course does not work, since between adding the element > > > to a and adding the element to b, the constraint is not met. > > > > > > so i cannot use a trigger. > > > > > > what i need is the execution of a check procedure at commit time. is > > > that somehow possible? > > > > > > my database has much more tables than just a and b, and most time > > > the other tables are modified and not a or b, so it would be nice to > > > execute the constraint checking procedure only if a or b was > > > modified. > > > > > > yes :) this question i asked in my former mail too, but I think it > > > looked like there was a workaround and my problem was wrong. so i > > > created this new kind of problem here to make things clearer. > > > > > > > > > cu > > > Erik > > > > > > > > > > > > > -- > > -Achilleus > > > > > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] inverse of "day of year"
On Mon, Mar 22, 2004 at 08:32:32 -0300, Martin Marques <[EMAIL PROTECTED]> wrote: > > Is there information on how other intervals are taken? I mean, how is '1 year' > afected with the leap years? Intervals are stored as two components. One is absolute time difference, and the other is in months. '1 year' is equivalent to '12 months'. The documentation on how they work in corner cases (when added or subtracted from timestamp(tz)) is sparse. It isn't documented whether the part in months or the absolute time is added first or what timezone is used (for timestamptz) when adding the months part. The basic idea is that months are added by looking at the timestamp as date and time and adding the appropiate number of months to the date and then converting back to a timestamp. It isn't documented what happens when the day of the month is past the end of the new month, but it looks like the last day of new month is used. If you convert an interval to an absolute time (such as by extracting the epoch), then months are converted to 30 days. Again, I don't think this is documented. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 05:26:04 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Mon, Mar 22, 2004 at 10:20:31 +0100, > Erik Thiele <[EMAIL PROTECTED]> wrote: > > hi, > > > > i have two tables > > > > create table a (x integer); > > create table b (y real); > > > > i have the special constraint that the sum of the number of rows in > > table a plus the number of rows in table b must be even. > > Does the above mean that the number of rows in a is equal to the > number of rows in b or that their sum is divisible by 2? it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > so there is a posibility of adding one element to a and one element > > to b, and again the constraint is met. > > > > but this of course does not work, since between adding the element > > to a and adding the element to b, the constraint is not met. > > > > so i cannot use a trigger. > > > > what i need is the execution of a check procedure at commit time. is > > that somehow possible? > > Do the real tables have candidate keys? If so you can use foreign key > references to pair a row in a to a row b (though I am not sure this > is what your real constraint is). no. it's not what i want. the example presented here is just for making it clear why i want to launch a postgresql function on commit time. i am looking for this command: IF TABLE A OR B MODIFIED DO CALL my_checking_function() BEFORE COMMIT; and of course this must be enforced on DB layer. user apps may not work around it. as a dirty hack the following would also work for a first: ON COMMIT CALL my_checking_function(); altough it does lots of unneccessary work if the tables were not accessed. the my_checking_function() throws an error and thus aborts the transaction if the consistency is not ok. (num(a)+num(b) odd) my_checking_function is so complicated that it is not workaroundable with other tools. i wanted to create this situation by introducing the funny constraint that the sum of the number of rows in the two tables is even. of course in my real db the constraint is a more useful one... cu erik -- Erik Thiele Horst Thiele Maschinenbau-Hydraulische Geräte GmbH Im Kampfrad 2 - 74196 Neuenstadt Tel.: 07139/4801-19 Fax.: 07139/4801-29 email: [EMAIL PROTECTED] Internet: http://www.thiele-hydraulik.de/ ---(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] inverse of "day of year"
El Lun 22 Mar 2004 09:50, escribió:
>
> Intervals are stored as two components. One is absolute time difference,
> and the other is in months. '1 year' is equivalent to '12 months'.
> The documentation on how they work in corner cases (when added or
> subtracted from timestamp(tz)) is sparse. It isn't documented whether the
> part in months or the absolute time is added first or what timezone is used
> (for
> timestamptz) when adding the months part.
> The basic idea is that months are added by looking at the timestamp
> as date and time and adding the appropiate number of months to the date
> and then converting back to a timestamp. It isn't documented what happens
> when the day of the month is past the end of the new month, but it looks
> like the last day of new month is used.
> If you convert an interval to an absolute time (such as by extracting the
> epoch), then months are converted to 30 days. Again, I don't think this
> is documented.
Any thoughts on how this could affect date manipulation?
mydb=> select '29/2/2004'::date + ((2005 - date_part('year', now())::int) ||
'years')::interval;
?column?
-
2005-02-28 00:00:00
AFAIKS with other dates this works OK. :-)
--
10:11:02 up 13 days, 14:42, 4 users, load average: 0.17, 0.12, 0.16
-
Martín Marqués| select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004, Erik Thiele wrote: > On Mon, 22 Mar 2004 12:13:29 +0200 (EET) > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > > > Did you check out the DEFERRABLE option on the constraint? > > > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. > > my constraint is not a foreign key constraint However, foreign keys are implemented using "constraint triggers". They're really not documented much (because they're a not really separately supported implementation detail), but you can theoretically make triggers that are deferred to immediately before commit time. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] special integrity constraints
On Mon, Mar 22, 2004 at 14:10:42 +0100, Erik Thiele <[EMAIL PROTECTED]> wrote: > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) OK, that means my FK suggestion won't help. The other suggestion about putting triggers on "a" and "b" to update a count in another table that has a deferred check constraint on it may be your best bet. This will be a source of contention, but that may or may not be all that important depending on how often you are updating "a" and "b". ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] inverse of "day of year"
On Mon, Mar 22, 2004 at 10:14:40 -0300,
Martin Marques <[EMAIL PROTECTED]> wrote:
>
> Any thoughts on how this could affect date manipulation?
This is consistant with what I explained about the behavior when adding
a month results in a day in a month past the end of the new month.
What do you expect to have happen here?
> mydb=> select '29/2/2004'::date + ((2005 - date_part('year', now())::int) ||
> 'years')::interval;
> ?column?
> -
> 2005-02-28 00:00:00
>
> AFAIKS with other dates this works OK. :-)
The real issue with intervals is that how they work in unusual cases is
not documented. The behavior could change in a future version without
much fanfare.
---(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] inverse of "day of year"
If you have the option to handle the date manipulation in Perl
use the DateTime modules. Also see Date::Calc.
A considerable amount of effort has been expended dealing with all
the nitty-gritty of time manipulation.
Use those modules , get your new date or time interval and
feed that to the dbms with a straightforward inequality .
Don't try to do date arithmetic in sql if you can avoid it you'll
run afoul of something or other.
On Mon, 22 Mar 2004, Bruno Wolff III wrote:
> On Mon, Mar 22, 2004 at 10:14:40 -0300,
> Martin Marques <[EMAIL PROTECTED]> wrote:
> >
> > Any thoughts on how this could affect date manipulation?
>
> This is consistant with what I explained about the behavior when adding
> a month results in a day in a month past the end of the new month.
> What do you expect to have happen here?
>
> > mydb=> select '29/2/2004'::date + ((2005 - date_part('year', now())::int) ||
> > 'years')::interval;
> > ?column?
> > -
> > 2005-02-28 00:00:00
> >
> > AFAIKS with other dates this works OK. :-)
>
> The real issue with intervals is that how they work in unusual cases is
> not documented. The behavior could change in a future version without
> much fanfare.
>
> ---(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 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 06:21:28 -0800 (PST) Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Mon, 22 Mar 2004, Erik Thiele wrote: > > > On Mon, 22 Mar 2004 12:13:29 +0200 (EET) > > Achilleus Mantzios <[EMAIL PROTECTED]> wrote: > > > > > O kyrios Erik Thiele egrapse stis Mar 22, 2004 : > > > > > > Did you check out the DEFERRABLE option on the constraint? > > > > > > > DEFERRABLE > > NOT DEFERRABLE > > > > This controls whether the constraint can be deferred. A constraint that > > is not deferrable will be checked immediately after every command. > > Checking of constraints that are deferrable may be postponed until the > > end of the transaction (using the SET CONSTRAINTS command). NOT > > DEFERRABLE is the default. > > > > Only foreign key constraints currently accept > > this clause. All other constraint types are not deferrable. > > > > my constraint is not a foreign key constraint > > However, foreign keys are implemented using "constraint triggers". They're > really not documented much (because they're a not really separately > supported implementation detail), but you can theoretically make > triggers that are deferred to immediately before commit time. could you paste some code? create table a (x integer); create or replace function alwaysfail() returns opaque as ' begin raise exception ''no no i dont want to''; end; ' language 'plpgsql'; MAGIC COMMAND INSERT HERE, INSTALL CALL OF alwaysfail() ON MODIFICATION OF TABLE a AT COMMIT TIME; begin transaction; insert into a values(4); (ok) insert into a values(10); (ok) commit; (no no i dont want to) is that possible? cu erik -- Erik Thiele ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004, Erik Thiele wrote: > On Mon, 22 Mar 2004 06:21:28 -0800 (PST) > Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > > On Mon, 22 Mar 2004, Erik Thiele wrote: > > > > However, foreign keys are implemented using "constraint triggers". They're > > really not documented much (because they're a not really separately > > supported implementation detail), but you can theoretically make > > triggers that are deferred to immediately before commit time. > > could you paste some code? > > > create table a (x integer); > > create or replace function alwaysfail() returns opaque as ' > begin > raise exception ''no no i dont want to''; > end; > ' language 'plpgsql'; > > MAGIC COMMAND INSERT HERE, INSTALL CALL OF alwaysfail() ON > MODIFICATION OF TABLE a AT COMMIT TIME; create constraint trigger a_foo1 after insert or update or delete on a initially deferred for each row execute procedure alwaysfail(); Like I said, it's an implementation detail, so it's not 100% guaranteed to exist forever, but it's almost certain to last until we have deferrable non-fk constraints. It does also obey set constraints so, set constraints a_foo1 immediate does the checks immediately just as if it were a deferred constraint. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] special integrity constraints
On Mon, 22 Mar 2004 09:12:11 -0600 Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Mon, Mar 22, 2004 at 14:10:42 +0100, > Erik Thiele <[EMAIL PROTECTED]> wrote: > > > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > OK, that means my FK suggestion won't help. The other suggestion about > putting triggers on "a" and "b" to update a count in another table > that has a deferred check constraint on it may be your best bet. > This will be a source of contention, but that may or may not be all > that important depending on how often you are updating "a" and "b". > there is no such thing as a deferred check constraint: DEFERRABLE NOT DEFERRABLE This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. !!! Only foreign key constraints currently accept this clause. All other constraint types are not deferrable. !!! cu erik -- Erik Thiele ---(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] special integrity constraints
On Mon, Mar 22, 2004 at 17:21:21 +0100, Erik Thiele <[EMAIL PROTECTED]> wrote: > > there is no such thing as a deferred check constraint: > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > !!! Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. !!! Sorry, I checked the syntax for CREATE TABLE in the documentation and it shows DEFERRABLE as allowed on any column constraint. However, that is apparently not correct. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] function definition documentation
I am having an impossible time porting some simple Stored Procedures from Informix to postgres. The documentation is almost self defeating. Are there any better descriptions of how to define functions that return several tuples to a wide variety of calling programs (e.g. I can't count on my users running the psql command line tool, but rather coldfusion, jsp etc.) ? Thanks ! Greg Williamson DBA GlobeXplorer LLC ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Sorting an aggregated column
Hi all, I ran these commands: create temp table dw_survey(survey int,question int,answer_bool boolean,answer_int int,answer_char varchar); insert into dw_survey values(1,1,'t',null,null); insert into dw_survey values(1,2,'f',null,null); insert into dw_survey values(1,3,'t',null,null); insert into dw_survey values(1,4,null,123,null); insert into dw_survey values(1,5,null,21,null); insert into dw_survey values(1,6,null,1,null); insert into dw_survey values(2,1,'t',null,null); insert into dw_survey values(2,2,'t',null,null); insert into dw_survey values(2,3,'t',null,null); insert into dw_survey values(2,4,null,3,null); insert into dw_survey values(2,5,null,2,null); insert into dw_survey values(2,6,null,1,null); and I now have a table with data like this: Table "pg_temp_5.dw_survey" Column| Type| Modifiers -+---+--- survey | integer | question| integer | answer_bool | boolean | answer_int | integer | answer_char | character varying | survey | question | answer_bool | answer_int | answer_char +--+-++- 1 |1 | t || 1 |2 | f || 1 |3 | t || 1 |4 | |123 | 1 |5 | | 21 | 1 |6 | | 1 | 2 |1 | t || 2 |2 | t || 2 |3 | t || 2 |4 | | 3 | 2 |5 | | 2 | 2 |6 | | 1 | Answers to a survey can be one of three types - boolean, integer or varchar. There can be any number of questions in a survey. I want to summarise the results of the survey like this: survey | answer1 | answer2 | answer3 | answer4 | answer5 | answer6 ---+-+-+-+-+-+ 1 |t|f|t| 123 |21 |1 2 |t|t|t| 3 | 2 |1 Or even like this: survey | answers ---+--- 1 | t,f,t,123,21,1 2 | t,t,t,3,2,1 In both cases the order of the answers must be ordered by the "question" column. I can do the second case with a user-defined string concatenating aggregate: select survey, list ( case when answer_bool = 't' then 'y'::varchar when answer_bool = 'f' then 'n'::varchar when answer_int is not null then answer_int::varchar when answer_char is not null then answer_char::varchar end ) from dw_survey group by survey order by survey; survey |list +- 1 | y, n, y, 123, 21, 1 2 | y, y, y, 3, 2, 1 This output is correct in this case but there is no guarantee that the answers will come out in "question" order. I can't see how to incorporate sorting by the "question" column using this approach. Can anyone suggest either how to improve my current approach or a different approach to get the desired result? Thanks, David Witham Telephony Platforms Architect Unidial Pty Ltd Level 1, 174 Peel St North Melbourne,VIC 3051 Australia Ph: 03 8628 3383 Fax: 03 8628 3399 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Sorting an aggregated column
"David Witham" <[EMAIL PROTECTED]> writes: > This output is correct in this case but there is no guarantee that the > answers will come out in "question" order. I can't see how to > incorporate sorting by the "question" column using this approach. As of PG 7.4 you can reliably use a sorted sub-select to determine the order of inputs to a user-defined aggregate function. See for instance http://archives.postgresql.org/pgsql-general/2003-02/msg00917.php regards, tom lane ---(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] Sorting an aggregated column
Hi Tom, Thanks for the advice. I was planning to upgrade from 7.3.2 to 7.4 soon but this adds a bit more impetus. Under 7.3.2 I rewrote the query as your example suggested: explain select survey, list ( case when answer_bool = 't' then 'y'::varchar when answer_bool = 'f' then 'n'::varchar when answer_int is not null then answer_int::varchar when answer_char is not null then answer_char::varchar end ) as answers from (select survey, answer_bool, answer_int, answer_char from dw_survey order by survey,question) as dws group by survey order by survey; Aggregate (cost=122.16..129.66 rows=100 width=45) -> Group (cost=122.16..127.16 rows=1000 width=45) -> Sort (cost=122.16..124.66 rows=1000 width=45) Sort Key: survey -> Subquery Scan dws (cost=69.83..72.33 rows=1000 width=45) -> Sort (cost=69.83..72.33 rows=1000 width=45) Sort Key: survey, question -> Seq Scan on dw_survey (cost=0.00..20.00 rows=1000 width=45) So I see that there is the extra sort above the sub-query that wouldn't be there using 7.4. Are you saying that the sort by survey after the sort by survey,question would potentially reorder the records initially sorted by survey,question? If the sub-query had already sorted by survey (along with question), would the sort by survey bother to reorder any of the rows? E.g. if the subselect returned (assuming 1 answer from the 3 answer columns): 1,t 1,f 1,t 1,123 1,21 1,1 2,t 2,t 2,t 2,3 2,2 2,1 would the sort by survey potentially reorder these rows even though they don't need to be? Regards, David -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, 23 March 2004 16:17 To: David Witham Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Sorting an aggregated column "David Witham" <[EMAIL PROTECTED]> writes: > This output is correct in this case but there is no guarantee that the > answers will come out in "question" order. I can't see how to > incorporate sorting by the "question" column using this approach. As of PG 7.4 you can reliably use a sorted sub-select to determine the order of inputs to a user-defined aggregate function. See for instance http://archives.postgresql.org/pgsql-general/2003-02/msg00917.php regards, tom lane ---(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] special integrity constraints
O kyrios Erik Thiele egrapse stis Mar 22, 2004 : Could it be possible to convert *any* CHECK constraint problem into an equivalent DEFERRABLE FK constraint problem? That is for *every* CHECK CONSTRAINT cc create (and maintain) 2 additional 1 row tables (cc1(val),cc2(val)) in such a way that cc is met if and only if (cc1.val = cc2.val). Its kinda awkard, and i think that if your situation is (gets) any more complicated tou should delegate that task to the application. The whole idea was for you too prove that using only supported pgsql features, you can achieve the equivalent effect of deferrable check constraints. > On Mon, 22 Mar 2004 09:12:11 -0600 > Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > > On Mon, Mar 22, 2004 at 14:10:42 +0100, > > Erik Thiele <[EMAIL PROTECTED]> wrote: > > > > > > it means (((count_rows(a)+count_rows(b)) modulo 2) == 0) > > > > OK, that means my FK suggestion won't help. The other suggestion about > > putting triggers on "a" and "b" to update a count in another table > > that has a deferred check constraint on it may be your best bet. > > This will be a source of contention, but that may or may not be all > > that important depending on how often you are updating "a" and "b". > > > > there is no such thing as a deferred check constraint: > > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that > is not deferrable will be checked immediately after every command. > Checking of constraints that are deferrable may be postponed until the > end of the transaction (using the SET CONSTRAINTS command). NOT > DEFERRABLE is the default. > > !!! Only foreign key constraints currently accept > this clause. All other constraint types are not deferrable. !!! > > > cu > erik > > -- -Achilleus ---(end of broadcast)--- TIP 8: explain analyze is your friend
