Re: [GENERAL] How to specify infinity for intervals ?
OK, added to TODO. --- Michael Glaesemann wrote: On Feb 25, 2006, at 12:09 , Bruce Momjian wrote: We have this TODO: o Allow infinite dates just like infinite timestamps Do we need to add intervals to this? I think to be consistent with the other datetime types, might as well. Then one would be able to get an answer to test=# select 'infinity'::timestamp - current_timestamp; ERROR: cannot subtract infinite timestamps Michael Glaesemann grzm myrealbox com ---(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 -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(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: [GENERAL] How to specify infinity for intervals ?
On Fri, Feb 24, 2006 at 10:09:25PM -0500, Bruce Momjian wrote: Karsten Hilbert wrote: I will also stay with the hope that one day before long we will have 'infinite'::interval. We have this TODO: o Allow infinite dates just like infinite timestamps Do we need to add intervals to this? Yes. Thanks. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to specify infinity for intervals ?
Karsten Hilbert wrote: Thanks to all for the suggestions. For the time being I will stay with using NULL. I will also stay with the hope that one day before long we will have 'infinite'::interval. We have this TODO: o Allow infinite dates just like infinite timestamps Do we need to add intervals to this? -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(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
Re: [GENERAL] How to specify infinity for intervals ?
On Feb 25, 2006, at 12:09 , Bruce Momjian wrote: We have this TODO: o Allow infinite dates just like infinite timestamps Do we need to add intervals to this? I think to be consistent with the other datetime types, might as well. Then one would be able to get an answer to test=# select 'infinity'::timestamp - current_timestamp; ERROR: cannot subtract infinite timestamps Michael Glaesemann grzm myrealbox com ---(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
Re: [GENERAL] How to specify infinity for intervals ?
Thanks to all for the suggestions. For the time being I will stay with using NULL. I will also stay with the hope that one day before long we will have 'infinite'::interval. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to specify infinity for intervals ?
On Mon, Feb 20, 2006 at 07:24:05PM +0100, Karsten Hilbert wrote: I am storing the maximum age a vaccination is due in a patient (eg. don't give this vaccination beyond the age of 10 years or some such). Some vaccinations are to be given regardless of age. Up to now I have used NULL to mean no maximum age. That doesn't really feel right and also complicates the SQL needed for retrieving data. I *could*, of course, use something like '999 years' as a special value to indicate no upper limit figuring that no one is going to live that long in the foreseeable future. However, the technically elegant and satisfying solution would be to be able to use infinite with interval data types much like infinity with timestamps. I have tried various syntax attempts, calculations and casts but haven't found any returning an interval of infinite length. The docs and Google don't help, either. I suspect that you could create either a domain or a custom type that would handle this they way you wanted. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to specify infinity for intervals ?
On Tue, Feb 21, 2006 at 12:24:09PM +0900, Michael Glaesemann wrote: I don't know the details of your database schema, If you want to you can look it up here: http://salaam.homeunix.com/twiki/bin/view/Gnumed/DatabaseSchema Feel free to comment ! but I think the relationally proper way to do would be to have a separate table for the maximum ages for vaccinations that have them. I know, but, oh no, yet another table ;-( And it would also just lead to another form of NULL via left joins as you point out below. I specifically wanted to avoid that by something like 'infinite'::interval in some way or other such that I could always do ... where now date_of_birth + max_age ... and not need an ... or max_age is null ... in all the places. Vaccinations that *don't* have a maximum age would not have an entry in the table. As you notice further down my predicate was wrong, actually. You found the proper predicate by yourself, though: Do not care about the age of the patient when deciding whether to give this vaccination. The special value method, e.g., 999 years is another way of indicated a special value, but in this case I think it's a bit different. As I see it, the predicate for the vaccination_max_ages table is The vaccination 'vaccination' must be given before the patient is 'maximum_age'. Using a special value changes this predicate to The vaccination 'vaccination' can be given at any time in the patient's life. As you point out, using a sufficiently large interval for maximum_age makes that statement very likely to be true, but the predicate is not exactly the same. Not having an entry in vaccination_max_ages is much closer to the idea that the vaccination has no maximum age. Well, but there's not really a medical difference between the two AFAICT. That's the theory, anyway. Hope this helps a bit. It confirms my thinking isn't entirely wrong. Currently on the todo list there's a mention of adding infinite dates, similar to infinite timestamps. Perhaps infinite intervals could be added as well? I'd be delighted to have that happen. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to specify infinity for intervals ?
On Feb 22, 2006, at 1:51 , Karsten Hilbert wrote: I specifically wanted to avoid that by something like 'infinite'::interval in some way or other such that I could always do ... where now date_of_birth + max_age ... and not need an ... or max_age is null ... in all the places. I'd wrap it in an SQL function (untested): create function ok_to_vaccinate(date, interval) returns boolean language sql as' select current_timestamp $1 + $2 or $2 is null '; Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to specify infinity for intervals ?
I am storing the maximum age a vaccination is due in a patient (eg. don't give this vaccination beyond the age of 10 years or some such). Some vaccinations are to be given regardless of age. Up to now I have used NULL to mean no maximum age. That doesn't really feel right and also complicates the SQL needed for retrieving data. I *could*, of course, use something like '999 years' as a special value to indicate no upper limit figuring that no one is going to live that long in the foreseeable future. However, the technically elegant and satisfying solution would be to be able to use infinite with interval data types much like infinity with timestamps. I have tried various syntax attempts, calculations and casts but haven't found any returning an interval of infinite length. The docs and Google don't help, either. I am running 7.4.9 on Debian 4.0. Anyone wants to comment/suggest something ? Thanks, Karsten GNUmed developer -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(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: [GENERAL] How to specify infinity for intervals ?
On Feb 21, 2006, at 3:24 , Karsten Hilbert wrote: I am storing the maximum age a vaccination is due in a patient (eg. don't give this vaccination beyond the age of 10 years or some such). Some vaccinations are to be given regardless of age. Up to now I have used NULL to mean no maximum age. That doesn't really feel right and also complicates the SQL needed for retrieving data. I don't know the details of your database schema, but I think the relationally proper way to do would be to have a separate table for the maximum ages for vaccinations that have them. Vaccinations that *don't* have a maximum age would not have an entry in the table. For example: create table vaccinations ( vaccination text primary key ); create table vaccination_max_ages ( vaccination text primary key references vaccinations (vaccination) , maximum_age interval not null ); This may make the SQL a bit more complicated, and you may end up doing quite a few left joins which will give you NULLs anyway in the result unless you use COALESCE. From one perspective (though not one I necessarily agree with), using NULL to represent no maximum age in the vaccinations table is a shorthand for this situation and reduces the number of joins required. The special value method, e.g., 999 years is another way of indicated a special value, but in this case I think it's a bit different. As I see it, the predicate for the vaccination_max_ages table is The vaccination 'vaccination' must be given before the patient is 'maximum_age'. Using a special value changes this predicate to The vaccination 'vaccination' can be given at any time in the patient's life. As you point out, using a sufficiently large interval for maximum_age makes that statement very likely to be true, but the predicate is not exactly the same. Not having an entry in vaccination_max_ages is much closer to the idea that the vaccination has no maximum age. That's the theory, anyway. Hope this helps a bit. Currently on the todo list there's a mention of adding infinite dates, similar to infinite timestamps. Perhaps infinite intervals could be added as well? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
