Sometimes you just want to find some value, regardless of which column it's in. If the table has few columns you can easily:
=$ SELECT * FROM TABLE WHERE a ~ '...' OR b ~ '...' OR c ~ '...'
But if there are many columns writing such query quickly becomes tedious. Luckily there is simple solution.
Continue reading DBA tips ‘n tricks – search in every field in a table
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.
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
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
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:
Continue reading Tips n' Tricks – return nothing from PL/PgSQL function
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.
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
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 | 6 M | 7 O | 8 O | 9 O | 10 I | 11 I | 12 I | 13 (13 ROWS)
Is it possible to add “rank" column, that will increment whenever t changed?
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?
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