The Vertica Database Forums

Connected as dbadmin

While querying a table , if schema name is not prefixed the table, then by default it considers the schema as Public and retrieves the data from Public schema.

Connected as a user X

Lets assume User X has permission on YYYY schema and public schema too.

While querying a table , if schema name is not prefixed the table, then by default,
Q1) Does it considers the schema as public or YYYY ??

But it is clearly mentioned in the Document that , if the schema name is not specified , then by default it considers the schema as PUBLIC.

So, In this case, when i connected with a user other than DBADMIN, who is having permissions on schema YYYY and Public , and if i tried to retrieve the data from a table without prefixing the schema name , from which schema does it retrieve the data???

PS: I gave the same name to SCHEMA_NAME and USER_NAME while creating the schema and user. Can i give like this or not??

Thanks,
Raj!!

Hello rajasekhart,

The public schema is the default schema for Vertica.

While querying a table , if schema name is not prefixed the table, then by default,
Q1) Does it considers the schema as public or YYYY ??

It considers the schema as public, as it is the default.

To check what is the default schema for the session use this command
This shows the default schema is public, that means if you use no schema_name before your table name, it will look into the schema public for your tables.

If you want to change the search_path for session use this command This will create schema foo and set search_path to foo for the current session.

So, In this case, when i connected with a user other than DBADMIN, who is having permissions on schema YYYY and Public , and if i tried to retrieve the data from a table without prefixing the schema name , from which schema does it retrieve the data???

It will search in public schema only.

If you want to search under different schema based on the user, that means when you login with different user, you can set schema for specific user like this
Now whenever you query table without any suffix the default is schema foo.

Hope this helps..

Hi nani,

Thanks for your quick reply..

But when i was tried to fire the Alter user Command , it is throwing an error,
"Alter user command is not supported"!!

Thanks,
Raj!!

Raj,

You must be using an older version of Vertica. I think these links can help you:

viewtopic.php?f=4&t=1216&p=3961
viewtopic.php?f=48&t=924&p=2838
viewtopic.php?f=7&t=10

Hello Raj,

Try this
After issuing this command, try connecting with Raj and check the default search_path.
This should solve your problem.


Hope this helps..

Hi Jim and nani,

Yes.. im using vertica version 6.0.1.
So, it is not allowing me to do that task.

Thanks a lot ..

Regards,
Raj!!