CODEWITHSUNDEEP
postgresqlpsql
zabouti
zabouti

Reputation: 639

How to use the psql command to list, create, use and examine databases?

I'm a postgreSQL newbie and I can't find any usable introduction to using the psql command. At least I think that's the command I want to use.

Is it possible in postgreSQL to simply connect to the server and then list, create, use and examine databases?

I'd like to be able to use psql to do something like this with MySQL (I've deleted many extra lines):

Connect without specifying a database - I can't seem to do that with psql:

$ mysql -u root -prootpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Server version: 5.5.28 MySQL Community Server (GPL)

I can list databases with mysql but the posgreSQL command SHOW doesn't seem to do it.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ocdp               |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.04 sec)

I can switch databases. 

mysql> use ocdp;
Database changed

I can't figure out this command in psql:

mysql> show tables;
+---------------------------------+
| Tables_in_ocdp                  |
+---------------------------------+
| OCAddresses                     |
| OCStreets                       |
+---------------------------------+
2 rows in set (0.00 sec)

I think I can do this in psql with 'createdb' and 'dropdb' commands:

mysql> create database foo;
Query OK, 1 row affected (0.00 sec)

mysql> drop database foo;
Query OK, 0 rows affected (0.03 sec)

I use \quit

mysql> quit
Bye

The answer to these questions should take only a moment for someone who knows postgreSQL but I just can't find documentation anywhere that shows how to do these simple operations. Maybe I shouldn't even be using psql at all for this?

Upvotes: 25

Views: 29519

Answers (4)

Dinesh Pallapa
Dinesh Pallapa

Reputation: 1212

Postgresql with terminal

enter into the postgresql 

   sudo -u postgres psql

find list Of databases in postgresql by

   $ \l

connect to database by following command

   $ \c databasename

view models in database by 

   $ \dt

Now list of table present in database are displayed and perform operations on the tables like 

   $ select * from table;

Upvotes: 2

SeanPlusPlus
SeanPlusPlus

Reputation: 9053

connect to server:

$ mysql -u root -prootpassword

$ su - postgres
$ psql

list databases:

mysql> show databases;

postgres=# \l

switch databases:

mysql> use ocdp;

postgres=# \c ocdp

show tables:

mysql> show tables;

postgres=# \dt

create database:

mysql> create database foo;

postgres=# create database foo;

drop database:

mysql> drop database foo;

postgres=# drop database foo;

quit:

mysql> quit

postgres=# \q

Upvotes: 51

msmukesh4
msmukesh4

Reputation: 589

Try Using this command if you are using Mac-OS-X and for postgres 9.4

psql --list

Upvotes: 3

user330315
user330315

Reputation:

Connect without specifying a database - I can't seem to do that with psql:

Quote from the manual

The default user name is your Unix user name, as is the default database name

I can switch databases

Quote from the manual

\connect [ dbname [ username ] [ host ] [ port ] ] Establishes a new connection to a PostgreSQL server

show tables

Quote from the manual

\d[S+] [ pattern ] For each relation (table, view, index, sequence, or foreign table) or composite type matching the pattern, show all columns

create database foo;

This is the same statement in PostgreSQL, which is documented in the manual

quit

Quote from the manual

\q or \quit Quits the psql program.

Upvotes: 2

Related Questions