4

How do you use the "java if-statement" in SQL => PostgreSQL, while creating a table/Column?

    CREATE TABLE Store(
           Discount INT 
                AS CASE 
         WHEN SOLD_Amount>100000 THEN 2
         WHEN SOLD_Amount>500000 THEN 5
         WHEN SOLD_Amount>1000000 THEN 10
         ELSE 0
     END       
           NOT NULL)

This is probally wrong, please tell us, the community how to do this kind of action.

Brian Tompsett - 汤莱恩
  • 5,438
  • 68
  • 55
  • 126
MrMe TumbsUp
  • 416
  • 1
  • 4
  • 17

2 Answers2

3

What you are looking for here is a computed column, which is not directly supported by Postgres. You could implement this in a view, like so:

CREATE VIEW someview AS
SELECT SOLD_Amount,
     CASE 
         WHEN SOLD_Amount>100000 THEN 2
         WHEN SOLD_Amount>500000 THEN 5
         WHEN SOLD_Amount>1000000 THEN 10
         ELSE 0
     END As Discount

Or you could use a trigger to populate the column on insert/update.

Eric Petroelje
  • 58,601
  • 9
  • 123
  • 175
2

You can use a special PostgreSQL feature: "generated" columns.

Based on an existing table, say:

CREATE TABLE store (sold_amount int, ...):

You could create this special function:

CREATE FUNCTION store_sold_amount(rec store)
  RETURNS int LANGUAGE SQL IMMUTABLE
AS
 $func$
 SELECT CASE
         WHEN rec.sold_amount > 100000 THEN 2
         WHEN rec.sold_amount > 500000 THEN 5
         WHEN rec.sold_amount > 1000000 THEN 10
         ELSE 0 END;
 $func$;

Then you can query:

SELECT s.amount, s.store_sold_amount
FROM   store s;

More under these related questions:
How can I create a column in postgres from values and selections based on other columns?
Store common query as column?

Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
  • This is the best solution, people can use this function in the create table statement of Store right ? – MrMe TumbsUp Dec 26 '12 at 23:24
  • Small mistake: `THEN THEN` – Clodoaldo Neto Dec 26 '12 at 23:29
  • @MrMe No. You can set a default value for a column in the create statement. But [from the manual](http://www.postgresql.org/docs/current/static/sql-createtable.html): _The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed)_ – Clodoaldo Neto Dec 26 '12 at 23:34
  • @MrMeTumbsUp: This cannot be used in the `CREATE` statement. The functionally dependent value is not *saved*, it's generated on the fly in queries. For redundant storage I would use a *trigger*. You'll have to observe a couple of eventualities to keep your value current, though. Redundant storage in the base tables is usually not such a great idea, but there are exceptions ... – Erwin Brandstetter Dec 27 '12 at 04:26