SQL Dialect Translator

Convert SQL queries between different database dialects (PostgreSQL, MySQL, SQL Server, Oracle, SQLite).

Trigger Conditions

Activate this skill when the user:

•Says "convert to PostgreSQL/MySQL/etc."
•Asks to migrate queries between databases
•Needs help with dialect-specific syntax
•Asks about differences between SQL dialects
•Has queries failing after database migration

Translation Workflow

code

1. IDENTIFY    -> Detect source dialect
2. ANALYZE     -> Find dialect-specific features
3. MAP         -> Identify equivalent features in target
4. TRANSLATE   -> Convert syntax
5. VALIDATE    -> Verify correctness
6. OPTIMIZE    -> Apply target dialect best practices

Dialect Feature Comparison

Data Types

Feature	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
Auto-increment	SERIAL, BIGSERIAL	AUTO_INCREMENT	IDENTITY	SEQUENCE	INTEGER PRIMARY KEY
Boolean	BOOLEAN	TINYINT(1), BOOLEAN	BIT	NUMBER(1)	INTEGER
Text (unlimited)	TEXT	LONGTEXT	VARCHAR(MAX)	CLOB	TEXT
Binary	BYTEA	BLOB	VARBINARY(MAX)	BLOB	BLOB
JSON	JSON, JSONB	JSON	NVARCHAR(MAX)	JSON (21c+)	TEXT
UUID	UUID	CHAR(36)	UNIQUEIDENTIFIER	RAW(16)	TEXT
Date	DATE	DATE	DATE	DATE	TEXT
Timestamp	TIMESTAMP	DATETIME	DATETIME2	TIMESTAMP	TEXT
Array	ARRAY[]	JSON	N/A	VARRAY	N/A

String Functions

Operation	PostgreSQL	MySQL	SQL Server	Oracle
Concatenate	`\|\|` or CONCAT	CONCAT	+ or CONCAT	`\|\|`
Substring	SUBSTRING(s, start, len)	SUBSTRING(s, start, len)	SUBSTRING(s, start, len)	SUBSTR(s, start, len)
Length	LENGTH	LENGTH, CHAR_LENGTH	LEN	LENGTH
Lowercase	LOWER	LOWER	LOWER	LOWER
Uppercase	UPPER	UPPER	UPPER	UPPER
Trim	TRIM	TRIM	LTRIM, RTRIM	TRIM
Position	POSITION(x IN y)	LOCATE(x, y)	CHARINDEX(x, y)	INSTR(y, x)
Replace	REPLACE	REPLACE	REPLACE	REPLACE
Pad left	LPAD	LPAD	RIGHT(REPLICATE...)	LPAD
Regex match	~	REGEXP	LIKE (limited)	REGEXP_LIKE

Date/Time Functions

Operation	PostgreSQL	MySQL	SQL Server	Oracle
Current date	CURRENT_DATE	CURDATE()	GETDATE()	SYSDATE
Current timestamp	CURRENT_TIMESTAMP	NOW()	GETDATE()	SYSTIMESTAMP
Extract part	EXTRACT(part FROM date)	EXTRACT(part FROM date)	DATEPART(part, date)	EXTRACT(part FROM date)
Date add	date + INTERVAL '1 day'	DATE_ADD(date, INTERVAL 1 DAY)	DATEADD(day, 1, date)	date + 1
Date diff	date1 - date2	DATEDIFF(date1, date2)	DATEDIFF(day, date1, date2)	date1 - date2
Format date	TO_CHAR(date, 'fmt')	DATE_FORMAT(date, 'fmt')	FORMAT(date, 'fmt')	TO_CHAR(date, 'fmt')
Parse date	TO_DATE(str, 'fmt')	STR_TO_DATE(str, 'fmt')	CONVERT(date, str)	TO_DATE(str, 'fmt')
Truncate	DATE_TRUNC('month', date)	DATE_FORMAT(date, '%Y-%m-01')	DATETRUNC(month, date)	TRUNC(date, 'MM')

Limiting Results

Dialect	Syntax
PostgreSQL	`LIMIT 10 OFFSET 20`
MySQL	`LIMIT 10 OFFSET 20` or `LIMIT 20, 10`
SQL Server	`OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY` (2012+) or `TOP 10`
Oracle	`OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY` (12c+) or `ROWNUM`
SQLite	`LIMIT 10 OFFSET 20`

Identifier Quoting

Dialect	Syntax
PostgreSQL	Double quotes: `"table"`
MySQL	Backticks: `table`
SQL Server	Square brackets: `[table]` or double quotes
Oracle	Double quotes: `"TABLE"` (case-sensitive)
SQLite	Double quotes, backticks, or square brackets

NULL Handling

