A few tips & tricks for properly managing views with Postgres:

  1. Name the return values with AS
  2. Type constant values by prefixing them with their type

For example consider the following:

$ CREATE VIEW myview AS SELECT 'bar';
WARNING:  column "?column?" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW

Here’s what the Postgres documentation says about it:

Be careful that the names and types of the view’s columns will be assigned the way you want. For example:

  CREATE VIEW vista AS SELECT 'Hello World';

is bad form in two ways: the column name defaults to ?column?, and the column data type defaults to unknown. If you want a string literal in a view’s result, use something like:

  CREATE VIEW vista AS SELECT text 'Hello World' AS hello;

First we’ll name our string to get rid of the “?column?” name:

$ CREATE VIEW myview AS SELECT 'bar' AS bar;
WARNING:  column "bar" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW

Second we set the type of our return value by prefixing with TEXT:

$ CREATE VIEW myview AS SELECT TEXT 'bar' AS bar;
CREATE VIEW

That is all.