Is schema name compulsory to prefix the table??

Moderator: NorbertKrupa

Post Reply
rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Is schema name compulsory to prefix the table??

Post by rajasekhart » Wed Aug 07, 2013 6:22 am

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!!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Is schema name compulsory to prefix the table??

Post by nnani » Wed Aug 07, 2013 7:51 am

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

Code: Select all

test=# SHOW search_path;
 search_path
--------------
 $user,public
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

Code: Select all

test=# SHOW search_path;
 search_path
--------------
 $user,public

test=# SET search_path TO foo;
SET
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

Code: Select all

ALTER USER test SET search_path TO foo;
Now whenever you query table without any suffix the default is schema foo.

Hope this helps..
nnani........
Long way to go

You can check out my blogs at vertica-howto

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: Is schema name compulsory to prefix the table??

Post by rajasekhart » Wed Aug 07, 2013 9:03 am

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"!! :o

Thanks,
Raj!!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Is schema name compulsory to prefix the table??

Post by JimKnicely » Wed Aug 07, 2013 2:46 pm

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
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Is schema name compulsory to prefix the table??

Post by nnani » Wed Aug 07, 2013 3:27 pm

Hello Raj,

Try this

Code: Select all

alter user raj search_path foo;
After issuing this command, try connecting with Raj and check the default search_path.
This should solve your problem.


Hope this helps..
nnani........
Long way to go

You can check out my blogs at vertica-howto

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: Is schema name compulsory to prefix the table??

Post by rajasekhart » Thu Aug 08, 2013 7:33 am

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!!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

Post Reply

Return to “Vertica Database Administration”