How to insert tsrange using perl

Question

Using DBD::Pg im attempting to make an insert statement with binded variables which one of them is a tsrange. I keep getting syntax errors can some one please explain the proper way to do this?

from perl script :

$sth->{'insert'}->execute($hashRef->{'<NUMBER>'}
                                    , $hashRef->{'<FIRSTNAME>'}
                                    , $hashRef->{'<LASTNAME>'}
                                    , $hashRef->{'<DATEIN>'} . ' ' .       $hashRef->{'<TIMEIN>'}
                                    , $hashRef->{'<DATEOUT>'} . ' ' .  $hashRef->{'<TIMEOUT>'}
                                    , $hashRef->{'<JOBCODE>'} 
                                    , $hashRef->{'<JOBCODEDESC>'} 
                                    , $hashRef->{'<COSTCODELEVEL1>'} 
                                    , $hashRef->{'<COSTCODELEVEL2>'} 
                                    , $hashRef->{'<COSTCODELEVEL3>'} 
                                    , $hashRef->{'<DEPARTMENT>'} 
            )or die $DBI::errstr;
    enter code here

from config file:

sql:
    insert: |-
        insert into etl.timeclock_plus values (
           ? 
         , ? 
         , ?  
         , [ ? , ? ] 
         , ? 
         , ? 
         , ? 
         , ? 
         , ?
         , ? 
        )   
The error : 
syntax error at or near "$4"

Seems rather unlikely that the error message you show would be generated by that code. — Dave Cross
– Dave Cross, Commented Oct 8, 2019 at 15:35
Uncaught exception from user code: DBD::Pg::st execute failed: ERROR: syntax error at or near "$4" LINE 5: , '[' $4 ',' $5 ']' ^ at TimeClock.pl line 161. — Joycel
– Joycel, Commented Oct 8, 2019 at 15:47
Well, that whole [ ? , ? ] section doesn't look like valid SQL. — Dave Cross
– Dave Cross, Commented Oct 8, 2019 at 15:48

Laurenz Albe · Accepted Answer · 2019-10-08 16:08:29Z

3

Instead of

[ $1, $2 ]

which is invalid SQL, use a range constructor function:

tstzrange($1, $2, '[)')

There is also tsrange and daterange if you need those data types.

answered Oct 8, 2019 at 16:08

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert tsrange using perl

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related