Re: Issue with NULL varchars

antony baxter wrote:
> I'm relatively knew to PostgreSQL, but have a fair amount of
> experience with JDBC and Oracle/SQL Server.
>
> I'm running PostgreSQL 8.3.0 on Mac OSX 10.5.2, am using the 8.3-603
> JDBC drivers, and using Java 1.5.0_13.
>
> I'm a bit confused by the results of the following test case code:

That is surprising, because the behaviour is the same on Oracle.
I ran your program there.
Can't say about SQL Server.

[...]
> // Retrieve that Locale's ID by its Data:
> p = c.prepareStatement("SELECT COUNT(*) FROM testing WHERE variant = ?");
> p.setNull(1, java.sql.Types.VARCHAR); // or p.setString(1, variant);
> rs = p.executeQuery();
> while (rs.next())
> {
> System.out.println("SELECT COUNT: count=" + rs.getInt(1));
> }
> rs.close();
[...]

> The output, when I run this, is
[...]
> SELECT COUNT: count=0
>
> which implies that the table is created, the row is inserted, the row
> is retrieved when selecting by its Id, but when we try and search for
> anything with a NULL varchar value, nothing is returned.
>
> I've also tried swapping the setNull statements with setString(n, null) - same result.
>
> What am I missing?!

The fact that "NULL = NULL" evaluates to NULL and not to TRUE.

You can run the statement from the command line, without JDBC, and you will
get the same result.

Maybe you wanted to use:

SELECT COUNT(*) FROM testing WHERE variant IS NULL;

which would return a nonzero result.

Yours,
Laurenz Albe