473,834 Members | 1,650 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Type conversions and nulls

Howdy all!

Just checking on whether this is the expected behaviour. I am transferring
data from multiple databases to single one, and I want to ensure that I
only have unique rows for some tables. Unfortunately, some of the rows
have nulls for various columns, and I want to compare them for exact
equality.

=> create table tmp (
bigint a,
bigint b,
primary key (a, b)
);

To test for existence, I would naively use:

=> select count(1) from tmp
where a = <value>
and b = <value>;

What I should use is:

=> select count(1) from tmp
where ((a = <value>) or (a is null and <value> is null))
and ((b = <value>) or (b is null and <value> is null));

Looking in the manual, I see I can get what I want by running:

=> set transform_null_ equals to on;

And I can go back to using my naive script and everything works.

However, as <values> are integers, I need to convert them to bigint's so
that the index can be used (Postgresql 7.4.2 automatic casts, unless this
has been fixed). So I wrote my script to do the following

=> select count(1) from tmp
where a = <value>::bigi nt
and b = <value>::bigint ;

And now the nulls don't match! As a further test, I did:

=> select null = null, null = null::bigint, null::bigint = null::bigint;
?column? | ?column? | ?column?
----------+----------+----------
t | t |
(1 row)

So, is there a way to do the casts such that this works? Other
alternatives? I did a search but couldn't find an answer on the archives.

Regards!
Ed

