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

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;

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

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;

Collectives™ on Stack Overflow

Creating a view in PostgreSQL using columns in a table

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related