SQL دو-گویشی: Oracle و PostgreSQLDual-Dialect SQL: Oracle & PostgreSQL

SELECT now();
SELECT 1 + 1;

SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
-- Oracle 23ai: FROM is no longer mandatory
-- SELECT SYSDATE;

CREATE TABLE "Employees" (id int);   -- the name is exactly Employees

SELECT * FROM Employees;    -- error: relation "employees" does not exist
SELECT * FROM "Employees";  -- correct

CREATE TABLE "Employees" (id NUMBER); -- the name is exactly Employees, not EMPLOYEES

SELECT * FROM Employees;    -- ORA-00942: table or view does not exist
SELECT * FROM "Employees";  -- correct

CREATE TABLE customer (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email        varchar(320)  NOT NULL UNIQUE,
  full_name    text          NOT NULL,
  credit_limit numeric(12,2) NOT NULL DEFAULT 0,
  is_active    boolean       NOT NULL DEFAULT true,
  attributes   jsonb,
  created_at   timestamptz   NOT NULL DEFAULT now()
);

CREATE TABLE customer (
  id           NUMBER GENERATED ALWAYS AS IDENTITY,
  email        VARCHAR2(320 CHAR) NOT NULL,
  full_name    VARCHAR2(200 CHAR) NOT NULL,
  credit_limit NUMBER(12,2) DEFAULT 0 NOT NULL,   -- DEFAULT before NOT NULL
  is_active    NUMBER(1) DEFAULT 1 NOT NULL,      -- 23ai: BOOLEAN DEFAULT TRUE
  attributes   CLOB CHECK (attributes IS JSON),   -- 21c+: native JSON type
  created_at   TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT pk_customer PRIMARY KEY (id),
  CONSTRAINT uq_customer_email UNIQUE (email)
);

SELECT 1 / 2;              -- 0  (integer division!)
SELECT 1.0 / 2;            -- 0.5
SELECT count(*)::numeric / total FROM stat;   -- the correct percentage

SELECT 1 / 2 FROM DUAL;    -- 0.5 (everything is NUMBER)
SELECT 1.0 / 2 FROM DUAL;  -- 0.5
SELECT COUNT(*) / total FROM stat GROUP BY total;  -- always fractional

SELECT '' IS NULL;        -- false
SELECT length('');        -- 0
SELECT '' = '';           -- true

SELECT CASE WHEN '' IS NULL THEN 'yes' ELSE 'no' END FROM DUAL;  -- yes
SELECT LENGTH('') FROM DUAL;                          -- NULL (not zero!)
SELECT COUNT(*) FROM customer WHERE full_name = '';   -- always 0

CREATE TABLE account (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  is_active boolean NOT NULL DEFAULT false
);
SELECT * FROM account WHERE is_active;      -- the column itself is a boolean expression

CREATE TABLE account (
  id        NUMBER GENERATED ALWAYS AS IDENTITY,
  is_active NUMBER(1) DEFAULT 0 NOT NULL
            CONSTRAINT ck_active CHECK (is_active IN (0, 1)),
  CONSTRAINT pk_account PRIMARY KEY (id)
);
-- Oracle 23ai: is_active BOOLEAN DEFAULT FALSE NOT NULL
SELECT * FROM account WHERE is_active = 1;  -- through 19c you must compare

-- the standard form: works exactly the same on Oracle
SELECT COALESCE(phone, mobile, 'N/A') AS contact,
       CASE status WHEN 'A' THEN 'Active'
                   WHEN 'I' THEN 'Inactive'
                   ELSE 'Unknown' END AS status_label
FROM   customer;

-- Oracle-native style (not portable)
SELECT NVL(phone, 'N/A') AS contact,
       DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') AS status_label
FROM   customer;

-- the same query in standard style — write this one
SELECT COALESCE(phone, mobile, 'N/A') AS contact,
       CASE status WHEN 'A' THEN 'Active'
                   WHEN 'I' THEN 'Inactive'
                   ELSE 'Unknown' END AS status_label
FROM   customer;

SELECT first_name || ' ' || last_name AS full_name FROM person;
SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4);   -- 'Post' (standard)
SELECT substr('PostgreSQL', 1, 4);             -- 'Post'
SELECT POSITION('@' IN 'a@b.com');             -- 2 (standard)
SELECT strpos('a@b.com', '@');                 -- 2

