Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL can't create user [duplicate]

Issue: I can create users or databases from the shell (bash, OSX) but not postgres cli. From bash I get no confirmation if successful.

If I try to CREATE ROLE in psql then I get no response and it doesn't generate any error. If I try to createuser from bash then if successful it reports back nothing, if unsuccessful then it does generate the error: "role username already exists".

Example:

Yunti-# CREATE ROLE testuser
Yunti-# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 Yunti     | Superuser, Create role, Create DB, Replication | {}
 anything  |                                                | {}
 monkey    |                                                | {}

Yunti-# CREATE DATABASE testdb
Yunti-# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 Yunti     | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/Yunti         +
           |       |          |             |             | Yunti=CTc/Yunti
 template1 | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/Yunti         +
           |       |          |             |             | Yunti=CTc/Yunti
 test      | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 test5     | Yunti | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(6 rows)

Yunti-# 

A similar thing happens when using createdb.

How can I create users and databases in postgres cli? And is this normal to get no response to most postgres commands in bash?

Info: users and their privileges:

Yunti-# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 Yunti     | Superuser, Create role, Create DB, Replication | {}
 anything  |                                                | {}
 monkey    |                                                | {}
like image 409
Yunti Avatar asked Aug 21 '15 19:08

Yunti


People also ask

How do you fix duplicate key value violates unique constraint?

How to fix PostgreSQL error "duplicate key violates unique constraint" SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1); That will set the sequence to the next available value that's higher than any existing primary key in the sequence.


1 Answers

Your statements are not executed, because you don't terminate them properly using a ;.

Quote from the manual:

A command is composed of a sequence of tokens, terminated by a semicolon (";").

And in the manual for psql:

At the prompt, the user can type in SQL commands. Ordinarily, input lines are sent to the server when a command-terminating semicolon is reached. An end of line does not terminate a command. Thus commands can be spread over several lines for clarity. If the command was sent and executed without error, the results of the command are displayed on the screen.

If you do that, you get an output like this:

psql (9.4.4)
Type "help" for help.

postgres=# CREATE ROLE testuser;
CREATE ROLE  ---<<<< this tells you the statement was executed

postgres=#
like image 124
a_horse_with_no_name Avatar answered Sep 22 '22 18:09

a_horse_with_no_name