simple SQL query

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.

Question: does a UNIQUE constraint create an index?

Maybe your fresh eyes will see something obvious.

Many thanks to taking a look at this issue.

KD

SECURITY - contains the list my in-house security list

CREATE TABLE "security"

(

securitykey serial NOT NULL,

securitytypekey integer,

securitydesc character varying(125),

bbcode character(25),

ric character(15),

sedol character(15),

cusip character(12),

isin character(15),

securityissuecurriso character varying(3),

underlyingcusip character varying(15),

ticker character(30),

underlyingisin character varying(15),

expirationdate date,

strikeprice numeric(19,6),

put_call character(1),

multiplier integer,

createdate timestamp without time zone DEFAULT now(),

ccy1isocode character(3),

ccy2isocode character(3),

contractdate date,

fwdrate numeric(15,8),

contract character(25),

contractsize integer,

unitprice numeric(10,6),

underlyingticker character(20),

underlyingbloomberg character(20),

couponrate numeric(15,8),

maturitydate date,

exchangekey integer,

CONSTRAINT pk_security PRIMARY KEY (securitykey),

CONSTRAINT fk_security_securitytype FOREIGN KEY (securitytypekey)

REFERENCES securitytype (securitytypekey) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,

CONSTRAINT unq_security_cusip UNIQUE (cusip, securitytypekey,
securityissuecurriso),

CONSTRAINT unq_security_isin UNIQUE (isin, securitytypekey,
securityissuecurriso)

)

WITHOUT OIDS;

ALTER TABLE "security" OWNER TO postgres;

-- Index: security_bbcode

-- DROP INDEX security_bbcode;

CREATE INDEX security_bbcode

ON "security"

USING btree

(bbcode, securitytypekey);

-- Index: "security_sectype_isoCurr"

-- DROP INDEX "security_sectype_isoCurr";

CREATE INDEX "security_sectype_isoCurr"

ON "security"

USING btree

(securitytypekey, securityissuecurriso);

TMP_INDEX_MEMBER - contains the members of indexes such as S&P 500 and
Russell 1000

CREATE TABLE tmp_index_member

(

tmp_index_member_key serial NOT NULL,

index_key integer,

taskrunkey integer,

isin character(15),

cusip character(12),

sedol character(12),

bbcode character(15),

curr character(5),

bbtype character(20),

secweight numeric(19,6),

securitykey integer,

gics_sector integer,

gics_sector_name character(75),

gics_industry_group integer,

gics_industry_group_name character(75),

gics_industry integer,

gics_industry_name character(75),

bbdesc character(50),

CONSTRAINT pk_tmp_index_member PRIMARY KEY (tmp_index_member_key),

CONSTRAINT fk_tmpindexmember_index_ FOREIGN KEY (index_key)

REFERENCES index_ (index_key) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION,

CONSTRAINT fk_tmpindexmember_taskrun FOREIGN KEY (taskrunkey)

REFERENCES taskrun (taskrunkey) MATCH SIMPLE

ON UPDATE NO ACTION ON DELETE NO ACTION

)

WITHOUT OIDS;

ALTER TABLE tmp_index_member OWNER TO postgres;

Kevin Duffy

WR Capital Management