Drop database owned by specific user in PostgreSQL

Question

I have created database with bad symbols at the end of its name. Now I want to drop it but I don't know how to type its name in the console. Can I remove databases owned by this user? I tried to DROP OWNED BY devuser but it does not worked. I see just DROP OWNED in the console.

Answer 1

You could also try this from your shell:

-bash-4.1$ bad_dbname=$(psql -t -q -c "select datname from pg_database where datname like 'bsw_ui%' limit 1")
-bash-4.1$ echo "the next command will drop ${bad_dbname}"
-bash-4.1$ psql -c "drop database ${bad_dbname}"

I verified this works using bash4.1 on centos6.3 with posgresql8.4 for a database using this unicode character. YMMV

Answer 2

I know this is an old post, but I would just like to offer a suggestion that has worked for me before. You can copy the symbol to clipboard and then past into the commands you need to use such as DROP TABLE

Answer 3

Interesting problem. The only solution I can think of involves a lot of coding.

My suggestion would be to SELECT the name of the database out of pg_database and then craft a statement in PL/PGSQL inside a function and then use dynamic sql to EXECUTE the statement. For this to work, you need to be in another database and also create/run the function in another database.