在Postgres中创建表时，将空列设置为带有时区的时间戳

Question

我正在尝试创建一个表，其中有一个名为decay_date的列，该列将具有空值(暂时)，但将格式化为带有时区数据类型的时间戳。如果我简单地这样做：

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

我得到一个错误:这个表稍后将与另一个表合并，该表具有相同的decay_date列，数据类型为timestamp Syntax error near 'TYPE' time zone。如何在创建表时为decay_date列设置时间戳数据类型？

Answer 1

''不是时间戳开头的有效值。而且您不能在列别名中像这样使用USING。只有在对表执行ALTER操作并更改列的类型时，才允许(也需要)这样做。

只需选择一个null值并将其转换为所需的类型：

null::timestamptz AS decay_date_lfd 

Answer 2

这是一个糟糕的语法。您必须将文本转换为所需的类型。

因此请使用空值：

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;

我会避免使用CREATE TABLE ... AS，而使用CREATE TABLE和INSERT INTO ... SELECT ...。