CODEWITHSUNDEEP
sqlpostgresqlview
tonga
tonga

Reputation: 11961

Creating a view in PostgreSQL using columns in a table

In PostgreSQL, I have the following table called cap_output with three columns:

 case_id  | libcap | libcap_android | 
----------+--------+-----------------
 06112301 |   72.6 |           75.6 |

Now I want to create a View based on the 2nd and 3rd columns in cap_output table. The view will have three columns. The second column diff will be the difference between libcap and libcap_android. The third column will be TRUE if the absolute value of the diff is less than 0.5 or FALSE otherwise. So I used the following statement to create the view

CREATE VIEW score_diff AS 
    SELECT c.case_id, 
    c.libcap - c.libcap_android AS diff, 
    (CASE WHEN abs(diff) < 0.5 THEN TRUE ELSE FALSE END) AS pass
    FROM cap_output AS c;

But after creating this view, when I queried the view score_diff, I got the following result

 case_id  | diff | pass 
----------+------+------
 06112301 |   -3 | t

Obviously, the diff is -3 and it should return FALSE (f) in the pass column of the view. But it returned TRUE (t). So why do I get wrong result?

Upvotes: 3

Views: 69

Answers (2)

klin
klin

Reputation: 121604

You cannot use diff in select list. Also, case is not necessary. Try this:

CREATE VIEW score_diff AS 
    SELECT c.case_id, 
    c.libcap - c.libcap_android AS diff, 
    abs(c.libcap - c.libcap_android) < 0.5 AS pass
    FROM cap_output AS c;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

This suggests that cap_output has a column called diff. As Marc points out, the view would otherwise have a syntax error.

So, just repeat the logic:

CREATE VIEW score_diff AS 
    SELECT c.case_id, 
           (c.libcap - c.libcap_android) AS diff, 
           (CASE WHEN abs(c.libcap - c.libcap_android) < 0.5 THEN TRUE ELSE FALSE END) AS pass
    FROM cap_output AS c;

Upvotes: 2

Related Questions