PostgreSQL 不支持 COUNT(<DYNAMIC_TYPE>) 函数。使用合适的函数或创建用户定义的函数

Question

从oracle数据库迁移到postgres时。一个过程是在oracle中使用select语句插入数据。以下是查询

INSERT INTO some_table (source_name,
unique_id,
source_system_name,
series_definition_code,
obsrv_dtm,
obsrv_cd,
update_dtm,
rnk,
file_count)
SELECT   source_name,
unique_id,
source_system_name,
series_definition_code,
obsrv_dtm,
obsrv_code,
update_dtm,
rnk,
file_count
FROM   (SELECT   source_name,
unique_id,
source_system_name,
series_definition_code,
obsrv_dtm,
obsrv_code,
update_dtm,
ROW_NUMBER ()
OVER (
PARTITION BY series_definition_code,
obsrv_dtm,
SUBSTR (
obsrv_code,
1,
INSTR (obsrv_code, '.', 1)
- 1
)
|| '.'
|| SUBSTR (obsrv_code,
INSTR (obsrv_code,
'.',
1,
2)
+ 1)
ORDER BY update_dtm DESC NULLS LAST
)
rnk,
COUNT (
DISTINCT update_dtm
)
OVER (
PARTITION BY series_definition_code,
obsrv_dtm,
SUBSTR (
obsrv_code,
1,
INSTR (obsrv_code, '.', 1)
- 1
)
|| '.'
|| SUBSTR (obsrv_code,
INSTR (obsrv_code,
'.',
1,
2)
+ 1)
/*MQC 1901*/
)
file_count
FROM   (SELECT                             /*+ ORDERED */
'STAGE' AS source_name,
b.obsrv_id AS unique_id,
a.extract_src_sys_name
AS source_system_name,
series_def_series_cd
AS series_definition_code,
obsrv_dtm AS obsrv_dtm,
b.obsrv_cd AS obsrv_code,
update_dtm AS update_dtm
FROM   table1 c,
table2 a,
table3 b
WHERE       a.actl_id = c.actl_id
AND a.partition_id = c.partition_id
AND a.actl_ref_id = b.actl_ref_id
AND a.partition_id = b.partition_id
AND c.partition_id = 4
AND c.property_value = 'refreshdata'
AND a.partition_id = 4
AND a.extract_src_sys_name = 'Bentek'
AND b.partition_id = 4
AND b.record_deleted_flag = 'N')
stage_suspend) all_rows
WHERE   all_rows.rnk <> 1 AND file_count > 1

Answer 1

DISTINCT不是为Postgres中的窗口函数实现的(至少在您的版本中是这样(，所以您需要重写它而不需要计数(DISTINCT…(over((