How to retrieve rows with empty value in numeric(12,8) columns
Dear all,
I have a table with more than 10 million rows in a postgresql database.
In the table two columns are of type numeric(12,8) and contains lat lon
of the locations. But in more than thousand rows values are empty.
Below is the snapshot of two rows :-
*"1004364";"MM";"Pye";"ENG";"Town";"2344818";;;"";"";"";"Bago";"Myanmar"*
"1004608";"MM";"Rangoon
Ahlone";"ENG";"Suburb";"1015662";16.78330000;96.11669900;"";"Yangon";"";"Yangon";"Myanmar"
I want to retrieve that rows that have empty lat lon but I am not able
to create a query for that as in character varying columns we retrieve
that rows with '' value, but this time the data type is different.
I checked the below commands but all fails :-
select * from table where lat =''; ---- error
select * from table where lat <=0;----- no rows
Please guide me if there is a proper query for that.
Thanks
select * from table where lat=NULL;
Above query also returns 0 rows.
Thanks
Bèrto ëd Sèra wrote:
Show quoted text
Hi,
haven't checked this personally, but first of all... what if they are
simply stored as NULLs?Bèrto
On 6 December 2011 13:39, Adarsh Sharma <adarsh.sharma@orkash.com
<mailto:adarsh.sharma@orkash.com>> wrote:Dear all,
Import Notes
Reply to msg id not found: CAKwGa_91D9kqD-e4YxdQydmZ5UjEF6AwzYtcM10SxaWdRiPHzg@mail.gmail.com
try
select * from table where lat IS NULL;
Bèrto
:-) It works, Thanks a lot Berto !
can you explain how it works or any link that explain the difference
between 2 queries.
Best regards
Adarsh
Import Notes
Reply to msg id not found: CAKwGa_8Py0LxT6Zp67QfUDPW3FL+0m=abYquaNsT4VVXVDRaKQ@mail.gmail.com
On 06/12/2011 11:00, Adarsh Sharma wrote:
try
select * from table where lat IS NULL;
Bèrto
:-) It works, Thanks a lot Berto !
can you explain how it works or any link that explain the difference
between 2 queries.
As I understand it, your first query returned nothing because NULL is
simply "unknown" - and since you can never know what it's equal to,
looking for equality with NULL will never find anything.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
On 6 December 2011 12:00, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
select * from table where lat IS NULL;
can you explain how it works or any link that explain the difference between
2 queries.
That's because of the 3-valued logic of SQL.
x=NULL always evaluates to NULL, because it is unknown whether the
two might be equal or not.
x IS NULL checks whether x is known (NOT NULL) or not (NULL) and
evaluates to TRUE or FALSE based on that.
WHERE-clauses only return rows where the expression evaluates to TRUE
and not rows where it evaluates to NULL or (obviously) FALSE.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
Hi,
As I understand it, your first query returned nothing because NULL is
simply "unknown" - and since you can never know what it's equal to,
looking for equality with NULL will never find anything.
Yes, you can basically think of NULLs in SQL as having the same role of
zeros in division. They make no sense in principle and must be treated
apart. I seem to recall Oracle treating empty strings as NULLs, but this is
very dangerous. A NULL means nothing has been assigned, i.e. data is not
stated in any way, while an empty string IS an assignment. There is a
difference between "nothing" and "this" (no matter what "this" is).
Bèrto