PostgreSQL not having an UPSERT command, but it can do with WITH statement more under cut

Let me dilute all our programming posts and questions with article mostly related to startups. On example of simple application we will depict some of the project's KPIs with Google Charts and some tricky ActiveRecord queries.

How to build plots like this under the cut

While refactoring old projects you should pay attention to indexes and how good optimised not only Ruby code but also #sql queries.

Of course you can manually look over each model in your #rails app, but there is a good gem 'lol_dba'. Usage example under the cut

Here is a trick I have found:

Assume we have a table abilities with field available.

select abilities.* from abilities order by 
(case when available then 1 when available is null then 2 else 3 end) asc

It replaces true as 1, null as 2 and false as 3 and then sorts by integers.

Do you know another solution?

#sql

So next added project will have id 2555

SELECT setval('projects_id_seq', 2554);

and check with

SELECT nextval('projects_id_seq');

#posgresql #sql

If you have same float values in your database you can't group by it.

But you are able to convert it to numeric and round it. For example:

SELECT COUNT(*) AS count_all, lat::float::numeric(9, 6), lng::float::numeric(9, 6) AS lat_float_numeric_9_6_lng_float_numeric_9_6 FROM "locations" GROUP BY lat::float::numeric(9, 6), lng::float::numeric(9, 6)

#sql #postgresql

Recently in my #rails I had a task to split one column into two - it was daily weather forecast and I made it day time forecast and night time. But in database there were already thousands of forecasts and logic was pretty simple - since the daily forecast was the average, night forecast was treated like the daily one where sun was switched to the moon :) So it is quite obvious the change I made was needed. More under the cut

I wanna optionally notify us about a new posts in flow.

I have created post and I need to find all users who see it in flow. Task

I am quite sure that nowadays every #rails developer adds indexes to migrations preventing creation of duplicates and making #db queries fast. But sometimes you face old projects with a lot of duplication which is needed to be removed.

In this post I will show you some techniques to remove duplications. More under the cut

I can't help posting this song about 7 databases, this is so fun! I think everyone who start studying databases should listen to this amazing song!

http://www.youtube.com/watch?v=jyx8iP5tfCI&feature=player_embedded

#db #nosql #sql #humor