PostgreSQL vs MySQL

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

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

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

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

SELECT * FROM users
LIMIT 10 OFFSET 20;

SELECT * FROM users
LIMIT 20, 10;

SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;

SELECT NOW();
SELECT CURRENT_TIMESTAMP();
SELECT CURDATE();

SELECT * FROM users
WHERE name ILIKE '%john%';

SELECT * FROM users
WHERE name LIKE '%john%';

SELECT * FROM users
WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';

SELECT * FROM users
WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$';

CREATE TABLE settings (
  id SERIAL PRIMARY KEY,
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  is_active TINYINT(1) DEFAULT 1
);

SELECT data->>'name' AS name
FROM users
WHERE data->>'age' > '25';

SELECT JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) AS name
FROM users
WHERE JSON_EXTRACT(data, '$.age') > 25;

INSERT INTO users (id, name, email)
VALUES (1, 'John', '[email protected]')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

INSERT INTO users (id, name, email)
VALUES (1, 'John', '[email protected]')
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  email = VALUES(email);

CREATE SEQUENCE user_id_seq;
SELECT nextval('user_id_seq');

-- No direct sequence support
-- Use AUTO_INCREMENT or application logic

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  tags TEXT[]
);

SELECT * FROM products
WHERE 'electronics' = ANY(tags);

-- No native array type
-- Use JSON or separate table
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tags JSON
);

SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('database & performance');

SELECT * FROM articles
WHERE MATCH(content) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

SELECT
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

SELECT
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

SELECT
  name,
  department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

SELECT
  name,
  department,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

WITH RECURSIVE subordinates AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

SELECT LENGTH('hello') AS len;
SELECT CHAR_LENGTH('hello') AS len;

SELECT LENGTH('hello') AS len;
SELECT CHAR_LENGTH('hello') AS len;

SELECT * FROM generate_series(1, 10);
SELECT * FROM generate_series(
  '2024-01-01'::DATE,
  '2024-01-31'::DATE,
  '1 day'::INTERVAL
);

-- No direct equivalent
-- Use recursive CTE or temporary table
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

INSERT INTO users (name, email)
VALUES ('John', '[email protected]')
RETURNING id;

INSERT INTO users (name, email)
VALUES ('John', '[email protected]');
SELECT LAST_INSERT_ID();

UPDATE orders o
SET status = 'shipped'
FROM customers c
WHERE o.customer_id = c.id
  AND c.country = 'USA';

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'shipped'
WHERE c.country = 'USA';

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id
  AND c.country = 'USA';

DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'USA';

CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  body TEXT
);

CREATE TABLE notes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  body TEXT
);

CREATE TABLE accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT
);

CREATE TABLE accounts (
  id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  name TEXT
);

SELECT SUBSTRING(name FROM 1 FOR 3) FROM users;

SELECT SUBSTRING(name, 1, 3) FROM users;

SELECT '42'::INTEGER AS n;
SELECT CAST('42' AS INTEGER) AS n;

SELECT CAST('42' AS SIGNED) AS n;

SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM orders;

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM orders;

SELECT NOW() + INTERVAL '7 days';

SELECT NOW() + INTERVAL 7 DAY;

created_at TIMESTAMPTZ DEFAULT NOW()

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

CREATE TYPE mood AS ENUM ('happy', 'sad');
CREATE TABLE t (m mood);

CREATE TABLE t (
  m ENUM('happy', 'sad')
);

\dt
-- or
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

SHOW TABLES;

INSERT INTO tags (name) VALUES
  ('a'), ('b'), ('c');

INSERT INTO tags (name) VALUES
  ('a'), ('b'), ('c');