MySQL, PostgreSQL

Convert MySQL INSERT IGNORE to PostgreSQL

MySQL INSERT IGNORE PostgreSQL

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.

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.

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.

Note that the ON CONFLICT is only available in PostgreSQL 9.5 and up.

 

You Might Also Like