SELECT first_name || ' ' || last_name AS full_name FROM person;
-- Oracle has no standard SUBSTRING
SELECT SUBSTR('PostgreSQL', 1, 4) FROM DUAL;   -- 'Post' (1-based)
-- Oracle has no POSITION; INSTR is the equivalent
SELECT INSTR('a@b.com', '@') FROM DUAL;        -- 2 (0 if absent)

SELECT * FROM person WHERE email ~* '^[a-z0-9._%+-]+@example\.com$';
SELECT regexp_replace('a  b   c', '\s+', ' ', 'g');   -- 'a b c'

-- case-insensitive search via a functional index (portable)
CREATE INDEX idx_person_email_lower ON person (lower(email));
SELECT * FROM person WHERE lower(email) = lower(:input);

SELECT * FROM person WHERE REGEXP_LIKE(email, '^[a-z0-9._%+-]+@example\.com$', 'i');
SELECT REGEXP_REPLACE('a  b   c', '[[:space:]]+', ' ') FROM DUAL;  -- 'a b c'

-- case-insensitive search via a functional index (portable)
CREATE INDEX idx_person_email_lower ON person (LOWER(email));
SELECT * FROM person WHERE LOWER(email) = LOWER(:input);

SELECT now();                 -- timestamptz, the transaction-start instant
SELECT clock_timestamp();     -- real wall clock, this exact moment
SELECT CURRENT_TIMESTAMP;     -- standard, same as now()

SELECT SYSDATE FROM DUAL;           -- DATE, server time, fresh every call
SELECT SYSTIMESTAMP FROM DUAL;      -- server TIMESTAMP WITH TIME ZONE
SELECT CURRENT_TIMESTAMP FROM DUAL; -- the session's time zone

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS');
SELECT to_date('2026-07-23', 'YYYY-MM-DD');
SELECT DATE '2026-07-23';                       -- standard literal
SELECT TIMESTAMP '2026-07-23 14:30:00';

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('2026-07-23', 'YYYY-MM-DD') FROM DUAL;
SELECT DATE '2026-07-23' FROM DUAL;             -- standard literal
SELECT TIMESTAMP '2026-07-23 14:30:00' FROM DUAL;

SELECT now() + interval '1 day';          -- tomorrow
SELECT now() + interval '1 month';        -- the ADD_MONTHS equivalent
SELECT date_trunc('month', now());        -- start of month
SELECT age(now(), created_at) FROM customer;
SELECT (end_at - start_at) FROM job_run;  -- difference = interval

SELECT SYSDATE + 1 FROM DUAL;             -- tomorrow (a number = days)
SELECT ADD_MONTHS(SYSDATE, 1) FROM DUAL;  -- the + interval '1 month' equivalent
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;    -- start of month
SELECT MONTHS_BETWEEN(SYSDATE, created_at) FROM customer;
SELECT (end_at - start_at) FROM job_run;  -- difference of two DATEs = days!

SELECT sku FROM inventory
EXCEPT                      -- ANSI standard
SELECT sku FROM discontinued;

SELECT sku FROM inventory
EXCEPT ALL                  -- without deduplication
SELECT sku FROM discontinued;

SELECT sku FROM inventory
MINUS                       -- Oracle's historical name, the only option through 19c
SELECT sku FROM discontinued;

-- Oracle 21c+ also added EXCEPT / EXCEPT ALL / MINUS ALL / INTERSECT ALL
SELECT sku FROM inventory
EXCEPT ALL
SELECT sku FROM discontinued;

-- page three, 20 rows per page
SELECT id, name FROM product
ORDER  BY created_at DESC, id DESC
LIMIT  20 OFFSET 40;

-- PostgreSQL supports the standard form too
SELECT id, name FROM product
ORDER BY created_at DESC, id DESC
OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY;

-- Oracle 12c+ : only the ANSI standard form
SELECT id, name FROM product
ORDER  BY created_at DESC, id DESC
OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY;

-- Oracle has no LIMIT at all:
-- SELECT id, name FROM product LIMIT 20 OFFSET 40;  -- ORA-00933

-- PostgreSQL has no ROWNUM pseudo-column; row_number() is the equivalent
SELECT * FROM (
  SELECT p.*, row_number() OVER (ORDER BY created_at DESC, id DESC) AS rn
  FROM   product p
) t
WHERE rn > 40 AND rn <= 60;

-- Legacy Oracle: the triple-nested ROWNUM pattern
SELECT * FROM (
  SELECT inner_q.*, ROWNUM rn FROM (
    SELECT id, name FROM product
    ORDER  BY created_at DESC, id DESC
  ) inner_q
  WHERE ROWNUM <= 60          -- offset + limit
)
WHERE rn > 40;                -- offset

