Skip to main content

PostgreSQL and PRQL Technical Documentation

PurposeBy  reading this documentation users will gain in-depth perspective on the capabilities of PostgreSQL, allowing them to learn of its features and effectively apply the syntax.
CreatedNovember 21, 2023

What is PostgreSQL?​

PostgreSQL is an open-source object-relational database management system (ORDBMS).

Within the main features that PostgreSQL offers we have:

  1. Complex Queries: PostgreSQL handles complex queries including the ability to carry out aggregations, subqueries, and complex joins.

  2. Foreign Keys: Foreign keys establish relationships between tables, ensuring referential integrity in the database.

  3. Triggers: Triggers are actions that respond to specific events in the database.

  4. Updatable Views: Views in PostgreSQL provide a way to represent data from one or more tables in a virtual table.

  5. Transactional Integrity: PostgreSQL follows the principles of ACID (Atomicity, Consistency, Isolation, Durability) to ensure transactional integrity.

  6. Multiversion Concurrency Control (MVCC): MVCC is a technique used by PostgreSQL to manage concurrent access to the database by multiple transactions.

For more information about PostgreSQL follow link below:

Learn more about PostgreSQL

For frequently asked question follow link below:

Learn more about frequently asked questions

PostgreSQL syntax examples:​

PostgreSQL, uses SQL (Structured Query Language) as its primary syntax for interacting with the database. SQL is a standardized language for defining, querying, and manipulating relational databases. Some common PostgreSQL commands include:

  • Select: Retrieve data from one or more tables.
SELECT column1, column2 FROM table_name WHERE condition

  • Group by: Group rows based on the values in one or more columns

    SELECT category, AVG(price) AS average_price, COUNT(*) AS total_products
    FROM products
    WHERE stock_quantity > 0
    GROUP BY category;

What is PRQL?​

PRQL, short for Pipelined Relational Query Language, is a modern language specifically crafted for transforming data. It serves the needs of data engineers, analysts, and tools by offering concise syntax, database agnosticism, and the ability to contain embedded SQL where needed.

Within its main features we can find:

  1. Conciseness and Expressiveness: PRQL provides concise syntax with abstractions like variables and functions for expressive queries.

  2. Database Agnosticism: It compiles seamlessly to various SQL dialects, ensuring compatibility with diverse database systems.

  3. Flexibility with Embedded SQL: PRQL is not limiting; it allows the inclusion of embedded SQL where necessary, providing versatility in query construction.

  4. Language Integration: Supporting bindings to major programming languages, PRQL facilitates seamless integration into various ecosystems.

  5. Data Exploration Efficiency: Designed for efficient data exploration, PRQL allows actions like commenting out filters or columns while maintaining query validity.

For more information about PRQL follow links below:

Learn more about PRQL

Learn more about PRQL language book

PRQL syntax examples:​

  • Selecting specific columns:
from employees | select {id, name, age}
  • Filtering based on a condition:
filter salary > 50000
  • Deriving a new column:
derive full_name = first_name + ' ' + last_name

  • Sorting in ascending order:

    sort {+date}

  • Using named arguments:

    from data | interp threshold: 50

  • Working with Tuples:

    {id, name, is_active}

  • Filtering based on a List:

    filter department_id in [1, 3, 5]

  • Conditional derivation:

    derive category = case [amount > 100 => 'High', amount > 50 => 'Medium', true => 'Low']

  • Handling null values:

    filter (name != null)

  • Using Line Wrap for readability:

    1 + 2 + 3 + \ 4 + 5

For more information on PRQL syntax follow link below:

Learn more about PRQL syntax