Star Expression
Syntax
The * expression can be used in a SELECT statement to select all columns that are projected in the FROM clause.
SELECT *FROM tbl;id | name | col1 | col2 | Some Column Name | i | a | b | c | number | number1 | number2 ----+----------+------+------+------------------+---+----+----+----+--------+---------+--------- 1 | mark | 1 | 2 | alpha | 1 | 10 | 20 | 30 | 10 | 100 | 1000 3 | bookmark | 4 | 5 | beta | 2 | 11 | 21 | 31 | 20 | 200 | 2000 7 | other | 9 | 16 | gamma | 3 | 12 | 22 | 32 | NULL | 300 | 3000
TABLE.* and STRUCT.*
The * expression can be prepended by a table name to select only columns from that table.
SELECT tbl.*FROM tblJOIN other_tbl USING (id);id | name | col1 | col2 | Some Column Name | i | a | b | c | number | number1 | number2 ----+----------+------+------+------------------+---+----+----+----+--------+---------+--------- 1 | mark | 1 | 2 | alpha | 1 | 10 | 20 | 30 | 10 | 100 | 1000 3 | bookmark | 4 | 5 | beta | 2 | 11 | 21 | 31 | 20 | 200 | 2000
Similarly, the * expression can also be used to retrieve all keys from a struct as separate columns.
This is particularly useful when a prior operation creates a struct of unknown shape, or if a query must handle any potential struct keys.
See the STRUCT data type and STRUCT functions pages for more details on working with structs.
For example:
SELECT st.* FROM (SELECT {'x': 1, 'y': 2, 'z': 3} AS st);x | y | z ---+---+--- 1 | 2 | 3
EXCLUDE Clause
EXCLUDE allows you to exclude specific columns from the * expression.
SELECT * EXCLUDE (col)FROM tbl;id | name | col1 | col2 | Some Column Name | i | a | b | c | number | number1 | number2 ----+----------+------+------+------------------+---+----+----+----+--------+---------+--------- 1 | mark | 1 | 2 | alpha | 1 | 10 | 20 | 30 | 10 | 100 | 1000 3 | bookmark | 4 | 5 | beta | 2 | 11 | 21 | 31 | 20 | 200 | 2000 7 | other | 9 | 16 | gamma | 3 | 12 | 22 | 32 | NULL | 300 | 3000
REPLACE Clause
REPLACE allows you to replace specific columns by alternative expressions.
SELECT * REPLACE (col1 / 1_000 AS col1, col2 / 1_000 AS col2)FROM tbl;id | name | col1 | col2 | Some Column Name | i | a | b | c | number | number1 | number2 ----+----------+-------+-------+------------------+---+----+----+----+--------+---------+--------- 1 | mark | 0.001 | 0.002 | alpha | 1 | 10 | 20 | 30 | 10 | 100 | 1000 3 | bookmark | 0.004 | 0.005 | beta | 2 | 11 | 21 | 31 | 20 | 200 | 2000 7 | other | 0.009 | 0.016 | gamma | 3 | 12 | 22 | 32 | NULL | 300 | 3000
RENAME Clause
RENAME allows you to replace specific columns.
SELECT * RENAME (col1 AS height, col2 AS width)FROM tbl;id | name | height | width | Some Column Name | i | a | b | c | number | number1 | number2 ----+----------+--------+-------+------------------+---+----+----+----+--------+---------+--------- 1 | mark | 1 | 2 | alpha | 1 | 10 | 20 | 30 | 10 | 100 | 1000 3 | bookmark | 4 | 5 | beta | 2 | 11 | 21 | 31 | 20 | 200 | 2000 7 | other | 9 | 16 | gamma | 3 | 12 | 22 | 32 | NULL | 300 | 3000
Column Filtering via Pattern Matching Operators
The pattern matching operators LIKE and GLOB allow you to select columns by matching their names to patterns.
SELECT * LIKE 'col%'FROM tbl; col1 | col2
------+------
1 | 2
4 | 5
9 | 16SELECT * GLOB 'col*'FROM tbl; col1 | col2
------+------
1 | 2
4 | 5
9 | 16The pattern applied to a star expression must be a constant, and only LIKE and GLOB are supported. The SIMILAR TO operator is not supported on a star expression and raises an error:
SELECT * SIMILAR TO 'col.'FROM tbl; col1 | col2
------+------
1 | 2
4 | 5
9 | 16The NOT variant behaves the same way and also raises an error:
SELECT * NOT SIMILAR TO 'col.'FROM tbl;id | name | Some Column Name | i | a | b | c | number | number1 | number2 ----+----------+------------------+---+----+----+----+--------+---------+--------- 1 | mark | alpha | 1 | 10 | 20 | 30 | 10 | 100 | 1000 3 | bookmark | beta | 2 | 11 | 21 | 31 | 20 | 200 | 2000 7 | other | gamma | 3 | 12 | 22 | 32 | NULL | 300 | 3000
COLUMNS Expression
The COLUMNS expression is similar to the regular star expression, but additionally allows you to execute the same expression on the resulting columns.
CREATE TABLE numbers (id INTEGER, number INTEGER);
INSERT INTO numbers VALUES (1, 10), (2, 20), (3, NULL);
SELECT min(COLUMNS(*)), count(COLUMNS(*)) FROM numbers;id | number | id | number ----+--------+----+-------- 1 | 10 | 3 | 2
SELECT min(COLUMNS(* REPLACE (number + id AS number))), count(COLUMNS(* EXCLUDE (number)))FROM numbers;id | min | id ----+-----+---- 1 | 11 | 3
COLUMNS expressions can also be combined, as long as they contain the same star expression:
SELECT COLUMNS(*) + COLUMNS(*) FROM numbers;id | number ----+-------- 2 | 20 4 | 40 6 | NULL
COLUMNS Expression in a WHERE Clause
COLUMNS expressions can also be used in WHERE clauses. The conditions are applied to all columns and are combined using the logical AND operator.
SELECT *FROM ( SELECT 'a', 'a' UNION ALL SELECT 'a', 'b' UNION ALL SELECT 'b', 'b') _(x, y)WHERE COLUMNS(*) = 'a';x | y ---+--- a | a
To combine conditions using the logical OR operator, you can UNPACK the COLUMNS expression into the variadic greatest function.
SELECT *FROM ( SELECT 'a', 'a' UNION ALL SELECT 'a', 'b' UNION ALL SELECT 'b', 'b') _(x, y)WHERE greatest(UNPACK(COLUMNS(*) = 'a'));x | y ---+--- a | a a | b
COLUMNS Expression in DISTINCT ON
COLUMNS expressions can be used in DISTINCT ON clauses to specify distinct columns by pattern:
SELECT DISTINCT ON (COLUMNS('x|y')) *FROM (VALUES (1, 2, 'a'), (1, 2, 'b'), (3, 4, 'c')) t(x, y, z);x | y | z ---+---+---------- 1 | 2 | (varies) 3 | 4 | c
Regular Expressions in a COLUMNS Expression
COLUMNS expressions don't currently support the pattern matching operators, but they do support regular expression matching by simply passing a string constant in place of the star:
SELECT COLUMNS('(id|numbers?)') FROM numbers;id | number ----+-------- 1 | 10 2 | 20 3 | NULL
Renaming Columns with Regular Expressions in a COLUMNS Expression
The matches of capture groups in regular expressions can be used to rename matching columns.
The capture groups are one-indexed; \0 is the original column name.
For example, to select the first three letters of column names, run:
SELECT COLUMNS('(\w{3}).*') AS '\1' FROM numbers;num ------ 10 20 NULL
To remove a colon (:) character in the middle of a column name, run:
CREATE TABLE tbl ("Foo:Bar" INTEGER, "Foo:Baz" INTEGER, "Foo:Qux" INTEGER);
SELECT COLUMNS('(\w*):(\w*)') AS '\1\2' FROM tbl;FooBar FooBaz FooQux
To add the original column name to the expression alias, run:
SELECT min(COLUMNS(*)) AS "min_\0" FROM numbers; min_id | min_number
--------+------------
1 | 10COLUMNS Lambda Function
COLUMNS also supports passing in a lambda function. The lambda function will be evaluated for all columns present in the FROM clause, and only columns that match the lambda function will be returned. This allows the execution of arbitrary expressions in order to select and rename columns.
SELECT COLUMNS(lambda c: c LIKE '%num%') FROM numbers; number
--------
10
20
NULLCOLUMNS List
COLUMNS also supports passing in a list of column names.
SELECT COLUMNS(['id', 'num']) FROM numbers;id | num ----+------ 1 | 10 2 | 20 3 | NULL
Unpacking a COLUMNS Expression
By wrapping a COLUMNS expression in UNPACK, the columns expand into a parent expression, much like the iterable unpacking behavior in Python.
Without UNPACK, operations on the COLUMNS expression are applied to each column separately:
SELECT coalesce(COLUMNS(['a', 'b', 'c'])) AS resultFROM (SELECT NULL a, 42 b, true c);result | result | result --------+--------+-------- NULL | 42 | t
With UNPACK, the COLUMNS expression is expanded into its parent expression, coalesce in the example above, which results in a single column:
SELECT coalesce(UNPACK(COLUMNS(['a', 'b', 'c']))) AS resultFROM (SELECT NULL AS a, 42 AS b, true AS c); result
--------
42The UNPACK keyword may be replaced by *, matching Python syntax, when it is applied directly to the COLUMNS expression without any intermediate operations.
SELECT coalesce(*COLUMNS(*)) AS resultFROM (SELECT NULL a, 42 AS b, true AS c); result
--------
42