On: Thu, 12 Mar 2009 18:39:27 -0400, A.M.
age...@themactionfaction.com wrote:
The one problem I foresee is that changes to the commodity_tax_rates
table may not reflect in transaction dates that have passed. What
happens if a tax is retroactively ended or applied outside these
barriers? Is
I am confronting a design decision involving null values and I
cannot seem to discern which way to go. Therefore, I would like
some commentary based on real world experience.
The basic issue is episodic duration, expressed as columns named
dt_effective_from and dt_superseded_after. Both are
James B. Byrne wrote:
The basic issue is episodic duration, expressed as columns named
dt_effective_from and dt_superseded_after. Both are datetime types
containing values normalized to utc. You see where this is going.
The issue is what to enter when the value is known to be unknown, as
Both ways will work.
Setting the superseded_after to a far off future will work but will have
to set to a real date when it actual does become superseded.
The same is true for nulls It boils down to how you and the users want
to look at the data.
To me to get the all the records that has
In response to James B. Byrne byrn...@harte-lyne.ca:
The basic issue is episodic duration, expressed as columns named
dt_effective_from and dt_superseded_after. Both are datetime types
containing values normalized to utc. You see where this is going.
The issue is what to enter when the
On Thu, 2009-03-12 at 11:32 -0400, James B. Byrne wrote:
The basic issue is episodic duration, expressed as columns named
dt_effective_from and dt_superseded_after. Both are datetime types
containing values normalized to utc. You see where this is going.
I assume you're concern is NULL for
On Thu, March 12, 2009 12:00, Richard Huxton wrote:
James B. Byrne wrote:
The basic issue is episodic duration, expressed as columns named
dt_effective_from and dt_superseded_after. Both are datetime
types containing values normalized to utc.
If it's unknown use null.
in some
On Mar 12, 2009, at 5:50 PM, James B. Byrne wrote:
...
and c.date_effective_from = transaction_date
and c.date_superseded_after = transaction_date
Have I understood things aright?
The one problem I foresee is that changes to the commodity_tax_rates
table may not reflect in
Richard Huxton wrote:
PFC wrote:
NULL usually means unknown or not applicable
Andrew Sullivan wrote:
Aaaargh! No, it doesn't. It means NULL. Nothing else.
If it meant unknown or not applicable or anything else, then
SELECT * FROM nulltbl a, othernulltbl b
WHERE a.nullcol =
Lew [EMAIL PROTECTED] writes:
Where NULL differs is that (NULL = NULL) is FALSE, and (NULL != NULL) is
FALSE.
No, that's not true. NULL=NULL is NULL. And NULL!=NULL is NULL as well. Ie,
it's exactly as your table describes.
The confusion comes because WHERE clauses treat NULL the same as
Scott Ribe wrote:
where a b or (a is null and b is not null) or (a is not null and
b is null)
In the absence of IS DISTINCT FROM, I think this has the same semantics:
where coalesce(a, b) coalesce(b, a)
although it's not as concise as one might wish.
- John D. Burger
MITRE
On þri, 2006-11-28 at 09:42 -0500, John D. Burger wrote:
Scott Ribe wrote:
where a b or (a is null and b is not null) or (a is not null and
b is null)
In the absence of IS DISTINCT FROM, I think this has the same semantics:
where coalesce(a, b) coalesce(b, a)
sorry, but no.
where a b or (a is null and b is not null) or (a is not null
and b is null)
In the absence of IS DISTINCT FROM, I think this has the same
semantics:
where coalesce(a, b) coalesce(b, a)
sorry, but no.
Argh, my expression is just nonsense - I was thinking of something like:
where a b or (a is null and b is not null) or (a is not null and
b is null)
In the absence of IS DISTINCT FROM, I think this has the same semantics:
where coalesce(a, b) coalesce(b, a)
sorry, but no.
So it would have to be where coalesce(a, b, 0) coalesce(b, a, 0) for your
On þri, 2006-11-28 at 12:28 -0700, Scott Ribe wrote:
where a b or (a is null and b is not null) or (a is not null and
b is null)
In the absence of IS DISTINCT FROM, I think this has the same semantics:
where coalesce(a, b) coalesce(b, a)
sorry, but no.
So it would have
no cigar.
Well, duh. Showing why IS DISTINCT FROM is useful.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
(Can we talk about NULL next? :P)
Seriously though, there is one thing I've been meaning to bring up. I
understand why NULLs compare the way they do in queries, and that's fine.
But there are times when I need to query what would be described in
relational terms as not known to be equal, and
On Nov 28, 2006, at 9:37 , Scott Ribe wrote:
(Can we talk about NULL next? :P)
Seriously though, there is one thing I've been meaning to bring up. I
understand why NULLs compare the way they do in queries, and that's
fine.
But there are times when I need to query what would be described
Scott Ribe [EMAIL PROTECTED] writes:
But there are times when I need to query what would be described in
relational terms as not known to be equal, and
where a b or (a is null and b is not null) or (a is not null and b is
null)
IS DISTINCT FROM ?
regards, tom lane
On Mon, 2006-11-27 at 17:37 -0700, Scott Ribe wrote:
(Can we talk about NULL next? :P)
Oh bring it on! :)
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive
Check out IS DISTINCT FROM
http://www.postgresql.org/docs/current/interactive/functions-
comparison.html
I think that will help you.
It's exactly what I was asking for.
Well, except for the deja-vu feeling where I'm wondering if I didn't read
that a long time ago and then forget it when
--- Scott Ribe [EMAIL PROTECTED] wrote:
(Can we talk about NULL next? :P)
Seriously though, there is one thing I've been meaning to bring up. I
understand why NULLs compare the way they do in queries, and that's fine.
But there are times when I need to query what would be described in
how about
SELECT *
FROM
YOURTABLE
where
( a = b ) IN UNKNOWN;
Well, actually, it would be:
a = b or (a = b) is unknown
But that certainly would more concise.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end
Scott Ribe [EMAIL PROTECTED] writes:
Well, actually, it would be:
a = b or (a = b) is unknown
But that certainly would more concise.
But
regression=# select (null = null) is unknown;
?column?
--
t
(1 row)
which I think is not what you wanted.
regards, tom
But
regression=# select (null = null) is unknown;
?column?
--
t
(1 row)
which I think is not what you wanted.
Right.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice
---(end of broadcast)---
Thanks, that looks useful. I'll investigate once I've got the servers upgraded
and replication running.
Bruno Wolff III wrote:
On Wed, Jul 13, 2005 at 18:15:12 +,
[EMAIL PROTECTED] wrote:
Many thanks Tom. Inconvenient from the point of view of the application
but still useful
On Wed, Jul 13, 2005 at 18:15:12 +,
[EMAIL PROTECTED] wrote:
Many thanks Tom. Inconvenient from the point of view of the application but
still useful information.
The situation is that I've got a query with numerous subselects, each of which
has to return exactly one row so I was doing
Where does PostgreSQL rank nulls when sorting a column of timestamps, is this
behaviour deterministic, and can I rely on it not changing in the future?
Apologies if this shows up as a repost, I've had gateway problems at this end.
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[EMAIL PROTECTED] writes:
Where does PostgreSQL rank nulls when sorting a column of timestamps, is this
behaviour deterministic, and can I rely on it not changing in the future?
Nulls sort high (in any datatype, not only timestamps). It's possible
that we'd offer an option to make them sort
Many thanks Tom. Inconvenient from the point of view of the application but
still useful information.
The situation is that I've got a query with numerous subselects, each of which
has to return exactly one row so I was doing a union with a nulled record then
selecting the most recent: obviously
On Wed, 2005-07-13 at 12:41, Tom Lane wrote:
[EMAIL PROTECTED] writes:
Where does PostgreSQL rank nulls when sorting a column of timestamps, is
this
behaviour deterministic, and can I rely on it not changing in the future?
Nulls sort high (in any datatype, not only timestamps). It's
Scott Marlowe [EMAIL PROTECTED] writes:
On Wed, 2005-07-13 at 12:41, Tom Lane wrote:
Nulls sort high (in any datatype, not only timestamps). It's possible
that we'd offer an option to make them sort low in the future, but I
can't imagine that we'd change the default behavior.
Isn't this
Scott Marlowe wrote:
On Wed, 2005-07-13 at 12:41, Tom Lane wrote:
[EMAIL PROTECTED] writes:
Where does PostgreSQL rank nulls when sorting a column of timestamps,
is this behaviour deterministic, and can I rely on it not changing in
the future?
Nulls sort high (in any datatype,
Tom Lane wrote:
According to the SQL spec it's implementation defined, which means
different DBs could do it differently but they have to tell you what
they will do. Implementation dependent effectively means the
behavior is not specified at all.
One problem is that even if the server is
/LurkingMode
NewbieMode
Could some kind soul explain this to me ?
test1=# select distinct "Cle" from "Utilisateurs";
Cle
-
1
2
3
4
(4 rows)
test1=# select distinct "CleUtil" from "Histoires";
CleUtil
-
1
(2 rows) -- Uuhhh !
test1=# select count(*) as NbRec from "Histoires" where
On Fri, 22 Dec 2000, Emmanuel Charpentier,,, wrote:
Could some kind soul explain this to me ?
test1=# select distinct "Cle" from "Utilisateurs";
Cle
-
1
2
3
4
(4 rows)
test1=# select distinct "CleUtil" from "Histoires";
CleUtil
-
1
(2 rows) -- Uuhhh !
test1=#
At 10:53 +0300 on 13/8/98, Aleksey Dashevsky wrote:
(note, that each NULL is treated as new value, I mean one NULL is
note equal to another one!)
Hey, wait a second. Wasn't this supposed to be fixed in 6.3?
I really hate the way nulls are treated in 6.2.1, and I'm pushing my
sysadmin hard
37 matches
Mail list logo
