SQL Reference

This section is your go-to SQL reference guide in Propel. The reference documentation and examples below demonstrate how to make the most out of Propel’s SQL capabilities.

This section provides reference documentation for the SELECT statement, along with all functions and operators supported in SQL. Propel supports all common PostgreSQL functions and operators, plus additional Propel-specific functions for added convenience and functionality.

All the examples provided use our Quickstart's "TacoSoft Demo Data”.

SELECT

You can query Propel's Data Pools using the SELECT SQL statement. The SELECT statement has the following clauses:

• Filter rows using the WHERE clause.
• Sort rows using the ORDER BY clause.
• Select a subset of rows from a table using the LIMIT and OFFSET clauses.
• Group rows into groups using the GROUP BY clause.
• Join with other tables using joins such as LEFT JOIN, RIGHT JOIN, FULL JOIN, and INNER JOINclauses.
• Perform set operations using DISTINCT, UNION, and ALL.
• Supports recursive selects with common table expressions using WITH.

Arithmetic expressions

add

Calculates the sum of two or more values. It takes columns and values as arguments.

SELECT
    taco_total_price,
    taco_total_price + 1 as taco_price_plus_one
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_total_price | taco_price_plus_one 
------------------+---------------------
              3.5 |                 4.5
                6 |                   7
             8.25 |                9.25
(3 rows)

SELECT
    taco_total_price,
    add(taco_total_price, 1) as taco_price_plus_one
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_total_price | taco_price_plus_one 
------------------+---------------------
              3.5 |                 4.5
                6 |                   7
             8.25 |                9.25
(3 rows)

subtract

Calculates the difference between two or more values. It takes columns and values as arguments.

SELECT
    taco_total_price,
    taco_total_price - 1 AS taco_price_minus_one
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_total_price | taco_price_minus_one 
------------------+----------------------
              3.5 |                  2.5
                6 |                    5
             8.25 |                 7.25
(3 rows)

SELECT
    taco_total_price,
    subtract(taco_total_price, 1) as taco_price_minus_one
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_total_price | taco_price_minus_one 
------------------+----------------------
              3.5 |                  2.5
                6 |                    5
             8.25 |                 7.25
(3 rows)

multiply

Calculates the product of two or more values. It takes columns and values as arguments.

SELECT
    taco_unit_price * quantity as total
FROM "TacoSoft Demo Data"
LIMIT 3;

 total 
-------
   3.5
     6
  8.25
(3 rows)

SELECT
    multiply(taco_unit_price, quantity) as total
FROM "TacoSoft Demo Data"
LIMIT 3;

 total 
-------
   3.5
     6
  8.25
(3 rows)

divide

Calculates the quotient of two or more values. It takes columns and values as arguments.

SELECT
    taco_total_price / quantity as unit_price
FROM "TacoSoft Demo Data"
LIMIT 3;

 unit_price 
------------
        3.5
          3
       2.75
(3 rows)

SELECT
    divide(taco_total_price, quantity) as unit_price
FROM "TacoSoft Demo Data"
LIMIT 3;

 unit_price 
------------
        3.5
          3
       2.75
(3 rows)

Logical functions

is_null

Returns a boolean value indicating if the value is NULL or not.

SELECT
    is_null(taco_name) as missing_taco_name
FROM "TacoSoft Demo Data"
LIMIT 3;

 missing_taco_name 
-------------------
                 0
                 0
                 0
(3 rows)

Mathematical functions

floor

Returns the largest round number that is less than or equal to the provided value.

SELECT
    taco_unit_price,
    floor(taco_unit_price) as rounded_down_unit_price
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_unit_price | rounded_down_unit_price 
-----------------+-------------------------
             3.5 |                       3
               3 |                       3
            2.75 |                       2
(3 rows)

ceil

Returns the largest round number greater than or equal to the provided value.

SELECT
    taco_unit_price,
    ceil(taco_unit_price) as rounded_up_unit_price
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_unit_price | rounded_up_unit_price 
-----------------+-----------------------
             3.5 |                     4
               3 |                     3
            2.75 |                     3
(3 rows)

Aggregation functions

count

The number of records in the group. It takes no arguments.

count_if

The number of records in the group that satisfy a boolean condition. It takes a boolean expression as an argument.

count_distinct

The number of distinct records in the group. It takes a column as an argument.

sum

The sum of the values in the group. It takes a column as an argument. To add two values in the same row, use the ADD function.

sum_if

The sum of the values in the group that satisfy a boolean condition. Its first argument is a column, and its second is a boolean expression.

avg

The average of the values in the group. It takes a column as an argument.

avg_if

The average of the values in the group that satisfy a boolean condition. Its first argument is a column, and its second is a boolean expression.

min

The minimum value in the group. It takes a column as an argument.

max

The maximum value in the group. It takes a column as an argument.

any

Selects the first encountered (non-NULL) value unless all rows have NULL values in that column.

first

Selects the first encountered (non-NULL) value in a column, assuming the order given by another column.

last

Selects the last encountered (non-NULL) value in a column, assuming the order given by another column.

percentile

The percentile value in the group. It takes the percentile as an argument.

top_k

Returns an array of the N most frequent values in the specified column. The resulting array is sorted in descending order of approximate frequency of values.

SELECT
    top_k(3, taco_name) as popular_tacos
FROM "TacoSoft Demo Data"
LIMIT 3;

      popular_tacos      
-------------------------
 Breakfast,Veggie,Shrimp
(1 row)

Date and time functions

current_date

The CURRENT_DATE function returns the current date in the default time zone of the database session.

now

