CODEWITHSUNDEEP
sqlruby-on-railspostgresql
ntonnelier
ntonnelier

Reputation: 1549

rails postgres ERROR: invalid input syntax for type double precision

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.

Upvotes: 2

Views: 5354

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

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'.

Upvotes: 4

Related Questions