🔧 Migrating from MySQL to PostgreSQL Key Query Differences and Considerations

When transitioning from MySQL to PostgreSQL, it is essential to understand the differences in how these databases handle queries, data types, and features. This article provides a detailed comparison to help ensure a smooth migration.

1. Data Types

MySQL and PostgreSQL use different data types for similar purposes. Below is a summary of key differences:

2. Case Sensitivity: LIKE and ILIKE

Case Sensitivity Behavior

• In MySQL, LIKE is case-insensitive by default for non-binary columns.
• In PostgreSQL:
  • LIKE is case-sensitive.
  • ILIKE is case-insensitive (PostgreSQL-specific extension).

Wildcards

The wildcard behavior for LIKE and ILIKE is the same in both databases:

• % matches zero or more characters.
• _ matches a single character.

Examples:

• MySQL (case-insensitive by default):

  SELECT * FROM users WHERE name LIKE 'john%';

• PostgreSQL (case-insensitive equivalent):

  SELECT * FROM users WHERE name ILIKE 'john%';

Or:

  SELECT * FROM users WHERE LOWER(name) LIKE 'john%';

Optimizing Searches

To optimize case-insensitive searches in PostgreSQL, create a functional index:

CREATE INDEX idx_users_name_lower ON users (LOWER(name));

3. Auto-Increment Columns

• MySQL uses AUTO_INCREMENT for primary keys.
• PostgreSQL uses SERIAL or GENERATED AS IDENTITY.

Examples:

• MySQL:

  CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
  );

• PostgreSQL:

  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
  );

4. String Functions

PostgreSQL has slightly different syntax for string functions compared to MySQL:

Examples:

• MySQL:

  SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

• PostgreSQL:

  SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

5. Pagination (LIMIT and OFFSET)

Both MySQL and PostgreSQL support LIMIT and OFFSET with identical syntax:

SELECT * FROM users LIMIT 10 OFFSET 20;

6. Default Values

PostgreSQL enforces stricter rules for default values:

• MySQL:

  CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  );

• PostgreSQL:

  CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

7. Joins

PostgreSQL enforces stricter SQL compliance for joins. Ensure the columns used in joins have compatible types or cast them explicitly:

SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.id::TEXT;

8. Full-Text Search

• MySQL supports FULLTEXT indexing.
• PostgreSQL uses TSVECTOR and TSQUERY for advanced full-text search capabilities.

Examples:

• MySQL:

  SELECT * FROM articles WHERE MATCH(content) AGAINST('search term');

• PostgreSQL:

  SELECT * FROM articles WHERE content @@ to_tsquery('search & term');

9. Foreign Key Constraints

PostgreSQL enforces stricter foreign key checks, requiring referenced tables and data integrity to match exactly. Double-check your schema and data integrity.

10. Case Sensitivity

• MySQL: Table and column names are case-insensitive (unless using a binary collation).
• PostgreSQL: Table and column names are case-sensitive if quoted with double quotes (").

Example:

• MySQL:

  SELECT `user_id` FROM `users`;

• PostgreSQL:

  SELECT "user_id" FROM "users";

11. Stored Procedures

Stored procedure syntax differs significantly:

• MySQL uses DELIMITER to define procedures.
• PostgreSQL uses DO blocks or CREATE FUNCTION.

Examples:

• MySQL:

  DELIMITER $$
  CREATE PROCEDURE example_proc()
  BEGIN
    SELECT 'Hello World';
  END$$
  DELIMITER ;

• PostgreSQL:

  CREATE OR REPLACE FUNCTION example_proc()
  RETURNS VOID AS $$
  BEGIN
    RAISE NOTICE 'Hello World';
  END; $$ LANGUAGE plpgsql;

12. Indexes

PostgreSQL offers advanced indexing options (GIN, GiST, BRIN) and supports functional indexes. To optimize case-insensitive searches, use a functional index:

CREATE INDEX idx_users_name_lower ON users (LOWER(name));

Migration Strategy

1. Use tools like pgLoader or AWS Database Migration Service for automated schema and data migration.
2. Manually review and update SQL queries, especially those involving:
   • LIKE and case-insensitivity.
   • Auto-increment fields.
   • Full-text search.
   • Joins with type mismatches.
   • Stored procedures.
3. Optimize indexes for PostgreSQL-specific capabilities.
4. Test thoroughly in a staging environment before finalizing the migration.

By understanding the differences between MySQL and PostgreSQL, you can ensure a successful migration with minimal disruption to your application.