Home > Postgresql - SQL > postgresql show tables, views, index, sequences

postgresql show tables, views, index, sequences

There are some alternatives to show tables in postgresql. 1) using information_schema.tables To show all tables in Database it’s possible to use :

SELECT *
 FROM information_schema.tables
 WHERE table_schema='public' and table_type='BASE TABLE'

to check if table exist

SELECT *
 FROM information_schema.tables
 WHERE table_schema='public' and table_type='BASE TABLE' and table_name='products'

2) using pg_catalog.pg_tables

SELECT * FROM pg_catalog.pg_tables

e fitrare

SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public'

3) a bit complex but show many objects. it’s possible to show using pg_class      tables, views, sequences, index, special :

  • table = WHERE c.relkind IN (‘r‘,”)
  • sequences = WHERE c.relkind IN (S‘,”)
  • view = WHERE c.relkind IN (‘v‘,”)
  • index = WHERE c.relkind IN (‘i‘,”)
  • special = WHERE c.relkind IN (s‘,”)
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','')
  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;

4) draft using pg_class

SELECT relname FROM  pg_class WHERE relname ='r'  ;

Resources:

Risorse:

Postgresql 

Plpgsql 

Creare eliminare database e tabelle

Advertisements
  1. Non c'è ancora nessun commento.
  1. No trackbacks yet.

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: