0

We are able to SELECT, INSERT, and DELETE records without an issue for table LOG.TABLE_NAME1

I need to update the DDL but surprisingly cannot seem to find details on this table anywhere except in SVV_TABLE_INFO. Is there any other way to get the DDL for this table? (I can recreate this table, but would like to understand what is the root-cause of this bizarre observation)

  1. We can view and modify the underlying data.

    SELECT count(1) FROM database_name1.LOG.TABLE_NAME1; --100K+ records`
    
  2. Can't seem to find it in the below tables:

    SELECT * 
    FROM information_schema.tables 
    WHERE UPPER("table_name") = 'TABLE_NAME1'; -- No result`
    
    SELECT * 
    FROM information_schema.columns 
    WHERE UPPER("table_name") = 'TABLE_NAME1'; -- No result
    
    SELECT * 
    FROM pg_table_def 
    WHERE UPPER("tablename") = 'TABLE_NAME1'; -- No result
    
  3. Finally found it in:

    SELECT database, schema, table_id, table 
    FROM SVV_TABLE_INFO WHERE UPPER("table") = 'TABLE_NAME1'; -- FOUND!!!
    
    database_name1 | log | 1742555 | table_name1
    
  4. No entry below:

    SELECT * 
    FROM pg_catalog.systable_globaldict 
    WHERE basetable_id = 1742555; -- No result
    

Notes

select VERSION() 
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.5290
  • 1
    Please do not tag Redshift questions with postgresql. Although they share common roots they are very different e.g. there is no pg_table_def or SVV_TABLE_INFO or systable_globaldict in Postgres – a_horse_with_no_name Dec 18 '18 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.