SQL Cheat Sheet: Database-Specific Extensions

1. Introduction to Database-Specific Extensions

Each major SQL database includes custom features beyond standard SQL:

• PostgreSQL: Rich data types and extension ecosystem
• MySQL: Lightweight features with wide adoption
• SQL Server: T-SQL extensions and Microsoft ecosystem integration

Use these extensions for performance, flexibility, and advanced capabilities.

2. PostgreSQL Extensions

-- Array Data Type
SELECT ARRAY[1, 2, 3];

-- JSONB Data Type
SELECT '{"name": "Alice"}'::jsonb;

-- Full-Text Search
SELECT * FROM docs WHERE to_tsvector(content) @@ to_tsquery('python');

-- PL/pgSQL Function
CREATE FUNCTION add(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Extensions Management
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

3. MySQL Extensions

-- AUTO_INCREMENT
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY);

-- ENUM Type
CREATE TABLE shirts (size ENUM('S', 'M', 'L'));

-- REPLACE INTO (Upsert)
REPLACE INTO users (id, name) VALUES (1, 'Alice');

-- User-defined Variables
SET @total = 0;
SELECT @total := SUM(price) FROM products;

4. SQL Server Extensions

-- IDENTITY Property
CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY);

-- Variable Declaration
DECLARE @name NVARCHAR(50);
SET @name = 'Alice';

-- TRY_CAST and TRY_CONVERT
SELECT TRY_CAST('123' AS INT), TRY_CONVERT(INT, 'abc');

-- OUTPUT Clause
UPDATE products SET price = price * 1.1
OUTPUT inserted.*;

5. Best Practices and Considerations

• 🔄 Portability: Avoid relying too heavily on non-standard features
• 🧱 Abstraction: Use ORMs or middleware to handle differences
• 📄 Documentation: Clearly note all proprietary extensions
• 🔍 Compatibility: Verify support on target systems before migration

6. Comparison of Data Types