Postgresql not creating db with “createdb” as superuser, yet not outputting errors

Question

I am working with a fresh postgresql install, with 'postgres' super user. Logged in via:

sudo -u postgres psql


postgres=# createdb database
postgres-# \list
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | 
 template0 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres

No errors, yet table is not being created. Any ideas?

Closely related to http://stackoverflow.com/questions/8537576/what-is-wrong-with-this-postgresql-query — Craig Ringer, Nov 12 '12 at 02:18

score 331 · Accepted Answer · edited Dec 14 '16 at 02:48

331

createdb is a command line utility which you can run from bash and not from psql. To create a database from psql, use the create database statement like so:

create database [databasename];

Note: be sure to always end your SQL statements with ;

edited Dec 14 '16 at 02:48

iwasrobbed

46,496
21
150
195

answered Nov 10 '12 at 09:59

Saddam Abu Ghaida

6,381
2
22
29

score 115 · Answer 2 · answered Dec 08 '15 at 15:19

115

Late to the party, but the accepted answer doesn't explain why no error is displayed. And as this is something Postgres newcomers often stumble upon, I wanted to add that.

TL/TR: always end your SQL statements with ;

Because the createdb database did not end with ; psql thinks the statement isn't finished and waits for more input. This is indicated by the prompt changing from postgres=# to postgres-#. An extremely subtle change that I wish psql would do differently (more "prominent").

By entering the meta-command \list the "current" SQL statement is "aborted" without executing it.

If the createdb had been ended with a ; the output would have been:

postgres=> createdb foobar;
ERROR:  syntax error at or near "createdb"
LINE 1: createdb foobar;
        ^
postgres=>

Clearly showing that something was wrong.

answered Dec 08 '15 at 15:19

6

Wow, this is indeed subtle but deadly. I was having this issue for quite some time. The first time you run the command with a semicolon you'll get the error because `createdb` isn't valid. But then repeating the exact same command with `create database` instead of `createdb` along with a semicolon works perfectly. – Glen Selle Mar 19 '16 at 13:28
1

@Helsing: that's what I wrote, I just explained why there wasn't an error message even though it is invalid – Dec 07 '18 at 18:01
@a_horse_with_no_name Yea, I misunderstood your purpose. Upvoted your answer as well. – Noel Dec 07 '18 at 18:10

score 8 · Answer 3 · edited Jul 04 '20 at 21:04

8

Using a node terminal, I had to run:

psql -U postgres

[enter your password]

then ...

CREATE DATABASE dbadmin;

What is confusing is that I entered these same commands before and it didn't work. Only after logging out and logging back in, was I able to use this standard command from the documentation: https://www.postgresql.org/docs/10/tutorial-createdb.html

edited Jul 04 '20 at 21:04

David Buck

3,752
35
31
35

answered Jul 04 '20 at 20:33

EndePointe

91
1
2

same for me, had to log out and log back in to make it work – Gerardo Tarragona Jun 23 '21 at 22:46

score 5 · Answer 4 · answered Jan 05 '19 at 01:05

5

I was in this situation not long ago. In case someone else experiences this, considering that the command prompt shows postgres-# you can execute the pending createdb command by simply typing ; and the return key.

answered Jan 05 '19 at 01:05

Fredrick Mgbeoma

71
1
4

score 4 · Answer 5 · answered Sep 28 '19 at 16:45

4

Create new data base in PostgreSQL is very simple, execute this command on Linux (CentOS 7 example):

sudo -u postgres psql -c "create database MyDb;"

answered Sep 28 '19 at 16:45

Avtandil Kavrelishvili

1,651
3
27
37

Postgresql not creating db with “createdb” as superuser, yet not outputting errors

5 Answers5

Linked

Related