Case statement inside create-table

Question

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.

I think what you are looking for here is a computed column.. — Eric Petroelje, Dec 26 '12 at 20:17

score 3 · Accepted Answer · answered Dec 26 '12 at 20:19

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.

score 2 · Answer 2 · edited May 23 '17 at 12:16

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?

edited May 23 '17 at 12:16

Community

1
1

answered Dec 26 '12 at 20:51

Erwin Brandstetter

605,456
145
1,078
1,228

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

score 0 · Answer 3 · answered Jul 28 '22 at 19:33

SELECT A.*,
       CASE
           WHEN B.Table2 IS NOT NULL
               THEN 'Yes'
           ELSE 'No'
           END AS results_column_name_here

INTO new_table_name
FROM Table1 A
         LEFT JOIN Table2 B
                   ON A.col_to_join_on = B.col_to_join_on

Case statement inside create-table

3 Answers3