Show tables, describe tables equivalent in redshift

Solution 1:

All the information can be found in a PG_TABLE_DEF table, documentation.

Listing all tables in a public schema (default) - show tables equivalent:

SELECT DISTINCT tablename
FROM pg_table_def
WHERE schemaname = 'public'
ORDER BY tablename;

Description of all the columns from a table called table_name - describe table equivalent:

SELECT *
FROM pg_table_def
WHERE tablename = 'table_name'
AND schemaname = 'public';

Update:

As pointed by @Kishan Pandey 's answer, if you are looking for details of a schema different by public, you need to set search_path to my_schema. (show search_path display current search path)

Listing tables in my_schema schema:

set search_path to my_schema;
select * from pg_table_def;

Solution 2:

I had to select from the information schema to get details of my tables and columns; in case it helps anyone:

SELECT * FROM information_schema.tables
WHERE table_schema = 'myschema'; 

SELECT * FROM information_schema.columns
WHERE table_schema = 'myschema' AND table_name = 'mytable';