Postgresql: Find the average in millions, extract the year from (yyyy-mm-dd) date column, and use where

You have two from clauses and that's confusing the parser.

SELECT EXTRACT(year FROM date) as year from table,
                                       ^^^^^^^^^^

AVG(employment_1000s)/1000 as avg_employment_millions FROM table
                                                      ^^^^^^^^^^
WHERE table.state = 'California'

AND table.industry = 'Gov'

AND year BETWEEN 2005 AND 2006;

Remove the first one.

SELECT
  EXTRACT(year FROM date) as year,
  AVG(employment_1000s)/1000 as avg_employment_millions
FROM "table"
WHERE state = 'California'
  AND industry = 'Gov'
  AND year BETWEEN 2005 AND 2006;

However, that won't work. You're using an aggregate function, avg, without aggregating with a group by. Second, you can't use a derived column, year, in a where clause.

You have to group by year (I assume), and repeat the extraction.

SELECT
  EXTRACT(year FROM date) as year,
  AVG(employment_1000s)/1000 as avg_employment_millions
FROM states
WHERE state = 'California'
  AND industry = 'Gov'
  AND extract(year from date) between 2005 and 2006
GROUP BY EXTRACT(year FROM date)

Or do the grouping in a CTE and then select from that.

with california_gov_years as (
  SELECT
    EXTRACT(year FROM date) as year,
    AVG(employment_1000s)/1000 as avg_employment_millions
  FROM states
  WHERE state = 'California'
    AND industry = 'Gov'
  group by year
)
select *
from california_gov_years
where year between 2005 and 2006

Demonstration.

Note, year and date are SQL keywords. Avoid using them as column names, they can cause confusion. They're also not descriptive of what the date is. Consider, for example, created_on instead.