In previous articles we have learned about basics of PostgreSQL, architecture and installation of PostgreSQL on windows
(Read here Introduction to PostgreSQL , Installation of PostgreSQL )
Now we will learn basic commands to administrates the PostgreSQL databases
Login to PostgreSQL
we can login to postgres database using psql with few options like
database name and postgres user, below we logged in to tech_pg database using user postgreu
tech_pg=#
Listing the database available
A single PostgreSQL server instance can have multiple databases, each and every database is stored as separate file under its own directory, to list database we can use \l or we can use pg_database table
Checking Current Database
tech_pg=#
Listing the Tables
one can simply list the tables created under specific database using \dt command, in Below example we can see there is no table present in database, if any present then it will list out with details.
Comment for feedback or in case need additional information, share the post if you like it.
(Read here Introduction to PostgreSQL , Installation of PostgreSQL )
Now we will learn basic commands to administrates the PostgreSQL databases
Login to PostgreSQL
we can login to postgres database using psql with few options like
database name and postgres user, below we logged in to tech_pg database using user postgreu
$ psql -d tech_pg -U postgreu
psql.bin
(9.6.2.7)
Type
"help" for help.
tech_pg=#
Listing the database available
A single PostgreSQL server instance can have multiple databases, each and every database is stored as separate file under its own directory, to list database we can use \l or we can use pg_database table
tech_pg=# \l
List of databases
Name | Owner |
Encoding | Collate | Ctype |
ICU | Access privileges
--------------+------------+----------+-------------+-------------+-----+----------------------------------
tech_pg | postgreu | UTF8 | en_US.UTF-8
| en_US.UTF-8 | |
postgres
| postgreu | UTF8 | en_US.UTF-8 | en_US.UTF-8
| |
template0 | postgreu | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| =c/postgreu
+
|
| |
| |
| postgreu=CTc/postgreu
template1
| postgreu | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
| =c/ postgreu
+
|
| |
| |
| postgreu=CTc/postgreu
(4
rows)
tech_pg=#
tech_pg=#
select datname from pg_database;
datname
--------------
postgres
tech_pg
template1
template0
(4
rows)
tech_pg=#
Connect to database (Switch Database)
By default PostgreSQL server instance has 3 databases i.e. postgres, template1 and template0
one can connect to database from postgres command line tool and switch to other database using \connect or \c
tech_pg=# \c postgres
You
are now connected to database "postgres" as user "postgreu".
postgres=#
postgres=#
\connect tech_pg
You
are now connected to database "tech_pg" as user "postgreu".
tech_pg=#Checking Current Database
We can check the name of the database in which we are currently logged in using different methods, i.e. using \c, \conninfo, select * from current_catalog and current_database() function. it has been illustrated below
tech_pg-# \c tech_pg
You
are now connected to database "tech_pg" as user "postgreu".
tech_pg-#
tech_pg=#
\conninfo
You
are connected to database "tech_pg" as user "postgreu" via
socket in "/tmp" at port "5444".
tech_pg=#
tech_pg=#
\c
You
are now connected to database "tech_pg" as user "postgreu".
tech_pg=#
tech_pg=#
select * from current_catalog;
current_database
------------------
tech_pg
(1
row)
tech_pg=#
select current_database() ;
current_database
------------------
tech_pg
(1
row)
Listing the Tables
one can simply list the tables created under specific database using \dt command, in Below example we can see there is no table present in database, if any present then it will list out with details.
tech_pg=# select current_database() ;
current_database
------------------
tech_pg
(1
row)
tech_pg=#
\dt
No
relations found.
tech_pg=#
Get list of all Schema
using command \dn, one can simply list out all the schema in postgres
using command \dn, one can simply list out all the schema in postgres
tech_pg=#
\dn
List of schemas
Name |
Owner
--------------------+------------
postgreu
| postgreu
dbms_job_procedure | postgreu
pgagent | postgreu
public | postgreu
(4 rows)
One can use information_schema.schemata view to list out all the schemas in current database
tech_pg=#
select catalog_name,schema_name,schema_owner from information_schema.schemata;
catalog_name| schema_name | schema_owner
-------------+--------------------+--------------
tech_pg | pg_toast | postgreu
tech_pg | pg_temp_1 | postgreu
tech_pg | pg_toast_temp_1 | postgreu
tech_pg | pg_catalog | postgreu
tech_pg | public | postgreu
tech_pg | information_schema | postgreu
tech_pg | sys | postgreu
tech_pg | dbo | postgreu
(8 rows)
Comment for feedback or in case need additional information, share the post if you like it.
0 comments:
Post a Comment