Thread: Connect to a database in a .sql file

Hi,
  I am writing a .sql file to create a database, connect to it and create tables etc. (basic db setup).
But I cannot figure out how to connect to the server (what statement) in a .sql file.

any help would be appreciated.

-assad

On Sun, 6 Nov 2005 18:06:11 -0700, Assad Jarrahian <jarraa@gmail.com> wrote:
> Hi,
> I am writing a .sql file to create a database, connect to it and create
> tables etc. (basic db setup).
> But I cannot figure out how to connect to the server (what statement) in a
> .sql file.
>
> any help would be appreciated.

What's going to play the script?  If psql, then check out the \c
command.

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

Hi,
    Lets say the script is called myDBSetup.sql

And the script contains:

//CREATE DATABASE section
//CREATE USERS SECTION
//COnnect to db
//CREATE TABLES, FUNCTIONS etc.

this script will be called from psql. The user will log connect to template1 and then run my script. What I really need is after the CREATE DB, I need to switch from template1 to the database name (so the CREATE tables etc will correspond to the right db). This has to be automated and done within the script.

\c does not work in .sql script run in psql.

Any suggestions would be helpful. Thanks.
-assad

Assad Jarrahian <jarraa@gmail.com> writes:
> \c does not work in .sql script run in psql.

Nonsense.  Try it again, and show us exactly what you did and what
message you got, rather than leaping to silly conclusions.

            regards, tom lane

Assad Jarrahian wrote:
>
> \c does not work in .sql script run in psql.

That sounds unlikely. What sort of error message are you getting?

--
   Richard Huxton
   Archonet Ltd

Sorry, I am a linux/OS X person. Was working on PGADMIN in windows and thought their query tool was the same as psql. Just ran the script in psql on my linux box and it worked.
My apologies.
   Tom, I am not leaping to silly conclusions. Calm down please. We are all trying to learn from people with knowledge. Be curtious.
Thanks.

-assad

Assad Jarrahian wrote:
> Sorry, I am a linux/OS X person. Was working on PGADMIN in windows and
> thought their query tool was the same as psql.

Nope - psql is psql, pgadmin is pgadmin. All the backslash commands are
psql-only rather than being part of the backend. Of course, pgadmin can
duplicate them if they want.

 > Just ran the script in psql
> on my linux box and it worked.

And on Windows too. Not sure if you need to change the line-endings though.

> My apologies.
> Tom, I am not leaping to silly conclusions. Calm down please. We are all
> trying to learn from people with knowledge. Be curtious.

The problem is Assad that your email saying \c doesn't work in psql is
now in the archive forever. In a couple of days Google will index it and
then the world at large will be coming across it. And it's not worded as
a question "I can't get \c to work in psql" - just says it doesn't work,
so it might be that a questioner doesn't look any further, assuming you
were right.

It's not the leaping to a conclusion that's done the damage, and it's
not the instant archiving, but the combination of the two.

Of course Tom could have been a little more gentle in his dressing down,
but since I'd guess he's been working pretty hard recently getting 8.1
out of the door (released today!) I'm prepared to cut him some slack.

--
   Richard Huxton
   Archonet Ltd

Got it. I will be more careful with words.

Thanks tom for getting 8.1!
one more question.
Where does the directory lie for psql (so I can put a .sql file in there and run it) for windows?
thanks.
-assad

Assad Jarrahian wrote:

I converted your HTML message to plain text for you.

> Where does the directory lie for psql (so I can put a .sql file in
> there and run it) for windows? thanks.

It's in the bin directory underneath wherever you install PostgreSQL.
But you don't need to put your .sql script there.  Either put the bin
directory in your path, or pass the full path to your .sql script to
psql.

--
Guy Rouillier

On Tue, 2005-11-08 at 07:31, Assad Jarrahian wrote:
> Hi,
>     Lets say the script is called myDBSetup.sql
>
> And the script contains:
>
> //CREATE DATABASE section
> //CREATE USERS SECTION
> //COnnect to db
> //CREATE TABLES, FUNCTIONS etc.
>
> this script will be called from psql. The user will log connect to
> template1 and then run my script. What I really need is after the
> CREATE DB, I need to switch from template1 to the database name (so
> the CREATE tables etc will correspond to the right db). This has to be
> automated and done within the script.
>
> \c does not work in .sql script run in psql.
>
> Any suggestions would be helpful. Thanks.
> -assad
>

As others have mentioned \c works from sql script, small example follows

-- test.sql
CREATE database test3;
\c test3
CREATE TABLE test_table (
  field1 integer,
  field2 varchar(10));
INSERT INTO test_table (field1, field2) VALUES (1,'VALUE1');
INSERT INTO test_table (field1, field2) VALUES (2,'VALUE2');
SELECT * FROM test_table;
-- Cut

[post800b1@raftahlid35 projects]$ psql -a -f test.sql template1
CREATE database test3;
CREATE DATABASE
\c test3
You are now connected to database "test3".
CREATE TABLE test_table (
  field1 integer,
  field2 varchar(10));
CREATE TABLE
INSERT INTO test_table (field1, field2) VALUES (1,'VALUE1');
INSERT 25513 1
INSERT INTO test_table (field1, field2) VALUES (2,'VALUE2');
INSERT 25514 1
SELECT * FROM test_table;
 field1 | field2
--------+--------
      1 | VALUE1
      2 | VALUE2
(2 rows)

--
Sigurdur

Assad Jarrahian wrote:
> Got it. I will be more careful with words.
>
> Thanks tom for getting 8.1!
> one more question.
> Where does the directory lie for psql (so I can put a .sql file in there and
> run it) for windows?

I'm not familiar with the Windows version, but the psql.exe file should
be with all the other executables. You can use the -f flag to supply a
file to process:
   psql -f <path-to-sql-file>

Also, if you start psql from a command-prompt you can process a file
from within psql using:
   \i <path-to-sql-file>

See the documentation for full details.

--
   Richard Huxton
   Archonet Ltd