Skip to main content

Select

Simple Select with some conditions​

SQL:

SELECT id, name FROM users WHERE (id IN ($1, $2, $3))

Args:

  • 100
  • 200
  • 300

Code:

psql.Select(
  sm.Columns("id", "name"),
  sm.From("users"),
  sm.Where(psql.Quote("id").In(psql.Arg(100, 200, 300))),
)

Case With Else​

SQL:

SELECT id, name, (CASE WHEN (id = '1') THEN 'A' ELSE 'B' END) AS "C" FROM users

Code:

psql.Select(
  sm.Columns(
    "id",
    "name",
    psql.Case().
      When(psql.Quote("id").EQ(psql.S("1")), psql.S("A")).
      Else(psql.S("B")).
      As("C"),
  ),
  sm.From("users"),
)

Case Without Else​

SQL:

SELECT id, name, (CASE WHEN (id = '1') THEN 'A' END) AS "C" FROM users

Code:

psql.Select(
  sm.Columns(
    "id",
    "name",
    psql.Case().
      When(psql.Quote("id").EQ(psql.S("1")), psql.S("A")).
      End().
      As("C"),
  ),
  sm.From("users"),
)

Select Distinct​

SQL:

SELECT DISTINCT id, name FROM users WHERE (id IN ($1, $2, $3))

Args:

  • 100
  • 200
  • 300

Code:

psql.Select(
  sm.Columns("id", "name"),
  sm.Distinct(),
  sm.From("users"),
  sm.Where(psql.Quote("id").In(psql.Arg(100, 200, 300))),
)

Select Distinct On​

SQL:

SELECT DISTINCT ON(id) id, name FROM users WHERE (id IN ($1, $2, $3))

Args:

  • 100
  • 200
  • 300

Code:

psql.Select(
  sm.Columns("id", "name"),
  sm.Distinct("id"),
  sm.From("users"),
  sm.Where(psql.Quote("id").In(psql.Arg(100, 200, 300))),
)

Select From Function​

SQL:

SELECT * FROM generate_series(1, 3) AS "x" ("p", "q", "s")

Code:

psql.Select(
  sm.From(psql.F("generate_series", 1, 3)).As("x", "p", "q", "s"),
)

Select from group of functions. Automatically uses the ROWS FROM syntax​

SQL:

SELECT *
FROM ROWS FROM
  (
    json_to_recordset($1) AS (a INTEGER, b TEXT),
    generate_series(1, 3)
  ) AS "x" ("p", "q", "s")
ORDER BY p

Args:

  • [{"a":40,"b":"foo"},{"a":"100","b":"bar"}]

Code:

psql.Select(
  sm.FromFunction(
    psql.F(
      "json_to_recordset",
      psql.Arg(`[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]`),
    )(
      fm.Columns("a", "INTEGER"),
      fm.Columns("b", "TEXT"),
    ),
    psql.F("generate_series", 1, 3)(),
  ).As("x", "p", "q", "s"),
  sm.OrderBy("p"),
)

Select from subquery with window function​

SQL:

SELECT status, avg(difference)
FROM (
  SELECT
    status, 
    (LEAD(created_date, 1, NOW())
    OVER(PARTITION BY presale_id ORDER BY created_date)
     - "created_date") AS "difference"
  FROM presales_presalestatus
) AS "differnce_by_status"
WHERE status IN ('A', 'B', 'C')
GROUP BY status

Code:

psql.Select(
  sm.Columns("status", psql.F("avg", "difference")),
  sm.From(psql.Select(
    sm.Columns(
      "status",
      psql.F("LEAD", "created_date", 1, psql.F("NOW"))(
        fm.Over().PartitionBy("presale_id").OrderBy("created_date"),
      ).Minus(psql.Quote("created_date")).As("difference")),
    sm.From("presales_presalestatus")),
  ).As("differnce_by_status"),
  sm.Where(psql.Quote("status").In(psql.S("A"), psql.S("B"), psql.S("C"))),
  sm.GroupBy("status"),
)

Select With Grouped IN​

SQL:

SELECT id, name FROM users WHERE (id, employee_id) IN (($1, $2), ($3, $4))

Args:

  • 100
  • 200
  • 300
  • 400

Code:

psql.Select(
  sm.Columns("id", "name"),
  sm.From("users"),
  sm.Where(
    psql.Group(psql.Quote("id"), psql.Quote("employee_id")).
      In(psql.ArgGroup(100, 200), psql.ArgGroup(300, 400))),
)

Simple select with limit and offset as argument​

SQL:

SELECT id, name FROM users LIMIT $1 OFFSET $2

Args:

  • 10
  • 15

Code:

psql.Select(
  sm.Columns("id", "name"),
  sm.From("users"),
  sm.Offset(psql.Arg(15)),
  sm.Limit(psql.Arg(10)),
)

Join Using​

SQL:

SELECT id FROM test1 LEFT JOIN test2 USING (id)

Code:

psql.Select(
  sm.Columns("id"),
  sm.From("test1"),
  sm.LeftJoin("test2").Using("id"),
)

CTE With Column Aliases​

SQL:

WITH c(id, data) AS (SELECT id FROM test1 LEFT JOIN test2 USING (id)) SELECT * FROM c

Code:

psql.Select(
  sm.With("c", "id", "data").As(psql.Select(
    sm.Columns("id"),
    sm.From("test1"),
    sm.LeftJoin("test2").Using("id"),
  )),
  sm.From("c"),
)

Window Function Over Empty Frame​

SQL:

SELECT row_number() OVER () FROM c

Code:

psql.Select(
  sm.Columns(
    psql.F("row_number")(fm.Over()),
  ),
  sm.From("c"),
)

Window Function Over Window Name​

SQL:

SELECT avg(salary) OVER (w)
FROM c 
WINDOW w AS (PARTITION BY depname ORDER BY salary)

Code:

psql.Select(
  sm.Columns(
    psql.F("avg", "salary")(fm.Over().From("w")),
  ),
  sm.From("c"),
  sm.Window("w").PartitionBy("depname").OrderBy("salary"),
)

Select With Order By And Collate​

SQL:

SELECT id, name FROM users ORDER BY name COLLATE "bg-BG-x-icu" ASC

Code:

psql.Select(
  sm.Columns("id", "name"),
  sm.From("users"),
  sm.OrderBy("name").Collate("bg-BG-x-icu").Asc(),
)

With Cross Join​

SQL:

SELECT id, name, type
FROM users AS u CROSS JOIN (
  SELECT id, type
  FROM clients
  WHERE ("client_id" = $1)
) AS "clients"
WHERE ("id" = $2)

Args:

  • "123"
  • 100

Code:

psql.Select(
  sm.Columns("id", "name", "type"),
  sm.From("users").As("u"),
  sm.CrossJoin(psql.Select(
    sm.Columns("id", "type"),
    sm.From("clients"),
    sm.Where(psql.Quote("client_id").EQ(psql.Arg("123"))),
  )).As("clients"),
  sm.Where(psql.Quote("id").EQ(psql.Arg(100))),
)

With Locking​

SQL:

SELECT id, name FROM users FOR UPDATE OF users SKIP LOCKED

Code:

psql.Select(
  sm.Columns("id", "name"),
  sm.From("users"),
  sm.ForUpdate("users").SkipLocked(),
)