2

I received a list of column names from another team. To ensure all field column names are valid inside a specific schema, I put them into a temporary table. I'm now wanting to see if they exist. When I try to join with information_schema.columns, I received errors below: 

ERROR: 0A000: Specified types or functions (one per INFO message) not supported on Redshift tables.
Column "c.column_name" has unsupported type     "information_schema.sql_identifier".
Column "a.*" has unsupported type "pg_attribute".
Column "t.*" has unsupported type "pg_type".
Function "format_type(oid,integer)" not supported.
Function "format_type(oid,integer)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.
Function "has_table_privilege(oid,text)" not supported.

I tried to cast column_name into different types, but had no luck. Would anyone please advise what's wrong and how I can achieve this goal?

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
Lee
  • 205
  • 2
  • 3
  • 12

1 Answers1

5

PG_TABLE_DEF

Amazon considers the internal functions that INFORMATION_SCHEMA.COLUMNS is using Leader-Node Only functions. Rather than being sensible and redefining the standardized INFORMATION_SCHEMA.COLUMNS, Amazon sought to define their own proprietary version. For that they made available another function PG_TABLE_DEF which seems to address the same need. Pay attention to the note in the center about adding the schema to search_path.

Stores information about table columns.

PG_TABLE_DEF only returns information about tables that are visible to the user. If PG_TABLE_DEF does not return the expected results, verify that the search_path parameter is set correctly to include the relevant schemas.

You can use SVV_TABLE_INFO to view more comprehensive information about a table, including data distribution skew, key distribution skew, table size, and statistics.

So using your example code (rewritten to use NOT EXISTS for clarity),

SET SEARCH_PATH to '$user', 'public', 'target_schema';

SELECT "column" 
FROM dev.fields f
WHERE NOT EXISTS (
  SELECT 1
  FROM PG_TABLE_DEF pgtd
  WHERE pgtd.column = f.field
  AND schemaname = 'target_schema'
);

See also,

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507