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
   │            ──┬─
   │              ╰─── unexpected `t = `<time>``
   │
   │ Help: this is probably a 'bad type' error (we are working on that)
───╯

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
• let - variable definition
• into - variable definition
• case - flow control
• type - type declaration
• func - explicit function declaration
• module - used internally
• internal - used internally
• true - literal
• false - literal
• null - literal

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

It may seem that transforms are also keywords, but they are normal functions within 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