我正在嘗試創建一個表，該表有一個名為的列，該列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
我收到一個錯誤：Syntax error near 'TYPE'此表稍后將與另一個表聯合，該表具有相同的decay_date列，資料型別為帶時區的時間戳。decay_date創建表時如何為我的列設定時間戳資料型別？
uj5u.com熱心網友回復：
''不是開始時時間戳的有效值。并且您不能USING在列別名中使用那樣的方法。這僅在您ALTER創建表并更改列型別時才允許（并且需要）。
只需選擇一個null值并將其轉換為所需的型別：
null::timestamptz AS decay_date_lfd
uj5u.com熱心網友回復：
那是糟糕的語法。您必須將文字轉換為所需的型別。
所以使用 NULL 值：
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 TABLEandINSERT INTO ... SELECT ...代替。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/328560.html
標籤：PostgreSQL 时间戳 sql数据类型