The NOW() function returns the current date and time with the time zone of the database server.

to_start_of_minute

Rounds down a date with time to the start of the minute. It takes a timestamp as an argument.

to_start_of_five_minutes

Rounds down a date with time to the start of the five-minute interval. It takes a timestamp as an argument.

to_start_of_ten_minutes

Rounds down a date with time to the start of the ten-minute interval. It takes a timestamp as an argument.

to_start_of_fifteen_minutes

Rounds down a date with time to the start of the fifteen-minute interval. It takes a timestamp as an argument.

to_start_of_hour

Rounds down a date with time to the start of the hour. It takes a timestamp as an argument.

to_start_of_day

Rounds down a date with time to the start of the day. It takes a timestamp as an argument.

to_start_of_week

Rounds down a date with time to the start of the week. It takes a timestamp as an argument.

to_start_of_month

Rounds down a date with time to the start of the month. It takes a timestamp as an argument.

to_start_of_year

Rounds down a date with time to the start of the year. It takes a timestamp as an argument.

timestamp_diff

Calculates the difference between two DATE or TIMESTAMP values. It accepts three or four arguments:

1. The first argument is the unit to return results in (see below).
2. The second argument is the start timestamp (the smaller timestamp).
3. The third argument is the end timestamp (the larger timestamp).
4. The fourth optional argument is the timezone (defaults to "UTC").

For example, the following calculates the differences between two TIMESTAMP columns, "createdAt" and "updatedAt", in minutes:

SELECT
    timestamp_diff('minute', to_start_of_year(timestamp), timestamp) as time_diff_in_mins
FROM "TacoSoft Demo Data"
LIMIT 3;

 time_diff_in_mins 
-------------------
            310661
            310661
            310661
(3 rows)

The following units are supported:

• millisecond
• second
• minute
• hour
• day
• week
• month
• quarter
• year

You can specify the unit singular or plural (for example, both "minute" and "minutes" are valid).

date_diff

Calculates the difference between two DATE or TIMESTAMP values. It accepts three or four arguments:

1. The first argument is the unit to return results in (see below).
2. The second argument is the start timestamp (the smaller timestamp).
3. The third argument is the end timestamp (the larger timestamp).
4. The fourth, optional argument is the timezone (defaults to "UTC").

For example, the following calculates the differences between two TIMESTAMP columns, "createdAt" and "updatedAt", in days:

SELECT
    date_diff('day', to_start_of_year(timestamp), timestamp) as time_diff_in_days
FROM "TacoSoft Demo Data"
LIMIT 3;

 time_diff_in_days 
-------------------
               215
               215
               215
(3 rows)

The following units are supported:

• millisecond
• second
• minute
• hour
• day
• week
• month
• quarter
• year

You can specify the unit singular or plural (for example, both "minute" and "minutes" are valid).

date_trunc

Truncates a DATE or TIMESTAMP value. It accepts two or three arguments:

1. The first argument is the unit used to truncate the result (see below).
2. The second argument is the timestamp to truncate.
3. The third, optional argument is the timezone (defaults to "UTC").

SELECT
    timestamp,
    date_trunc('day', timestamp, 'UTC')
FROM "TacoSoft Demo Data"
LIMIT 3;

        timestamp         | dateTrunc('day', timestamp, 'UTC') 
--------------------------+------------------------------------
 2023-08-04T17:41:09.155Z | 2023-08-04T00:00:00Z
 2023-08-04T17:41:09.155Z | 2023-08-04T00:00:00Z
 2023-08-04T17:41:09.155Z | 2023-08-04T00:00:00Z
(3 rows)

The following units are supported:

• millisecond
• second
• minute
• hour
• day
• week
• month
• quarter
• year

to_unix_timestamp

Converts a DATE or TIMESTAMP value to a Unix timestamp, in seconds. It accepts two arguments:

1. The first argument is the DATE or TIMESTAMP value.
2. The second, optional argument is the timezone (defaults to "UTC").

to_timestamp

Converts a STRING to a TIMESTAMP according to a MySQL format string.

It accepts two or three arguments:

1. The first argument is the STRING that will be converted into a TIMESTAMP.
2. The second argument is the MySQL format that will be used to parse the first argument as a TIMESTAMP.
3. The third, optional argument is the timezone (defaults to "UTC").

String functions

concat

Concatenates strings listed in the arguments without any separator.

SELECT
    taco_name,
    sauce_name,
    concat(taco_name, sauce_name)
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_name |  sauce_name   | concat(taco_name, sauce_name) 
-----------+---------------+-------------------------------
 Chorizo   | Queso Blanco  | ChorizoQueso Blanco
 Breakfast | Salsa Verde   | BreakfastSalsa Verde
 Veggie    | Chipotle Mayo | VeggieChipotle Mayo
(3 rows)

concat_with_separator

Concatenates the given strings with a given separator.

SELECT
    taco_name,
    sauce_name,
    concat_with_separator('-', taco_name, sauce_name)
FROM "TacoSoft Demo Data"
LIMIT 3;

 taco_name |  sauce_name   | concatWithSeparator('-', taco_name, sauce_name) 
-----------+---------------+-------------------------------------------------
 Chorizo   | Queso Blanco  | Chorizo-Queso Blanco
 Breakfast | Salsa Verde   | Breakfast-Salsa Verde
 Veggie    | Chipotle Mayo | Veggie-Chipotle Mayo
(3 rows)

Type conversion functions

cast

Converts an input value to the specified data type. It accepts two arguments:

1. The first argument is the value to convert. It can be of any type.
2. The second argument is the ColumnType to which the value will be cast and passed as a string.