[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1626
  • Last Modified:

PostgreSQL conditions

Hi!

How do I check some conditions in PostgreSQL ?!
I couldn't find any "IF" or "CASE" commands in the help.

All I want is something like that:
IF 1=1 SELECT 'True' ELSE SELECT 'False'

and more advanced:
how do I check if a table exists?
I know it's stored in pg_class table and I can check the relname field, but how can I perform something after that ?
Let's say I want to drop the table if it exists...
In Microsoft SQL server I could write somthing like that:
IF EXISTS (SELECT the table name from a system tables)
  DROP the table

How can it be done with PostgreSQL??

Thanks.
0
alex_b
Asked:
alex_b
1 Solution
 
xbrentCommented:
1>
> How do I check some conditions in PostgreSQL ?!

PostgreSQL provides for this in the form of "pl/pgsql". It is a procedural language for postgres.  See the docs (http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4091.htm) for more.

IF var_i < 1 THEN
  SELECT true_column FROM table;
ELSE
  SELECT false_column FROM table;
END IF;

2>
>how do I check if a table exists..then drop?

You're correct - you check for the table using plain SQL with some plpgsql logic:

IF (SELECT tablename
      FROM pg_tables
     WHERE tablename = 'checking_for_table') ISNULL THEN
 DROP TABLE checking_for_table;
END IF;

(LANGUAGE 'plpgsql';)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Tackle projects and never again get stuck behind a technical roadblock.
Join Now