Show table structure and list of tables in PostgreSQL

SHOW TABLES and DESCRIBE TABLE are MySQL-specific admin commands, and nothing to do with standard SQL.

You want the:

\d

and

\d+ tablename

commands from psql.

These are implemented client-side. I find this odd myself, and would love to move them server-side as built-in SQL commands one day.

Other clients provide other ways to browse the structure - for example, PgAdmin-III.

If you want a portable way to get table structure in code, you should use the information_schema views, which are SQL-standard. See information_schema. They're available in MySQL, PostgreSQL, Ms-SQL, and most other DBs. The downside is that they're fiddlier to use, so they aren't convenient for quick access when you're just browsing a DB structure.

As per the Documentation

SELECT
    table_schema || '.' || table_name as show_tables
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

for more convenience make it as a function

create or replace function show_tables() returns SETOF text as $$
SELECT
    table_schema || '.' || table_name as show_tables
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');
$$
language sql; 

So we can get the tables using

select show_tables()

---

For the table description

 select column_name, data_type, character_maximum_length
 from INFORMATION_SCHEMA.COLUMNS where table_name ='table_name';

as a Function

create or replace function describe_table(tbl_name text) returns table(column_name   
varchar, data_type varchar,character_maximum_length int) as $$
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = $1;
$$
language 'sql';

select  *  from describe_table('a_table_name');