Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    8

    Unanswered: Newbie in postgre

    Hi all, I already have a new user 'am' and new database 'am' with table 'y'. After that I log in using psql -U am and then create new database again 'amDB'. Then I create table 'x' without specifying the ownership/database. I view using pgAdmin and see that I now have 2 database 'am' and 'amDB'. Both table 'y' and 'x' is under earlier database 'am'. Why table 'x' choose database 'am' and not 'amDB'? I don't specify ownership on it. Is it because the user and database have same name?
    Last edited by hanugro; 12-04-07 at 22:44.

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    ok, I think this will need more diagnosis, but I will take a guess at what happened.

    you have 2 dbs already in the system when it is created. template & template1. did you create table y in one of the templates. Or if not that, then did you use 'am' as a template when creating 'amDB'? (what is the command you used to create 'amDB'?)

    If you created a table in one of the templates then when the system makes a new table it will follow the template. I am guessing that you created table 'y' and it got copied onto the new db while you explicitly made table 'x' in 'amDB'.

    when you log in using 'psql -U am', what db are you logging into?
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    And, in a related issue, once you start using schemas, you will find that unless you specify otherwise, tables are created in the public schema, and unless you explicitly specify the schema name, objects not located in the search path are not found.

    To see the curent search path, or to alter it, use the SHOW search_path and SET Search_Path commands, respectively. (ref http://www.postgresql.org/docs/8.2/s...l-schemas.html)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Nov 2007
    Posts
    8
    Thanks for answering. I guess this is issue where I still don't understand. When we log using 'psql -U am' we are actually logging with user 'am' right? Do we still have to specify to which database and how to do that? I think under oracle when we log in as user we can use any database we own.

    Also this public schema, what is it? Is it a role? I always think schema is linked with user. I intend to create other users that will be given specific right to use the database (but not able to drop table/database). With that in mind, do I need to create table in other schema than public?

    Well when I create database 'amDB', I think I am not specifying to use specific template (while when creating database 'am' earlier, I specifically use template1). Isn't this a default template to use even though you do not specify it? Well to clarify both table 'x' and 'y' is under database 'am'. Database 'amDB' do NOT have table in it. That is why I asked why it automatically choose database'am'. Is it because I log in using psql -U am.

    One more thing. I first created user am in pgAdminIII after I got database running. Then I try to use psql -U am but I think it rejected this. Not until I create database 'am' that psql -U am let me into the system.
    Last edited by hanugro; 12-05-07 at 22:06.

  5. #5
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    you should look into the psql command.

    with 'psql -U am,' that will log you in with the user 'am,' but it will also try to log you into the database with the same name. If no database is specified it will assume the username is the database name you want to connect to.

    you can leave your schema as public (don't have to worry about it) if you want. users will automatically have public access. A schema is a bit like a namespace and you can control access to items in the database with different schemas.

    if you do not specify a template then template1 is used.

    yes, you log into 'am' because you are typing 'psql -U am.'

    I am pretty confused by your last sentence. pgAdminIII shouldn't really matter in all this.. it is an external app that you can admin the db from, but it still relies on how the db functions. When you say 'it rejected this,' I assume you are talking about trying to connect with pgAdmin. If everything connect info/user/db isn't set up correctly then pgAdmin isn't going to be able to connect. It sounds like you might be running info permission problems which means you might not have your pg_hba file configured correctly.

    At this point I would recommend going through the pg manual. I think it is one of the most well written manuals I have ever seen. It seems like you are just running into basic problems that a look through the beginning will help with.

    I am sure people can help you with specific issues, but you should post errors that you get and as much detail about what you tried when you post. I am glad you are trying out postgres.. I think its a great system. Hopefully this has helped a little!
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I can also highly recommend the second edition of PostgreSQL, by Douglas & Douglas (Sams). It's very well written, covers pg through v8, and leads you down the path of understanding postgresql quite well.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Nov 2007
    Posts
    8
    Thanks. That explain why the table get into 'am' database. I will try to read the manual and some book thoroughly. I have downloaded the online book but I just browse it.

    Well, my last paragraph is that when I run psql -U am it say something does not exist (I forget the exact wording). I created user am before with pgAdmin (with user postgre that is created when I install the database). I haven't created any database yet at this point. After psql -U am reject my login, I then created the database 'am' using pgAdmin then I run psql -U am again and this time it is OK. That make me conclude (at that time) that you need to have both user and database with the same name? Is that the case?

    Here is the time line:

    Install postgre
    Use pgAdmin login as postgre browse around and then created user am with it
    Try login using psql -U am with command prompt but it won't let me
    Use pgAdmin to create database am
    Try psql -U am again and it work.
    Last edited by hanugro; 12-06-07 at 20:08.

  8. #8
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    users and databases are completely separate ideas. Postgres calls users roles really.

    http://www.postgresql.org/docs/8.2/i...ser-manag.html

    You should just try connecting with psql maybe.. it sounds like pgAdmin might be confusing things for you I am not saying do not use pgAdmin, but just use it after it makes sense how to log in.

    from the timeline it seems that you are not specifying the database to connect to. I imagine you got an error like: FATAL: database "am" does not exist.
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  9. #9
    Join Date
    Nov 2007
    Posts
    8
    Well, this is new to me. However I am a bit confused why it won't let me enter without creating database first? So we need to log in by specifying database name. But why psql -U am don't try to login to database that created during installation (postgres)?

    I guess I have to read this manual carefully. Thanks again.
    Last edited by hanugro; 12-07-07 at 23:27.

  10. #10
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    if you do a new install and then try: psql -U am, then it will try to connect to the am database because you didn't specify one and that is the user you are using. am will not exist and you will get an error.

    good luck!
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

Posting Permissions

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