#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0

    Drop database noob


    created a user pgsql_admin, created xxxxxxdb as that user. now i want to delete it. hate to ask but how's it done?

    i've logged in as the user and ran "dropdb xxxxxxdb" logged in as postgres and ran "drobdb xxxxxxdb".
    logged in as postgres and ran DROP DATABASE [IF EXISTS] xxxxxxdb.

    I've the PostSQL 9.1.9 documentation DROP DATABASE open in front of me right now. I've googled for it, and I've searched this forum and came up with this hit.

    forums.devshed.com/postgresql-help-21/deleting-database-204027.html?&highlight=drop+database

    But when I run '\l" the database is listed.

    Running Wheezy for the o/s.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    From within psql:
    Code:
    drop database [db_name];
    Caveat: you cannot be connected to that database at the moment.
    The psql prompt shows what db you're currently connected to. Check this out:
    Code:
    [ceverett@taco ~]$ psql
    psql (9.2.4)
    Type "help" for help.
    
    ceverett=# create database foo;
    CREATE DATABASE
    ceverett=# create database bar;
    CREATE DATABASE
    ceverett=# \c foo;
    You are now connected to database "foo" as user "ceverett".
    foo=# drop database foo;
    ERROR:  cannot drop the currently open database
    foo=# drop database bar;
    DROP DATABASE
    foo=# \c ceverett
    You are now connected to database "ceverett" as user "ceverett".
    ceverett=# drop database foo;
    DROP DATABASE
    So when I was connected to the database "foo" the prompt was "foo=#", and I couldn't drop the database named "foo" right then because I had an open connection to it, but I could drop "bar", because it was not then in use. When I switched my connection to my default database "ceverett" I was then able to drop foo with no issue.

    If your database name has special capitalization or spaces or anything else (silly) in it like "MyDataBase" or "My Database" whatever you'll need to put double quotes around the name.

    Comments on this post

    • jim_armstrong agrees : Helped me out big time.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    The semi colon did the trick. Thanks for helping me out it was driving me crazy,LOL. Looking at the documentation it seemed simple. But the doc's don't mention the semi colon and for greenhorns that's a pita. And thanks for the detailed information I appreciate you taking the time to school me.

    Originally Posted by zxq9
    From within psql:
    Code:
    drop database [db_name];
    Caveat: you cannot be connected to that database at the moment.
    The psql prompt shows what db you're currently connected to. Check this out:
    Code:
    [ceverett@taco ~]$ psql
    psql (9.2.4)
    Type "help" for help.
    
    ceverett=# create database foo;
    CREATE DATABASE
    ceverett=# create database bar;
    CREATE DATABASE
    ceverett=# \c foo;
    You are now connected to database "foo" as user "ceverett".
    foo=# drop database foo;
    ERROR:  cannot drop the currently open database
    foo=# drop database bar;
    DROP DATABASE
    foo=# \c ceverett
    You are now connected to database "ceverett" as user "ceverett".
    ceverett=# drop database foo;
    DROP DATABASE
    So when I was connected to the database "foo" the prompt was "foo=#", and I couldn't drop the database named "foo" right then because I had an open connection to it, but I could drop "bar", because it was not then in use. When I switched my connection to my default database "ceverett" I was then able to drop foo with no issue.

    If your database name has special capitalization or spaces or anything else (silly) in it like "MyDataBase" or "My Database" whatever you'll need to put double quotes around the name.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by jim_armstrong
    But the doc's don't mention the semi colon
    Yes it does, right at the beginning where the SQL syntax is explained:
    Originally Posted by The Manual
    SQL input consists of a sequence of commands. A command is composed of a sequence of tokens, terminated by a semicolon (";")
    Taken from: http://www.postgresql.org/docs/curre...x-lexical.html

    (And besides: this is also one of the most basic SQL syntax rules).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by shammat
    Yes it does, right at the beginning where the SQL syntax is explained:Taken from: www_postgresql_org_docs_current_static_sql-syntax-lexical

    (And besides: this is also one of the most basic SQL syntax rules).
    Here's the link I'm referring too. No mention of the semi colon here.

    www_postgresql_org_docs_9.1_static_sql-dropdatabase

    I didn't say I knew jack about SQL. Should I be doing this probably not. But I'm stuck with it. Someone else left in the middle of the project.

    I'll check out the link you provided

IMN logo majestic logo threadwatch logo seochat tools logo