HomeCodeSQL ConvertersSQL to PostgreSQL Converter

SQL to PostgreSQL Converter

Converting SQL Syntax to PostgreSQL – Key Differences and Modifications

This table covers various scenarios like data types, functions, auto-increment behavior, and more.

FeatureStandard SQL SyntaxPostgreSQL SyntaxDescription
AUTO_INCREMENTINT AUTO_INCREMENTSERIAL or BIGSERIALIn PostgreSQL, use SERIAL for auto-incrementing integers and BIGSERIAL for larger numbers.
Current TimestampCURRENT_TIMESTAMPNOW()PostgreSQL uses NOW() to get the current timestamp.
String ConcatenationCONCAT(str1, str2)`str1
String LengthLENGTH(string)CHAR_LENGTH(string)PostgreSQL uses CHAR_LENGTH() to get the length of a string.
String SubstringSUBSTRING(string, start, length)SUBSTRING(string FROM start FOR length)PostgreSQL uses a slightly different syntax for SUBSTRING().
Limit RowsLIMIT 5LIMIT 5PostgreSQL supports the LIMIT clause as is, no change needed.
AUTO_INCREMENT Primary KeyINT AUTO_INCREMENT PRIMARY KEYSERIAL PRIMARY KEYConvert AUTO_INCREMENT to SERIAL for primary keys.
Text Data TypeTEXTTEXTNo change needed. Both SQL and PostgreSQL use TEXT.
Date FormattingDATE_FORMAT(date, '%Y-%m-%d')TO_CHAR(date, 'YYYY-MM-DD')Use TO_CHAR() to format dates in PostgreSQL.
Date and Time FunctionsCURDATE()CURRENT_DATEUse CURRENT_DATE in PostgreSQL.
Integer Divisiona / ba / b::float or a / b::numericPostgreSQL returns integers for integer division, cast to float or numeric for decimals.
Case Sensitivity in LIKELIKE 'text%'ILIKE 'text%'Use ILIKE for case-insensitive string matching in PostgreSQL.
IF StatementIF(condition, true_value, false_value)CASE WHEN condition THEN true_value ELSE false_value ENDPostgreSQL uses CASE expressions instead of IF().
Comments-- single line comment-- single line commentComments are handled the same way in PostgreSQL. No change required.
Foreign Key ConstraintsFOREIGN KEY (col) REFERENCES table(col)FOREIGN KEY (col) REFERENCES table(col)No change needed for foreign key constraints.
Update with JoinUPDATE 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 TypeTINYINT(1) or BIT(1)BOOLEANPostgreSQL uses BOOLEAN for true/false values.
Escape Single Quote\''' (Two single quotes)PostgreSQL escapes single quotes using two single quotes ''.
Replace StringREPLACE(string, 'search', 'replace')REPLACE(string, 'search', 'replace')No change needed; REPLACE() works the same in PostgreSQL.
Join TypesINNER JOIN, LEFT JOIN, RIGHT JOIN, etc.INNER JOIN, LEFT JOIN, RIGHT JOINNo change needed; join types are the same in PostgreSQL.
Data Type: IntegerINTINTEGERPostgreSQL uses INTEGER, but INT works as well.
Data Type: DecimalDECIMAL(p, s)NUMERIC(p, s)PostgreSQL uses NUMERIC instead of DECIMAL.
ON DUPLICATE KEY UPDATEINSERT 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 ByORDER BY col ASCORDER BY col ASC NULLS LASTPostgreSQL explicitly handles NULLS FIRST or NULLS LAST in ORDER BY.
Limit OffsetLIMIT 10 OFFSET 5LIMIT 10 OFFSET 5No change needed; this works the same way in PostgreSQL.

More Tools