This post shows an example of the MySQL INSERT IGNORE statement in the PostgreSQL version. There are good reasons why you should use PostgreSQL at the onset or migrate to it as soon as possible. One reason is you cannot distribute MySQL binaries to your customers along with your application. These binaries include the MySQL Connector/J, which is a JDBC Driver. Whereas for PostgreSQL, you can.
MySQL INSERT IGNORE Statement
Consider the following codes. They create a table person with a primary key and insert two records into it. Notice the insert statement. It uses the MySQL IGNORE keyword along with INSERT. As a result, when we insert rows with already existing or the same person_id, MySQL skips those rows and doesn’t insert them into the table.
1 2 3 4 5 6 7 8 | CREATE TABLE `person` ( `person_id` int(11) NOT NULL, `person_name` varchar(45) DEFAULT NULL, PRIMARY KEY (`person_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert ignore into person values(1, "Karl"), (1, "Lei"); |
Therefore, this is how we ignore insert in MySQL on key conflicts, e.g., duplicate keys. Now, how about PostgreSQL? Read on.
PostgreSQL Equivalent
PostgreSQL is a little different in terms of SQL syntax. Nevertheless, we can achieve the same results. Consider the following codes. These codes create a person table with the primary key person_id. Then, they insert two records into the same table using keywords different from those of MySQL.
1 2 3 4 5 6 7 | CREATE TABLE person ( person_id int NOT NULL, person_name varchar(45) DEFAULT NULL, PRIMARY KEY (person_id) ); insert into person values(1, 'Karl') , (1, 'Lei') ON CONFLICT (person_id) DO NOTHING; |
In PostgreSQL, we use more keywords than in MySQL to ignore insert operations on key conflicts. Instead of using INSERT with IGNORE keyword, we use ON CONFLICT and DO NOTHING keywords. Quite a mouthful. The ON CONFLICT keywords generate events when there are key violations, and PostgreSQL can respond to such events. But for PostgreSQL to handle the events accordingly, we need to specify the action it needs to take. The ON CONFLICT keywords refer to an action PostgreSQL should take when there are issues with the keys. However, that action is only one of two actions available that we can use. The other one is the following.
1 | DO UPDATE SET column_1 = value_1, .. WHERE condition |
Note that the ON CONFLICT is only available in PostgreSQL 9.5 and up.
