How do I connect to PostgreSQL without specifying a database name?

Question

I need to connect to some PostgreSQL server providing some credentials, and print a list of available databases on that host for a given user.

I am trying:

<?php
    $connection = pg_connect("host=localhost user=testuser password=123 connect_timeout=5");
?>

And I get:

Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL server: FATAL: database "testuser" does not exist in /var/www/test.php on line 56

I thought this must be possible because phpPgAdmin does it, but I looked at phpPpAdmin sources and found that they connect to a database named template1.

From http://www.postgresql.org/docs/8.1/interactive/manage-ag-templatedbs.html:

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the "template" from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/pgSQL in template1, it will automatically be available in user databases without any extra action being taken when those databases are made.

Is there a way to connect without specifying any database?

Milen A. Radev · Accepted Answer · 2010-12-19 14:51:16Z

117

You have to connect to a database. Which database you could use for a "maintenance database" depends on the installation and the subsequent administration. After a default installation there are 2 databases that could be used for the initial connection - "template1" and "postgres". It's wise to create a new user and a database with the same name and use those.

answered Dec 19, 2010 at 14:51

Milen A. Radev

63.1k22 gold badges111 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abel Callejo Over a year ago

If you need to create the database, you can use the createdb statement in the command-line. You'll just need to pass your access credentials as parameters.

Ihor Pomaranskyy Over a year ago

BEWARE! It's a bad idea to use template1 for initial connection, because it's supposed to be used as default template for new databases creation. If there are active connections to this database — no one can create new databases.

Fandi Susanto Over a year ago

@IgorPomaranskiy we still can create database with CREATE DATABASE somedb TEMPLATE template0

Victoria Stuart Over a year ago

createdb zzz; psql -d zzz -U victoria; \l; \c <desired db, or create new db in psql shell>. Use dropdb zzz if you later want to delete it, or leave it as a default, "dummy" db for psql connections.

ontrack · Accepted Answer · 2010-12-19 13:46:58Z

22

Why don't you connect to your Maintenance DB (usually postgres?). I don't know if that'll work. But I believe you'll have to query this database anyway to retrieve the databases available to a given user.

answered Dec 19, 2010 at 13:46

ontrack

3,0432 gold badges18 silver badges14 bronze badges

Comments

rik · Accepted Answer · 2010-12-19 13:35:04Z

6

As the manual of pg_connect says the dbname parameter in the connection string defaults to the value of the user parameter. That's why it uses 'testuser'. If you want dbname to be empty, try "... dbname='' ...".

answered Dec 19, 2010 at 13:35

rik

8,6321 gold badge28 silver badges21 bronze badges

2 Comments

Silver Light Over a year ago

if I do so, the error is the same. PostgreSQL still tries to connect to database 'testuser'...

FiftiN Dec 26, 2024 at 20:16

Doesn't work for me

Collectives™ on Stack Overflow

How do I connect to PostgreSQL without specifying a database name?

3 Answers 3

4 Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

2 Comments

Linked

Related