So, yes. There is no need in test database. My bad! 🙂
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".
Timing is on.
test_role_db=# DO $$
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.
As for me I’m pretty sure that it’s not cool to use non-Latin letters for database objects names. In other words this is true Mauvais Ton.
However the reality dictates its own rules. I saw a lot of databases with names in Hebrew, Russian etc. What is the disadvantage of such names using?
If we’re talking about Linux console then the answer most likely will be: “There is no disadvantage at all”, though I haven’t every existing distributive tested, but only a few. In this article we will use pg_live – a Xubuntu-based live CD designed and optimized expressly for the PostgreSQL Database administrator and enthusiast.
Let’s create database with Cyrillic letters under gorgeous name of “Колбаса”. First of all start psql. You may use such command:
$psql -h /tmp -U ubuntu ubuntu
After psql started we’ll create and connect to “Колбаса” database:
As you can see no magic is needed. In Windows it’s quite the contrary.
Just for information: I’m using WinXP with SP3 installed.
Let’s the wizardry begin. First of all we must set code page used in the Windows console :
C:\Program Files\PostgreSQL\8.3\bin\>chcp 1251
Active code page: 1251
Could not find this for three days, was cursing up hill and down dale. But it turnes out the only thing needed is the Notes for Windows users chapter in the psql manual.
Do not forget to set Lucida Console font either:
C:\Program Files\PostgreSQL\8.3\bin\>psql -U pasha "Test"
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.
Type: copyright for distribution terms
h for help with SQL commands
? for help with psql commands
g or terminate with semicolon to execute query
q to quit