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
Column | Type | Modifiers
id | integer | not null default nextval('users_id_seq'::regclass)
username | text |
registered | timestamp with time zone |
"users_pkey" PRIMARY KEY, btree (id)
Continue reading Tips N’ Tricks – Generating readable reports with plain SQL
I had this interesting case at work. We have imports of objects. Each object in import file has its “ID" (which can be any string). Same “ID" is in database.
So the idea is pretty simple – we can/should check how many of IDs from import were in database. Unfortunately – we'd rather not really do the comparison in DB, as it is pretty loaded.
Continue reading Set operations in shell
This post is basically just an reply to Josh Berkus blog post. Additionally, it refers to “SQL Coding Standards To Each His Own" by Leo Hsu and Regina Obe.
Continue reading Maintainable queries – my point of view
Today, on #postgresql on IRC, guy (can't contact him now to get his permission to name him), said:
I have a table called problematic_hostnames. It contains a list of banned hostnames in column “hostname" (varchar). I would like to display the top 10 troll ISPs based on this. Does PG have a way of spotting a “pattern"? Some ISPs are example.net while others are foo.bar.example.net, so you can't just regexp the last X.Y (since that would cause “.co.uk" to be one of the top troll ISPs).
Continue reading Getting list of most common domains
In PostgreSQL 8.2, we got “RETURNING" clause in INSERT/UPDATE/DELETE queries.
Unfortunately it could not be used as source of rows for anything in sql.
INSERT INTO table_backup DELETE FROM TABLE WHERE ... returning *;
Well, it's still not possible, but it is a one step closer, thanks to patch written and committed by Tom Lane on 31st of October:
Allow SQL-LANGUAGE functions TO RETURN the output OF an INSERT/UPDATE/DELETE
RETURNING clause, NOT just a SELECT AS formerly.
A side effect OF this patch IS that WHEN a set-returning SQL FUNCTION IS used
IN a FROM clause, performance IS improved because the output IS collected INTO
a tuplestore WITHIN the FUNCTION, rather than USING the less efficient
Continue reading Waiting for 8.4 – sql-wrappable RETURNING
SoftNum asked on irc:
< SoftNum> does postgresql have a config option to automatically trim (both ' ' from blah) on string compares?
So, can you?
Of course there is no such option, but maybe there is a way to tell PostgreSQL to do this trim for given field? Sure there is 🙂
Continue reading Text comparisons that does automatic trim()
Cezio wrote post about removing elements from arrays in PostgreSQL.
Unfortunately his blog engine requires registration before comment, which I don't like, so I decided to comment using my own blogspace.
Continue reading Removing elements from arrays
Every now and then I see people ask the question – how to create table if it doesn't exist yet, how to drop it, but only if it does exist and so on.
Well, starting from 8.2 dropping should be not a problem anymore. But what about create? Alter?
Let's try to do it…
Continue reading Conditional DDL?
another new, cool feature commited by tom lane: “Support statement-level ON TRUNCATE triggers."
original patch was submitted by simon riggs, and tom commited it today/yesterday (depending on time zone).
Continue reading waiting for 8.4