Creating a view in PostgreSQL using columns in a table

Question

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?

generally speaking, you can't use an alias in the same field list it's being defined in. as written your view definition SHOULD have died with a syntax error. — Marc B
– Marc B, Commented Aug 10, 2015 at 18:35
@MarcB I executed the CREATE query without any syntax error message (Pg 9.4). So how can I refer the 2nd diff column in the pass column in Postgres? — tonga
– tonga, Commented Aug 10, 2015 at 18:39

Gordon Linoff · Accepted Answer · 2015-08-10 18:38:44Z

2

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;

answered Aug 10, 2015 at 18:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tonga Over a year ago

This is indeed the cause of the problem. I have a column diff in the table cap_output (just omitted it in my original question, thought it was irrelevant).

klin · Accepted Answer · 2015-08-10 18:43:36Z

1

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;

answered Aug 10, 2015 at 18:43

klin

123k15 gold badges240 silver badges262 bronze badges

1 Comment

tonga Over a year ago

Thanks. This is simpler than mine.

Collectives™ on Stack Overflow

Creating a view in PostgreSQL using columns in a table

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related