When no schema name is provided while creating a table, Redshift relies on 'search_path' to retrieve the default one available. By default 'search_path' value is
Here $user corresponds to the SESSION_USER. If it finds a schema name with the same name as session user, the table is created here. If redshift didn't find a schema, it looks for the next schema name in the list which is 'public'.
If none of the names listed in 'search_path' has a corresponding schema, then create table operation will fail with error "no schema has been selected to create in".
To confirm the table creation is successful, verify the schema name provided from python is referenced in 'search_path'.
-- SQL script to view contents of search_path. If the schema mentioned in the python script isn't listed, then you need to reference it.
-- SQL script to include a schema to search_path. Note: this overwrites existing schema names in the list,
set search_path to replace_with_schema_name;
-- In order to append a new entry to existing search_path. Assuming the existing default values are '$user',public
set search_path to '$user',public, replace_with_schema_name
You can find more on search_path here https://docs.aws.amazon.com/redshift/latest/dg/r_search_path.html
The other possibilities could be
Can you confirm if commit() is called after the table creation.
Ensure you are looking at the right aws region, cluster and database name the table was created under.
Make sure the logged in user has necessary permissions to view the table.
Querying the system table, will return rows referencing schema name, if the table creation was successful. Please ensure the 'search_path' contains the schema name, referenced in your python code, else no records will be returned.
SELECT * FROM SVV_TABLE_INFO where "table" like '%replace_with_your_table_name%';
SELECT * FROM PG_TABLE_DEF where tablename like '%replace_with_your_table_name%';
You can find more details on Create table syntax here https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html
Redshift Automatic Table Optimization and table swapsAccepted Answerasked 2 years ago
Cannot create table with Geojson schemaasked 13 days ago
Redshift serverless: error while trying to create an external tableasked 3 months ago
Partition schema mismatch in Glue Tableasked 4 months ago
python redshift_connector executes create table statement with out errors but table not created in redshift clusterasked 6 months ago
Migrating partitioned table from postgres to Redshift with pglogicalasked 10 days ago
Is there a way to create a Redshift Table from a Glue table's schema?Accepted Answerasked 5 months ago
Redshift Insert into s3 file data into existing tableasked 5 months ago
Restoring Redshift Schema (only schema no data)form Prod cluster to non-prod clusterasked 7 months ago
use Redshift Spectrum to query both Redshift table and s3 fileAccepted Answerasked 2 years ago