Re: simple SQL query - Mailing list pgsql-sql

On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote:
> Hello:
>
>
>
> I have a couple of queries that are giving me headaches.
>
> They are actually very simple, but I do not understand why
>
> I am not getting the expected results.  Maybe I need new glasses.
>
> Please be kind.
>
>
>
> The table definitions are below.
>
>
>
> The table TMP_INDEX_MEMBER contains 21057 rows.
>
> These rows contain 3167 distinct ISINs.  ISIN is a type of unique
> security identifier.
>
>
>
> This query
>
> select * from security
>
>    where securitytypekey NOT IN ( 5,27) and  ISIN IN
>
>   (select ISIN from tmp_index_member )
>
> returns 3069 rows.  This tells me that there are 3069 ISINs
>
> in the SECURITY table. ISINs that I already know about.
>
>
>
>
>
> update tmp_index_member set securitykey = security.securitykey
>
>    from security
>
>    where securitytypekey NOT IN (5,27)  and tmp_index_member.ISIN =
> security.ISIN
>
> results in Query returned successfully: 20545 rows affected, 2169 ms
> execution time.
>
>
>
> There are now 512 row in TMP_INDEX_MEMBER that have not been updated.
>
> OK now the confusion begins.
>
>
>
> I would expect the following query to return 512 rows.  It returns zero.
>
> select * from tmp_index_member tim
>
>   where tim.ISIN  NOT IN
>
>   (select distinct sec.ISIN from security sec where securitytypekey NOT
> IN ( 5,27) )
>
>
>
>
>
> I want to add to SECURITY the securities that are new to me.  To do this
> I need the above
>
> query to work.

I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like:
... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))...

> Question:  does a UNIQUE constraint create an index?

Yes.

> Maybe your fresh eyes will see something obvious.
>
> Many thanks to taking a look at this issue.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+