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@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