CODEWITHSUNDEEP
sqlruby-on-railssqlitepostgresql
brad
brad

Reputation: 9773

Finding null values when I write a query in rails

I have just written the following query and gotten a surprise (to me) result;

Customer.where("satisfaction != ?","Very satisfied")

This returned far fewer values than I expected and the reason turns out to be null values in the database for my satisfaction column. To get the records I want I have to write the following query

Customer.where("satisfaction != ? or satisfaction is null","Very satisfied")

This works but it's ugly and I'm hoping there's a better way (especially as I plan on generating these queries dynamically so I want them to be as simple as possible). Is there an operator that incorporates null values along with unequal values?

My database is SQLite3 in development and Postgresql in production.

Upvotes: 1

Views: 755

Answers (1)

mu is too short
mu is too short

Reputation: 434665

In PostgreSQL you could use IS DISTINCT FROM to get around the usual NULL issues:

Customer.where('satisfaction is distinct from ?', 'Very satisfied')

In SQLite I think you're stuck with your explicit IS NULL check or an equally ugly COALESCE:

Customer.where("coalesce(satisfaction, '') != ?", 'Very satisfied')

COALESCE evaluates to its first non-NULL argument and will work in both SQLite and PostgreSQL; you can use anything that won't match your 'Very satisfied' in place of the empty string.

I'd strongly recommend that you stop developing on top of SQLite when you're deploying on top of PostgreSQL. You're just asking for a bad time on production server when SQLite's lax interpretation of SQL conflicts with PostgreSQL's stricter interpretation. Don't do this to yourself, develop and deploy using the same database (where same includes the version number).

You might also want to revisit your schema to see if you can add a NOT NULL constraint to your satisfaction column. An explicit 'Unknown' string might serve just as well and would avoid the usual NULL nonsense. I tend to manually default everything to NOT NULL and only allow NULLs if I can justify it.

Upvotes: 5

Related Questions