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

Postgesql query that shows your indicies size and usage SQL under the cut

In order to get valid database functions like lower() with non-ascii data you should create your database with right collocation and ctype. More under the cut

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

gist:3226836 #ruby #postgres

gist:2949374 #shell #postgresql #pgdump

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" test` ; do  psql -c "alter table $tbl owner to test" test ; done

#pg #postgres

After creation wiki we had got a problem: we have a difference tags about one thing. For example: #js and #javascript. This situation is ok because everyone sometimes loves to write short messages and sometimes full.

We solve this problem by determining main tag and linking it with aliases. More under the cut

Last night we deployed release version and after few hours db had been crashed. In order to prevent it I change db to #postgresql but troubles founds me: #sqlite3 dump is not suitable to pg.

After fix it I had got new problem - unicorns crashes with pg... Few hours later I found a solution and fixed some other bugs.

Seems like all stabilized now.

I hope this history will never repeat.

#gistflow team.

If you don't want to spend hours installing some full text search engine here is super easy solution.

Code is under the cut. More under the cut

I remember times when I write rake tasks to export some query result to csv. Actually there is a pretty way to do this in #Postgresql:

my_project=# \o ~/Desktop/taggings.csv
my_project=# select * from taggings;
my_project=# \q

Sometimes in #rails you don't want to touch database.yml file and to leave username and password as they were. But migrations will fail because you don't have a role (username and password like in your database.yml). More under the cut

It is much faster and if you have huge Gemfile and rake task lasts forever - dropdb will save you some time. More under the cut