postgresql 如何使用 SQL 语句测试表中是否存在列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9991043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I test if a column exists in a table using an SQL statement
提问by CSharpened
Is there a simple alternative in PostgreSQL to this statement produced in Oracle?
在 PostgreSQL 中有一个简单的替代方法来替代 Oracle 中生成的这个语句吗？
select table_name from user_tab_columns
where table_name = myTable and column_name = myColumn;
I am then testing whether the query returns anything so as to prove the column exists.
然后我测试查询是否返回任何内容以证明该列存在。
I am aware that using psql I can find these out individually but this is required to produce a result in a program I am writing to validate that a requested attribute field exists in my database table.
我知道使用 psql 我可以单独找到这些，但这需要在我编写的程序中生成结果以验证我的数据库表中是否存在请求的属性字段。
回答by Ramandeep Singh
Try this :
尝试这个 ：
SELECT column_name
FROM information_schema.columns
WHERE table_name='your_table' and column_name='your_column';
回答by juan Isaza
Accepted answer is correct, but is missing the schema and nicer output (True/False):
接受的答案是正确的，但缺少架构和更好的输出（真/假）：
SELECT EXISTS (SELECT 1
FROM information_schema.columns
WHERE table_schema='my_schema' AND table_name='my_table' AND column_name='my_column');
回答by Erwin Brandstetter
This is simpler (and SQLi-safe) with PostgreSQL's object identifier types:
使用 PostgreSQL 的对象标识符类型更简单（并且 SQLi 安全）：
SELECT TRUE
FROM pg_attribute
WHERE attrelid = 'myTable'::regclass -- cast to a registered class (table)
AND attname = 'myColumn'
AND NOT attisdropped -- exclude dropped (dead) columns
-- AND attnum > 0 -- exclude system columns (you may or may not want this)
Read about the significance of the columns in the manual.
If you are building dynamic SQL and your column name is supplied as parameter, you might want to use quote_ident()to avoid SQL injection:
如果您正在构建动态 SQL 并且您的列名作为参数提供，您可能希望使用quote_ident()以避免 SQL 注入：
...
AND attname = quote_ident('myColumn');
Works for tables outside the search_path, too:
也适用于 之外的表search_path：
...
WHERE attrelid = 'mySchema.myTable'::regclass
...
回答by aleroot
SELECT attname
FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'YOURTABLENAME')
AND attname = 'YOURCOLUMNNAME';
Of course, replace YOURTABLENAMEand YOURCOLUMNNAMEwith the proper values. If a row is returned, a column with that name exists, otherwise it does not.
当然，用正确的值替换YOURTABLENAME和YOURCOLUMNNAME。如果返回一行，则存在具有该名称的列，否则不存在。
回答by a_horse_with_no_name
Unlike Oracle, PostgreSQL supports the ANSI standard INFORMATION_SCHEMAviews.
与 Oracle 不同，PostgreSQL 支持 ANSI 标准INFORMATION_SCHEMA视图。
The corresponding standard view to Oracle's user_tab_columns is information_schema.columns
Oracle 的 user_tab_columns 对应的标准视图是 information_schema.columns
http://www.postgresql.org/docs/current/static/infoschema-columns.html
http://www.postgresql.org/docs/current/static/infoschema-columns.html
回答by user2434435
Here is a similar variant of Erwin Brandstetter answer. Here we check schema too in case we have similar tables in different schema.
这是 Erwin Brandstetter 答案的类似变体。在这里我们也检查架构，以防我们在不同架构中有相似的表。
SELECT TRUE FROM pg_attribute
WHERE attrelid = (
SELECT c.oid
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE
n.nspname = CURRENT_SCHEMA()
AND c.relname = 'YOURTABLENAME'
)
AND attname = 'YOURCOLUMNNAME'
AND NOT attisdropped
AND attnum > 0
