How to delete table or view from PostgreSQL database?

Question

I have a name of table or view in PostgreSQL database and need to delete in in single pgSQL command. How can i afford it?

I was able to select form system table to find out if there any table with such a name but stuck with procedural part:

SELECT count(*) FROM pg_tables where tablename='user_statistics';

empi · Accepted Answer · 2009-01-20 22:36:47Z

16

DROP TABLE user_statistics;

DROP VIEW user_statistics;

complete syntax:

DROP TABLE

DROP VIEW

And if you want a complete function, i tried something like this:

CREATE OR REPLACE FUNCTION delete_table_or_view(objectName varchar) RETURNS integer AS $$
DECLARE
    isTable integer;
    isView integer;
BEGIN
    SELECT INTO isTable count(*) FROM pg_tables where tablename=objectName;
    SELECT INTO isView count(*) FROM pg_views where viewname=objectName;

    IF isTable = 1 THEN
        execute 'DROP TABLE ' || objectName;
        RETURN 1;
    END IF;

    IF isView = 1 THEN
        execute 'DROP VIEW ' || objectName;
        RETURN 2;
    END IF;

    RETURN 0;

END;
$$ LANGUAGE plpgsql;

edited Jan 20, 2009 at 22:36

answered Jan 20, 2009 at 21:59

empi

16k8 gold badges64 silver badges78 bronze badges

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

Comments

Artem Tikhomirov · Accepted Answer · 2012-03-14 19:25:06Z

-4

Consider using DROP TABLE IF EXISTS and DROP VIEW IF EXISTS. That way you won't get an error message if it fails, just a notice.

edited Mar 14, 2012 at 19:25

Artem Tikhomirov

21.8k11 gold badges52 silver badges71 bronze badges

answered Aug 13, 2009 at 6:32

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

2 Comments

Wolph Over a year ago

That won't work actually. If you're doing DROP VIEW IF EXISTS and the view that you are trying to drop is actually a table than Postgres will give you an error that it is not a view (the same goes for DROP TABLE IF EXISTS). You need to know for sure if it's a table or a view.

a'' Over a year ago

DROP TABLE IF EXISTS name_of_table - to be precise

Collectives™ on Stack Overflow

How to delete table or view from PostgreSQL database?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related