Repository: GoogleCloudPlatform/pgadapter
Pull request #2992 (2025-03-07 15:54:29)

docs: update SQLAlchemy2 sample to use serial
Updates the SQLAlchemy 2.x sample to use serial for auto-generated primary key values.


Patch:
diff --git a/samples/python/sqlalchemy-sample/README.md b/samples/python/sqlalchemy-sample/README.md
index e0d510d0..9cafe308 100644
--- a/samples/python/sqlalchemy-sample/README.md
+++ b/samples/python/sqlalchemy-sample/README.md
@@ -81,8 +81,6 @@ The following limitations are currently known:
 | Creating and Dropping Tables | Cloud Spanner does not support the full PostgreSQL DDL dialect. Automated creation of tables using `SQLAlchemy` is therefore not supported.                                                                                                                         |
 | metadata.reflect()           | Cloud Spanner does not support all PostgreSQL `pg_catalog` tables. Using `metadata.reflect()` to get the current objects in the database is therefore not supported.                                                                                                |
 | DDL Transactions             | Cloud Spanner does not support DDL statements in a transaction. Add `?options=-c spanner.ddl_transaction_mode=AutocommitExplicitTransaction` to your connection string to automatically convert DDL transactions to [non-atomic DDL batches](../../../docs/ddl.md). |
-| Generated primary keys       | Manually assign a value to the primary key column in your code. The recommended primary key type is a random UUID. Sequences / SERIAL / IDENTITY columns are currently not supported.                                                                               |
-| INSERT ... ON CONFLICT       | `INSERT ... ON CONFLICT` is not supported.                                                                                                                                                                                                                          |
 | SAVEPOINT                    | Rolling back to a `SAVEPOINT` can fail if the transaction contained at least one query that called a volatile function.                                                                                                                                             |
 | SELECT ... FOR UPDATE        | Only `SELECT ... FOR UPDATE` without any additional options is supported. The `NOWAIT` and `SKIP LOCKED` options are not supported.                                                                                                                                 |
 | Server side cursors          | Server side cursors are currently not supported.                                                                                                                                                                                                                    |
@@ -91,39 +89,14 @@ The following limitations are currently known:
 | User defined functions       | Cloud Spanner does not support User Defined Functions.                                                                                                                                                                                                              |
 | Other drivers than psycopg2  | PGAdapter does not support using SQLAlchemy with any other drivers than `psycopg2`.                                                                                                                                                                                 |
 
-### Generated Primary Keys
-Generated primary keys are not supported and should be replaced with primary key definitions that
-are manually assigned. See https://cloud.google.com/spanner/docs/schema-design#primary-key-prevent-hotspots
-for more information on choosing a good primary key. This sample uses random UUIDs that are generated
-by the client and stored as strings for primary keys.
-
-```python
-from uuid import uuid4
-
-class Singer(Base):
-  id = Column(String, primary_key=True)
-  name = Column(String(100))
-
-singer = Singer(
-  id="{}".format(uuid4()),
-  name="Alice")
-```
-
-### ON CONFLICT Clauses
-`INSERT ... ON CONFLICT ...` are not supported by Cloud Spanner and should not be used. Trying to
-use https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#sqlalchemy.dialects.postgresql.Insert.on_conflict_do_update
-or https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#sqlalchemy.dialects.postgresql.Insert.on_conflict_do_nothing
-will fail.
-
 ### SAVEPOINT - Nested transactions
 Rolling back to a `SAVEPOINT` can fail if the transaction contained at least one query that called a
 volatile function or if the underlying data that has been accessed by the transaction has been
 modified by another transaction.
 
 ### Locking - SELECT ... FOR UPDATE
