Hi All

I have a script that went haywire and created 2 databases with what
looks like
empty names OR names made up of spaces. Here is the output of my psql
-l command
excluding the 1,2,3,4. As you can see dbs 1 and 3 seem to have no name.
psql -l
Name | Owner | Encoding
---------------+----------+-----------

My Question is: How do I remove these noname databases.....?
Commands that fail are ..
dropdb ' '
dropdb ' '
dropdb ' '
dropdb " "
dropdb " "
dropdb " "
dropdb " "
thanx
Jonathon




---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Jonathon Batson <jonathon (AT) octahedron (DOT) com.au> writes:
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

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
(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.au> writes:
Based on that, I'd venture that you had a carriage return at the end of
the broken names.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend