Identifiers & keywords

Identifiers can contain alphanumeric characters and _ and must not start with a number. They can be chained together with the . indirection operator, used to retrieve a tuple from a field or a variable from a module.

hello
_h3llo
hello.world

this & that

this refers to the current relation:

PRQL

from invoices
aggregate (
    count this
)

SQL

SELECT
  COUNT(*)
FROM
  invoices

Within a join, that refers to the other table:

PRQL

from invoices
join tracks (this.track_id==that.id)

SQL

SELECT
  invoices.*,
  tracks.*
FROM
  invoices
  JOIN tracks ON invoices.track_id = tracks.id

this can also be used to remove any column ambiguity. For example, currently using a bare time as a column name will fail, because it’s also a type:

PRQL

from invoices
derive t = time

Error

Error:
   ╭─[:2:12]
   │
 2 │ derive t = time
   │            ──┬─
   │              ╰─── expected a value, but found a type
───╯

But with this.time, we can remove the ambiguity:

PRQL

from invoices
derive t = this.time

SQL

SELECT
  *,
  time AS t
FROM
  invoices

Quoting

To use characters that would be otherwise invalid, identifiers can be surrounded by with backticks.

When compiling to SQL, these identifiers will use dialect-specific quotes and quoting rules.

PRQL

prql target:sql.mysql
from employees
select `first name`

SQL

SELECT
  `first name`
FROM
  employees

PRQL

prql target:sql.postgres
from employees
select `first name`

SQL

SELECT
  "first name"
FROM
  employees

PRQL

prql target:sql.bigquery
from `project-foo.dataset.table`
join `project-bar.dataset.table` (==col_bax)

SQL

SELECT
  `project-foo.dataset.table`.*,
  `project-bar.dataset.table`.*
FROM
  `project-foo.dataset.table`
  JOIN `project-bar.dataset.table` ON `project-foo.dataset.table`.col_bax = `project-bar.dataset.table`.col_bax

Schemas & database names

Identifiers of database tables can be prefixed with schema and databases names.

PRQL

from my_database.chinook.albums

SQL

SELECT
  *
FROM
  my_database.chinook.albums

Note that all of following identifiers will be treated as separate table definitions: tracks, public.tracks, my_database.public.tracks.

Keywords

PRQL uses following keywords:

• prql - query header more…
• let - variable definition more…
• into - variable definition more…
• case - flow control more…
• type - type declaration
• func - explicit function declaration more…
• module - used internally
• internal - used internally
• true - boolean more…
• false - boolean more…
• null - NULL more…

Keywords can be used as identifiers (of columns or variables) when encased in backticks: `case` .

Transforms are normal functions within the std namespace, not keywords. That is, std.from is the same function as from. In the example below, the resulting query is the same as without the std. namespace:

PRQL

std.from my_table
std.select {from = my_table.a, take = my_table.b}
std.take 3

SQL

SELECT
  a AS "from",
  b AS take
FROM
  my_table
LIMIT
  3