Type cast a field created on-the-fly using AS "new_field" in a SELECT subquery

Question

Context

I have a really tiny issue while creating a PostgreSQL table using a SELECT sub-query where I'd like to initialize a field with NULL values of type INTEGER (they default to TEXT if I do not specify the type).

Enough talking, here is the piece of code with the background explanations:

CREATE TABLE my_table AS
  SELECT
    0 AS fid, -- initiate new field here, it doesn't exist in the table where the select is run on
    gid,
    min(distance) AS mindist
  FROM
    schema.points
  GROUP BY
    gid;

This is working as expected, I've got 0 everywhere in the fid field that I need to create at this time.

But I'm not quite happy with 0 values because they will have a significant meaning in further computations, hence I'd like to initialize this fid field with NULL values (don't worry, it's not a pkey).

Therefore, I've changed it to this:

CREATE TABLE my_table AS
  SELECT
    NULL AS fid, -- initiate new field here, it doesn't exist in the table where the select is run on
    gid,
    min(distance) AS mindist
  FROM
    schema.points
  GROUP BY
    gid;

And it's working well!
Except that now the field is a TEXT field (it looks like it's the "default" type when NULL is used), and I really need it as an INTEGER (I'm running into further trouble if I let it as TEXT).

Question

How could I set (i.e. cast, technically speaking) the fid field to NULL and of type INTEGER at the same time in this particular query?

None of these works (I'm facing syntax errors each time):

• NULL AS fid::INTEGER
• INTEGER NULL AS fid
• NULL INTEGER AS fid

Environment

PostgreSQL 10.12

Answer 1

You actually have to set the type cast after null, using:

CREATE TABLE my_table AS
  SELECT
    NULL::INTEGER AS fid, -- initiate new field here as a NULL INTEGER
    gid,
    min(distance) AS mindist
  FROM
    schema.points
  GROUP BY
    gid;

Then you will have it:

Results in pgAdmin.