Operation	PostgreSQL	MySQL	SQL Server	Oracle
Coalesce	COALESCE, NULLIF	COALESCE, IFNULL, NULLIF	COALESCE, ISNULL, NULLIF	COALESCE, NVL, NULLIF
NULL-safe equal	IS NOT DISTINCT FROM	<=>	N/A	N/A
First non-null	COALESCE	COALESCE	COALESCE	NVL, COALESCE

Conditional Logic

Operation	PostgreSQL	MySQL	SQL Server	Oracle
If-then	CASE WHEN	CASE WHEN, IF()	CASE WHEN, IIF()	CASE WHEN, DECODE
Null check	NULLIF, COALESCE	NULLIF, IFNULL	NULLIF, ISNULL	NVL, NVL2

Upsert/Merge

PostgreSQL:

sql

INSERT INTO table (id, name) VALUES (1, 'test')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

MySQL:

sql

INSERT INTO table (id, name) VALUES (1, 'test')
ON DUPLICATE KEY UPDATE name = VALUES(name);

SQL Server:

sql

MERGE INTO table AS target
USING (VALUES (1, 'test')) AS source (id, name)
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

Oracle:

sql

MERGE INTO table target
USING (SELECT 1 AS id, 'test' AS name FROM dual) source
ON (target.id = source.id)
WHEN MATCHED THEN UPDATE SET target.name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

Common Table Expressions

All modern versions support standard CTE syntax:

sql

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

Recursive CTEs:

•PostgreSQL: WITH RECURSIVE
•MySQL 8.0+: WITH RECURSIVE
•SQL Server: WITH (recursive by default when self-referencing)
•Oracle: WITH (use CONNECT BY for older versions)

Window Functions

All modern databases support standard window functions. Key differences:

Feature	PostgreSQL	MySQL	SQL Server	Oracle
FILTER clause	Yes	No	No	No
Frame: GROUPS	Yes	Yes (8.0+)	No	Yes
PERCENTILE_CONT	Yes	No (use variable)	Yes	Yes
STRING_AGG	STRING_AGG	GROUP_CONCAT	STRING_AGG	LISTAGG

JSON Support

PostgreSQL:

sql

SELECT data->>'name' FROM table;
SELECT data->'nested'->>'field' FROM table;
SELECT data @> '{"key": "value"}' FROM table;

MySQL:

sql

SELECT JSON_EXTRACT(data, '$.name') FROM table;
SELECT data->'$.name' FROM table;
SELECT JSON_CONTAINS(data, '"value"', '$.key') FROM table;

SQL Server:

sql

SELECT JSON_VALUE(data, '$.name') FROM table;
SELECT JSON_QUERY(data, '$.nested') FROM table;

Translation Examples

LIMIT/OFFSET Translation

PostgreSQL:

sql

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

To SQL Server:

sql

SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

To Oracle (12c+):

sql

SELECT * FROM users ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

To Oracle (11g):

sql

SELECT * FROM (
    SELECT t.*, ROWNUM rn FROM (
        SELECT * FROM users ORDER BY id
    ) t WHERE ROWNUM <= 30
) WHERE rn > 20;

Auto-Increment Translation

PostgreSQL:

sql

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

To MySQL:

sql

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

To SQL Server:

sql

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

To Oracle:

sql

CREATE TABLE users (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(100)
);

String Concatenation Translation

PostgreSQL:

sql

SELECT first_name || ' ' || last_name AS full_name FROM users;

To MySQL:

sql

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

To SQL Server:

sql

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

Date Arithmetic Translation

PostgreSQL:

sql

SELECT created_at + INTERVAL '30 days' FROM users;
SELECT DATE_TRUNC('month', created_at) FROM users;

To MySQL:

sql

SELECT DATE_ADD(created_at, INTERVAL 30 DAY) FROM users;
SELECT DATE_FORMAT(created_at, '%Y-%m-01') FROM users;

To SQL Server:

sql

SELECT DATEADD(day, 30, created_at) FROM users;
SELECT DATETRUNC(month, created_at) FROM users;  -- SQL Server 2022+
SELECT DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1) FROM users;  -- Earlier

To Oracle:

sql

SELECT created_at + 30 FROM users;
SELECT TRUNC(created_at, 'MM') FROM users;

Boolean Translation

PostgreSQL:

sql

SELECT * FROM users WHERE is_active = TRUE;

To MySQL:

sql

SELECT * FROM users WHERE is_active = 1;
SELECT * FROM users WHERE is_active = TRUE;  -- Also works

To SQL Server:

sql

SELECT * FROM users WHERE is_active = 1;

To Oracle:

sql

SELECT * FROM users WHERE is_active = 1;

Output Format

When translating queries:

•Source: Original query with identified dialect
•Target: Translated query for target dialect
•Changes Made: List of modifications
•Caveats: Any functionality differences or limitations
•Alternatives: Other approaches if applicable

Additional Resources

File	Content
examples.md	Complete translation examples