Thread: PostreSQL SQL for MySQL SQL

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)?


-Chuck

> 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

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? +
++++++++++++++++++++++++++++++++++

 

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

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