-- row-value comparison: PostgreSQL has it and uses the composite index
SELECT id, name FROM product
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
FETCH FIRST 20 ROWS ONLY;

-- Oracle has no row-value comparison with <; you must expand it
SELECT id, name FROM product
WHERE created_at < :last_created_at
   OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESC
FETCH FIRST 20 ROWS ONLY;

CREATE TABLE orders (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total  numeric(12,2) NOT NULL
);
-- modes: ALWAYS | BY DEFAULT
INSERT INTO orders (total) VALUES (99.90);

CREATE TABLE orders (
  id     NUMBER GENERATED ALWAYS AS IDENTITY,
  total  NUMBER(12,2) NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (id)
);
-- modes: ALWAYS | BY DEFAULT | BY DEFAULT ON NULL
INSERT INTO orders (total) VALUES (99.90);

-- nextval/currval are functions and the sequence name is a string
CREATE SEQUENCE seq_order START 1 INCREMENT 1 CACHE 100;
INSERT INTO orders (id, total) VALUES (nextval('seq_order'), 99.90);
SELECT currval('seq_order');
-- PostgreSQL's default CACHE is 1

-- NEXTVAL/CURRVAL are pseudo-columns
CREATE SEQUENCE seq_order START WITH 1 INCREMENT BY 1 CACHE 100 NOORDER;
INSERT INTO orders (id, total) VALUES (seq_order.NEXTVAL, 99.90);
SELECT seq_order.CURRVAL FROM DUAL;
-- Oracle's defaults are CACHE 20 and NOORDER

SELECT setval(pg_get_serial_sequence('orders', 'id'),
              (SELECT COALESCE(MAX(id), 0) FROM orders));
SELECT setval('seq_order', 1000, true);   -- for a standalone sequence

ALTER SEQUENCE seq_order RESTART START WITH 1001;   -- since 12.2

-- for an identity column, advance the counter to the max value
ALTER TABLE orders MODIFY (id GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE));

-- PostgreSQL: upsert on a unique key (since 9.5)
INSERT INTO inventory (sku, qty, updated_at)
VALUES ('ABC-1', 10, now())
ON CONFLICT (sku)
DO UPDATE SET qty        = inventory.qty + EXCLUDED.qty,
              updated_at = EXCLUDED.updated_at
RETURNING id, qty;
-- DO NOTHING is available if you want to skip the conflict silently

-- Oracle: MERGE (SQL standard, since 9i) — there is no ON CONFLICT
MERGE INTO inventory t
USING (SELECT 'ABC-1' AS sku, 10 AS qty FROM DUAL) s
ON (t.sku = s.sku)
WHEN MATCHED THEN
  UPDATE SET t.qty = t.qty + s.qty, t.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (sku, qty, updated_at) VALUES (s.sku, s.qty, SYSTIMESTAMP);

-- PostgreSQL 17: MERGE with RETURNING and merge_action()
MERGE INTO inventory t
USING (VALUES ('ABC-1', 10)) AS s(sku, qty)
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
RETURNING merge_action() AS action, t.sku, t.qty;

-- Oracle 23ai: MERGE with RETURNING and OLD/NEW values
DECLARE
  v_old NUMBER; v_new NUMBER;
BEGIN
  MERGE INTO inventory t
  USING (SELECT 'ABC-1' AS sku, 10 AS qty FROM DUAL) s
  ON (t.sku = s.sku)
  WHEN MATCHED THEN UPDATE SET t.qty = t.qty + s.qty
  WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty)
  RETURNING OLD t.qty, NEW t.qty INTO v_old, v_new;
END;
/

-- RETURNING yields a real result set
INSERT INTO orders (total) VALUES (99.90) RETURNING id, created_at;
UPDATE orders SET total = total * 1.1 WHERE id = 5 RETURNING id, total;
DELETE FROM orders WHERE id = 5 RETURNING id;

-- RETURNING ... INTO targets PL/SQL variables and binds
DECLARE
  v_id    orders.id%TYPE;
  v_total orders.total%TYPE;
BEGIN
  INSERT INTO orders (total) VALUES (99.90) RETURNING id INTO v_id;
  UPDATE orders SET total = total * 1.1 WHERE id = v_id
    RETURNING total INTO v_total;
END;
/

