CREATE OR REPLACE for a CREATE TABLE Statement - PostgreSQL

In PostgreSQL, when writing a CREATE FUNCTION statement, you can add the statement OR REPLACE into the CREATE line, so it looks like this CREATE OR REPLACE FUNCTION.  This checks if the function exists, and if it doesn't it creates it.  If the function does exist, then it replaces it.

With a table CREATE statement, I tried to do the same thing, but I received a syntax error on the OR REPLACE.  

Is there a way to check to see if a table exists, if so, then DROP it and then Re-Create it.  I want my script to be able to run on databases that don't have the table created yet as well as databases that have the table created, but I need to update the table.

Thanks,

FT
FreightTrainAsked:
Who is Participating?
 
gamebitsCommented:
To check if a table exist or not

 SELECT relname FROM pg_class  
 WHERE relname = 'mytable';
0
 
gamebitsCommented:
Using a script you can check if a table exist if so drop it and then make the new one, but if you know you are going to replace it anyway why don't you just run a drop table script and then the create table script?
0
 
FreightTrainAuthor Commented:
The DROP statement fails if you always run it first.  

Do you have the code that checks to see if a table exists or not?  I have tried several things, but the parser doesn't like my if exists statement.  I know you can check it by querying pg_tables, but as funny as this sounds, I am struggling with the IF statement syntax and have used examples directly from the PostgreSQL documentation...Thanks in advance for your help...
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
FreightTrainAuthor Commented:
This is great.  So when I am trying to encompass a statement like this into a function, I get a message like this:  ERROR: syntax error at or near "IF"

IF (SELECT EXISTS(SELECT relname FROM pg_class WHERE relname = 'Angelina_VR_Data')) == true THEN
SELECT 1
ELSE SELECT 2
END IF;

Can you help me understand why I am getting an error and what is wrong with my code above...I have really been trying to get this to work for the past day or so...  Thank you so much for your help.  I have a tight deadline and don't have the PostgreSQL experience yet that I need...

0
 
gamebitsCommented:
Are you using a server side language like PHP, ASP, Coldfusion etc. or are you trying to run the query directly in PostgreSQL?
0
 
FreightTrainAuthor Commented:
I am running the query using the SQL language directly in PostgreSQL...
0
 
FreightTrainAuthor Commented:
You are made me think about the language.  I changed the language and now it is working just fine...that was the problem...PLPGSQL...In this area, PostgreSQL is much more acceptable of different server side coding languages...I need to keep that in mind...  thanks for the hint... Below is my code, note the last line of the function create that specifies the language:  LANGUAGE 'PLPGSQL' VOLATILE SECURITY DEFINER;  It used to display LANGUAGE 'SQL'...that was the problem...

CREATE OR REPLACE FUNCTION pbs."getRegStatus"("Suspended" character varying)
  RETURNS bigint AS
$BODY$
BEGIN
      IF (SELECT TRIM($1)) = 'S'
      THEN
            RETURN 2;
      ELSE
            RETURN 1;
      END IF;
END;
$BODY$
  LANGUAGE 'PLPGSQL' VOLATILE SECURITY DEFINER;
ALTER FUNCTION pbs."getRegStatus"("Suspended" character varying) OWNER TO postgres;
0
 
gamebitsCommented:
Ok, try

SELECT relname FROM pg_class WHERE relname = 'Angelina_VR_Data'
IF relname IS NULL THEN
SELECT 2
ELSE SELECT 1
END IF;
0
 
gamebitsCommented:
Good stuff, forget my last comment.
0
 
FreightTrainAuthor Commented:
I FOUND A BETTER WAY:

DROP TABLE IF EXISTS myschema.mytablename;
1
 
threadyCommented:
Nothing I've tried from within this thread works from within ODBC, yet, it works when I type it directly in psql...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.