CODEWITHSUNDEEP
postgresqlsql-insertnot-exists
mudsao
mudsao

Reputation: 13

PostqreSQL Duplicate Values WHERE NOT EXISTS

I made this code:

INSERT into author(authorfirstname1, authorlastname1,authorfirstname2, authorlastname2)
select '".addslashes($_POST['authorfirstname1'])."','".addslashes($_POST['authorlastname1'])."','".addslashes($_POST['authorfirstname2'])."','".addslashes($_POST['authorlastname2'])."'
from author
where not exists(select authorfirstname1, authorlastname1, authorfirstname2, authorlastname2 from author
where author.authorfirstname1='".addslashes($_POST['authorfirstname1'])."'
and author.authorlastname1='".addslashes($_POST['authorlastname1'])."'
and author.authorfirstname2='".addslashes($_POST['authorfirstname2'])."'
and author.authorlastname2='".addslashes($_POST['authorlastname2'])."'
);

The point of this code should be that it checks, if a value allready exists in database and if it doesnt, then it enters it. This '".addslashes($_POST['authorlastname2'])."' represents an input, but could easyly be replaced with '%myentereddata%'

My problem is that it doesnt do anything...doesnt even give error message, its success, but it doesnt enter data if data doesnt exist in db allready and not sure if it stops entering data if data exists.

So I would be thankful if someone could help me to solve the problem with this code or give another example how to do it differently, so that it would work.

My id is primary and serial, so no need to insert it

INSERT INTO author (authorfirstname1, authorlastname1,authorfirstname2, authorlastname2)
VALUES ('one','two','three','four');

Query returned successfully: 1 row affected, 60 ms execution time.

INSERT into author(authorfirstname1, authorlastname1,authorfirstname2, authorlastname2)
select 'one','two','three','four'
from author
where not exists(select authorfirstname1, authorlastname1, authorfirstname2, authorlastname2 from author
where author.authorfirstname1='one'
and author.authorlastname1='two'
and author.authorfirstname2='three'
and author.authorlastname2='four'
);

Query returned successfully: 657 rows affected, 40 ms execution time.

INSERT into author(authorfirstname1, authorlastname1,authorfirstname2, authorlastname2)
select 'new','new','new','new'
from author
where not exists(select authorfirstname1, authorlastname1, authorfirstname2, authorlastname2 from author
where author.authorfirstname1='new'
and author.authorlastname1='new'
and author.authorfirstname2='new'
and author.authorlastname2='new'
);

Query returned successfully: 1314 rows affected, 70 ms execution time.

Upvotes: 1

Views: 490

Answers (2)

Daniel Vérité
Daniel Vérité

Reputation: 61686

The problem is that there are two FROM clauses instead of one. You are in effect inserting the same row as many times as there are rows in the entire table (when the WHERE clause is satisfied). The query should be (note that the first FROM has been removed compared to your version):

INSERT into author(authorfirstname1, authorlastname1,authorfirstname2, authorlastname2)
select 'one','two','three','four'
where not exists(select 1 from author
where author.authorfirstname1='one'
and author.authorlastname1='two'
and author.authorfirstname2='three'
and author.authorlastname2='four'
);

The columns in the inner select have also been removed. SELECT 1 FROM... is good enough to check if a row exists, there's no need to extract specific columns, they're going to be discarded on the upper level anyway.

Another unrelated problem is that the escaping done with addslashes will produce an invalid query as soon as any injected parameter contain a quote character.

This is because when PG's standard_conforming_strings is set to ON, the backslash is a normal character that doesn't escape anything. Since PostgreSQL 9.1, it's ON by default.

Use pg_escape_string instead.

Upvotes: 1

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

In case you just want to get sure that there are no duplicate entries in the table, you should use UNIQUE.

That way whenever you INSERT a line that is already there, you will get an error.

Like this:

CREATE UNIQUE INDEX ON author (authorfirstname1, authorlastname1, authorfirstname2, authorlastname2);

Afterwards you will not be able to INSERT the same set of firstnames and lastnames twice.

You then INSERT like this:

INSERT into author(authorfirstname1, authorlastname1,authorfirstname2, authorlastname2)
VALUES ('".addslashes($_POST['authorfirstname1'])."', '".addslashes($_POST['authorlastname1'])."', '".addslashes($_POST['authorfirstname2'])."', '".addslashes($_POST['authorlastname2'])."')

Upvotes: 0

Related Questions