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!!
Is schema name compulsory to prefix the table??
Moderator: NorbertKrupa
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Is schema name compulsory to prefix the table??
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
Re: Is schema name compulsory to prefix the table??
Hello rajasekhart,
The public schema is the default schema for Vertica.
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.
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..
The public schema is the default schema for Vertica.
It considers the schema as public, as it is the default.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 ??
To check what is the default schema for the session use this command
Code: Select all
test=# SHOW search_path;
search_path
--------------
$user,public
If you want to change the search_path for session use this command
Code: Select all
test=# SHOW search_path;
search_path
--------------
$user,public
test=# SET search_path TO foo;
SET
It will search in public schema only.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???
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
Code: Select all
ALTER USER test SET search_path TO foo;
Hope this helps..
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Is schema name compulsory to prefix the table??
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!!
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!!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Is schema name compulsory to prefix the table??
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
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Is schema name compulsory to prefix the table??
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..
Try this
Code: Select all
alter user raj search_path foo;
This should solve your problem.
Hope this helps..
-
- Intermediate
- Posts: 87
- Joined: Wed Apr 25, 2012 8:10 am
Re: Is schema name compulsory to prefix the table??
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!!
Yes.. im using vertica version 6.0.1.
So, it is not allowing me to do that task.
Thanks a lot ..
Regards,
Raj!!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1