Shape / columns · All operations

Add a calculated column

New column from a formula or expression.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
one row out per row in, with columns added or changed
Level
one clause of SQL

The DuckDB step

A column from an expression

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
SELECT order_id, amount, round(amount * 1.2, 2) AS gross FROM orders;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT max(gross) FILTER (WHERE order_id = 1) = 144 AND count(gross) = 9 FROM result)) THEN 'as expected' ELSE error('gross is not amount times 1.2') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “gross is not amount times 1.2”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Add a column from an expression

Add a column whose value for each row is computed by an expression over that row's columns.

Add a constant or empty column

Add a column holding the same literal value, or no value, in every row.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (3)
  • Formularead 2026-09-27

    Create or update columns from one or more expressions

  • Formula In-DBread 2026-09-27

    Create or update fields inside the database with a native SQL expression

  • Multi-Field Formularead 2026-09-27

    Apply one expression across many fields to create or update them

Apache DataFusion (5)
  • Comparison Operatorsread 2026-09-27

    17 operators: =, !=, <, IS DISTINCT FROM, ~ (regex match), also ~~ (LIKE)

  • EXTENDread 2026-09-27

    Pipe: add computed columns to the input, BigQuery-style |> syntax

  • Logical Operatorsread 2026-09-27

    2 operators: AND, OR

  • Other Operatorsread 2026-09-27

    3 operators: || (concatenate), @> (array contains), <@ (array is contained by)

  • with_columnread 2026-09-27

    DataFrame method: Add or replace a column from an expression

Apache Hop (6)
Apache NiFi (5)
  • Evaluating Multiple Attributesread 2026-09-27

    8 functions: anyAttribute, allAttributes, anyMatchingAttribute, allMatchingAttributes, anyDelineatedValue, …

  • Standalone Functionsread 2026-09-27

    32 functions: substring, substringAfter, substringAfterLast, substringBefore, substringBeforeLast, …

  • Subjectless Functionsread 2026-09-27

    8 functions: ip, hostname, UUID, nextInt, literal, …

  • UpdateAttributeread 2026-09-27

    Set FlowFile attributes with Expression Language, or delete attributes matching a regex

  • UpdateRecordread 2026-09-27

    Update the record fields selected by RecordPath with user-defined values

Apache SeaTunnel (3)
  • Copyread 2026-09-27

    Copy a field to one or more new fields

  • Metadataread 2026-09-27

    Expose row metadata (database, table, row kind, event time, delay, partition) as fields

  • RowKindExtractorread 2026-09-27

    Turn a change stream into append-only rows and keep the original row kind in a new field

AWS Glue (2)
  • Add Current Timestampread 2026-09-27

    Mark each row with the time at which the data was processed

  • Derived Columnread 2026-09-27

    Define a column by a math formula or SQL expression over other columns and constants

Azure Data Factory (3)
Bruin (1)
  • Dimensionsread 2026-09-27

    Named fields or expressions, with time granularities, to select, group, filter or sort by

dbt-utils (1)
DuckDB (1)
Duckle (9)
  • Add Columnread 2026-09-25

    add a column from a SQL expression

  • Audit Stampread 2026-09-25

    append loaded-at, source and batch id columns

  • Compare Columnsread 2026-09-25

    boolean column from comparing two columns

  • Format Stringread 2026-09-25

    format a string from a template

  • Lengthread 2026-09-25

    string length

  • Repeatread 2026-09-25

    repeat a string N times

  • Reverseread 2026-09-25

    reverse the characters of a string

  • Substringread 2026-09-25

    substring by position

  • Text Matchread 2026-09-25

    boolean column: contains, starts with or ends with a substring

EasyMorph (2)
Ibis (1)
  • Literalread 2026-09-27

    A typed constant placed in an expression

KNIME Analytics Platform (7)
OpenRefine (6)
Pentaho Data Integration (6)
Polars (9)
  • DataFrame.foldread 2026-09-27

    Reduce across the columns row by row with a user function that combines two Series

  • DataFrame.insert_columnread 2026-09-27

    Insert a Series or expression as a column at a given position

  • DataFrame.replace_columnread 2026-09-27

    Swap the column at a given position for a new Series

  • DataFrame.with_columnsread 2026-09-27

    Add columns from expressions, replacing any that share a name

  • DataFrame.with_columns_seqread 2026-09-27

    As with_columns, but evaluating the expressions one after another rather than in parallel

  • Expr computation: position lookups (group)read 2026-09-27

    5 expressions: arg_unique, index_of, search_sorted, peak_max, peak_min; find positions of values and local peaks

  • foldread 2026-09-27

    Left fold across columns row by row with a user function and a start value

  • litread 2026-09-27

    A literal value as an expression

  • reduceread 2026-09-27

    Left fold across columns row by row with a user function, no start value