-Locking clauses, like `SELECT ... FOR UPDATE`, are not supported (see also https://docs.sqlalchemy.org/en/20/orm/queryguide/query.html#sqlalchemy.orm.Query.with_for_update).
-These are normally also not required, as Cloud Spanner uses isolation level `serializable` for
-read/write transactions.
+`SELECT ... FOR UPDATE` is only supported without any additional options. The `NOWAIT` and
+`SKIP LOCKED` options are not supported.
 
 ## Performance Considerations
 
diff --git a/samples/python/sqlalchemy2-sample/README.md b/samples/python/sqlalchemy2-sample/README.md
index 609ee85f..efa71cb7 100644
--- a/samples/python/sqlalchemy2-sample/README.md
+++ b/samples/python/sqlalchemy2-sample/README.md
@@ -116,17 +116,22 @@ The following limitations are currently known:
 | Other drivers than psycopg 3.x | PGAdapter does not support using SQLAlchemy 2.x with any other drivers than `psycopg 3.x`.                                                                                                                                                                          |
 
 ### Generated Primary Keys
-Generated primary keys can be used in combination with a bit-reversed sequence.
+Spanner supports the `serial` data type. Columns with this data type use a backing bit-reversed
+sequence to generate unique values that are safe to use as primary key values in Spanner. The values
+are not monotonically increasing.
 
-The `TicketSale` model in this sample application uses an auto-generated primary key that is
-generated from a bit-reversed sequence:
+You must set a `default_sequence_kind` for your database before you can create a column with data
+type `serial`. The [create_data_model.sql](create_data_model.sql) file includes a statement to set
+the default.
+
+The `TicketSale` model in this sample application uses a `serial` for auto-generated primary keys:
 1. See [model.py](model.py) for the model definition.
-2. See [create_data_model.sql](create_data_model.sql) for the sequence and table definition.
+2. See [create_data_model.sql](create_data_model.sql) for the table definition.
 
-See https://cloud.google.com/spanner/docs/primary-key-default-value#bit-reversed-sequence for more
-information on bit-reversed sequences in Cloud Spanner.
+See https://cloud.google.com/spanner/docs/primary-key-default-value#serial-auto-increment for more
+information on the `serial` data type in Spanner.
 
-#### Example Mapping for Generated Primary Key using a Bit-Reversed Sequence
+#### Example Mapping for Generated Primary Key using serial
 
 Python model definition:
 
@@ -140,12 +145,12 @@ session.add(singer)
 session.commit()
 ```
 
-Sequence and table definition:
+Database option and table definition:
 
 ```sql
-create sequence if not exists singers_seq bit_reversed_positive;
+alter database db set spanner.default_sequence_kind='bit_reversed_positive';
 create table if not exists singers (
-  id   bigint not null primary key default nextval('singers_seq'),
+  id   serial primary key,
   name varchar not null
 );
 ```
diff --git a/samples/python/sqlalchemy2-sample/create_data_model.sql b/samples/python/sqlalchemy2-sample/create_data_model.sql
index 7ca401e8..8f6f3d6e 100644
--- a/samples/python/sqlalchemy2-sample/create_data_model.sql
+++ b/samples/python/sqlalchemy2-sample/create_data_model.sql
@@ -61,17 +61,10 @@ create table if not exists concerts (
     constraint chk_end_time_after_start_time check (end_time > start_time)
 );
 
--- Create a bit-reversed sequence that will be used to generate identifiers for the ticket_sales table.
--- See also https://cloud.google.com/spanner/docs/reference/postgresql/data-definition-language#create_sequence
--- Note that the 'bit_reversed_positive' keyword is required for Spanner.
-create sequence if not exists ticket_sale_seq
-    bit_reversed_positive
-    skip range 1 1000
-    start counter with 50000
-;
+alter database db set spanner.default_sequence_kind='bit_reversed_positive';
 
 create table if not exists ticket_sales (
-    id bigint not null primary key default nextval('ticket_sale_seq'),
+    id               serial primary key,
     version_id       int not null,
     concert_id       varchar not null,
     customer_name    varchar not null,
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/session/RemoveEscapeClauseEnum.java b/src/main/java/com/google/cloud/spanner/pgadapter/session/RemoveEscapeClauseEnum.java
new file mode 100644
index 00000000..0827f7a5
--- /dev/null
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/session/RemoveEscapeClauseEnum.java
@@ -0,0 +1,30 @@
+// Copyright 2025 Google LLC
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package com.google.cloud.spanner.pgadapter.session;
+
+public enum RemoveEscapeClauseEnum {
+  /** Do not remove any LIKE ... ESCAPE clauses. */
+  NONE,
+  /**
+   * Only remove LIKE ... ESCAPE '\' clauses. '\' is the default escape character in PostgreSQL, and
+   * removing it from a LIKE expression does not change the behavior.
+   */
+  DEFAULT,
+  /**
+   * Remove all LIKE ... ESCAPE clauses. LIKE expressions with a non-default ESCAPE clause will
+   * behave differently than on open-source PostgreSQL.
+   */
+  ALL,
+}
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java b/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
index 4a852d5d..7349e4e2 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/session/SessionState.java
@@ -462,6 +462,23 @@ public class SessionState {
     return getBoolSetting("spanner", "replace_for_update", false);
   }
 
+  /** Returns whether LIKE ... ESCAPE clauses are removed. */
+  public RemoveEscapeClauseEnum getRemoveEscapeClause() {
+    PGSetting setting = internalGet(toKey("spanner", "remove_escape_clause"), false);
+    if (setting == null) {
+      return RemoveEscapeClauseEnum.DEFAULT;
+    }
+    return tryGetFirstNonNull(
+        RemoveEscapeClauseEnum.DEFAULT,
+        () -> RemoveEscapeClauseEnum.valueOf(upper(setting.getSetting())),
+        () -> RemoveEscapeClauseEnum.valueOf(upper(setting.getResetVal())),
+        () -> RemoveEscapeClauseEnum.valueOf(upper(setting.getBootVal())));
+  }
+
+  private static String upper(String s) {
+    return s == null ? null : s.toUpperCase(Locale.ENGLISH);
+  }
+
   /** Returns the current setting for replacing pg_catalog tables with common table expressions. */
   public boolean isReplacePgCatalogTables() {
     PGSetting setting = internalGet(toKey("spanner", "replace_pg_catalog_tables"), false);
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java
index cd477f08..044bfdde 100644
--- a/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/statements/BackendConnection.java
@@ -57,6 +57,7 @@ import com.google.cloud.spanner.pgadapter.error.PGExceptionFactory;
 import com.google.cloud.spanner.pgadapter.error.SQLState;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata;
 import com.google.cloud.spanner.pgadapter.metadata.OptionsMetadata.DdlTransactionMode;
+import com.google.cloud.spanner.pgadapter.session.RemoveEscapeClauseEnum;
 import com.google.cloud.spanner.pgadapter.session.SessionState;
 import com.google.cloud.spanner.pgadapter.statements.SessionStatementParser.SessionStatement;
 import com.google.cloud.spanner.pgadapter.statements.SimpleParser.TableOrIndexName;
@@ -370,6 +371,12 @@ public class BackendConnection {
             updatedStatement =
                 replaceForUpdate(updatedStatement, sqlLowerCase, /*replaceWithHint=*/ false);
           }
+          RemoveEscapeClauseEnum removeEscapeClauseEnum = sessionState.getRemoveEscapeClause();
+          if (removeEscapeClauseEnum != RemoveEscapeClauseEnum.NONE) {
+            updatedStatement =
+                EscapeClauseParser.removeEscapeClauses(
+                    updatedStatement, sqlLowerCase, removeEscapeClauseEnum);
+          }
           updatedStatement = bindStatement(updatedStatement, sqlLowerCase);
           result.set(analyzeOrExecute(updatedStatement));
         }
diff --git a/src/main/java/com/google/cloud/spanner/pgadapter/statements/EscapeClauseParser.java b/src/main/java/com/google/cloud/spanner/pgadapter/statements/EscapeClauseParser.java
new file mode 100644
index 00000000..1f3483ff
--- /dev/null
+++ b/src/main/java/com/google/cloud/spanner/pgadapter/statements/EscapeClauseParser.java
@@ -0,0 +1,70 @@
+// Copyright 2025 Google LLC
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package com.google.cloud.spanner.pgadapter.statements;
+
+import com.google.cloud.spanner.SpannerExceptionFactory;
+import com.google.cloud.spanner.Statement;
+import com.google.cloud.spanner.pgadapter.session.RemoveEscapeClauseEnum;
+import com.google.cloud.spanner.pgadapter.statements.SimpleParser.QuotedString;
+import com.google.common.cache.Cache;
+import com.google.common.cache.CacheBuilder;
+import com.google.common.collect.ImmutableList;
+import java.util.concurrent.ExecutionException;
+
+class EscapeClauseParser {
+  private static final Cache<String, Statement> REMOVED_ESCAPE_CLAUSES_CACHE =
+      CacheBuilder.newBuilder()
+          .maximumSize(1000)
+          .concurrencyLevel(Runtime.getRuntime().availableProcessors())
+          .build();
+
+  /** Removes LIKE ... ESCAPE '\' clauses from the statement. */
+  static Statement removeEscapeClauses(
+      Statement statement, String lowerCaseSql, RemoveEscapeClauseEnum setting) {
+    // If there is no 'escape' clause, then we know that we don't have to analyze any further.
+    if (!lowerCaseSql.contains("escape")) {
+      return statement;
+    }
+    try {
+      return REMOVED_ESCAPE_CLAUSES_CACHE.get(
+          setting.name() + statement.getSql(), () -> removeEscapeClauses(statement, setting));
+    } catch (ExecutionException executionException) {
+      throw SpannerExceptionFactory.asSpannerException(executionException.getCause());
+    }
+  }
+
+  static Statement removeEscapeClauses(Statement statement, RemoveEscapeClauseEnum setting) {
+    SimpleParser parser = new SimpleParser(statement.getSql());
+    while (parser.getPos() < parser.getSql().length()) {
+      parser.parseExpressionUntilKeyword(ImmutableList.of("escape"), false, false, false);
+      if (parser.getPos() == parser.getSql().length()) {
+        break;
+      }
+      int pos = parser.getPos();
+      if (parser.eatKeyword("escape")) {
+        QuotedString escape = parser.readSingleQuotedString();
+        if ("\\".equals(escape.getValue()) || setting == RemoveEscapeClauseEnum.ALL) {
+          int endPos = parser.getPos();
+          statement =
+              Statement.of(
+                  statement.getSql().substring(0, pos) + statement.getSql().substring(endPos));
+          parser = new SimpleParser(statement.getSql());
+          parser.setPos(pos);
+        }
+      }
+    }
+    return statement;
+  }
+}
diff --git a/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt b/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt
index 59e6334d..59d9dabc 100644
--- a/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt
+++ b/src/main/resources/com/google/cloud/spanner/pgadapter/session/pg_settings.txt
@@ -354,7 +354,8 @@ spanner.copy_max_non_atomic_commit_size	5000000	\N	COPY / The max number of byte
 spanner.copy_commit_size_multiplier_factor	2.0	\N	COPY / Factor for estimating COPY commit size	The factor that the size of the incoming payload is multiplied with to estimate whether the current commit size will exceed the maximum commit size.	\N	user	real	default	\N	\N	\N	2.0	2.0	\N	\N	f
 spanner.copy_pipe_buffer_size	65536	\N	COPY / Buffer size for incoming COPY data messages	Internal buffer size for incoming COPY data messages.	\N	internal	integer	default	\N	\N	\N	100000000	100000000	\N	\N	f
 spanner.ddl_transaction_mode	Batch	\N	PGAdapter Options	DDL transaction mode.	\N	user	enum	default	\N	\N	{Single,Batch,AutocommitImplicitTransaction,AutocommitExplicitTransaction}	Batch	Batch	\N	\N	f
-spanner.emulate_pg_class_tables	on	\N	PGAdapter Options Emulate pg_class and related tables using common table expressions and textual OIDs	Generate commont table expressions for pg_class and related tables. The OID of an object is set to its fully qualified name.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+spanner.emulate_pg_class_tables	on	\N	PGAdapter Options Emulate pg_class and related tables using common table expressions and textual OIDs	Generate common table expressions for pg_class and related tables. The OID of an object is set to its fully qualified name.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
 spanner.force_autocommit	off	\N	PGAdapter Options Execute all statements in autocommit mode	Ignore all transaction statements (BEGIN / COMMIT / ROLLBACK) and execute all statements in autocommit mode.	\N	user	bool	default	\N	\N	\N	off	off	\N	\N	f
+spanner.remove_escape_clause	DEFAULT	\N	PGAdapter Options	Remove escape clause.	\N	user	enum	default	\N	\N	{NONE,DEFAULT,ALL}	DEFAULT	DEFAULT	\N	\N	f
 spanner.replace_pg_catalog_tables	on	\N	PGAdapter Options	Automatically replace pg_catalog table references with Common Table Expressions.	\N	user	bool	default	\N	\N	\N	on	on	\N	\N	f
 spanner.well_known_client	UNSPECIFIED	\N	PGAdapter Options	The well-known client that is connected to PGAdapter.	\N	backend	string	default	\N	\N	\N	UNSPECIFIED	UNSPECIFIED	\N	\N	f


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/AbortedMockServerTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/AbortedMockServerTest.java
index 277216d4..91639dd3 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/AbortedMockServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/AbortedMockServerTest.java
@@ -1474,7 +1474,7 @@ public class AbortedMockServerTest extends AbstractMockServerTest {
           }
           count++;
         }
-        assertEquals(361, count);
+        assertEquals(362, count);
       }
     }
   }
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcTest.java
index 7c31f0e2..7532e9b4 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/ITJdbcTest.java
@@ -1174,7 +1174,7 @@ public class ITJdbcTest implements IntegrationTest {
 
       // Verify that we can get all pg_settings and that the number of setting is equal to the
       // expected value.
-      int numExpectedSettings = 30;
+      int numExpectedSettings = 31;
       int rowCount = 0;
       try (ResultSet resultSet =
           connection.createStatement().executeQuery("select * from pg_settings order by name")) {
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
index 52bb2e07..c05b240f 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/JdbcMockServerTest.java
@@ -4195,7 +4195,7 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
           }
           count++;
         }
-        assertEquals(361, count);
+        assertEquals(362, count);
       }
     }
   }
@@ -5247,6 +5247,43 @@ public class JdbcMockServerTest extends AbstractMockServerTest {
     }
   }
 
+  @Test
+  public void testRemoveDefaultEscapeClause() throws SQLException {
+    String expectedSql = "SELECT * FROM users WHERE name LIKE 'test%' AND city LIKE 'NY%'";
+    mockSpanner.putStatementResult(
+        StatementResult.query(Statement.of(expectedSql), SELECT1_RESULTSET));
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      try (ResultSet resultSet =
+          connection
+              .createStatement()
+              .executeQuery(
+                  "SELECT * FROM users WHERE name LIKE 'test%' ESCAPE '\\' AND city LIKE 'NY%' ESCAPE '\\'")) {
+        assertTrue(resultSet.next());
+        assertEquals(1, resultSet.getInt(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
+  @Test
+  public void testRemoveAllEscapeClauses() throws SQLException {
+    String expectedSql = "SELECT * FROM users WHERE name LIKE 'test%' AND city LIKE 'NY%'";
+    mockSpanner.putStatementResult(
+        StatementResult.query(Statement.of(expectedSql), SELECT1_RESULTSET));
+    try (Connection connection = DriverManager.getConnection(createUrl())) {
+      connection.createStatement().execute("set spanner.remove_escape_clause=all");
+      try (ResultSet resultSet =
+          connection
+              .createStatement()
+              .executeQuery(
+                  "SELECT * FROM users WHERE name LIKE 'test%' ESCAPE '%' AND city LIKE 'NY%' ESCAPE 'Y'")) {
+        assertTrue(resultSet.next());
+        assertEquals(1, resultSet.getInt(1));
+        assertFalse(resultSet.next());
+      }
+    }
+  }
+
   @Test
   public void testEmulatePgClass() throws SQLException {
     String withEmulation = "with " + EMULATED_PG_CLASS_PREFIX + "\nselect 1 from pg_class";
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
index 71d35fec..ab733a2c 100644
--- a/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/session/SessionStateTest.java
@@ -162,7 +162,7 @@ public class SessionStateTest {
   public void testGetAll() {
     SessionState state = new SessionState(mock(OptionsMetadata.class));
     List<PGSetting> allSettings = state.getAll();
-    assertEquals(360, allSettings.size());
+    assertEquals(361, allSettings.size());
   }
 
   @Test
@@ -200,7 +200,7 @@ public class SessionStateTest {
     state.setLocal("spanner", "custom_local_setting", "value2");
 
     List<PGSetting> allSettings = state.getAll();
-    assertEquals(362, allSettings.size());
+    assertEquals(363, allSettings.size());
 
     PGSetting applicationName =
         allSettings.stream()
@@ -433,6 +433,7 @@ public class SessionStateTest {
         + "'{\"name\":\"spanner.ddl_transaction_mode\",\"setting\":\"Batch\",\"unit\":null,\"category\":\"PGAdapter Options\",\"short_desc\":null,\"extra_desc\":null,\"context\":\"user\",\"vartype\":\"enum\",\"min_val\":null,\"max_val\":null,\"enum_vals\":[\"Single\", \"Batch\", \"AutocommitImplicitTransaction\", \"AutocommitExplicitTransaction\"],\"boot_val\":\"Batch\",\"reset_val\":\"Batch\",\"source\":\"default\",\"sourcefile\":null,\"sourceline\":null,\"pending_restart\":false}'::jsonb,\n"
         + "'{\"name\":\"spanner.emulate_pg_class_tables\",\"setting\":\"on\",\"unit\":null,\"category\":\"PGAdapter Options Emulate pg_class and related tables using common table expressions and textual OIDs\",\"short_desc\":null,\"extra_desc\":null,\"context\":\"user\",\"vartype\":\"bool\",\"min_val\":null,\"max_val\":null,\"enum_vals\":null,\"boot_val\":\"off\",\"reset_val\":\"off\",\"source\":\"default\",\"sourcefile\":null,\"sourceline\":null,\"pending_restart\":false}'::jsonb,\n"
         + "'{\"name\":\"spanner.force_autocommit\",\"setting\":\"off\",\"unit\":null,\"category\":\"PGAdapter Options Execute all statements in autocommit mode\",\"short_desc\":null,\"extra_desc\":null,\"context\":\"user\",\"vartype\":\"bool\",\"min_val\":null,\"max_val\":null,\"enum_vals\":null,\"boot_val\":\"off\",\"reset_val\":\"off\",\"source\":\"default\",\"sourcefile\":null,\"sourceline\":null,\"pending_restart\":false}'::jsonb,\n"
+        + "'{\"name\":\"spanner.remove_escape_clause\",\"setting\":\"DEFAULT\",\"unit\":null,\"category\":\"PGAdapter Options\",\"short_desc\":null,\"extra_desc\":null,\"context\":\"user\",\"vartype\":\"enum\",\"min_val\":null,\"max_val\":null,\"enum_vals\":[\"NONE\", \"DEFAULT\", \"ALL\"],\"boot_val\":\"DEFAULT\",\"reset_val\":\"DEFAULT\",\"source\":\"default\",\"sourcefile\":null,\"sourceline\":null,\"pending_restart\":false}'::jsonb,\n"
         + "'{\"name\":\"spanner.replace_pg_catalog_tables\",\"setting\":\"false\",\"unit\":null,\"category\":\"PGAdapter Options\",\"short_desc\":null,\"extra_desc\":null,\"context\":\"user\",\"vartype\":\"bool\",\"min_val\":null,\"max_val\":null,\"enum_vals\":null,\"boot_val\":\"on\",\"reset_val\":\"on\",\"source\":\"default\",\"sourcefile\":null,\"sourceline\":null,\"pending_restart\":false}'::jsonb,\n"
         + "'{\"name\":\"spanner.well_known_client\",\"setting\":\"UNSPECIFIED\",\"unit\":null,\"category\":\"PGAdapter Options\",\"short_desc\":null,\"extra_desc\":null,\"context\":\"backend\",\"vartype\":\"string\",\"min_val\":null,\"max_val\":null,\"enum_vals\":null,\"boot_val\":\"UNSPECIFIED\",\"reset_val\":\"UNSPECIFIED\",\"source\":\"default\",\"sourcefile\":null,\"sourceline\":null,\"pending_restart\":false}'::jsonb,\n"
         + "'{\"name\":\"transaction_isolation\",\"setting\":\"serializable\",\"unit\":null,\"category\":\"Client Connection Defaults / Statement Behavior\",\"short_desc\":null,\"extra_desc\":null,\"context\":\"user\",\"vartype\":\"enum\",\"min_val\":null,\"max_val\":null,\"enum_vals\":[\"serializable\", \"repeatable read\", \"read committed\", \"read uncommitted\"],\"boot_val\":\"serializable\",\"reset_val\":\"serializable\",\"source\":\"override\",\"sourcefile\":null,\"sourceline\":null,\"pending_restart\":false}'::jsonb,\n"
diff --git a/src/test/java/com/google/cloud/spanner/pgadapter/statements/LikeEscapeTest.java b/src/test/java/com/google/cloud/spanner/pgadapter/statements/LikeEscapeTest.java
new file mode 100644
index 00000000..29ff7422
--- /dev/null
+++ b/src/test/java/com/google/cloud/spanner/pgadapter/statements/LikeEscapeTest.java
@@ -0,0 +1,109 @@
+// Copyright 2025 Google LLC
+//
+// Licensed under the Apache License, Version 2.0 (the "License");
+// you may not use this file except in compliance with the License.
+// You may obtain a copy of the License at
+//
+//      http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing, software
+// distributed under the License is distributed on an "AS IS" BASIS,
+// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+// See the License for the specific language governing permissions and
+// limitations under the License.
+
+package com.google.cloud.spanner.pgadapter.statements;
+
+import static com.google.cloud.spanner.pgadapter.statements.EscapeClauseParser.removeEscapeClauses;
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertSame;
+
+import com.google.cloud.spanner.Statement;
+import com.google.cloud.spanner.pgadapter.session.RemoveEscapeClauseEnum;
+import java.util.Locale;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.JUnit4;
+
+@RunWith(JUnit4.class)
+public class LikeEscapeTest {
+
+  @Test
+  public void testRemoveDefaultEscapeClauses() {
+    assertSameAfterRemoveEscape("select 1", RemoveEscapeClauseEnum.DEFAULT);
+    assertSameAfterRemoveEscape(
+        "select col1, col2 from my_table where bar like 'one'", RemoveEscapeClauseEnum.DEFAULT);
+    assertSameAfterRemoveEscape(
+        "update my_table set bar=1 where foo like 'two'", RemoveEscapeClauseEnum.DEFAULT);
+    assertSameAfterRemoveEscape("select col1 from my_table", RemoveEscapeClauseEnum.DEFAULT);
+    assertSameAfterRemoveEscape(
+        "select col1 from foo where test=1 /* and test like 'one' escape '\\' */",
+        RemoveEscapeClauseEnum.DEFAULT);
+    assertSameAfterRemoveEscape(
+        "select col1, col2 from my_table where bar like 'one' escape '*'",
+        RemoveEscapeClauseEnum.DEFAULT);
+
+    assertEquals(
+        Statement.of("select col1 from foo where bar like 'test'"),
+        internalRemoveEscape(
+            "select col1 from foo where bar like 'test' escape '\\'",
+            RemoveEscapeClauseEnum.DEFAULT));
+    assertEquals(
+        Statement.of("select col1 from foo where bar like 'test' and baz like 'test' escape '*'"),
+        internalRemoveEscape(
+            "select col1 from foo where bar like 'test' escape '\\' and baz like 'test' escape '*'",
+            RemoveEscapeClauseEnum.DEFAULT));
+    assertEquals(
+        Statement.of("select col1 from foo where bar like 'test' and baz like 'test'"),
+        internalRemoveEscape(
+            "select col1 from foo where bar like 'test' escape '\\' and baz like 'test' escape '\\'",
+            RemoveEscapeClauseEnum.DEFAULT));
+    assertEquals(
+        Statement.of("select col1 from foo where bar like $1"),
+        internalRemoveEscape(
+            "select col1 from foo where bar like $1 escape '\\'", RemoveEscapeClauseEnum.DEFAULT));
+    assertEquals(
+        Statement.of(
+            "select col1 from foo where bar in (select val from t where id like $1) and id=1"),
+        internalRemoveEscape(
+            "select col1 from foo where bar in (select val from t where id like $1 escape '\\') and id=1",
+            RemoveEscapeClauseEnum.DEFAULT));
+    assertEquals(
+        Statement.of(
+            "select col1 from foo where bar like 'test' escape '' and baz like 'test' escape ''"),
+        internalRemoveEscape(
+            "select col1 from foo where bar like 'test' escape '' and baz like 'test' escape ''",
+            RemoveEscapeClauseEnum.DEFAULT));
+  }
+
+  @Test
+  public void testRemoveAllEscapeClauses() {
+    assertSameAfterRemoveEscape("select 1", RemoveEscapeClauseEnum.ALL);
+    assertSameAfterRemoveEscape(
+        "select col1, col2 from my_table where bar like 'one'", RemoveEscapeClauseEnum.ALL);
+    assertSameAfterRemoveEscape(
+        "update my_table set bar=1 where foo like 'two'", RemoveEscapeClauseEnum.ALL);
+    assertSameAfterRemoveEscape("select col1 from my_table", RemoveEscapeClauseEnum.ALL);
+    assertEquals(
+        Statement.of("select col1 from foo where bar like 'test' and baz like 'test'"),
+        internalRemoveEscape(
+            "select col1 from foo where bar like 'test' escape '' and baz like 'test' escape ''",
+            RemoveEscapeClauseEnum.ALL));
+    assertEquals(
+        Statement.of("select col1 from foo where bar like 'test' and baz like 'test'"),
+        internalRemoveEscape(
+            "select col1 from foo where bar like 'test' escape '\\' and baz like 'test' escape '&'",
+            RemoveEscapeClauseEnum.ALL));
+  }
+
+  private void assertSameAfterRemoveEscape(String sql, RemoveEscapeClauseEnum setting) {
+    Statement statement = Statement.of(sql);
+    assertSame(
+        statement,
+        removeEscapeClauses(statement, statement.getSql().toLowerCase(Locale.ENGLISH), setting));
+  }
+
+  private Statement internalRemoveEscape(String sql, RemoveEscapeClauseEnum setting) {
+    return removeEscapeClauses(Statement.of(sql), sql.toLowerCase(Locale.ENGLISH), setting);
+  }
+}