Postgresql Drop Table If exists

Question

I Work with Postgresql and I want to drop Table if exists .My code is

execute 'drop table if exists production.'|| layer_name || '' into result_var;
exception 
    when others then 
    execute 'insert into production.'|| layer_name || ' select * from staging.'|| layer_name ;
return 1;

but table if not exists thows exeption .I do not want to trow exeption

Can anybody help me?

As Daniel mentions, the "WHEN OTHERS" block is hiding the error. If you are going to use that catch-all, it is usually helpful to emit the error message using SQLERRM or GET STACKED DIAGNOSTICS. See postgresql.org/docs/current/static/… — bma
– bma, Commented Oct 29, 2013 at 18:55

Daniel Vérité · Accepted Answer · 2013-10-29 14:01:52Z

3

DROP TABLE does not return any row, so the INTO clause must not be used.

Also, don't use WHEN OTHERS in an exception block that eats any error whatever it is. It's frustrating both for debugging and troubleshooting problems later when the code is deployed.

If it wasn't for this clause, you'd had the error message saying why it doesn't work.

answered Oct 29, 2013 at 14:01

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Drop Table If exists

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related