---------------------------(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

Nov 23 '05 #1
4 2534
On Tue, 11 May 2004, Edmund Dengler wrote:
Just checking on whether this is the expected behaviour. I am transferring
data from multiple databases to single one, and I want to ensure that I
only have unique rows for some tables. Unfortunately, some of the rows
have nulls for various columns, and I want to compare them for exact
equality.

=> create table tmp (
bigint a,
bigint b,
primary key (a, b)
);

To test for existence, I would naively use:

=> select count(1) from tmp
where a = <value>
and b = <value>;

What I should use is:

=> select count(1) from tmp
where ((a = <value>) or (a is null and <value> is null))
and ((b = <value>) or (b is null and <value> is null));

Looking in the manual, I see I can get what I want by running:

=> set transform_null_ equals to on;

And I can go back to using my naive script and everything works.

However, as <values> are integers, I need to convert them to bigint's so
that the index can be used (Postgresql 7.4.2 automatic casts, unless this
has been fixed). So I wrote my script to do the following

=> select count(1) from tmp
where a = <value>::bigi nt
and b = <value>::bigint ;

And now the nulls don't match! As a further test, I did:

=> select null = null, null = null::bigint, null::bigint = null::bigint;
?column? | ?column? | ?column?
----------+----------+----------
t | t |
(1 row)

So, is there a way to do the casts such that this works? Other
alternatives? I did a search but couldn't find an answer on the archives.


Use the full version or if you're generating the query strings on the fly
put only the check that matters (either the equality or the is null as
appropriate for the values). Or if all you care about is true/false,
possibly
select exists(select 1 from tmp where ...)
may be better.

Transform_null_ equals is meant convert a very specific x = NULL or NULL =
x into x IS NULL. It doesn't (nor is it meant to) change how nulls
compare.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #2
Ahh, thanks. So it is simply a syntactic transform, and not really a
"proper" internal comparison operator change.

Regards,
Ed

On Tue, 11 May 2004, Stephan Szabo wrote:
Use the full version or if you're generating the query strings on the fly
put only the check that matters (either the equality or the is null as
appropriate for the values). Or if all you care about is true/false,
possibly
select exists(select 1 from tmp where ...)
may be better.

Transform_null_ equals is meant convert a very specific x = NULL or NULL =
x into x IS NULL. It doesn't (nor is it meant to) change how nulls
compare.


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
I think coalesce may help you here.

On Tue, 11 May 2004, Edmund Dengler wrote:
Howdy all!

Just checking on whether this is the expected behaviour. I am transferring
data from multiple databases to single one, and I want to ensure that I
only have unique rows for some tables. Unfortunately, some of the rows
have nulls for various columns, and I want to compare them for exact
equality.

=> create table tmp (
bigint a,
bigint b,
primary key (a, b)
);

To test for existence, I would naively use:

=> select count(1) from tmp
where a = <value>
and b = <value>;

What I should use is:

=> select count(1) from tmp
where ((a = <value>) or (a is null and <value> is null))
and ((b = <value>) or (b is null and <value> is null));

Looking in the manual, I see I can get what I want by running:

=> set transform_null_ equals to on;

And I can go back to using my naive script and everything works.

However, as <values> are integers, I need to convert them to bigint's so
that the index can be used (Postgresql 7.4.2 automatic casts, unless this
has been fixed). So I wrote my script to do the following

=> select count(1) from tmp
where a = <value>::bigi nt
and b = <value>::bigint ;

And now the nulls don't match! As a further test, I did:

=> select null = null, null = null::bigint, null::bigint = null::bigint;
?column? | ?column? | ?column?
----------+----------+----------
t | t |
(1 row)

So, is there a way to do the casts such that this works? Other
alternatives? I did a search but couldn't find an answer on the archives.

Regards!
Ed

---------------------------(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

Nov 23 '05 #4
Edmund Dengler <ed*****@eSenti re.com> writes:
What I want is an '=' that compares nulls as equal (rather than as
not-equal, which is the normal case).


IS DISTINCT FROM may help you here. It's a not-equals operator rather
than an equals operator, but it does what you want.

(Unless what you wanted included being able to use an index...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
2253
by: Rade | last post by:
Following a discussion on another thread here... I have tried to understand what is actually standardized in C++ regarding the representing of integers (signed and unsigned) and their conversions. The reference should be 3.9.1 (Fundamental types), and 4.7 (Integral conversions). It seems to me that the Standard doesn't specify: 1) The "value representation" of any of these types, except that (3.9.1/3) "... The range of nonnegative...
15
2305
by: buda | last post by:
Let me see if I got this :) 1. I know the rules for type conversions in arithmetic expressions 2. I know that an implicit type conversion is done at assignment, so float x = 1.23; int t = (int) x; is equivalent to int t = x; (could the latter produce a warning on some complier?) 3. I know that implicit conversions take place with function arguments, but am a bit shaky here. I suppose that passing a char to a function accepting
51
4576
by: jacob navia | last post by:
I would like to add at the beginning of the C tutorial I am writing a short blurb about what "types" are. I came up with the following text. Please can you comment? Did I miss something? Is there something wrong in there? -------------------------------------------------------------------- Types A type is a definition for a sequence of storage bits. It gives the meaning of the data stored in memory. If we say that the object a is an
2
12360
by: Sam Sungshik Kong | last post by:
Hello! I studied C# a little bit and am trying to compare it with VB.Net. There's 'Option Strict' in VB.Net. I thought that if I turn it on, it is as strict as C# when checking types. See the following codes. <vb.net>
3
21225
by: mra | last post by:
I want to cast an object that I have created from a typename to the corresponding type. Can anycone tell me how to do this? Example: //Here, Create the object of type "MyClass" object obj=Activator.CreateInstance(strAssemblyName, "MyClass"); //Now, I want to do something like ((MyClass)obj).Method //Can I do this?
16
12804
by: Enekajmer | last post by:
Hi, 1 int main() 2 { 3 float a = 17.5; 4 printf("%d\n", a); 5 printf("%d\n", *(int *)&a); 6 return 0; 7 }
16
11293
by: Martin Jørgensen | last post by:
Hi, Short question: Any particular reason for why I'm getting a warning here: (cast from function call of type int to non-matching type double) xdouble = (double)rand()/(double)RAND_MAX;
5
7831
by: Akaketwa | last post by:
guys help me on this rather nubish question.I have created a new data type in java and i also want to create a new data type object in db2 that will store the money data object. The money implementation takes 2 arguments and returns. I hava read abt user defined structured data types but i seem not to understand their work. anyone pliz help. my db is \ DB2 administration tools level:
23
9810
by: neha_chhatre | last post by:
which is the best format specifier(data type) if i have to work with decimal number. also please tell me the syntax for truncating a decimal number please reply as soon as possible
0
9797
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9644
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10793
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10547
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10219
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7757
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6954
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4427
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3977
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.