asked on

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

gamebits

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?

FreightTrain

ASKER

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...

ASKER CERTIFIED SOLUTION

gamebits

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

FreightTrain

ASKER

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...

gamebits

Are you using a server side language like PHP, ASP, Coldfusion etc. or are you trying to run the query directly in PostgreSQL?

FreightTrain

ASKER

I am running the query using the SQL language directly in PostgreSQL...

FreightTrain

ASKER

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;

gamebits

Ok, try

SELECT relname FROM pg_class WHERE relname = 'Angelina_VR_Data'
IF relname IS NULL THEN
SELECT 2
ELSE SELECT 1
END IF;

gamebits

Good stuff, forget my last comment.

FreightTrain

ASKER

I FOUND A BETTER WAY:

DROP TABLE IF EXISTS myschema.mytablename;

thready

Nothing I've tried from within this thread works from within ODBC, yet, it works when I type it directly in psql...