Syntax error near "AS"

Question

I am trying to execute the below statement, and I'm getting an error stating

ERROR: syntax error at or near "AS"

Below is my SQL statement

CREATE TEMPORARY TABLE IF NOT EXISTS temp_users 
AS 
(SELECT patient.patientid as patient 
 FROM patients 
 WHERE patient.name = "Ann");

My Postgres version is 9.5.6. I tried many ways but failed. Does anyone know the fix for this?

Answer 1

You are selecting from a table named patients so the reference to patient in patient.patientid is invalid, the same for patient.name (although I don't know why the error references the as, it should rather be "missing FROM-clause entry for table "patient"")

Also: string constants need to be put in single quotes, not double quotes. And the parentheses around the select are useless.

The following should work:

CREATE TEMPORARY TABLE IF NOT EXISTS temp_users 
AS 
SELECT p.patientid as patient 
FROM patients p
WHERE p.name = 'Ann';

Online example: http://rextester.com/PVMT64289