SELECT * FROM Posts ;
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.
Here is a trick I have found:
Assume we have a table
abilities with field
select abilities.* from abilities order by (case when available then 1 when available is null then 2 else 3 end) asc
3 and then sorts by integers.
Do you know another solution?
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)
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!