List All Databases and Tables Using PostgreSQL

Wikitechy | 1539 Views | sql server | 07 Jun 2016

 

  • PostgreSQL also called as Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. 
  • The primary functions are to store data securely and effective retrieval at the request of other software applications. 
  • It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
  • PostgreSQL is cross-platform and runs on many operating systems including 
  1. Linux, 
  2. FreeBSD, 
  3. OS X, 
  4. Solaris, and 
  5. Microsoft Windows.
  • PostgreSQL provides the concurrency management through a system known as multiversion concurrency control (MVCC), which gives each transaction a "snapshot" of the database, allowing changes to be made without being visible to other transactions until the changes are committed.
  • This largely eliminates the read locks on the data, and ensures the database to maintain the ACID (atomicity, consistency, isolation, durability) properties.
  • PostgreSQL offers three levels of transaction isolation:
  1. Read Committed, 
  2. Repeatable Read and 
  3. Serializable

Option 1 :

  • Now am trying to show how the  list all databases and tables using psql  (PostgreSQL)
    To list all the databases in the server, use the command 

            \list or \l

    To list all the tables in current database, use the command 

        \dt

(Note: Before executing the above statement it’s advisable To execute the below query alter database <databasename> set search_path=data, public;The reason is, the tables inserted cannot be amended to the schema. Here is the technique to add the table into the schema)

  • You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects).
To switch databases :
   
                \connect database_name

Option 2 :

To list down the database is by executing the query

SELECT datname FROM pg_database;

(or)

SELECT datname FROM pg_database
WHERE datistemplate = false;

  • Login as pg_admin and execute the below query will provide we can list out the tables in the current database
(or)
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;

(or)

SELECT * 
FROM information_schema.tables 
WHERE table_type = 'BASE TABLE' 
    AND table_schema = 'public' 
ORDER BY table_type, table_name

Applies to sqlserver version : 

  • SQL Server 1.1 (16 bit)
  • SQL Server 4.2A (16 bit)
  • SQL Server 4.2B (16 bit)
  • SQL Server 4.21a
  • SQL Server 6.0
  • SQL Server 6.5
  • SQL Server 7.0
  • SQL Server 7.0 OLAP Tools
  • SQL Server 2000
  • SQL Server 2000 64-bit Edition
  • SQL Server 2005
  • SQL Server 2008
  • Azure SQL DB
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016


Workshop

IOT Hackathon
Webinar

Join our Community

Advertise