CODEWITHSUNDEEP
amazon-redshift
Raphi
Raphi

Reputation: 420

SET/RESET command in ALTER DATABASE is not supported

Encountered this issue when trying to modify the search_path to my new Redshift db.

Presently, I've migrated the contents of my MySQL db into a redshift cluster via AWS' Data Migration Service. The data was imported into a schema lets call my_schema. When I try to execute queries against the cluster it requires me to prefix table names with the schema name

i.e.

select * from my_schema.my_table

I wanted to change the setup so that I can reference the table directly without needing the prefix. After a bit of looking around I found out that this was possible by modifying the search_path attribute.

First I tried doing this by running

set search_path = "$user", my_schema;

This appeared to work but then I realized that this was simply setting my_schema as the default schema in the context of the current session, I wanted it set on a database level. I found several sources saying that the way to do this was to use the alter command like so...

alter database my_db set search_path = "$user", public, my_schema

However, running this command results in the following error which somehow shows up in 0 google results:

SET/RESET commmand in ALTER DATABASE is not supported

I'm pretty baffled by how the above error hasn't ever had a post made about it but I'm also pretty interested in figuring out how to resolve my initial issue of setting a global default schema for my redshift cluster.

Upvotes: 3

Views: 1195

Answers (1)

hadooper
hadooper

Reputation: 746

ALTER DATABASE SET is not supported in Redshift. However you can SET/RESET configuration parameters at USER level using the ALTER USER SET SEARCH_PATH TO <SCHEMA1>,<SCHMEA2>;

Please check: http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_USER.html http://docs.aws.amazon.com/redshift/latest/dg/r_search_path.html

When you set the search_path to <SCHEMA1>,<SCHMEA2> in db1 for a user it is not for just current session, it will be set for all future sessions.

Upvotes: 2

Related Questions