Acquire / generate · All operations

Type rows in by hand

A variant of Generate rows

Build a small table from values written into the step or query itself.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Write rows into the query with their types

SQL
CREATE TEMP TABLE result AS
SELECT id::INTEGER AS id, name::VARCHAR AS name, joined::DATE AS joined, score::DECIMAL(5, 2) AS score
FROM (VALUES (1, 'Ann', '2026-01-05', 9.5), (2, 'Bo', NULL, NULL), (3, 'Cy', '2026-03-09', 7.25)) t(id, name, joined, score);
SELECT * FROM result;
SELECT CASE WHEN ((SELECT list(column_type) FROM (DESCRIBE result)) = ['INTEGER', 'VARCHAR', 'DATE', 'DECIMAL(5,2)'] AND (SELECT count(*) = 3 AND count(joined) = 2 AND sum(score) = 16.75 AND max(joined) = DATE '2026-03-09' FROM result)) THEN 'as expected' ELSE error('the three typed rows were not built with an integer, text, date and decimal column and the one missing date and score') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the three typed rows were not built with an integer, text, date and decimal column and the one missing date and score”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Alteryx Designer (1)
  • Text Inputread 2026-09-27

    Type rows in by hand to make a small input or lookup table

Apache DataFusion (1)
  • VALUES listsread 2026-09-27

    Produce rows from literal value lists

Apache Hop (1)
  • Data Gridread 2026-09-27

    Emit a static table of rows typed into a grid

DuckDB (1)
Duckle (1)
  • Inline Rowsread 2026-09-25

    rows written into the pipeline itself, repeated rowCount times over range()

EasyMorph (1)
  • Create listread 2026-09-27

    Create a one-column table from up to 100 hand-entered values

Ibis (1)
  • DummyTableread 2026-09-27

    A table built from scalar expressions alone, with no source table

KNIME Analytics Platform (1)
  • Table Creatorread 2026-09-27

    Create a table by typing values into a spreadsheet-like editor

Pentaho Data Integration (1)
  • Data Gridread 2026-09-27

    Emit rows of static data typed into a grid

Polars (1)
  • selectread 2026-09-27

    Evaluate expressions with no input frame, giving a new frame

Power Query M (1)
  • #tableread 2026-09-27

    Build a table from literal column names or a table type and a list of rows

RapidMiner Studio (1)
  • Create ExampleSetread 2026-09-27

    Create a table from expressions, numeric series, date series or typed-in comma-separated text

Talend (1)
  • tFixedFlowInputread 2026-09-27

    Generate a fixed flow of rows from values or variables set in the component

VisiData (1)
  • open-newread 2026-09-23

    Open a new blank sheet with one column for manual entry

Background

In PostgreSQL, VALUES generates a constant table that a query can use without creating and populating a table on disk.[1] In DuckDB, the VALUES clause specifies a fixed number of rows, and can stand alone, sit in a FROM clause, or feed an INSERT INTO statement.[2]

When to use it

EasyMorph's Create List action keeps reference lists in a project without loading them from external files, and its two-column mode creates static lookup tables.[3] EasyMorph's Create List action holds up to 1000 manually entered values in one column.[3] In Apache Hop, the Data Grid transform holds a static list of rows entered in a grid, usually for testing, reference or demo purposes.[4] Type rows in when the table is small, settled and yours to keep, such as a mapping of codes or a few test cases, and load anything longer or anything that changes from a file. (Meridian's view)

In PostgreSQL, every row of a VALUES list must have the same number of elements, and the entries in each column must have compatible data types.[1] In PostgreSQL, the data type of each column of a VALUES table is determined by the same rules as for UNION.[1] In Apache Hop's Data Grid, every cell is a string, which Hop converts using the type and format set for its column on the Meta tab.[4] Cast each column of typed-in rows to the type you mean, rather than trusting how the written values are read. (Meridian's view)

In PostgreSQL, the columns of a VALUES table are named column1, column2 and so on by default.[1] The column names of a VALUES table are not specified by the SQL standard, and different database systems name them differently.[1] Name the columns with a table alias list every time, so that the rows mean the same thing in every database. (Meridian's view)

The method

In PostgreSQL, VALUES followed by expression lists is treated as equivalent to a SELECT and can appear anywhere a SELECT can, for example as part of a UNION.[1] In PostgreSQL, VALUES is most commonly used as the data source in an INSERT command, and next most commonly as a subquery.[1] In Apache DataFusion, VALUES can be used as a table expression with an alias naming the table and its columns, as in VALUES (1, 'a'), (2, 'b') AS t(id, label).[5] In DuckDB, CREATE TABLE AS with a SELECT from a VALUES clause creates a table directly from rows written in the query.[2]

In Power Query M, #table creates a table value from columns and rows, where the rows are a list of lists, each holding the column values for a single row.[6] In Power Query M, the columns argument of #table can be a list of column names, a table type, a number of columns, or null.[6] In Power Query M, #table(type table [Name = text, Score = number], ...) creates a table with an explicit type for each column.[6]

Compared with the alternatives

In PostgreSQL, VALUES (1, 'one'), (2, 'two'), (3, 'three') is effectively equivalent to three single-row SELECT statements joined by UNION ALL.[1]

Sources

  1. [1]PostgreSQL: Documentation: 18: 7.7. VALUES Listsdocumentation
  2. [2]VALUES Clause – DuckDBdocumentation
  3. [3]CREATE LIST [EasyMorph Help]documentation
  4. [4]Data Grid | Apache Hopdocumentation
  5. [5]SELECT syntax — Apache DataFusion main documentationdocumentation
  6. [6]#table - PowerQuery M | Microsoft Learndocumentation