Skip to content

Hardcoded SQL

Hardcoded table

CREATE TABLE table_name(name1, name2) AS (
  VALUES ('value1', FALSE),
         ('value2', TRUE)
)
WITH table_name(name1, name2) AS (
  VALUES ('value1', FALSE),
         ('value2', TRUE)
)
SELECT *
FROM table_name

Complex table

SELECT *
FROM
  (VALUES
    ('Col1 Item 1', 'Col2 Item 1', 'Col3 Item 1', NULL),
    ('Col1 Item 2', 'Col2 Item 2', 'Col3 Item 2', FALSE)
  )
AS column_names(col1, col2, col3, bool_col)

generate_series

In a SELECT

SELECT generate_series(1,5) AS my_col;
+--------+
| my_col |
|--------|
| 1      |
| 2      |
| 3      |
| 4      |
| 5      |
+--------+

generate_series in a FROM

SELECT  
COUNT(*) AS unfiltered,  
COUNT(*) FILTER (WHERE i < 5) AS filtered
FROM generate_series(1,10) AS s(i);
+------------+----------+
| unfiltered | filtered |
|------------+----------|
| 10         | 4        |
+------------+----------+

Series of months

SELECT
  GENERATE_SERIES(
    '2023-02-01'::DATE,
    '2024-01-31'::DATE,
    INTERVAL '1 MONTH'
  )::DATE AS month
+------------+
| month      |
|------------|
| 2023-02-01 |
| 2023-03-01 |
| 2023-04-01 |
| 2023-05-01 |
| 2023-06-01 |
| 2023-07-01 |
| 2023-08-01 |
| 2023-09-01 |
| 2023-10-01 |
| 2023-11-01 |
| 2023-12-01 |
| 2024-01-01 |
+------------+
SELECT "month"
  FROM GENERATE_SERIES(
      ( 2023 || '-02-01')::DATE,
      ( 2023 ::INT + 1 || '-01-31')::DATE,
      INTERVAL '1 MONTH'
    ) AS "month"

Hardcoded JSON

x

Last update: 2023-02-03