Preventing and reporting duplicate records in a table

[clay1]

I have a script which takes a file of customers and inserts the records into my postgres table.

 

The email column in my table is set as a unique key, as I only want new records inserted into the table.

 

If the file has a duplicate email-- how can I get a report of which records are excluded?

 

Currently I get this error:

 

Query failed: ERROR: duplicate key value violates unique constraint "leads_e-mail_key"

 

I'd like to suppress that and instead display which records already existed

[halfman]

I have a script which takes a file of customers and inserts the records into my postgres table.

 

The email column in my table is set as a unique key, as I only want new records inserted into the table.

 

If the file has a duplicate email-- how can I get a report of which records are excluded?

 

Currently I get this error:

 

Query failed: ERROR: duplicate key value violates unique constraint "leads_e-mail_key"

 

I'd like to suppress that and instead display which records already existed

Just write SELECT sql and get the duplication. make a proper error dump.

[clay1]

I don't understand your answer-- could you explain?

 

[halfman]

Can you first check the DB for the similar email if it exsit just generate a report and enter the report in a text file or somewhere in databse if not insert the email.

[clay1]

Thanks for the tip

 

I've got it working using this code.. is there anything I could do better here?

 

						$email = $line[$emailkey];
					$checkdupes = "SELECT * from \"leads\" WHERE \"e-mail\" = '{$email}'";
					$emailresult = pg_query($conn, $checkdupes);
					if ($dupes = pg_fetch_row($emailresult)){
						echo 'Line: ' . $row . ' ' . $line[$namekey] . ' ' . $line[$emailkey] . ' is a duplicate<br><br>';
					$rowsinserted--;
					}
						elseif(empty($email)){
						echo 'Line: ' . $row . ' ' . $line[$namekey] . ' ' . $line[$emailkey] . ' has no email address<br><br>';
					$rowsinserted--;
					}
					elseif(empty($telephone)){
						echo 'Line: ' . $row . ' ' . $line[$namekey] . ' ' . $line[$emailkey] . ' has no phone number<br><br>';
					$rowsinserted--;
					}

					else {
							if ($age < 25){
						echo 'Line: ' . $row . ' ' . $line[$namekey] . ' ' . $line[$emailkey] . ' is a under 25 years old<br><br>';
					$rowsinserted--;
					}
							$status = insertRow('leads', $vars, $nulls, $format, $types);