Tips N’ Tricks – Running your queries from within Vim

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 results to my blogpost in another vim.

It worked, but wasn't really nice.

Today, I realized that I can do something much smarter.

I can just type in Vim, and then pass the data to psql, using simple “visual mapping":

:vmap R :!psql -e<enter>

How does it work? When I'm in Vim, and I select (visual) some text, I press shift-R, and the selected blob is sent to psql.

Of course – psql has to know which database to connect to, as which user, and so on, but this is handled by setting PG* environment variables before running Vim.

Thanks to “-e" option, I get all the queries printed back to me, so I don't lose them from my text file.

It works just great.

While I didn't show it in the ascii cast, I can of course also run in this way multiple queries, use transactions, and everything else. The only problem might be that every such run is executed in new psql, which means that you don't have single session.

But, that doesn't seem to be big problem (at least for me).

It would be nice to have vim as full blown sql client, and I think it's perfectly possible, but I just don't care enough to spend time writing necessary scripts.

How to get shortest connection between two cities

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 < rafasc> Is there a easy way to compute the shortest path between two cities?

Well, I was not really in a mood to solve it, so I just told him to try with recursive queries, and went on my way.

But I thought about it. And decided to see if I can write the query.

Continue reading How to get shortest connection between two cities

Tips N’ Tricks – getting sizes of relations without locks

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 for graphing purposes).

This (getting pg_relation_size for rows in pg_class) has one problem – it can lock, or it can fail.

Continue reading Tips N' Tricks – getting sizes of relations without locks

Tips N’ Tricks – using GNU Screen as shell

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 way to start screen automatically? YES.

Continue reading Tips N' Tricks – using GNU Screen as shell

Tips N’ Tricks – looking for value in all columns of a table

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 application get some value from, and you want to find it in table. Table that has 1-2 fields is trivial to search, but if you have 15 columns, naming them all in WHERE clause is tedious.

Continue reading Tips N' Tricks – looking for value in all columns of a table

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 posts in forums – each get it's ID, but they also have creation timestamp. And it usually is so that higher ids were added later than the lower ids.

So, let's assume you have such table, and somebody asks you to make a report on data from last month. How?

Continue reading Tips n' Tricks – using “wrong" index

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
                                    Table "public.users"
   Column   |           Type           |                     Modifiers
------------+--------------------------+----------------------------------------------------
 id         | integer                  | not null default nextval('users_id_seq'::regclass)
 username   | text                     |
 registered | timestamp with time zone |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Continue reading Tips N’ Tricks – Generating readable reports with plain SQL

Tips N’ Tricks – setting field based on order

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 update first 3 records (ordered by id) to have following values in priority:

  1. 10000
  2. 5000
  3. 1000

Continue reading Tips N’ Tricks – setting field based on order