Thread: drop user / role if exists

Hi,

How under postgreSQL 7 do the equivalent as DROP USER / ROLE  IF EXISTS ?
my web provider only support postgreSQL 7 :-(

thanks a lot,

Maileen

---

Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min with Yahoo! Messenger with Voice.

On Sun, May 21, 2006 at 02:08:38AM -0700, P.M wrote:
> Hi,
>
> How under postgreSQL 7 do the equivalent as DROP USER / ROLE  IF EXISTS ?
> my web provider only support postgreSQL 7 :-(

Do the DROP USER and ignore the error if it doesn't exist.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Thanks Martijn, but i'm currently working on the SQL script to create the full database. Not on some stored procedure.

And when i run the SQL script, PostgreSQL stops just on first error....so if user does not exist, my script is not any longer run :-(

do you have any idea how to pass over this issue ?

thanks a lot,

Maileen

Martijn van Oosterhout <kleptog@svana.org> wrote:

On Sun, May 21, 2006 at 02:08:38AM -0700, P.M wrote:
> Hi,
>
> How under postgreSQL 7 do the equivalent as DROP USER / ROLE IF EXISTS ?
> my web provider only support postgreSQL 7 :-(

Do the DROP USER and ignore the error if it doesn't exist.
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

---

Be a chatter box. Enjoy free PC-to-PC calls with Yahoo! Messenger with Voice.

On Sun, May 21, 2006 at 02:25:44AM -0700, P.M wrote:
> Thanks Martijn, but i'm currently working on the SQL script to create the full database. Not on some stored
procedure.
>
> And when i run the SQL script, PostgreSQL stops just on first
> error....so if user does not exist, my script is not any longer run
> :-(
>
> do you have any idea how to pass over this issue ?

That's odd. psql doesn't stop on errors unless you enable
ON_ERROR_STOP, which is not the default. What are you using to run the
script?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

To run the script i use :

under psql command line, i load my SQL script file via :

psql -i myfile;


Martijn van Oosterhout <kleptog@svana.org> wrote:

On Sun, May 21, 2006 at 02:25:44AM -0700, P.M wrote:
> Thanks Martijn, but i'm currently working on the SQL script to create the full database. Not on some stored procedure.
>
> And when i run the SQL script, PostgreSQL stops just on first
> error....so if user does not exist, my script is not any longer run
> :-(
>
> do you have any idea how to pass over this issue ?

That's odd. psql doesn't stop on errors unless you enable
ON_ERROR_STOP, which is not the default. What are you using to run the
script?

Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

---

Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.

On Sun, May 21, 2006 at 02:59:25AM -0700, P.M wrote:
> To run the script i use :
>
> under psql command line, i load my SQL script file via :
>
> psql -i myfile;

Don't know what -i does, it's not a supported option, perhaps you mean
-f?

In any case, my version of postgres 7.4.7 works fine:

$ cat /tmp/f
drop user idontexist;
drop user indontexisteither;
drop user foo;
$ psql -f /tmp/f kleptog
psql:/tmp/f:1: ERROR:  user "idontexist" does not exist
psql:/tmp/f:2: ERROR:  user "indontexisteither" does not exist
psql:/tmp/f:3: ERROR:  user "foo" does not exist
$

So I think you need to post the actual error before we can help you
further.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Hi,

On Sun, 2006-05-21 at 02:25 -0700, P.M wrote:

> And when i run the SQL script, PostgreSQL stops just on first
> error....so if user does not exist, my script is not any longer
> run :-(

I think you are running your query inside a transaction. Remove the
BEGIN and COMMIT parts...

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

first of all, it's not -i as i wrote but \i
i work under windows OS.

and my script is working now.
thanks for your help

Martijn van Oosterhout <kleptog@svana.org> wrote:

On Sun, May 21, 2006 at 02:59:25AM -0700, P.M wrote:
> To run the script i use :
>
> under psql command line, i load my SQL script file via :
>
> psql -i myfile;

Don't know what -i does, it's not a supported option, perhaps you mean
-f?

In any case, my version of postgres 7.4.7 works fine:

$ cat /tmp/f
drop user idontexist;
drop user indontexisteither;
drop user foo;
$ psql -f /tmp/f kleptog
psql:/tmp/f:1: ERROR: user "idontexist" does not exist
psql:/tmp/f:2: ERROR: user "indontexisteither" does not exist
psql:/tmp/f:3: ERROR: user "foo" does not exist
$

So I think you need to post the actual error before we can help you
further.

Have a nice day,
--
Martijn van Oosterhout http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

---

How low will we go? Check out Yahoo! Messenger’s low PC-to-Phone call rates.