Data Definition Language (DDL)

DDL statements create and manage table structures in Teide. Tables are columnar and stored in-memory for high-performance analytical queries.

CREATE TABLE

Creates a new table with explicitly defined columns and types.

CREATE TABLE table_name (
  column1 type1,
  column2 type2,
  ...
);

Example

CREATE TABLE employees (
  id INTEGER,
  name VARCHAR,
  department SYM,
  salary REAL,
  hired DATE,
  active BOOLEAN
);

CREATE TABLE AS SELECT (CTAS)

Creates a new table from the result of a query. Column names and types are inferred from the query output.

CREATE TABLE table_name AS
  SELECT ...;

Examples

-- Create from a query
CREATE TABLE senior_employees AS
  SELECT name, department, salary
  FROM employees
  WHERE salary > 100000;

-- Create from CSV file
CREATE TABLE sales AS
  SELECT * FROM read_csv('data/sales.csv');

-- Create with aggregation
CREATE TABLE dept_stats AS
  SELECT department,
         COUNT(*) AS headcount,
         AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department;

CREATE OR REPLACE TABLE

Creates a table, dropping any existing table with the same name first. This is useful for reloading data or rebuilding derived tables.

CREATE OR REPLACE TABLE table_name (
  column1 type1,
  column2 type2,
  ...
);

-- Also works with AS SELECT
CREATE OR REPLACE TABLE daily_summary AS
  SELECT date, SUM(amount) AS total
  FROM transactions
  GROUP BY date;

CREATE TABLE IF NOT EXISTS

Creates a table only if no table with the same name already exists. If the table exists, the statement is a no-op (no error is raised).

CREATE TABLE IF NOT EXISTS config (
  key VARCHAR,
  value VARCHAR
);

DROP TABLE

Removes a table and all its data. Raises an error if the table does not exist.

DROP TABLE table_name;

Example

DROP TABLE employees;

DROP TABLE IF EXISTS

Removes a table if it exists. If the table does not exist, the statement is a no-op.

DROP TABLE IF EXISTS table_name;

Example

-- Safe cleanup before reload
DROP TABLE IF EXISTS sales;
CREATE TABLE sales AS SELECT * FROM read_csv('sales.csv');

Using DDL from Node.js

import { Context } from 'teide-js';

const ctx = new Context();

// Create a table with explicit schema
ctx.sqlSync(`
  CREATE TABLE sensors (
    sensor_id INTEGER,
    location SYM,
    reading REAL,
    ts TIMESTAMP
  )
`);

// Load CSV into a table
ctx.sqlSync(`CREATE TABLE logs AS SELECT * FROM read_csv('logs.csv')`);

// Replace a table (idempotent reload)
ctx.sqlSync(`CREATE OR REPLACE TABLE logs AS SELECT * FROM read_csv('logs.csv')`);

// Drop when done
ctx.sqlSync(`DROP TABLE IF EXISTS sensors`);

ctx[Symbol.dispose]();