ads' corner

BOOLEAN datatype with PHP-compatible output

If you use pure PHP (no database abstraction layer) with PostgreSQL, you may run into the problem that your BOOLEAN columns are not recognized by PHP but the value is instead returned as a string.


table size, database size

I have seen this question more than once in the past: “How much storage space do I need for my table?”

PostgreSQL can give you this information.


stop psql on first error

Because I always forget how to stop psql on the first error:

1
\set ON_ERROR_STOP

You can include this at the top of your script/file, psql will stop right after the first error and will not try to continue any other commands in the chain.


Avoid autovacuum for entire databases

We have a database with production data for a platform and another database with a production database dump for testing queries. We also have a test database but this one has slightly different DDL, because it’s for developing new features. Now Autovacuum is running on this server and keeps vacuuming this test database.


Ringbuffer in SQL

On a student platform we show, on every page, a random user in the upper right corner. Because we have over 11.000 users, the database has to select all matching users and sort them by random(). Thats very expensive and one of the longest running queries in the current version. And because we are currently redeveloping the whole platform, I decided to try something different for the random user. The user must not be really random, it should be enough to just display a different user every time the page is displayed. My idea was to implement a ringbuffer in SQL and forward the pointer to the next user in the buffer if we need to search the next random user.


Log Table Changes in PostgreSQL with tablelog

Ever had the need to know what happened with your data? Tablelog could be your answer.


Avoid Unique Key violation

If you have a table with an unique key field, want to insert new values but you don’t know if the value may already be in the table, you can use the following query: