How To Store UUIDs in PostgreSQL

This story is a just another example of what I call

Using varchar(255) for everything won’t make you a sorcerer.

Every database has many different types. They are there on purpose, not by accident. I’ve seen that too many times: a programmer (most companies don’t have DBAs for that) creates a database strsucture using only ONE type. Sometimes they use more… but hardly ever they use all those nice features they database engine provides.

This is a common pattern:

Pay thousands of dollars for the database licence – use it like a CSV file.

PostGIS and Small Number of Huge Geometries

A couple of weeks ago I had quite a strange situation: a PostgreSQL table with small number of huge geometries. A program had to query the table really often, so the speed was a crucial thing.

The table was really small, but the spatial query, using PostGIS, was very slow.

Why Is A Query Slow?

A couple of days ago a colleague of mine had quite a normal problem. A query was slow. The whole application was written in Java, using Hibernate, all the tables were really small.

There were about 30 rows in one table. 10 rows in another. One small join. One call of a PostGIS function.

The query took several seconds to return just about 20 rows. Quite bad.

Problem with random() in PostgreSQL Subselect

In a simple project I work sometimes on, I needed to generate a lot of random data in a PostgreSQL database. I’ve got a couple of functions for that, but all of them are based on the simple random() function.

The random() function returns value from the range [0, 1):

    # select random();
          random
    -------------------
     0.583591205533594
    (1 row)

So let’s generate the random number ten times.

How to Ensure There is One Row With a Value

Let’s imagine a business requirement that there should be only one active row in a table. Active means that the column is_active is set to true. There can be many rows with the false or null values. The row with the only one value can be only one.

The simple table could look like this:

    CREATE TABLE test(
        id SERIAL PRIMARY KEY,
        is_active BOOLEAN NOT NULL
    );