Variable with Double Quoted Strings in SQL query

Queries and Resources
1

Ok, I'm struggling with using a string variable in my SQL query, specifically an interval string. My Query:

image
image751×337 37.8 KB

INSERT INTO
  public.scheduled_shifts (start_time, end_time, staff_id)
SELECT
  start_time + interval {{copy_wk_diff.value}},
  end_time + interval {{copy_wk_diff.value}},
  staff_id
FROM
  public.scheduled_shifts
WHERE
  start_time BETWEEN {{moment(cpy_wk_src.view.start)}} AND {{moment(cpy_wk_src.view.end)}};

My goal is to copy a set of shifts from one week into another by adding or subtracting a number of weeks to the original rows dates. However my interval string that I have formatted seems to be in form "1 week". and I get syntax error at or near "$1" It seems like the issue is with the double quotes.

This is on a Postgres Database

2

Hi @jgschaaf,

Welcome to the community!

The issue is indeed with the double quotes around the interval string. Postgres doesn't support interval strings enclosed in double quotes. To resolve this, you need to modify the query to use single quotes instead of double quotes for the interval string. Here's the corrected query:

INSERT INTO
  public.scheduled_shifts (start_time, end_time, staff_id)
SELECT
  start_time + interval '1 week',
  end_time + interval '1 week',
  staff_id
FROM
  public.scheduled_shifts
WHERE
  start_time BETWEEN {{moment(cpy_wk_src.view.start)}} AND {{moment(cpy_wk_src.view.end)}};

By using single quotes, the interval string is correctly interpreted by Postgres, and the query should execute successfully.

Hope this helps.

:grinning:

Patrick

3

Im looking to generate the internal dynamically though and it seems every time I stuff a variable with a string using JavaScript it is incompatible with the SQL query.

4

In the end, I moved all the mangling of the variable right into the double brackets in the SQL query:

INSERT INTO
  public.scheduled_shifts (start_time, end_time, staff_id)
SELECT
  start_time + {{moment(cpy_wk_dest.view.start).diff(moment(cpy_wk_src.view.start), 'weeks')+ ' weeks'}}::INTERVAL,
  end_time + {{moment(cpy_wk_dest.view.start).diff(moment(cpy_wk_src.view.start), 'weeks')+' weeks'}}::INTERVAL,
  staff_id
FROM
  public.scheduled_shifts
WHERE
  start_time BETWEEN {{moment(cpy_wk_src.view.start)}} AND {{moment(cpy_wk_src.view.end)}};

This code worked.

2 Likes