SELECT product_id, category, sales,
       RANK()     OVER (PARTITION BY category ORDER BY sales DESC) AS rk,
       SUM(sales) OVER (PARTITION BY category)                     AS cat_total,
       LAG(sales) OVER (PARTITION BY category ORDER BY month)      AS prev_month
FROM   product_sales;

SELECT product_id, category, sales,
       RANK()     OVER (PARTITION BY category ORDER BY sales DESC) AS rk,
       SUM(sales) OVER (PARTITION BY category)                     AS cat_total,
       LAG(sales) OVER (PARTITION BY category ORDER BY month)      AS prev_month
FROM   product_sales;

SELECT department_id,
       STRING_AGG(last_name, ', ' ORDER BY last_name) AS names
FROM   employees
GROUP  BY department_id;

SELECT department_id,
       LISTAGG(last_name, ', ' ON OVERFLOW TRUNCATE '...')
         WITHIN GROUP (ORDER BY last_name) AS names
FROM   employees
GROUP  BY department_id;

-- ANSI standard: WITH RECURSIVE (the RECURSIVE keyword is mandatory)
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS lvl, name::text AS path
  FROM   employee WHERE manager_id IS NULL          -- the roots
  UNION ALL
  SELECT e.id, e.name, e.manager_id, o.lvl + 1, o.path || '/' || e.name
  FROM   employee e
  JOIN   org o ON e.manager_id = o.id               -- the recursive step
)
SELECT lvl, path, name FROM org ORDER BY path;

-- Oracle-native style: CONNECT BY
SELECT LEVEL AS lvl,
       SYS_CONNECT_BY_PATH(name, '/') AS path,
       name
FROM   employee
START WITH manager_id IS NULL                       -- the roots
CONNECT BY PRIOR id = manager_id                    -- the recursive step
ORDER SIBLINGS BY name;

-- Oracle has supported the standard WITH form since 11gR2 (no RECURSIVE keyword)

CREATE TABLE doc (id bigint GENERATED ALWAYS AS IDENTITY, body jsonb);

SELECT body ->> 'name'                AS name,      -- text
       body -> 'address' ->> 'city'   AS city,      -- nested
       body @> '{"active": true}'     AS is_active  -- containment
FROM   doc
WHERE  body @> '{"type": "customer"}';              -- benefits from a GIN index

CREATE INDEX idx_doc_body ON doc USING gin (body);

-- PostgreSQL 17: standard SQL/JSON functions
SELECT JSON_VALUE(body, '$.address.city') FROM doc WHERE JSON_EXISTS(body, '$.type');

CREATE TABLE doc (id NUMBER GENERATED ALWAYS AS IDENTITY, body JSON);
-- before 21c: body CLOB CHECK (body IS JSON)

SELECT d.body.name                       AS name,   -- dot notation (needs an alias)
       JSON_VALUE(d.body, '$.address.city') AS city,
       JSON_QUERY(d.body, '$.tags')      AS tags
FROM   doc d
WHERE  JSON_EXISTS(d.body, '$?(@.type == "customer")');

CREATE SEARCH INDEX idx_doc_body ON doc (body) FOR JSON;

-- the same standard functions, compatible with PostgreSQL 17
SELECT JSON_VALUE(body, '$.address.city') FROM doc;

-- both the definition and the data are temporary; dropped at session end
CREATE TEMP TABLE stage_order (
  id bigint, total numeric(12,2)
) ON COMMIT DELETE ROWS;   -- or ON COMMIT DROP

INSERT INTO stage_order SELECT id, total FROM orders
WHERE created_at > now() - interval '1 day';
ANALYZE stage_order;       -- so the planner isn't blind

-- the definition is permanent and lives in the dictionary; only data is session-scoped
CREATE GLOBAL TEMPORARY TABLE stage_order (
  id NUMBER, total NUMBER(12,2)
) ON COMMIT DELETE ROWS;   -- or ON COMMIT PRESERVE ROWS

INSERT INTO stage_order SELECT id, total FROM orders
WHERE created_at > SYSDATE - 1;
-- Oracle 18c+ : CREATE PRIVATE TEMPORARY TABLE ora$ptt_stage AS SELECT ...

-- levels: READ COMMITTED (default), REPEATABLE READ, SERIALIZABLE (true SSI)
BEGIN ISOLATION LEVEL REPEATABLE READ;
  SELECT SUM(total) FROM orders;
COMMIT;

VACUUM (ANALYZE) orders;
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

-- levels: READ COMMITTED (default), SERIALIZABLE, READ ONLY — no REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT SUM(total) FROM orders;
COMMIT;

