rails postgres ERROR: invalid input syntax for type double precision

Question

i'm trying the following:

user_calendars.where("extract(day from time_from) = ? ", next_day)

But i keep getting this Error:

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type double precision: "Saturday" LINE 1: ..."user_id" = $1 AND (extract(day from time_from) = 'Saturday'...

Not sure why it points out type double.

Mike Sherrill 'Cat Recall' · Accepted Answer · 2014-11-29 15:49:20Z

4

In PostgreSQL, the expression extract(day from time_from) returns a number of type double, representing the day of the month. Saturday is clearly not a valid double.

If you need the argument to where() to match the string 'Saturday' (to match the day of the week), then use the to_char() function.

user_calendars.where("trim(to_char(time_from, 'Day')) = ? ", next_day)

You need trim(), because this kind of call to to_char() is padded to 9 characters.

Case is significant for the argument 'Day'. If you key it as 'day', the returned value won't match 'Saturday'. Instead, an expression like to_char(time_from, 'day') will return something like 'saturday'.

edited Nov 29, 2014 at 15:49

answered Nov 29, 2014 at 15:42

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

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

1 Comment

mu is too short Over a year ago

Or convert the Ruby next_day string to a number that matches what extract(day ...) returns.

Collectives™ on Stack Overflow

rails postgres ERROR: invalid input syntax for type double precision

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related