I'm completely new to postgresql. How do i perform a condition to produce a table only when it doesn't already is available? Code example appreciated.
Just produce the table and do not be worried about whether or not this is available. Whether it does not exist it will likely be produced whether it does exist the table will not be modified. You could look into the return worth of your SQL query to determine if the table been around or otherwise whenever you performed the create statement.
create or replace function update_the_db() returns void as $$ begin if not exists(select * from information_schema.tables where table_catalog = CURRENT_CATALOG and table_schema = CURRENT_SCHEMA and table_name = 'your_table_name_here') then create table your_table_name_here ( the_id int not null, name text ); end if; end; $$ language 'plpgsql'; select update_the_db(); drop function update_the_db();
I believe to determine the pg_class table possibly assist you to, something of that nature:
Choose a = COUNT (relname) FROM pg_class WHERE relname = 'mytable'
if your = then (CREATE IT)
I am unsure if this was added, but with regard to completeness Let me explain that in version 9.1 (maybe before) Otherwise EXIST may be used.
This can only produce the table whether it does not exist already.
It is really an old question. I am only returning to suggest another answer. Note: other better solutions already exist, case for educational reasons.
The simplest strategy is to complete what others have stated perform CREATE TABLE if you wish to keep your existing data, or execute a DROP IF Is available after which a CREATE TABLE, if you prefer a freshly produced table.
Another alternative would be to query the machine table because of its existence and proceed after that.
SELECT true FROM pg_tables WHERE tablename = <table> [AND schemaname = <schema>];
-- schema independent: SELECT true FROM pg_tables WHERE tablename = 'foo'; -- schema dependent: SELECT true FROM pg_tables WHERE tablename = 'foo' AND schemaname = 'bar';
Whether it matches you will have a true value, otherwise it will return a clear dataset. You should use that value to find out if you want to execute a CREATE TABLE.
The right syntax a minimum of in version 8.4 beta 2 is
DROP TABLE IF Is available TABLENAME
create or replace function build_foo_table() returns void as $$ create table foo_table (id int); $$ language sql; create or replace function foo_table_exists() returns int as $$ select count(table_name)::int from information_schema.tables where table_name = 'foo_table'; $$ language sql; select case when foo_table_exists()=0 then build_foo_table()end; drop function build_foo_table(); drop function foo_table_exists();
DROP TABLE [ IF Is available ] title [, ...] [ CASCADE RESTRICT ]