PostreSQL SQL for MySQL SQL

I'm fairly new to PostreSQL, coming from MySQL. My Python application
generates these MySQL commands:

drop database if exists Foo;
create database Foo;
use Foo;
Using the PostgreSQL online docs, my closest translation is:

drop database Foo;
create database Foo;

You supply the database name on connect.. I don't *think* you can change
databases after you've connected but I could be very wrong (if you can, I'd
sure like to know!)

What I'm missing is:
* "if exists" for the drop, in order to avoid an error the very first time
this program runs
* the "use Foo;"

Can I do these things in PostreSQL from SQL (e.g., not from the command

line)?

Not sure on these...

-Mitch

Hi,

I'm fairly new to PostreSQL, coming from MySQL. My Python application
generates these MySQL commands:

drop database if exists Foo;
create database Foo;
use Foo;

Using the PostgreSQL online docs, my closest translation is:

drop database Foo;
create database Foo;

What I'm missing is:
* "if exists" for the drop, in order to avoid an error the very first time
this program runs
* the "use Foo;"

Can I do these things in PostreSQL from SQL (e.g., not from the command line)?

Just let the DROP fail if it does not already exist.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Tue, 6 Feb 2001, John Burski wrote:

There is no "if exists" functionality in PostgreSQL. However, you could
probably create your own "if exists" type of functionality. I've found that
it's not really necessary. I access my databases either interactively, via
psql, via the Perl Pg module, or via PHP. If you attempt to drop a database
that doesn't exist, PostgreSQL will issue an error message. If you're running
interactively, you'll see the message; if you're accessing via a Perl module or
PHP, you can check the query results to see if an error occurred. I'm fairly
certain that this same mechanism exists if you're using C or C++ to access your
databases.

If you are using Perl, if you wrap your attempt to drop the table in an
eval statement and then check for anything in $@, you can see if an
exception was thrown without having anything mess up your display and
still have the appropriate logic for testing the existance of the table.
This is more or less the equivalent to doing 'try... catch' in Java or
C++.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
When God saw how faulty was man He tried again and made woman. As to
why he then stopped there are two opinions. One of them is woman's.
-- DeGourmont

Mitch Vincent wrote:

I'm fairly new to PostreSQL, coming from MySQL. My Python application
generates these MySQL commands:

drop database if exists Foo;
create database Foo;
use Foo;
Using the PostgreSQL online docs, my closest translation is:

drop database Foo;
create database Foo;

You supply the database name on connect.. I don't *think* you can change
databases after you've connected but I could be very wrong (if you can, I'd
sure like to know!)

If you're running PostgreSQL interactively (via the "psql" command) you can
connect to any database in the installation, provided the user in question has
been granted rights to access the database, by using the "\connect" command.
The format is "\connect dbname".

What I'm missing is:
* "if exists" for the drop, in order to avoid an error the very first time
this program runs
* the "use Foo;"

Can I do these things in PostreSQL from SQL (e.g., not from the command

line)?

Not sure on these...

There is no "if exists" functionality in PostgreSQL. However, you could
probably create your own "if exists" type of functionality. I've found that
it's not really necessary. I access my databases either interactively, via
psql, via the Perl Pg module, or via PHP. If you attempt to drop a database
that doesn't exist, PostgreSQL will issue an error message. If you're running
interactively, you'll see the message; if you're accessing via a Perl module or
PHP, you can check the query results to see if an error occurred. I'm fairly
certain that this same mechanism exists if you're using C or C++ to access your
databases.

I'm not familiar with the "use Foo" functionality of MySQL, so I can't discuss
it.

-Mitch

--
John Burski
Chief IT Cook and Bottlewasher
911 Emergency Products, St. Cloud, MN
(320) 656 0076 www.911ep.com

++++++++++++++++++++++++++++++++++
+ How's your cheese holding out? +
++++++++++++++++++++++++++++++++++

At 01:10 PM 2/6/2001 -0600, John Burski wrote:

There is no "if exists" functionality in PostgreSQL. However, you could
probably create your own "if exists" type of functionality. I've found
that it's not really necessary. I access my databases either
interactively, via psql, via the Perl Pg module, or via PHP. If you
attempt to drop a database that doesn't exist, PostgreSQL will issue an
error message. If you're running interactively, you'll see the message;
if you're accessing via a Perl module or PHP, you can check the query
results to see if an error occurred. I'm fairly certain that this same
mechanism exists if you're using C or C++ to access your databases.

