dbExistsTable fails on Amazon Redshift

[ilyaminati]

dbExistsTable on Redshift returns:

Error in result_create(conn@ptr, statement) :
Failed to prepare query: ERROR: function generate_subscripts(name[], integer) does not exist
HINT: No function matches the given name and argument types. You may need to add explicit type casts.

This is likely because generate_subscripts is unsupported on Redshift.

[krlmlr]

Thanks. I'm not familiar with RedShift, can you suggest an alternative query?

RPostgres/R/tables.R

Lines 269 to 276 in e09b014

	query <- paste0(
	"(SELECT nr, schemas[nr] AS table_schema FROM ",
	"(SELECT *, generate_subscripts(schemas, 1) AS nr FROM ",
	"(SELECT current_schemas(true) AS schemas) ",
	"t) ",
	"tt WHERE schemas[nr] <> 'pg_catalog') ",
	"ttt"
	)

[ilyaminati]

Sorry, I no longer have access to a Redshift instance, I'm no help here. But what's the reason for doing such a search in the first place? For dbExistsTable, is it not sufficient to just query against PG_TABLE_DEF?

[krlmlr]

Sure, but will this work for Postgres?

[krlmlr]

We don't currently support Redshift very well. Closing in favor of #215.

[github-actions]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.