Setting an empty column as a timestamp with time zone when creating a table in Postgres-CodePudding

I am trying to create a table that has an column called decay_date that will have empty values (for the time being) but be formatted as a timestamp with time zone data type. If I simply do this:

CREATE TABLE my_schema.final_summed_table AS
  SELECT
   'Unstudied' AS table_name,
   r.region,
   r.state,
   r.co_fips,
   c.co_name AS county,
  'TIER 0' AS tiermetric_lfd,
  'UNASSESSED' AS val_combine_lfd,
  'Unmapped' AS mod_unmod_lfd,
  '' AS det_approx_lfd,
  '' AS decay_date_lfd TYPE TIMESTAMP WITH TIME ZONE NULL USING decay_date_lfd::TIMESTAMP
FROM my_schema.unmapped r, cnms.counties c
WHERE r.co_fips = c.co_fips

I get an error: Syntax error near 'TYPE' This table will be UNIONED later with another table that has the same decay_date column with the data type timestamp with time zone. How do I set the timestamp datatype for my decay_date column while creating my table?

CodePudding user response：

'' isn't a valid value for a timestamp to begin with. And you can't use USING like that in a column alias. That's only allowed (and needed) when you ALTER a table and change the type of a column.

Just select a null value and cast that to the desired type:

null::timestamptz AS decay_date_lfd

CodePudding user response：

That is bad syntax. You'd have to cast the literal to the desired type.

So use a NULL value:

CREATE TABLE my_schema.final_summed_table AS
  SELECT
   CAST ('Unstudied' AS text) AS table_name,
   r.region,
   r.state,
   r.co_fips,
   c.co_name AS county,
   CAST ('TIER 0' AS text) AS tiermetric_lfd,
   CAST ('UNASSESSED' AS text) AS val_combine_lfd,
   CAST ('Unmapped' AS text) AS mod_unmod_lfd,
   CAST (NULL AS text) AS det_approx_lfd,
   CAST (decay_date_lfd AS timestamp with time zone) AS decay_date_lfd
FROM my_schema.unmapped r, cnms.counties c
WHERE r.co_fips = c.co_fips;

I'd avoid CREATE TABLE ... AS and use CREATE TABLE and INSERT INTO ... SELECT ... instead.