CODEWITHSUNDEEP
postgresql
Guanhuan
Guanhuan

Reputation: 408

List all databases in PostgreSQL when there isn't a db with same name of the current user's

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?

Upvotes: 4

Views: 3542

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23361

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.

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95552

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

Upvotes: 3

Related Questions