Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Some illogical query results

Status
Not open for further replies.
dmg2206

dmg2206

MIS
Joined
Feb 15, 2002
Messages
54
Location
US
I've gotten the following:

Code: 
db=# select count(*) from table1 where field1 in (select field2 from table2);
 count
-------
   175
(1 row)

db=# select count(*) from table1 where field1 not in (select field2 from table2);
 count
-------
     0
(1 row)

db=# select count(*) from table1;
 count
-------
   955
(1 row)

OK, isn't this mathematically impossible? Any idea what's going on here?
 
Sort by date Sort by votes
Yes, most likely you have some NULLs involved here. Aggregate functions such as COUNT(*) cannot perform certain kinds of logic with a NULL, because NULL doesn't mean "no value"; in relational query terms it means "we don't know if there is a value", thus it can't logically tell whether field1 does or does not match field2. This is why many serious DB practitioners will caution you to avoid creating columns that allow NULLs unless you have a specific reason to do so. -------------------------------------------

Big Brother: "War is Peace" -- Big Business: "Trust is Suspicion"
(
 
Upvote 0 Downvote
Now I've got a new problem. I've set table2 to mirror table1 in structure, and now I've dumped new data into table2. I'm trying to insert all the data from table2 into table1, but I keep getting this error:

Code: 
ERROR:  Cannot insert a duplicate key into unique index indx_unique_table1_field

indx_unique_table1_field is a unique index I set up on the column "field" in table1. However, when I search for these duplicate values, I get the following:

Code: 
select field from table2 where field in (select field from table1);
 field
-------
(0 rows)

There are no null values in either of the columns in question.
 
Upvote 0 Downvote
Here's the two tables:
Code: 
CREATE TABLE "tbclients" (
	"id" integer DEFAULT nextval('"seq_tbclients_id"'::text) NOT NULL,
	"ssn" character varying(11),
	"firstname" character varying(20),
	"lastname" character varying(30),
	"street" character varying(50),
	"city" character varying(25),
	"state" character varying(2) DEFAULT 'IN',
	"zipcode" integer,
	"phone" character varying(14),
	"dob" date,
	"sex" character varying(6),
	"township" character varying(20),
	"veteran" boolean,
	"ethnicity" character varying(20),
	"maritalstatus" character varying(20),
	"residentdate" date,
	"church" character varying(50),
	"insurance" character varying(50),
	"resident" character varying(15),
	"homeless" boolean,
	Constraint "tbclients_pkey" Primary Key ("id")
);


CREATE TABLE "ccsnclients" (
	"id" integer DEFAULT nextval('"seq_ccsnclients_id"'::text) NOT NULL,
	"ssn" character varying(11),
	"firstname" character varying(20),
	"lastname" character varying(30),
	"street" character varying(50),
	"city" character varying(25),
	"state" character varying(2) DEFAULT 'IN',
	"zipcode" integer,
	"phone" character varying(14),
	"dob" date,
	"sex" character varying(6),
	"township" character varying(20),
	"veteran" boolean,
	"ethnicity" character varying(20),
	"maritalstatus" character varying(20),
	"residentdate" date,
	"church" character varying(50),
	"insurance" character varying(50),
	"resident" character varying(15),
	"homeless" boolean,
	Constraint "ccsnclients_pkey" Primary Key ("id")
);

I'm trying to insert data from ccsnclients into tbclients. I use the SQL command

Code: 
insert into tbclients (select * from ccsnclients);

and get the error message

Code: 
ERROR:  Cannot insert a duplicate key into unique index indx_unique_tbclients_ssn

 
Upvote 0 Downvote
Never mind, I've discovered the problem. The "ccsnclients" table had some duplicate values in the ssn column. I thought I had taken care of that, but apparently when we made the last update those duplicate values reappeared.

"Don't try this at home. This should only be done by trained professional idiots." :-b
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

beakerboy
  • Locked
  • Question Question
Query from tables sharing primary keys
Replies
0
Views
469
beakerboy
beakerboy
LD50
  • Locked
  • Question Question
psql query output
Replies
1
Views
522
feherke
feherke
emotionuk
  • Locked
  • Question Question
SQL Query/logic help
Replies
0
Views
512
emotionuk
emotionuk
AP81
  • Locked
  • Question Question
using COPY command with SQL query
Replies
1
Views
329
feherke
feherke
rokkena
  • Locked
  • Question Question
Query runs out of memory
Replies
1
Views
297
feherke
feherke

Part and Inventory Search

Sponsor

Back
Top