Redshift data-share-write ALTER TABLE rename not working

Thank you for providing details about the issue you're encountering with the ALTER TABLE RENAME command in Amazon Redshift. I can offer some insights and potential solutions to help resolve this problem.

1. Permissions: For executing the ALTER TABLE RENAME command, the user typically needs to have the ALTER or ALL privilege on the table being renamed. Additionally, the user should have the necessary permissions on the schema containing the table.

2. Additional considerations: There are a few factors that could contribute to the error you're seeing:

a) Schema ownership and privileges: Ensure that you have the appropriate schema-level privileges. Sometimes, even if you can see the table, you might not have the necessary permissions to alter it.

b) Search path: The default search path in Redshift might not include the schema where your table is located. This could cause Redshift to look for the table in the wrong place.

c) Data sharing context: Since you mentioned "data-share-write" in your question, it's important to note that while ALTER TABLE RENAME is supported for data sharing with writes, there are some limitations. Make sure you're connected directly to the datashare database or have run the USE command to switch to the correct database context.

3. Case sensitivity and quoting: Redshift is generally case-insensitive for table and schema names unless they are double-quoted. However, it's a good practice to be consistent with your naming conventions. If the actual table name has any special characters or is case-sensitive, you should use double quotes around the table name.

To troubleshoot and resolve the issue:

1. Verify the table exists and you have the correct permissions:

   SELECT * FROM information_schema.tables 
   WHERE table_schema = 'mytestschema' AND table_name = 'tbl1';
   

2. Check your current search path:

   SHOW search_path;
   

   If necessary, set the search path to include your schema:

   SET search_path TO kuhnen_test, mytestschema, public;
   

3. Try using the fully qualified name with quotes:

   ALTER TABLE "kuhnen_test"."mytestschema"."tbl1" RENAME TO "tbl2";
   

4. If you're in a data sharing context, ensure you're connected to the correct database:

   SELECT current_database();
   

   Use the appropriate database if needed:

   USE database_name;
   

If the issue persists after trying these steps, you may want to consult with your database administrator to verify your permissions and the exact configuration of your Redshift environment, especially in the context of data sharing.
Sources
Considerations for data sharing in Amazon Redshift - Amazon Redshift
Editing datashares created in your account in Amazon Redshift - Amazon Redshift

In order to alter a table in Amazon Redshift, the user that alters a table needs the proper privilege for the command to succeed.

For the ALTER TABLE RENAME command, one of the following privileges is required.

1. Superuser
2. Users with the ALTER or ALL privilege on the table being renamed.
3. Table owner with the USAGE privilege on the schema

From the error that you getting, "ERROR: Relation "tbl1" does not exist in the database", it appears that the table could be existing in a separate database and schema, different from the database that you are connected to.

Please confirm that you are connected to database: kuhnen_test and rerun the command.

You can confirm your current database by running :

select current_database();

Resources:

1. https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html
2. https://docs.aws.amazon.com/redshift/latest/dg/r_CURRENT_DATABASE.html