Re: Creating 1 million users in PostgreSQL

Hans-Jürgen Schönig wrote a great post “Creating 1 million users in PostgreSQL”. I just love such things! Well done! Thanks.

But since there is no possibility to leave comments under original post (BTW why?), I’ll create my own post.

  1. When testing such tricks always create temp database. Because I accidentally used… no, no, not the production one.. but anyway. 🙂

    Since when temp database matters when creating cluster-wide role?
    © Ervin Weber

    So, yes. There is no need in test database. My bad! 🙂

  2. Hans-Jürgen used shell, psql and \gexec combination. However, we may do all these steps using psql only

To make a long story short, this is my variant of creating 1 million users in PostgreSQL:

Test=# CREATE DATABASE test_role_db;
Time: 7947,657 ms

Test=# \c test_role_db
You are now connected to database "test_role_db".

test_role_db=# \timing
Timing is on.

test_role_db=# DO $$
test_role_db$# BEGIN
test_role_db$#   FOR i IN 1..1000000 LOOP
test_role_db$#     EXECUTE 'CREATE ROLE xy' || i;
test_role_db$#   END LOOP;
test_role_db$# END; $$;
Time: 81267,482 ms

On my test environment generating 1 million users in a single transaction takes around 1 minute 21 seconds. This is a little bit longer than Hans-Jürgen’s timing. Suppose this is due to the hardware difference.


Code Quality Comparison of Firebird, MySQL, and PostgreSQL

I have read very interesting post “Code Quality Comparison of Firebird, MySQL, and PostgreSQL” today about static analysis of three open-source RDBMS. And I wonder, should we use static code analyzers on an ongoing basis, e.g. PVS Studio?


So, the code-quality rankings are as follows:

    • 1 place – Firebird and PostgreSQL.
    • 2 place – MySQL.



Please remember that any review or comparison, including this one, is subjective. Different approaches may produce different results (though it is mostly true for Firebird and PostgreSQL, but not for MySQL).


Easy PostgreSQL docs for Sublime Text

I’m using this perfect tool called Sublime Text 3 for a bunch of tasks. One of them is viewing SQL scripts from time to time. ST3 has perfect SQL highlighting, but what I miss the most is the context help functionality, e.g. I select “LEFT JOIN” and hit F1 hot key. But that’s not problem since ST3 has a lot of packages. To solve my problem I need GotoDocumentation package and some tuning. Here is my settings for GotoDocumentation:

"docs": {
// obj containing the docs for each scope
// these are merged with the default ones
// the key value pair represent scope -> doc url
// supported placeholders:
// - %(query)s the selected text/word
// - %(scope)s the current scope
"sql": ""

"pascal": "",
// if we have no docs for the current scope
// we will try using the fallback one,
// to disable set to false
"fallback_scope": "google"

ST3 context help  for pg sql
ST3 context help for pg sql

Creating titles from text (SQL)

Task: get title (or excerpt) from given text (body)

left(body, 29) ||
COALESCE( substring( substr( body, 30 ) from '(.+?)(\,| )' ), '' ) as title,

Details: Take the first 29 characters, concatenate them with substring started from the 30-th character till the first occurrence of comma or space. Or concatenate with empty string, if pattern doesn’t match (COALESCE).

MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:


  1. MERGE INTO acme_obj_value d
  2. USING ( SELECT object_id
  3.         FROM   acme_state_tmp
  4.       ) s
  5. ON (d.object_id = s.object_id)
  6.   WHEN matched THEN
  7.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  8.   WHEN NOT matched THEN
  9.     INSERT (, d.object_id, d.date_value)
  10.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)

PostgreSQL statement:


  1. WITH s AS (
  2.      SELECT object_id
  3.      FROM   acme_state_tmp
  4. ),
  5. upd AS (
  6.      UPDATE acme_obj_value
  7.      SET    date_value = LEAST(l_dt, d.date_value)
  8.      FROM   s
  9.      WHERE  acme_obj_value.object_id = s.object_id
  10.      RETURNING acme_obj_value.object_id
  11. )
  12. INSERT INTO acme_obj_value(id, object_id,  date_value)
  13. SELECT NEXTVAL(‘acme_param_sequence’), s.object_id, l_dt
  14. FROM   s
  15. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)