從表中獲取主鍵列

我有一個postgresql 11數據庫，我正在嘗試關注這篇關於從表中獲取主鍵列的官方文章。但是，我收到一條錯誤消息，說我的表不是現有列。

ERROR:  column "tablename" does not exist
LINE 10: ...* from pg_index WHERE indisprimary AND indrelid = "tablename"...
                                                             ^

程式碼如下所示：

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                    AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = "tablename"::regclass -- <--- This is the problem
AND    i.indisprimary;

我相信這個問題與::regclass演員陣容無關，因為查看完整的表格並用"tablename"::regclassindrelid 替換數字工作正常，在我的情況下是 16490。

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                    AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 16490 -- "tablename"::regclass <--- replace with the number
AND    i.indisprimary;

關於為什麼官方範例不起作用以及如何解決它的任何想法？

如a_horse_with_no_name 所示，tablename必須在單引號內，即使雙引號用於保留大寫字母，這是我需要的。使用兩者都解決了問題

你

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                    AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = '"tablename"'::regclass -- <--- Using both single and double quotation marks
AND    i.indisprimary;

https://stackoverflow.com/questions/1214576/how-do-i-get-the-primary-keys-of-a-table-from-postgres-via-plpgsql …

如果需要架構，請稍微修改為

SELECT               
 pg_attribute.attname, 
 format_type(pg_attribute.atttypid, pg_attribute.atttypmod) 
FROM pg_index, pg_class, pg_attribute, pg_namespace 
WHERE 
 pg_class.oid = 'MY TABLE'::regclass AND 
 indrelid = pg_class.oid AND 
 nspname = 'MY CLASS' AND 
 pg_class.relnamespace = pg_namespace.oid AND 
 pg_attribute.attrelid = pg_class.oid AND 
 pg_attribute.attnum = any(pg_index.indkey)
 AND indisprimary

引用自：https://dba.stackexchange.com/questions/253709