如何使用SQL语句测试表中是否存在列

6 回答

• 24

  试试这个 ：

  SELECT column_name 
  FROM information_schema.columns 
  WHERE table_name='your_table' and column_name='your_column';
  

  回复于 2024-04-29T18:47:51+08:00
• 1

  以下是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
  

  回复于 2024-04-29T18:47:51+08:00
• 3

  接受的答案是正确的，但缺少架构和更好的输出（真/假）：

  SELECT EXISTS (SELECT 1 
  FROM information_schema.columns 
  WHERE table_schema='my_schema' AND table_name='my_table' AND column_name='my_column');
  

  回复于 2024-04-29T18:47:51+08:00
• 3

  使用PostgreSQL的object identifier types更简单（并且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)
  

  阅读significance of the columns in the manual .

  如果要构建动态SQL并且您的列名作为参数提供，则可能需要使用quote_ident()来避免SQL注入：

  ...
  AND    attname = quote_ident('myColumn');
  

  适用于 search_path 之外的表格：

  ...
  WHERE  attrelid = 'mySchema.myTable'::regclass
  ...
  

  回复于 2024-04-29T18:47:51+08:00
• 14

  与Oracle不同，PostgreSQL支持ANSI标准 INFORMATION_SCHEMA 视图 .

  Oracle的user_tab_columns的相应标准视图是 information_schema.columns

  http://www.postgresql.org/docs/current/static/infoschema-columns.html

  回复于 2024-04-29T18:47:51+08:00
• 86

  SELECT attname 
  FROM pg_attribute 
  WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'YOURTABLENAME') 
  AND attname = 'YOURCOLUMNNAME';
  

  当然，用适当的值替换 YOURTABLENAME 和 YOURCOLUMNNAME . 如果返回一行，则存在具有该名称的列，否则不存在 .

  回复于 2024-04-29T18:47:51+08:00