Where did my database go in postgres?

Question

postgres refuses to work. I am using 9.2 and a newbie.

I create a database. I list and its not there? There is no error! Where did it go? Was it ever created?

postgres-# creatdb test
postgres-# \list
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_PH.UTF-8 | en_PH.UTF-8 | 
 template0 | postgres | UTF8     | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_PH.UTF-8 | en_PH.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres-# 



postgres@ubuntu:/home/ubuntu$ psql -h 127.0.0.1 -U postgres -d test
Password for user postgres: 
psql: FATAL:  database "test" does not exist

Answer 1

You have two errors:

1. createdb is an operating system command, it's not a SQL command. In psql you need to use SQL statements and that would be: CREATE DATABASE. For details see the manual: http://www.postgresql.org/docs/current/static/sql-createdatabase.html

2. Each SQL statement needs to be terminated with a ;. As you didn't do that, your statement wasn't executed and thus you didn't get an error. For details see the manual: http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html

postgres=# createdb test;
ERROR:  syntax error at or near "createdb"
LINE 1: createdb test;
        ^
postgres=# create database test;
CREATE DATABASE
postgres=# \list
                                          List of databases
   Name    |  Owner   | Encoding |       Collate       |        Ctype        |   Access privileges
-----------+----------+----------+---------------------+---------------------+-----------------------
 postgres  | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | 
 template0 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | =c/postgres          +
           |          |          |                     |                     | postgres=CTc/postgres
 template1 | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 | postgres=CTc/postgres+
           |          |          |                     |                     | =c/postgres
 test      | postgres | UTF8     | German_Germany.1252 | German_Germany.1252 |
 10 rows)


postgres=#

Answer 2

createdb is a shell command not the postgres command that's why it does not show any error or success message.

To create database from your terminal you have to do this.

sudo -u postgres createdb databaseName

To create database from postgres command you have to run the following command one after another

sudo -u postgres psql
CREATE DATABASE testDB;

If database create successfully then you will get a success message by "CREATE DATABASE"

To see the list of your database write this command

\l