Re: Removing Database Names as Spaces?

Some interesting results with the Database name and spaces issue

template1=# select datname,length(datname),ascii(datname[0]) as
ascii0,ascii(datname[1]) as ascii1, ascii(datname[2]) as ascii2 from pg_da
tabase;
datname | length | ascii0 | ascii1 | ascii2
---------------+--------+--------+--------+--------
template1 | 9 | 116 | 101 | 109
template0 | 9 | 116 | 101 | 109
jess | 4 | 106 | 101 | 115
| 11 | 99 | 97 | 109
| 10 | 106 | 117 | 115
swim_user_log | 13 | 115 | 119 | 105
(6 rows)

So there are regular characters in the datname field but where are they?.
And yet there is a name when doing the following query names are in the
result.

template1=# select oid, datname from pg_database;
oid | datname
--------+---------------
1 | template1
16975 | template0
58468 | jess
520136 | cam_testdb
540414 | justatest
358025 | swim_user_log
(6 rows)

So from here with the oids is was easy if brutal to drop the dbs.

update pg_database set datname = 'foo' where oid = 520136;
drop database foo;

Thanx Tom

Tom Lane wrote:

>Jonathon Batson <jonathon(at)octahedron(dot)com(dot)au> writes:
>
>
>>I have a script that went haywire and created 2 databases with what
>>looks like empty names OR names made up of spaces.
>>
>>
>
>Judging by your lack of success and the funny formatting of the -l
>output, they're not spaces. Possibly carriage returns and other stuff.
>
>One approach is to find out exactly what you got, instead of guessing.
>Try "select datname,length(datname) from pg_database" then
>"select datname,ascii(datname[N]) from pg_database" where N ranges
>from 0 to one less than the length you just found out. That will
>give you the ASCII codes of the characters that are there. After
>that you can start thinking about how to type it ;-)
>
>Also, there's always the brute-force way of renaming databases:
>
> select oid, datname from pg_database;
> update pg_database set datname = 'foo' where oid = <number from above>
>
>This isn't ordinarily recommended but it seems safe enough, especially
>if you're going to drop the database immediately after ...
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>