Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2010
    Posts
    9

    Unanswered: New to PostgreSQL couple of questions

    I am trying to write a script for the full creation of a database and all of its objects, similar to the following:

    Code:
    CREATE DATABASE DBName;
    
    \connect DBName;
    
    CREATE TABLE TableOne
    (
        id BIGSERIAL NOT NULL PRIMARY KEY
        , RowDesc VARCHAR(50)
    );
    I'm running into a problem in that there doesn't appear to be a way to change the database connection mid-script. Do I just need to append the newly-created database name to all following CREATE TABLE commands (e.g. CREATE TABLE DBName.TableOne...)?

    Secondly, is there a way to maintain the capitalization used in the table name in the CREATE TABLE command? If I execute CREATE TABLE TableOne, I get a table created named tableone.

    Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Quasimojo View Post
    I'm running into a problem in that there doesn't appear to be a way to change the database connection mid-script.
    No there isn't.
    Do I just need to append the newly-created database name to all following CREATE TABLE commands (e.g. CREATE TABLE DBName.TableOne...)?
    You need to exit psql and then re-start it connecting to that database.
    Btw: you can also create a database using the createdb command from inside the batch file, then start psql afterwards.
    For more details see the manual: http://www.postgresql.org/docs/curre...-createdb.html

    Secondly, is there a way to maintain the capitalization used in the table name in the CREATE TABLE command? If I execute CREATE TABLE TableOne, I get a table created named tableone.
    Only if you put it into double qutoes, e.g.: "TableOne" but then you have to use double quotes all the time as the name becomes case-sensitive then.
    For more details see the manual: http://www.postgresql.org/docs/curre...AX-IDENTIFIERS
    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

  3. #3
    Join Date
    Oct 2010
    Posts
    9
    Hmm. Ok. I think it's starting to make sense. The vast majority of my database experience has been with MS SQL Server, so many of these concepts are new to me.

    From your response, here's the script I'm thinking I would create, taking the name of the database to be created as an argument...

    Code:
    #!/bin/bash
    
    # Name of database ($1) passed as argument to the script
    
    echo "Creating database: " $1
    createdb $1
    echo "Database " $1 " created."
    
    echo "Creating tables on: " $1
    psql -d $1 -f /path/to/psql_script
    echo "Tables created."
    Short of missing a few recommended/necessary parameters for createdb and psql, does that sound about right?

    Thanks.
    Last edited by Quasimojo; 07-26-12 at 17:50.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Quasimojo View Post
    Short of missing a few recommended/necessary parameters for createdb and psql, does that sound about right?
    Looks good to me
    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

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Quasimojo View Post
    I'm running into a problem in that there doesn't appear to be a way to change the database connection mid-script.
    I went through the manual again, and it seems I was wrong, the \connect command can indeed be used to change the current database connection:

    Code:
    c:>psql template1 postgres
    psql (9.1.3)
    Type "help" for help.
    
    template1=# create database foo;
    CREATE DATABASE
    template1=# \connect foo postgres
    You are now connected to database "foo" as user "postgres".
    foo=# select current_catalog;
     current_database
    ------------------
     foo
    (1 row)
    
    
    foo=#
    I didn't get prompted for a password, because I have a password file setup: PostgreSQL: Documentation: 9.1: The Password File

    An alternative to prevent the password prompt is to set an environment variable: PostgreSQL: Documentation: 9.1: Environment Variables
    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

  6. #6
    Join Date
    Oct 2010
    Posts
    9
    Yes, I am able to change connections from within psql, but for some reason it wouldn't work when executing psql and passing it the script file. I don't have the error message available to me at the moment, but I will provide it later if necessary. What I *can* say is that the script file is basically what I specified in my OP.

    Thanks.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Quasimojo View Post
    Yes, I am able to change connections from within psql, but for some reason it wouldn't work when executing psql and passing it the script file. I don't have the error message available to me at the moment, but I will provide it later if necessary. What I *can* say is that the script file is basically what I specified in my OP.
    I guess it was a password prompt then, maybe that conflicts somehow with the script execution.

    Btw: do subscribe to the mailing list (or read it through a news reader as they are mirrored on news.gmane.org). There is a wealth of information there and you have direct contact to the leading developers of Postgres: http://www.postgresql.org/community/lists/
    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

  8. #8
    Join Date
    Oct 2010
    Posts
    9
    I've created a .pgpass file in my home directory with the following entry and chmod 0600 on it:

    Code:
    *.*.*.[myusername].[mypassword]
    This is what happens when I try to execute the script:

    Code:
    :/home$ psql -d postgres -f /home/jo.sql
    CREATE DATABASE
    psql:/home/jo.sql:1: \connect: could not connect to server: Connection timed out
            Is the server running on host "TABLE" and accepting
            TCP/IP connections on port RefTypes?
    :/home$
    It's like it's not seeing the semicolon at the end of the \connect command and taking the first words of the following CREATE TABLE command as arguments to \connect or something, as RefTypes is the name of the first table created in the script.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Quasimojo View Post
    It's like it's not seeing the semicolon at the end of the \connect command
    psql internal commands (those that start with \) do not need a semicolon because they aren't SQL.
    If you remove the semicolon it should work just fine.
    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

  10. #10
    Join Date
    Oct 2010
    Posts
    9
    Quote Originally Posted by shammat View Post
    psql internal commands (those that start with \) do not need a semicolon because they aren't SQL.
    If you remove the semicolon it should work just fine.
    I removed the semicolon at the end of the \connect line and I still get the same result.

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Quasimojo View Post
    I removed the semicolon at the end of the \connect line and I still get the same result.
    Then there must be something you are not telling us.
    I tried the script you posted initially and it works just fine for me. Can you copy & paste the complete session from the console?
    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

  12. #12
    Join Date
    Oct 2010
    Posts
    9
    Oh, for crying out loud. I had written the script on another machine (Windows) and uploaded it to my DropBox account. Then, in Linux, I retrieved it from DrobBox using wget. I remembered seeing [MAC Format] in the file save prompt when I made subsequent changes to it. As I use nano for my text editor in Linux, it was easy enough to save the script in DOS format, instead. It works without a hitch, now.

    Sorry for running you around on this. Turns out it was something stupidly simple. Curse you, DropBox!!! (ok, not really)

    Thanks loads for trying to help. I still learned a thing or two along the way.

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The good old PEBKAC

    Been there before as well
    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
  •