4

First of all, I am new to PostgreSQL.

So am I right thinking that one cannot run most of the psql util commands nor non-db-specified sql commands if there isn't a db with same name of the current user's?

That is saying e.g., if I run psql "show databases;" as user postgres while there isn't a db called "postgres", I won't be able to run the command.

Question is that in this case, one cannot find the list of the dbs before knowing any of db exits, is that how it works?

Improve this question
3
  • You just have to connect to a different database, i.e. specify one instead of relying on the default assumption that the db will be the same as the username. psql -l dbname instead of psql -l for example. Commented Dec 23, 2015 at 12:07
  • Thanks Craig. However my actual question in the last line is that if you don't know the name of any db than you cannot then find the list of the dbs? Commented Dec 23, 2015 at 12:09
  • Correct, but the DBs postgres and template1 basically always exist. If you were really stuck and had somehow lost track of all known DBs you could still recover by stopping Pg and putting it in single user mode. The reason you can't list DBs without knowing one is that authentication is against a specific database, there's no way to say "this user can access the whole system but not necessarily any particular database". Commented Dec 23, 2015 at 12:50

2 Answers 2

Reset to default
3

You have to connect to a database. By default, the databases "template1" and "postgres" will exist and will accept connections.

If your PostgreSQL admin has changed things in such a way that you can't connect to either of those databases, you'll have to do one of two things.

  1. Ask the PostgreSQL admin what database you're supposed to connect to.
  2. Create a database, then connect to it. There's more than one way to do this.

If you have CREATEDB privileges, you can create a database on the psql command line. For example, I have CREATEDB privileges here, so I can do this, which creates the database "mike" and exits.

$ psql -h localhost -p 5435 -U mike -c "create database mike"

Now I can connect to "mike" by either taking advantage of the default database name, or by specifying it.

$ psql -h localhost -p 5435 -U mike    
$ psql -h localhost -p 5435 -U mike mike
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

You can. If you connect (with proper user, usually postgres) to the postgres database there are several tables on the pg_catalog (PostgreSQL) among those is pg_database table a simple select * from pg_database will show all databases.

Here is an image showing that on pgAdmin III Tool enter image description here

There is no way of doing exactly what you want without, at least, knowing the database catalog. The postgres database is default and will exist in all installed instances (unless someone had droped it). All RDBMs is the same they all have the catalog (also named information_schema, or other names depending on vendor) which holds all information about the databases, tables, constraints, etc.

Improve this answer

9 Comments

Thanks for the example but this is still based on of knowing one existing db (in this case postgres which is same name of the user postgres ). Please let me know if my post does not explain my question well.
@Guanhuan I've edited the answer with a explanation.
Thanks Jorge, this makes much more sense now.
The database "postgres" doesn't always exist.
I think the only database(s) you can rely on is template0 and template1. Although template0 is usually configured to not accept connections and template1 can theoretically be dropped as well
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.