How to select multiple rows filled with constants?
In PostgreSQL, you can do:
SELECT *FROM ( VALUES (1, 2), (3, 4) ) AS q (col1, col2)In other systems, just use UNION ALL:
SELECT 1 AS col1, 2 AS col2-- FROM dual-- uncomment the line above if in OracleUNION ALLSELECT 3 AS col1, 3 AS col2-- FROM dual-- uncomment the line above if in OracleIn Oracle, SQL Server and PostgreSQL, you also can generate recordsets of arbitrary number of rows (providable with an external variable):
SELECT levelFROM dualCONNECT BY level <= :nin Oracle,
WITH q (l) AS ( SELECT 1 UNION ALL SELECT l + 1 FROM q WHERE l < @n )SELECT lFROM q-- OPTION (MAXRECURSION 0)-- uncomment line above if @n >= 100in SQL Server,
SELECT lFROM generate_series(1, $n) lin PostgreSQL.