Converting SQL Syntax to PostgreSQL – Key Differences and Modifications
This table covers various scenarios like data types, functions, auto-increment behavior, and more.
| Feature | Standard SQL Syntax | PostgreSQL Syntax | Description |
|---|---|---|---|
| AUTO_INCREMENT | INT AUTO_INCREMENT | SERIAL or BIGSERIAL | In PostgreSQL, use SERIAL for auto-incrementing integers and BIGSERIAL for larger numbers. |
| Current Timestamp | CURRENT_TIMESTAMP | NOW() | PostgreSQL uses NOW() to get the current timestamp. |
| String Concatenation | CONCAT(str1, str2) | `str1 | |
| String Length | LENGTH(string) | CHAR_LENGTH(string) | PostgreSQL uses CHAR_LENGTH() to get the length of a string. |
| String Substring | SUBSTRING(string, start, length) | SUBSTRING(string FROM start FOR length) | PostgreSQL uses a slightly different syntax for SUBSTRING(). |
| Limit Rows | LIMIT 5 | LIMIT 5 | PostgreSQL supports the LIMIT clause as is, no change needed. |
| AUTO_INCREMENT Primary Key | INT AUTO_INCREMENT PRIMARY KEY | SERIAL PRIMARY KEY | Convert AUTO_INCREMENT to SERIAL for primary keys. |
| Text Data Type | TEXT | TEXT | No change needed. Both SQL and PostgreSQL use TEXT. |
| Date Formatting | DATE_FORMAT(date, '%Y-%m-%d') | TO_CHAR(date, 'YYYY-MM-DD') | Use TO_CHAR() to format dates in PostgreSQL. |
| Date and Time Functions | CURDATE() | CURRENT_DATE | Use CURRENT_DATE in PostgreSQL. |
| Integer Division | a / b | a / b::float or a / b::numeric | PostgreSQL returns integers for integer division, cast to float or numeric for decimals. |
| Case Sensitivity in LIKE | LIKE 'text%' | ILIKE 'text%' | Use ILIKE for case-insensitive string matching in PostgreSQL. |
| IF Statement | IF(condition, true_value, false_value) | CASE WHEN condition THEN true_value ELSE false_value END | PostgreSQL uses CASE expressions instead of IF(). |
| Comments | -- single line comment | -- single line comment | Comments are handled the same way in PostgreSQL. No change required. |
| Foreign Key Constraints | FOREIGN KEY (col) REFERENCES table(col) | FOREIGN KEY (col) REFERENCES table(col) | No change needed for foreign key constraints. |
| Update with Join | UPDATE t1 INNER JOIN t2 ON ... SET t1.col = ... | UPDATE t1 SET t1.col = ... FROM t2 WHERE ... | PostgreSQL uses FROM in UPDATE statements with joins. |
| Boolean Data Type | TINYINT(1) or BIT(1) | BOOLEAN | PostgreSQL uses BOOLEAN for true/false values. |
| Escape Single Quote | \' | '' (Two single quotes) | PostgreSQL escapes single quotes using two single quotes ''. |
| Replace String | REPLACE(string, 'search', 'replace') | REPLACE(string, 'search', 'replace') | No change needed; REPLACE() works the same in PostgreSQL. |
| Join Types | INNER JOIN, LEFT JOIN, RIGHT JOIN, etc. | INNER JOIN, LEFT JOIN, RIGHT JOIN | No change needed; join types are the same in PostgreSQL. |
| Data Type: Integer | INT | INTEGER | PostgreSQL uses INTEGER, but INT works as well. |
| Data Type: Decimal | DECIMAL(p, s) | NUMERIC(p, s) | PostgreSQL uses NUMERIC instead of DECIMAL. |
| ON DUPLICATE KEY UPDATE | INSERT INTO table (...) VALUES (...) ON DUPLICATE KEY UPDATE ... | INSERT INTO table (...) VALUES (...) ON CONFLICT (...) DO UPDATE SET ... | Use ON CONFLICT in PostgreSQL for upsert functionality. |
| Null Handling in Order By | ORDER BY col ASC | ORDER BY col ASC NULLS LAST | PostgreSQL explicitly handles NULLS FIRST or NULLS LAST in ORDER BY. |
| Limit Offset | LIMIT 10 OFFSET 5 | LIMIT 10 OFFSET 5 | No change needed; this works the same way in PostgreSQL. |