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@octahedron.com.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
