I use VIM. For more or less everything. Including writing blogposts. Usually, when I was working on blogpost about PostgreSQL, I would write an sql file, switch to another console with psql running, run \i, get output, and then copy/paste the ...

Yesterday, on #postgresql on irc some guy asked: 22:28 < rafasc> i am trying to use plpgsql to find the shortest path between two cities, each pair of cities has one or more edges, each edge has a different wheight. 22:28 < ...

If you have production DB servers, chances are you're running variant of these queries: SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'i' SELECT sum(pg_relation_size(oid)) from pg_class where relkind = 'r' To get summarized size of tables and/or indexes in your database (for example ...

Every now and then I need a function that returns nothing. As you perhaps know, even defining function as "RETURNS VOID" doesn't fully solve the problem:

I'm quite often doing stuff on remote machines, and quite frequently I start some long-running job, when I remember that I didn't ran it via screen - so it will break, if my network connection will die. Is there any sane ...

Every so often you might need to find a value regardless of which column it's in. Of course not in application code, as this would be very slow. But you might be in situation where you just don't know where did ...

I got asked this: having this table: # select * from a order by d; t | d ---+---- O | 1 O | 2 O | 3 M | 4 M | 5 M ...

April 15th, 2010 by depesz | Tags: , , , | Comments Off on Tips n’ Tricks – using “wrong” index

More than once I've seen situation when there is a table, with serial primary key, and rows contain also some kind of creation timestamp, which is usually monotonic, or close to monotonic. Example of such case are for example comments or ...

June 23rd, 2009 by depesz | Tags: , , , , | Comments Off on Tips N’ Tricks – Generating readable reports with plain SQL

Let's say you imported some data, but it contains duplicates. You will have to handle them in some way, but to make sensible choice on how to handle it, you need more information. So, let's start. We have table: # \d users ...

Let's imagine following situation: create table test (id int4 primary key, priority int4); insert into test (id) select distinct (random() * 100000000)::int4 from generate_series(1,1000); Table test will now contain some (up to 1000) records, with random ids. Now, we want to ...