How to Use Variables in a Query

PopSQL Query Variables make it convenient to add filters or logic to your SQL for use in the PopSQL app OR if you are sharing query results with a coworker and want to give them easy-to-use filters without having to edit the underlying SQL. This feature is not available on the PopSQL free plan.

Introduction to Query Variables

PopSQL will automatically create a variable as soon as you write some text between double curly braces, like: {{email}}. You'll see the variable immediately appear at the top of your query editor:

Configuring a query variable

Once you have a query variable in your SQL, you'll see an input for it above your query. Click the settings icon to configure the variable. In the settings popup, you can:

• Rename your variable
• Change the type:
  • String
  • Number
  • Date
  • Boolean
  • Dropdown
  • Multiselect
• Set a default value
• Mark the variable required

When you run a query with variables, PopSQL will first replace your variables with the values you input, then run the query against your database. If variable fields are left empty, the curly brackets and variable names will be replaced by nothing and will likely cause your query unless you have handled that scenario in your SQL statement (see below for examples).

Examples

String: find user by email

String variables need to be wrapped in single quotes to work correctly since the curly brackets and variable name are replaced with the text from the variable form and no quotes are added automatically.

For string comparisons it's best practice to convert the column and the variable text into the same case so you don't run into issues matching strings just because the case of a letter doesn't match. In the example below we're using the LOWER function, which works in Redshift, Snowflake, BigQuery, and others.

select *
from users
where lower(email) = lower('{{email}}')

You can also use variables to do a partial search by using the LIKE or ILIKE operators:

select *
from users 
where email ilike '%{{email_domain}}'

In this example we recommend using ILIKE if your database supports it since it does a case-insensitive comparison. If your database doesn't support ILIKE you can still convert the email column to lowercase and only use lowercase letters when typing the email domain into the variable form.

If you want an empty variable form field to still execute the query without a filter, you can add some simple SQL logic that evaluates to TRUE when the variable returns as NULL:

select *
from users 
where (
    lower(email) = lower('{{email}}') 
    or 
    {{email}} is null
)

Number: find user by a numeric ID

The curly brackets and variable name are simply replaced by the number input into the variable form field. If you leave the form blank, the variable returns as NULL.

select *
from users
where id = {{user_id}}

If you want a blank variable selection to return the query successfully with no filter, you can add some SQL logic like this:

select *
from users
where (
    id = {{user_id}}
    or 
    {{user_id}} is null 
)

Date: count users signed up after a certain date

Dates need to be wrapped in single quotes. We also suggest converting your timestamps to dates and using >= to make your date ranges inclusive of the date chosen in the variable form.

select count(1)
from orders
where created_at::date >= '{{starts_at}}'

If your variable name ends with _date or _at, PopSQL will automatically assume the variable is a date and display a date picker in the variable form:

select *
from users
where created_at between '{{created_at}}' and '{{end_date}}' -- both of these will automatically show a date picker in the UI

Boolean: find activated users

select *
from users
where activated is {{activated}}

Dropdown: find paid users

Dropdown variables work just like String variables. The only difference is that you can provide a list of options rather than allowing for raw text input.

In this example we are using a Liquid control flow if statement to execute some logic on variables before the query runs. Liquid is the template language used to power PopSQL query variables.

select *
from users
{% if segment == 'free' %}
  where plan is null
{% elsif segment == 'paid' %}
  where plan is not null
{% endif %}

You can also use a standard text comparison like the String variable examples:

select * 
from users 
where user_segment = '{{segment}}'

Multiselect: find multiple event names

When you set up a multiselect variable, PopSQL will automatically wrap the selected values in single quotes and add commas between selected values. If only a single value is chosen, it is still wrapped in single quotes, just no comma.

select *
from events
where name in ({{ event_names }})

Advanced

PopSQL uses Liquid as its template language, so there's a lot of useful stuff you can do.

You can use an if and unless statements in your query:

select *
from users
{% unless skip_join %}
  inner join orders on orders.user_id = users.id
{% endunless %}
{% if email %}
  where users.email = '{{email}}'
  limit 10
{% endif %}

Keep in mind that the Liquid template will be evaluated before running the SQL query.