SELECT tablespace_name, status, SUM(bytes) FROM dba_undo_extents
GROUP BY tablespace_name, status;

-- work queue: each worker takes a batch without contention
SELECT id, payload FROM job_queue
WHERE  status = 'READY'
ORDER  BY created_at
FOR UPDATE SKIP LOCKED
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;   -- fail immediately
SET lock_timeout = '5s';   -- PostgreSQL has no "WAIT n"

-- same pattern, same syntax
SELECT id, payload FROM job_queue
WHERE  status = 'READY'
ORDER  BY created_at
FOR UPDATE SKIP LOCKED
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;   -- ORA-00054
SELECT * FROM orders WHERE id = 1 FOR UPDATE WAIT 5;   -- wait 5 seconds

BEGIN;
  ALTER TABLE orders ADD COLUMN note text;
  CREATE INDEX idx_orders_note ON orders (note);
ROLLBACK;             -- nothing remains

-- every DDL commits itself; ROLLBACK is a no-op
ALTER TABLE orders ADD (note VARCHAR2(500 CHAR));   -- implicit commit
CREATE INDEX idx_orders_note ON orders (note);      -- implicit commit
ROLLBACK;             -- nothing is undone

ALTER TABLE customer ADD CONSTRAINT uq_customer_email UNIQUE (email);
-- message: duplicate key value violates unique constraint "uq_customer_email"

ALTER TABLE customer ADD CONSTRAINT uq_customer_email UNIQUE (email);
-- message: ORA-00001: unique constraint (APP.UQ_CUSTOMER_EMAIL) violated

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;        -- estimated

-- the real plan with timing and I/O — write this one
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE customer_id = 42;

ANALYZE orders;   -- refresh statistics

EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 42;  -- estimated
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- the real plan with execution statistics — write this one
SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));

BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); END;
/

-- PostgreSQL has no in-query hints (a deliberate project decision)
SET enable_seqscan = off;
SELECT * FROM orders WHERE customer_id = 42;
RESET enable_seqscan;
-- for real hints you must install the pg_hint_plan extension

-- Oracle has rich, official hints
SELECT /*+ INDEX(o idx_orders_customer) */ * FROM orders o WHERE o.customer_id = 42;
SELECT /*+ LEADING(c o) USE_NL(o) */ *
FROM   customer c JOIN orders o ON o.customer_id = c.id;
SELECT /*+ PARALLEL(o, 4) */ COUNT(*) FROM orders o;

-- the most expensive queries (requires the pg_stat_statements extension)
SELECT calls, mean_exec_time, rows, query
FROM   pg_stat_statements ORDER BY total_exec_time DESC
FETCH FIRST 10 ROWS ONLY;

SELECT pid, state, wait_event_type, wait_event, query
FROM   pg_stat_activity WHERE state <> 'idle';

-- the most expensive queries (no extra licence needed)
SELECT executions, elapsed_time/GREATEST(executions,1) AS avg_us, sql_text
FROM   v$sql ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

SELECT sid, status, event, sql_id
FROM   v$session WHERE status = 'ACTIVE';

CREATE OR REPLACE FUNCTION order_total(p_id bigint)
RETURNS numeric
LANGUAGE plpgsql
AS $$
DECLARE
  v_total numeric := 0;
BEGIN
  SELECT COALESCE(SUM(qty * price), 0) INTO v_total
  FROM   order_line WHERE order_id = p_id;
  RETURN v_total;
EXCEPTION
  WHEN no_data_found THEN RETURN 0;
END;
$$;

SELECT order_total(42);

CREATE OR REPLACE FUNCTION order_total(p_id NUMBER)
RETURN NUMBER
IS
  v_total NUMBER := 0;
BEGIN
  SELECT NVL(SUM(qty * price), 0) INTO v_total
  FROM   order_line WHERE order_id = p_id;
  RETURN v_total;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RETURN 0;
END;
/

SELECT order_total(42) FROM DUAL;

-- the fastest path: COPY (use \copy in psql for a client-side file)
COPY orders (id, customer_id, total)
FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

ANALYZE orders;   -- refresh statistics after a bulk load

-- direct-path insert with the proprietary APPEND hint
INSERT /*+ APPEND */ INTO orders (id, customer_id, total)
SELECT id, customer_id, total FROM ext_orders;
COMMIT;   -- the table stays locked until you commit

-- outside SQL: SQL*Loader or an external table
BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS'); END;
/