How do you connect to a recently create postgres database?

Question

I am just learning sql and starting with PostgreSQL. Here is what I am trying to do:

psql postgres
CREATE DATABASE newdb;

Then I want to do something like this (in the same psql session)

CONNECT DATABASE newdb;

Of course this doesn't work. But after doing the statement I should be able to do something like:

CREATE TABLE newtable

And "newtable" should appear in the newdb database. I feel like there's something simple that I'm missing.

Answer 1

On the shell you just do \c for connecting:

\c newdb

Answer 2

You are missing something simple: how to use the available documentation. I suggest this because your question is rather basic and PostgreSQL has a breadth of documentation that will be very helpful in the future - it could save you a lot of time.

You have a few options:

1. look at the documentation :)
2. perform a \? from the command line for command help
3. look at the man page entry

Though the following doesn't help with an execution command, like \c or \connect, which is what you need; for other commands that you're questioning the SQL behind, you could set ECHO_HIDDEN to display system queries.
Example:

    psql -E <rest of your db connection>
    -- then do something like "\d"

You'll then see how Postgres is performing the queries:

postgres@ubuntu:/home/fooUser$ psql -E
postgres=# \d

********* QUERY **********
SELECT n.nspname as "Schema",
      c.relname as "Name",
      CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
      pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************