Table Operations

Function	Description
CREATE TABLE	Create a new table and its columns from a SQL query executed against an existing table.
DELETE FROM	Remove specific rows of data from a table using an (optional) constraint.
DROP TABLES	Deletes the specified table, unregistering it.
EXPLAIN	Returns the Polars execution plan for a given SQL query.
SHOW TABLES	Returns a list of all tables registered in the given context.
GENERATE_SERIES	Create a table with one column of values from start to stop, stop included.
RANGE	Create a table with one column of values from start to stop, stop not included.
UNNEST	Unnest one or more arrays as columns in a new table object.
TRUNCATE	Remove all data from a table without actually deleting it.

CREATE TABLE

Create a new table from a sequence of column definitions, a SQL query executed against an existing table, or create an empty table from the schema of an existing table.

Example:

CREATE TABLE tbl(colx VARCHAR, coly DATE, colz ARRAY<DOUBLE>, cols STRUCT<a INT, b VARCHAR>)

CREATE TABLE new_table AS
SELECT * FROM existing_table WHERE value > 42

CREATE TABLE new_table LIKE existing_table

CREATE TEMP TABLE (or TEMPORARY) always registers the table in the context, also when a default catalog is attached; its name cannot start with an attached catalog. Unlike CREATE TABLE, it raises an error if the table already exists, unless IF NOT EXISTS (leave it as it is) or OR REPLACE (replace it) is given.

CREATE OR REPLACE TEMP TABLE staging AS
SELECT * FROM existing_table WHERE value > 42

With an attached Iceberg catalog (see SQLContext.register_catalog()), a CREATE TABLE whose name resolves to the catalog, as in a query, creates an empty table in the catalog from its column definitions. NOT NULL makes a column a required field; other constraints are not supported. LOCATION '...' or WITH ('location' = '...') sets the table location, and the other WITH options become table properties, such as 'format-version'. IF NOT EXISTS leaves an existing table as it is. CREATE TABLE ... AS SELECT and partitioning are not supported for catalog tables yet, nor are LIKE and OR REPLACE.

CREATE TABLE lake.sales.orders (id BIGINT NOT NULL, amount DECIMAL(10, 2))
WITH ('format-version' = 2)

DELETE

Remove specific rows from a table using an (optional) constraint. Omitting the constraint deletes all rows, equivalent to TRUNCATE.

Example:

DELETE FROM some_table WHERE value < 0

DROP TABLES

Deletes the specified table, unregistering it. Dropping a table that does not exist raises an error unless the optional IF EXISTS clause is given, in which case the statement is a no-op.

Example:

DROP TABLE actual_table
DROP TABLE IF EXISTS possible_table

EXPLAIN

Returns the Polars execution plan for a given SQL query.

Example:

EXPLAIN SELECT * FROM some_table

SHOW TABLES

Returns a list of all tables registered in the given context.

Example:

SHOW TABLES

GENERATE_SERIES

Create a table with one column of values from start to stop, stop included. Takes the same arguments as the GENERATE_SERIES function; the arguments must be constants. The column is named generate_series unless renamed by an alias.

Example:

SELECT * FROM
  GENERATE_SERIES(DATE '2026-01-01', DATE '2026-12-31', INTERVAL '1 day') AS cal (d)

RANGE

Like GENERATE_SERIES, but stop is not included. The column is named range unless renamed by an alias.

Example:

SELECT * FROM RANGE(0, 10, 2)

UNNEST

Unnest one or more arrays as columns in a new table object.

Example:

SELECT * FROM
  UNNEST(
    [1, 2, 3, 4],
    ['ww','xx','yy','zz'],
    [23.0, 24.5, 28.0, 27.5]
  ) AS tbl (x,y,z)

TRUNCATE

Remove all data from a table without deleting the table itself. Truncating a table that does not exist raises an error unless the optional IF EXISTS clause is given, in which case the statement is a no-op.

Example:

TRUNCATE TABLE actual_table
TRUNCATE TABLE IF EXISTS possible_table