Thread: Change Default Database
Hi list,
I wrote a database creation script that begins with commands to drop the existing database (if it exists) and create it from scratch. These commands execute fine, the problem is that all subsequent commands are executed on the default database 'postgres'. What command can I use to set the default database to my newly created database (for the duration of the script)? SQL Server has a 'USE [dbname]' command, is there an equivalent command in PostgreSQL?
Thanks in advance,
Rommel Edwards
Software Developer,
Barbados, Caribbean.
Attachment
Rommel the iCeMAn wrote: > Hi list, > > I wrote a database creation script that begins with commands to drop the > existing database (if it exists) and create it from scratch. These commands > execute fine, the problem is that all subsequent commands are executed on > the default database 'postgres'. What command can I use to set the default > database to my newly created database (for the duration of the script)? SQL > Server has a 'USE [dbname]' command, is there an equivalent command in > PostgreSQL? See "man psql" (or the client applications section of the manuals) for details on the backslash commands. \c will be what you're after. -- Richard Huxton Archonet Ltd
On 2/26/07, Rommel the iCeMAn <icecrew@gmail.com> wrote: > Hi list, > > I wrote a database creation script that begins with commands to drop the > existing database (if it exists) and create it from scratch. These commands > execute fine, the problem is that all subsequent commands are executed on > the default database 'postgres'. What command can I use to set the default > database to my newly created database (for the duration of the script)? SQL > Server has a 'USE [dbname]' command, is there an equivalent command in > PostgreSQL? > > Thanks in advance, > > Rommel Edwards > Software Developer, > Barbados, Caribbean. If using psql: \c dbname then the rest of the commands.
Thanks for the swift responses, I tried the \c command but I got the following error: ERROR: syntax error at or near "\" SQL state: 42601 Character: 520 Here's a code snippet ... <snip> -- -- TOC entry 1685 (class 1262 OID 16453) -- Name: test_db; Type: DATABASE; Schema: -; Owner: postgres -- DROP DATABASE IF EXISTS test_db; CREATE DATABASE test_db WITH TEMPLATE = template0 ENCODING = 'UTF8'; ALTER DATABASE test_db OWNER TO postgres; \c test_db; -- query tool doesn't like this command :-( </snip> Rommel Edwards Software Developer, Barbados, Caribbean.
On 2/26/07, Rommel the iCeMAn <icecrew@gmail.com> wrote: > Thanks for the swift responses, > > I tried the \c command but I got the following error: > > ERROR: syntax error at or near "\" > SQL state: 42601 > Character: 520 > > Here's a code snippet ... > > <snip> > -- > -- TOC entry 1685 (class 1262 OID 16453) > -- Name: test_db; Type: DATABASE; Schema: -; Owner: postgres > -- > > DROP DATABASE IF EXISTS test_db; > CREATE DATABASE test_db WITH TEMPLATE = template0 ENCODING = 'UTF8'; > > > ALTER DATABASE test_db OWNER TO postgres; > > \c test_db; -- query tool doesn't like this command :-( > > </snip> > > Rommel Edwards > Software Developer, > Barbados, Caribbean. \c only works in psql. See: http://www.postgresql.org/docs/8.2/static/app-psql.html
On Mon, 2007-02-26 at 10:52, Rommel the iCeMAn wrote: > Hi list, > > I wrote a database creation script that begins with commands to drop > the existing database (if it exists) and create it from scratch. These > commands execute fine, the problem is that all subsequent commands are > executed on the default database 'postgres'. What command can I use to > set the default database to my newly created database (for the > duration of the script)? SQL Server has a 'USE [dbname]' command, is > there an equivalent command in PostgreSQL? A couple of points: 1: PLEASE don't post html email, especially with an annoying background that makes it harder to read your message. 2: What kind of script is this? sql? bash? PHP? perl? If you post the script, we can probably help you out a bit.
On Mon, 2007-02-26 at 14:52, Rommel the iCeMAn wrote: > Hi again, > > Apologies for the HTML. > > This is an SQL script, it was generated by doing a schema-only database dump > using pgAdminIII (which in turn uses pgdump I believe). I've attached the > entire script. I was attempting to run this script in pgAdminIII because > that's what I used to generate it, but the more I read it seems I should try > the psql command line tool to execute this script. I'm getting the > impression that pgAdmin doesn't recognize the /connect directive. Please CC the list, as more eyes are more likely to have an answer than just mine... Yeah, I always run my scripts from psql, often called from a bash shell script. That way I can substitute server / database names in the shell script depending on what I'm trying to do. \ commands exist within the framework of psql, not outside. If pgAdmin doesn't have an equivalent type of command structure, then it won't work for this. You could always drop the db by hand, create it, connect to it, and THEN run the rest of your script, but I too think it would be easier to just run the script against psql.
I seem to be blundering a lot today! I thought I was replying to the entire list, didn't realize I replied to one person :-) Nothing was wrong with my script, I assumed that since it was generated by pgAdmin that I could run it inside pgAdmin. It works perfectly when I run it using psql. It is very much like SQL Server's osql command line tool. Thanks to everyone that helped ... So I've learnt three valuable lessons: 1. It's better to execute scripts using the command line tool 'psql'. 2. Make sure I'm replying to the list and not to an individual. 3. Do not post HTML messages!!! :-)) Thanks again everyone. Rommel Edwards Software Developer, Barbados, Caribbean.
On Mon, 2007-02-26 at 16:02, Rommel the iCeMAn wrote: > I seem to be blundering a lot today! I thought I was replying to the entire > list, didn't realize I replied to one person :-) > > Nothing was wrong with my script, I assumed that since it was generated by > pgAdmin that I could run it inside pgAdmin. It works perfectly when I run it > using psql. It is very much like SQL Server's osql command line tool. Thanks > to everyone that helped ... > > So I've learnt three valuable lessons: > > 1. It's better to execute scripts using the command line tool 'psql'. > > 2. Make sure I'm replying to the list and not to an individual. > > 3. Do not post HTML messages!!! :-)) And don't forget number 4. The pgsql mailing lists provide better support than most commercial db companies do. > Thanks again everyone. You're welcome, and considering the weather we're having here in Chicago, I might book a flight to come and visit you down there... :)
Brrrr!!!, I was there late last November, it must be far worse now! I'll be sticking close to the forums from now on :-)