CODEWITHSUNDEEP
postgresqlpostgresql-9.4
abhisek
abhisek

Reputation: 347

ERROR: invalid input syntax for type timestamp:

I am getting an Error in my PostgreSQL query as ERROR: invalid input syntax for type timestamp:

I am setting the value using \set.

\set dueDateEarliest '2018-04-01'
\set dueDateLatest '2018-08-01'
\set dueDateLatest '2018-08-01'

And trying to use these value in my query as below

SELECT DISTINCT(bu.id) as "user_id",c.organization_name as "name",round(i.balance,2) as "amount_due",i.id as "invoice_number",i.due_date as "due_date",CONCAT('collectionMonth', LPAD(cf2.content,2,'0')) as "collection_date" FROM base_user bu,contact c,contact_field cf, invoice i, contact_field cf2 WHERE bu.id = c.user_id AND bu.deleted = 0 AND cf.contact_id = c.id AND cf.type_id = 7 AND cf.content = 'DD' AND i.user_id = bu.id AND i.balance > 0 AND i.is_review != 1 AND i.deleted != 1 AND due_date BETWEEN 'dueDateEarliest' AND 'dueDateLatest' AND cf2.contact_id = c.id AND cf2.type_id = 8 ORDER BY bu.id limit 20;

This is giving error as ERROR: invalid input syntax for type timestamp:

I am not getting any way to fix it. And moreover the way I am setting value using \set is it fine ? Or should I use SET to set the values. Because in actual when I have to run these command from a shell script I will be calling/setting as
`set dueDateEarliest '$dueDateEarliest'` from shell script.

Which is the best way ?

Attaching the screen shot as wellenter image description here

Upvotes: 1

Views: 7635

Answers (1)

Jeremy
Jeremy

Reputation: 6713

It's an issue with how you formatted your query. Let's simplify it a bit:

# \set dueDateEarliest '2018-04-01'
# select 'dueDateEarliest'::timestamp;
ERROR:  invalid input syntax for type timestamp: "dueDateEarliest"
LINE 1: select 'dueDateEarliest'::timestamp;

It doesn't work, because it's trying to use the string 'dueDateEarliest', not the variable.

Here's the correct way:

# select :'dueDateEarliest'::timestamp;
      timestamp
---------------------
 2018-04-01 00:00:00
(1 row)

Upvotes: 1

Related Questions