I'd prefer to skip the error message, because otherwise my regression test
suite will barf, saying something like "Test X failed due to SQL error". I
suppose I work in some code to catch this and swallow it.

I'm not familiar with the "use Foo" functionality of MySQL, so I can't
discuss it.

I think you may have answered it with your "\connect dbname" comment.
Provided that I can put that after the "create database" in the SQL script
and feed the whole mess to psql.

I'll give this stuff a try. Thanks to all who answered.

I think it's still a good idea to provided the "if exists" clause in a
future PostgreSQL version for these reasons:
1. it's convenient
2. it doesn't interfere with existing functionality or performance
3. it makes porting from MySQL to PostgreSQL easier

-Chuck

hello.

i am trying to develop a relatively clean process for creating/populating a
new database. i have all of the table creation scripts and am about to
start writing scripts to populate the db with the necessary default data (an
admin user, a guest user, default application info, default preferences, etc
etc etc). is there any easy way to do this?

my current approach is to write a bunch of insert statements in a text
editor. i can do this, but it's error prone (or i guess i am). obviously
if i already have a source db i can do some table copying, but i would like
to make this work for a fresh install on a new machine.

is there a better way?

the limit of my imaginatin is this - some sort of command/utility that
queries a table and generates the insert statements from the results. then
i wouldn't have to worry about updating my default data scripts everytime
data changes.

i appreciate your help/suggestions.

chris

: I think it's still a good idea to provided the "if exists" clause in a
: future PostgreSQL version for these reasons:
: 1. it's convenient
: 2. it doesn't interfere with existing functionality or
performance
: 3. it makes porting from MySQL to PostgreSQL easier

I second this! It should probably be easy functionality to add (although
I'm no C guru and definetly don't know the ins and out of postgres). I've
made an equivalent in PHP, but it would be much easier if I could use it
within pgsql dump/import scripts. Plus it requires an additional call to the
database.

-Dan

At 15:02 06.02.2001 -0500, chris markiewicz wrote:

the limit of my imaginatin is this - some sort of command/utility that
queries a table and generates the insert statements from the results. then
i wouldn't have to worry about updating my default data scripts everytime
data changes.

pg_dump lives up to You wildest dreams :-)

check out the -d, -D, -a and -s options to seperate the definitions from
the data.

Yours,

Stefan

On 6 Feb 2001, at 14:24, Chuck Esterbrook wrote:

At 01:10 PM 2/6/2001 -0600, John Burski wrote:

interactively, via psql, via the Perl Pg module, or via PHP. If you
attempt to drop a database that doesn't exist, PostgreSQL will issue an
error message. If you're running interactively, you'll see the message;
if you're accessing via a Perl module or PHP, you can check the query
results to see if an error occurred. I'm fairly certain that this same
mechanism exists if you're using C or C++ to access your databases.

I'd prefer to skip the error message, because otherwise my regression test
suite will barf, saying something like "Test X failed due to SQL error". I
suppose I work in some code to catch this and swallow it.

You can always search the system catalog to know if a DB exists
or not.

This is how I do it using pglib, in C:

sprintf(Query,"SELECT * from pg_database where
datname='%s';",DBname);
res = PQexec(conn, Query);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
Throw_Error(121);
NumMax=PQntuples(res);

if(NumMax==1)
{
sprintf(Query,"DROP DATABASE %s;",DBname);
res = PQexec(conn, Query);
if (PQresultStatus(res) != PGRES_COMMAND_OK)
Throw_Error(122);
}

I'm not familiar with the "use Foo" functionality of MySQL, so I can't
discuss it.

I think you may have answered it with your "\connect dbname" comment.
Provided that I can put that after the "create database" in the SQL script
and feed the whole mess to psql.

Sure you can. If you use psql as command interpreter, "\connect
dbname" has almost 1:1 functionality in respect to MySql's "use
foo". If you use a pglib-based API (i.e. you're using C, Perl, PHP or
other) you got to use the connection function to select the db.

HTH, bye!

/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/

Fabrizio Ermini Alternate E-mail:
C.so Umberto, 7 faermini@tin.it
loc. Meleto Valdarno Mail on GSM: (keep it short!)
52020 Cavriglia (AR) faermini@sms.tin.it