Repository: pgjdbc/pgjdbc
Pull request #1699 (2020-02-07 01:56:02)

refactor: make PSQLState enum consts for integrity constraint violations
Previously the `PSQLState` enum did not include values for any of the integrity constraint violation error codes mentioned in the PostgreSQL docs (as seen under Class 25 [here](https://www.postgresql.org/docs/current/errcodes-appendix.html)); this required hardcoding or the creation of consts wherever they were needed (as seen in `LogServerMessagePropertyTest.java` lines `30` and `47`). This commit adds all of them (except `restrict_violation` which doesn't seem to appear in the wild) and creates a test checking for each of them.

### All Submissions:

* [✓] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [✓] Have you checked to ensure there aren't other open [Pull Requests](../../pulls) for the same update/change?

### Changes to Existing Features:

* [✓] Have you added an explanation of what your changes do and why you'd like us to include them?
* [✓] Have you written new tests for your core changes, as applicable?
* [✓] Have you successfully run tests with your changes locally?



Patch:
diff --git a/pgjdbc/src/main/java/org/postgresql/util/PSQLState.java b/pgjdbc/src/main/java/org/postgresql/util/PSQLState.java
index 21ff5c85..4e68d67b 100644
--- a/pgjdbc/src/main/java/org/postgresql/util/PSQLState.java
+++ b/pgjdbc/src/main/java/org/postgresql/util/PSQLState.java
@@ -58,6 +58,12 @@ public enum PSQLState {
   MOST_SPECIFIC_TYPE_DOES_NOT_MATCH("2200G"),
   INVALID_PARAMETER_VALUE("22023"),
 
+  NOT_NULL_VIOLATION("23502"),
+  FOREIGN_KEY_VIOLATION("23503"),
+  UNIQUE_VIOLATION("23505"),
+  CHECK_VIOLATION("23514"),
+  EXCLUSION_VIOLATION("23P01"),
+
   INVALID_CURSOR_STATE("24000"),
 
   TRANSACTION_STATE_INVALID("25000"),


Test patch:
diff --git a/pgjdbc/src/test/java/org/postgresql/test/core/LogServerMessagePropertyTest.java b/pgjdbc/src/test/java/org/postgresql/test/core/LogServerMessagePropertyTest.java
index daab6935..e5de8f24 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/core/LogServerMessagePropertyTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/core/LogServerMessagePropertyTest.java
@@ -8,6 +8,7 @@ package org.postgresql.test.core;
 import org.postgresql.PGProperty;
 import org.postgresql.core.ServerVersion;
 import org.postgresql.test.TestUtil;
+import org.postgresql.util.PSQLState;
 
 import org.junit.Assert;
 import org.junit.Assume;
@@ -27,7 +28,6 @@ public class LogServerMessagePropertyTest {
   private static final String SECRET_VALUE = "some_secret_value";
   private static final String INSERT_SQL =
       "INSERT INTO pg_temp.lms_test (id) VALUES ('" + SECRET_VALUE + "')";
-  private static final String UNIQUE_VIOLATION_SQL_STATE = "23505";
 
   /**
    * Creates a connection with the additional properties, use it to
@@ -44,7 +44,7 @@ public class LogServerMessagePropertyTest {
       // Second insert should throw a duplicate key error
       TestUtil.execute(INSERT_SQL, conn);
     } catch (SQLException e) {
-      Assert.assertEquals("SQL state must be for a unique violation", UNIQUE_VIOLATION_SQL_STATE, e.getSQLState());
+      Assert.assertEquals("SQL state must be for a unique violation", PSQLState.UNIQUE_VIOLATION.getState(), e.getSQLState());
       return e.getMessage();
     } finally {
       conn.close();
diff --git a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ServerErrorTest.java b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ServerErrorTest.java
index df03bfa9..9f06d462 100644
--- a/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ServerErrorTest.java
+++ b/pgjdbc/src/test/java/org/postgresql/test/jdbc2/ServerErrorTest.java
@@ -12,6 +12,7 @@ import static org.junit.Assert.fail;
 import org.postgresql.core.ServerVersion;
 import org.postgresql.test.TestUtil;
 import org.postgresql.util.PSQLException;
+import org.postgresql.util.PSQLState;
 import org.postgresql.util.ServerErrorMessage;
 
 import org.junit.Test;
@@ -57,6 +58,7 @@ public class ServerErrorTest extends BaseTest4 {
       assertEquals("public", err.getSchema());
       assertEquals("testerr", err.getTable());
       assertEquals("testerr_pk", err.getConstraint());
+      assertEquals(PSQLState.UNIQUE_VIOLATION.getState(), err.getSQLState());
       assertNull(err.getDatatype());
       assertNull(err.getColumn());
     }
@@ -95,4 +97,82 @@ public class ServerErrorTest extends BaseTest4 {
     stmt.close();
   }
 
+  @Test
+  public void testNotNullConstraint() throws Exception {
+    Statement stmt = con.createStatement();
+    try {
+      stmt.executeUpdate("INSERT INTO testerr (val) VALUES (1)");
+      fail("Should have thrown a not-null exception.");
+    } catch (SQLException sqle) {
+      ServerErrorMessage err = ((PSQLException) sqle).getServerErrorMessage();
+      assertEquals("public", err.getSchema());
+      assertEquals("testerr", err.getTable());
+      assertEquals("id", err.getColumn());
+      assertEquals(PSQLState.NOT_NULL_VIOLATION.getState(), err.getSQLState());
+      assertNull(err.getDatatype());
+    }
+    stmt.close();
+  }
+
+  @Test
+  public void testForeignKeyConstraint() throws Exception {
+    TestUtil.createTable(con, "testerr_foreign", "id int not null, testerr_id int,"
+        + "CONSTRAINT testerr FOREIGN KEY (testerr_id) references testerr(id)");
+    Statement stmt = con.createStatement();
+    stmt.executeUpdate("INSERT INTO testerr (id, val) VALUES (1, 1)");
+    try {
+      stmt.executeUpdate("INSERT INTO testerr_foreign (id, testerr_id) VALUES (1, 2)");
+      fail("Should have thrown a foreign key exception.");
+    } catch (SQLException sqle) {
+      ServerErrorMessage err = ((PSQLException) sqle).getServerErrorMessage();
+      assertEquals("public", err.getSchema());
+      assertEquals("testerr_foreign", err.getTable());
+      assertEquals(PSQLState.FOREIGN_KEY_VIOLATION.getState(), err.getSQLState());
+      assertNull(err.getDatatype());
+      assertNull(err.getColumn());
+    }
+    TestUtil.dropTable(con, "testerr_foreign");
+    stmt.close();
+  }
+
+  @Test
+  public void testCheckConstraint() throws Exception {
+    TestUtil.createTable(con, "testerr_check", "id int not null, max10 int CHECK (max10 < 11)");
+    Statement stmt = con.createStatement();
+    stmt.executeUpdate("INSERT INTO testerr_check (id, max10) VALUES (1, 5)");
+    try {
+      stmt.executeUpdate("INSERT INTO testerr_check (id, max10) VALUES (2, 11)");
+      fail("Should have thrown a check exception.");
+    } catch (SQLException sqle) {
+      ServerErrorMessage err = ((PSQLException) sqle).getServerErrorMessage();
+      assertEquals("public", err.getSchema());
+      assertEquals("testerr_check", err.getTable());
+      assertEquals(PSQLState.CHECK_VIOLATION.getState(), err.getSQLState());
+      assertNull(err.getDatatype());
+      assertNull(err.getColumn());
+    }
+    TestUtil.dropTable(con, "testerr_check");
+    stmt.close();
+  }
+
+  @Test
+  public void testExclusionConstraint() throws Exception {
+    TestUtil.createTable(con, "testerr_exclude", "id int, EXCLUDE (id WITH =)");
+    Statement stmt = con.createStatement();
+    stmt.executeUpdate("INSERT INTO testerr_exclude (id) VALUES (1108)");
+    try {
+      stmt.executeUpdate("INSERT INTO testerr_exclude (id) VALUES (1108)");
+      fail("Should have thrown an exclusion exception.");
+    } catch (SQLException sqle) {
+      ServerErrorMessage err = ((PSQLException) sqle).getServerErrorMessage();
+      assertEquals("public", err.getSchema());
+      assertEquals("testerr_exclude", err.getTable());
+      assertEquals(PSQLState.EXCLUSION_VIOLATION.getState(), err.getSQLState());
+      assertNull(err.getDatatype());
+      assertNull(err.getColumn());
+    }
+    TestUtil.dropTable(con, "testerr_exclude");
+    stmt.close();
+  }
+
 }