Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CREATE OR REPLACE for a CREATE TABLE Statement - PostgreSQL

Posted on 2007-10-12
11
Medium Priority
?
19,343 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:FreightTrain
  • 5
  • 5
11 Comments
 
LVL 28

Expert Comment

by:gamebits
ID: 20065059
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
 

Author Comment

by:FreightTrain
ID: 20065164
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
 
LVL 28

Accepted Solution

by:
gamebits earned 2000 total points
ID: 20065410
To check if a table exist or not

 SELECT relname FROM pg_class  
 WHERE relname = 'mytable';
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:FreightTrain
ID: 20065497
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
 
LVL 28

Expert Comment

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

Author Comment

by:FreightTrain
ID: 20065669
I am running the query using the SQL language directly in PostgreSQL...
0
 

Author Comment

by:FreightTrain
ID: 20065753
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
 
LVL 28

Expert Comment

by:gamebits
ID: 20065759
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
 
LVL 28

Expert Comment

by:gamebits
ID: 20065767
Good stuff, forget my last comment.
0
 

Author Comment

by:FreightTrain
ID: 20072835
I FOUND A BETTER WAY:

DROP TABLE IF EXISTS myschema.mytablename;
1
 
LVL 1

Expert Comment

by:thready
ID: 20742348
Nothing I've tried from within this thread works from within ODBC, yet, it works when I type it directly in psql...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Screencast - Getting to Know the Pipeline
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question