Search code examples
sqlpostgresqlcreate-table

Case statement inside create-table

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.

Solution

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