Scribd
Upload
10 views2 pages

PostgreSQL vs MySQL Syntax Differences

The document outlines key syntax differences between PostgreSQL and MySQL, including auto-increment columns, string concatenation, and data types. It highlights PostgreSQL's support for features like arrays, full-text search, and materialized views, which MySQL lacks. Additionally, it covers conditional expressions, type casting, and boolean support variations between the two databases.

Uploaded by

priyanshukansal59
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
10 views2 pages

PostgreSQL vs MySQL Syntax Differences

The document outlines key syntax differences between PostgreSQL and MySQL, including auto-increment columns, string concatenation, and data types. It highlights PostgreSQL's support for features like arrays, full-text search, and materialized views, which MySQL lacks. Additionally, it covers conditional expressions, type casting, and boolean support variations between the two databases.

Uploaded by

priyanshukansal59
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

PostgreSQL vs MySQL: Syntax Differences

1. Auto-Increment / Identity Columns


MySQL:
id INT AUTO_INCREMENT PRIMARY KEY

PostgreSQL:
id SERIAL PRIMARY KEY
-- or --
id INT GENERATED ALWAYS AS IDENTITY

2. String Concatenation
MySQL:
SELECT CONCAT('a', 'b');

PostgreSQL:
SELECT 'a' || 'b';

3. Data Types
MySQL uses TINYINT(1) for booleans, while PostgreSQL uses BOOLEAN.
MySQL supports ENUM directly; PostgreSQL requires CREATE TYPE.
PostgreSQL supports native UUID, arrays, JSONB, and full CHECK constraints.

4. Conditional Expressions
MySQL:
SELECT IF(condition, value_if_true, value_if_false);

PostgreSQL:
SELECT CASE WHEN condition THEN value_if_true ELSE value_if_false END;

5. Type Casting
MySQL:
CAST('123' AS CHAR)

PostgreSQL:
'123'::TEXT or CAST('123' AS TEXT)

6. Upsert (Insert or Update)


MySQL:
INSERT INTO table ... ON DUPLICATE KEY UPDATE ...

PostgreSQL:
INSERT INTO table ... ON CONFLICT (column) DO UPDATE SET ...

7. Full Text Search


PostgreSQL vs MySQL: Syntax Differences

PostgreSQL supports built-in full-text search using to_tsvector and tsquery.


MySQL requires FULLTEXT indexes with limited capabilities.

8. Arrays and ENUMs


PostgreSQL supports array columns (e.g., TEXT[]) and custom ENUM types.
MySQL does not support true arrays.

9. Common Table Expressions (CTEs)


PostgreSQL:
WITH cte AS (SELECT ...) SELECT * FROM cte;

10. Materialized Views


PostgreSQL supports:
CREATE MATERIALIZED VIEW view_name AS SELECT ...;

11. Boolean Support


MySQL:
Uses TINYINT(1) where 0 = false and 1 = true.

PostgreSQL:
Uses BOOLEAN type with TRUE and FALSE.

12. Other Differences


- PostgreSQL supports DROP ... CASCADE.
- PostgreSQL allows multiple schemas in a database.
- PostgreSQL is case-sensitive with quoted identifiers.

You might also like