Skip to content

SQL Reference

GhostSQL supports a broad subset of PostgreSQL-compatible SQL, including arithmetic and advanced filtering.

Expressions & Operators

Arithmetic

GhostSQL supports standard arithmetic operators in SELECT and WHERE clauses: - + (Addition) - - (Subtraction) - * (Multiplication) - / (Division)

Example: SELECT (salary + 5000) * 1.1 AS bonus FROM employees;

Comparison & Pattern Matching

  • =, !=, <, >, <=, >=
  • IN (val1, val2, ...)
  • LIKE 'pattern' (use % for any string, _ for single character)

Example: SELECT * FROM users WHERE name LIKE 'A%' AND id IN (1, 10, 100);

Data Manipulation (DML)

INSERT

INSERT INTO table_name (col1, col2) VALUES (val1, val2);
Response tag: INSERT 0 n (PostgreSQL standard, where n is the row count).

UPDATE

UPDATE table_name SET col1 = val1 WHERE condition;

DELETE

DELETE FROM table_name WHERE condition;

TRUNCATE

TRUNCATE TABLE table_name;

Data Definition (DDL)

CREATE TABLE

CREATE TABLE table_name (
    column1 data_type [constraints],
    ...
);
The executing role becomes the owner of the table and has full access automatically.

ALTER TABLE

ALTER TABLE table_name ADD COLUMN column_name data_type;
ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

DROP TABLE

DROP TABLE [IF EXISTS] table_name;

Access Control (DCL)

CREATE ROLE

CREATE ROLE rolename WITH LOGIN PASSWORD 'pass';
CREATE ROLE rolename WITH LOGIN PASSWORD 'pass' SUPERUSER;

DROP ROLE

DROP ROLE [IF EXISTS] rolename;

GRANT

-- Database-level
GRANT CONNECT ON DATABASE dbname TO rolename;
GRANT CREATE ON DATABASE dbname TO rolename;

-- Table-level
GRANT SELECT ON TABLE tablename TO rolename;
GRANT INSERT, UPDATE, DELETE ON TABLE tablename TO rolename;
GRANT ALL PRIVILEGES ON TABLE tablename TO rolename;

REVOKE

REVOKE SELECT ON TABLE tablename FROM rolename;

CREATE POLICY (Row-Level Security)

CREATE POLICY policy_name ON table_name
FOR SELECT
TO all
USING (owner_column = current_user());

Transaction Control

GhostSQL accepts but does not yet fully implement ACID transactions. These statements are parsed and accepted as no-ops:

BEGIN;
COMMIT;
ROLLBACK;

Session Variables

SET datestyle TO 'ISO';   -- accepted as no-op (driver compatibility)

Metadata & Discovery

SHOW TABLES

List all tables in the current database.

SHOW COLUMNS

SHOW COLUMNS FROM table_name;

COMMENT ON

COMMENT ON TABLE table_name IS 'description';

System Catalog (pg_catalog)

SELECT relname FROM pg_catalog.pg_class WHERE relkind = 'r';
SELECT nspname FROM pg_catalog.pg_namespace;