Power Query M (5)
  • Table.AddColumnread 2026-09-27

    Add a column computed by a function applied to each row

  • Table.DuplicateColumnread 2026-09-27

    Add a copy of a column under a new name, keeping values and type

  • Table.TransformColumnsread 2026-09-27

    Apply a function to the values of named columns in place

  • Table.TransformRowsread 2026-09-27

    Apply a function to each row and return the list of results

  • Value functionsread 2026-09-27

    36 functions: Value.Type, Value.Compare, Value.ReplaceType, Value.Metadata, Value.NativeQuery

RapidMiner Studio (12)
Tableau Prep (4)
  • Clean Stepread 2026-09-27

    Hold cleaning operations: filter, rename, split, group, calculate, remove fields

  • Create Calculated Fieldread 2026-09-27

    New field from a calculation written by hand or built in the visual editor

  • Custom Calculationread 2026-09-27

    New field from an expression typed in the calculation editor

  • Duplicate Fieldread 2026-09-27

    Create a copy of a field and its values

Talend (1)
  • tMapread 2026-09-27

    Map inputs to outputs with expressions, joining lookup flows and filtering and routing rows

Trifacta (6)
  • Derive Transformread 2026-09-27

    Create a column from a value expression, optionally computed per group

  • Edit with formularead 2026-09-27

    Set the values of one or more columns to the result of a formula

  • New formularead 2026-09-27

    Create a column from the result of a formula

  • Other Functions (group)read 2026-09-27

    15 functions: COALESCE, RAND, IF, IPTOINT, URLPARAMS; conditionals, random, IP and URL parts, ranges

  • Set Transformread 2026-09-27

    Overwrite a column with a literal or expression, optionally only on rows meeting a test

  • filepathread 2026-09-27

    New column holding the path of the source file each row came from

VisiData (9)
  • addcol-audiometadataread 2026-09-23

    Add metadata columns for audio files on a Directory Sheet

  • addcol-bulkread 2026-09-23

    Add N empty editable columns

  • addcol-exprread 2026-09-23

    Add column computed from a Python expression with column names as variables

  • addcol-iterread 2026-09-23

    Add column filled from a Python sequence expression, repeated to fit

  • addcol-newread 2026-09-23

    Add an empty editable column

  • addcol-sourceread 2026-09-23

    Add a copy of current column to the source sheet

  • setcell-exprread 2026-09-23

    Set current cell to the result of a Python expression on its row

  • setcol-exprread 2026-09-23

    Set current column for selected rows to the result of a Python expression

  • setcol-iterread 2026-09-23

    Set current column for selected rows from items of a Python sequence expression

Background

Adding a calculated column gives every row a new value worked out from that row's columns, such as a unit price multiplied by a quantity.[1] Database theory calls the operation extended projection: a projection whose list computes new values as well as picking columns.[1][2]

When to use it

In DuckDB, / between two integers performs floating-point division, so 5 / 2 is 2.5, while // performs integer division.[3] Check what each operator does with integers and with zero when a formula moves from one engine to another. (Meridian's view)

The method

Basic relational algebra has no operation that computes on the data, such as multiplying a unit price by a quantity to obtain a total price.[1] A projection may modify attribute values as well as choose attributes: projecting a, b × 0.5 and c returns the relation with every b value halved.[2]

In DuckDB, the expressions of the SELECT clause are logically executed only at the end of the query, although the clause is written first.[4] DuckDB lets a later expression in a SELECT list reuse an earlier one's alias, as in SELECT i + 1 AS j, j + 2 AS k, a feature also known as lateral column aliases.[5] DuckDB also accepts prefix aliases, so res: col1 + col2 names a column just as col1 + col2 AS res does.[4] Lateral aliases keep a chain of formulas readable; where a query must also run on other engines, check that those engines accept them. (Meridian's view)

Compared with the alternatives

A DuckDB generated column declares a calculated column in the table definition itself, with GENERATED ALWAYS AS (expr) or simply AS (expr).[6] DuckDB computes a VIRTUAL generated column from its expression every time the column is referenced, and VIRTUAL is currently the only kind it supports.[6] A calculated column in a query suits one step of a pipeline; a generated column suits a formula that every reader of a table should get in the same way. (Meridian's view)

Sources

  1. [1]Relational algebra - Wikipediasecondary source
  2. [2]Projection (relational algebra) - Wikipediasecondary source
  3. [3]Numeric Functions – DuckDBdocumentation
  4. [4]SELECT Clause – DuckDBdocumentation
  5. [5]Friendly SQL – DuckDBdocumentation
  6. [6]CREATE TABLE Statement – DuckDBdocumentation

Used in

10 Protocols use this operation: