PostgreSQL: Drop DB which exits with no parameters

Question

I am trying from 3 days to drop the 'test123' DB which is not getting drop. Postgres reply of no such db exists.. i have no idea how it listed like this. and the db with \r

template1=# select * from pg_database;
        datname        | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace |               datacl                
-----------------------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+-------------------------------------
 template1             |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         12035 |          709 |          1663 | {=c/postgres,postgres=CTc/postgres}
 template0             |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         12035 |          709 |          1663 | {=c/postgres,postgres=CTc/postgres}
 postgres              |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12035 |          709 |          1663 | 
 PremierSuppliers      |  16384 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12035 |          709 |          1663 | 
 \r                   +|  16384 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12035 |          709 |          1663 | 
 test123               |        |          |             |             |               |              |              |               |              |               |

Any workaround solution? to drop \r and test123 . i need to know what is the '+' placed in \r . and how to drop it. just to let you know that i already tried the steps given for similiar issue here but my problem not resolved.

I think you have one database called \r...test123 (where ... are a bunch of spaces) or similar. — mu is too short
– mu is too short, Commented Oct 15, 2013 at 21:00

Daniel Vérité · Accepted Answer · 2013-10-16 12:28:05Z

1

The problematic database name seems to consist of a carriage return (ASCII code 13, shown as \r) followed by a newline (ASCII code 10) followed by the string test123

The sequence of characters 13,10 is the end of line in Windows.

The + sign at the end of the column is added by psql as a visual indication that the column continues on the next line. It's not part of the database name.

You may confirm the exact character codes by issuing:

SELECT encode(datname::bytea,'hex') from pg_database where datname like '%test123';

The expected result would be 0d0a74657374313233 if there are no spaces in addition to the CRLF. (otherwise please update the question with the actual result).

If using psql in Unix with readline capabilities, you may drop the database with this sequence of keystrokes:

DROP DATABASE "Ctrl+VCtrl+MEntertest123";Enter

When hitting Ctrl+VCtrl+M the screen should display ^M

EDIT:

If you can't manage it within psql in interactive mode, as an alternative this should also work from bash, to be launched by the postgres user or the database owner:

echo -e "DROP DATABASE \"\r\ntest123\";" | psql -d template1

The -e option to echo enables the interpretation of backslash escapes.

edited Oct 16, 2013 at 12:28

answered Oct 15, 2013 at 22:30

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Johnbritto Over a year ago

I really appreciate you for the help. Yes the expect result of SELECT command was indeed shows template1=# SELECT encode(datname::bytea,'hex') from pg_database where datname like '%test123'; encode -------------------- 0d0a74657374313233 (1 row) In order to drop DB, i.e pressing ctrl+V ctrl+M i see it display ^M template1=# DROP DATABASE "^M ENTER , it seems screen goes to new one. template1"# Can you pls suggest how to drop it and how to know if psql in linux enable with readline capablilies. Excuse me for this questions as i m in learning stage on postgresql.

Daniel Vérité Over a year ago

@Johnbritto: if screen goes to new one means it goes to the next line, that's fine. Continue typing the rest of the keystrokes and hit Enter after the ; terminating the command. Then it should display DROP DATABASE and the db will be gone.

Johnbritto Over a year ago

Great, thanks u so much. it solved the issue. test123 is no more.. could you please educate what might be the cause of such database creation and getting locked. i think one of my dev would have commited this mistake unknowingly.

Daniel Vérité Over a year ago

@Johnbritto: the CR,LF sequence suggests a Windows program with a multiline input field for the database name, but that's speculation.

Collectives™ on Stack Overflow

PostgreSQL: Drop DB which exits with no parameters

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related