Errors while passing parameters into a psql command

Question

I'm trying to connect to a Redshift cluster and run a SQL script with parameters, but am getting errors.

Here's the Bash command:

psql -v schema="$REDSHIFT_SCHEMA" -h "$REDSHIFT_HOSTNAME" -d "$REDSHIFT_DATABASE" -U "$REDSHIFT_USERNAME" -p "$REDSHIFT_PORT" -f "nulltest.sql" -v inputStartDateId=20170318 -v inputEndDateId=20170404

And the contents of nulltest.sql is:

select 
    COALESCE( $inputStartDateId, min(StartDateID)),
    COALESCE( $inputEndDateId, max(EndDateID))
from (
    select 
        row_number()over(order by FiscalWeekID desc) as rownum,
        FiscalWeekID,
        min(dateid)StartDateID
        ,max(dateid)EndDateID 
    from 
        dm.dim_Time
    where DateKey < getdate()
    group by FiscalWeekID
    ) a
where rownum <= 3

The error I'm getting is:

psql:nulltest.sql:15: ERROR:  syntax error at or near "$"
LINE 2:  COALESCE( $inputStartDateId, max(StartDateID))

Am I calling the parameter incorrectly in the SQL script? The SQL runs perfectly fine through Aginity when I pass values into the parameter at run time. I'm not sure what's supposed to be different when calling the SQL through the psql command though

what's the difference between the top min/max and the inner one? — RoMEoMusTDiE, Apr 04 '17 at 01:36
are you passing the dates to create a date range or something else — RoMEoMusTDiE, Apr 04 '17 at 01:42
The inner one helps define and find the StarDateID and EndDateID. I'm passing the parameters in because sometimes I have to manually set the dates — simplycoding, Apr 04 '17 at 13:59

score 1 · Accepted Answer · edited May 23 '17 at 12:02

1

You're passing your parameters correctly on the command line, but your nulltest.sql file should use :inputStartDateId or :'inputStartDateId' instead of $inputStartDateId. Previous answer: https://stackoverflow.com/a/30850125/6650678 .

edited May 23 '17 at 12:02

Community

1
1

answered Apr 04 '17 at 16:01

Jason Rosendale

801
6
7

Errors while passing parameters into a psql command

1 Answers1