Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2017
    Posts
    12

    Unanswered: how to get rid of schema prefix of table

    Hi,

    I installed postres in my system and created a sample schema and tables from pgAdmin GUI. Everytime I query the table, I need to prefix schema name from query window of pgAdmin. Like, select * from anand."EMP"
    Is there a way to query the table with just the table name? Why mentioning of table name within double quotes is needed? Can I not execute just -

    SELECT * FROM EMP;

    Above command fails. Even inside the function, I need to have fully qualified name like anand."EMP"

    Please clarify.

    Thanks,
    -Anand

  2. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Change your search_path.

    Either just for the session:
    Code:
    set search_path = anand,public;
    or permanently by changing that for your user:
    Code:
    alter user current_user set search_path to 'anand, public';
    Don't forget to commit that if you disabled auto-commit.

    If you want to get rid of the necessity to specify those dreaded double quotes, you have to rename to table in order to make the name not case sensitive.

    Quoted identifiers are case sensitive in SQL, so "EMP" is a different table name then "emp".

    In contrast to the SQL standard, Postgres folds unquoted identifiers to lowercase so CREATE TABLE EMP (...) actually creates a table with the name "emp".

    More details can be found in the manual: https://www.postgresql.org/docs/curr...AX-IDENTIFIERS

    So to make your table not case sensitive rename it to lower case:

    Code:
    alter table "EMP" rename to emp;
    Again: don't forget to commit that if you turned off auto-commit.

    In general I highly recommend to simply never use double quotes in SQL.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •