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. |