select c.relname as "表名", cast(obj_description(c.oid) as varchar) as "表注释", a.attnum as "序号", a.attname as "字段名称", d.description as "字段注释", concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '(.*)')) as "字段类型" from pg_attribute a left join pg_description d on d.objoid = a.attrelid and d.objsubid = a.attnum left join pg_class c on a.attrelid = c.oid left join pg_type t on a.atttypid = t.oid where a.attnum >= 0 and c.relname in('ods_vem_busi_base') order by c.relname desc, a.attnum asc;这段pgsql查出的表结构与真实表结构不一致，对比真实表结构字段翻倍这是为什么

这种情况可能是由于表中存在了多个相同的字段导致的。在 PostgreSQL 中，允许在一个表中定义多个同名的字段，只要它们的数据类型、约束条件等属性都相同。而这段 SQL 查询语句是基于系统表 pg_attribute 来获取表结构信息的，它会将每个同名的字段都列出来，因此导致了结果集中每个字段都重复出现了多次。如果想要查看真实的表结构，可以使用 \d+ 命令或者在 pgAdmin 等图形化界面中查看。

相关问题

你能把pg视图改为sqlserver的视图吗 SELECT c.relname AS table_name, a.attname AS column_name, d.description AS column_comment, CASE WHEN a.attnotnull AND con.conname IS NULL THEN 1 ELSE 0 END AS is_required, CASE WHEN con.conname IS NOT NULL THEN 1 ELSE 0 END AS is_pk, a.attnum AS sort, CASE WHEN "position"(pg_get_expr(ad.adbin, ad.adrelid), ((c.relname::text || '_'::text) || a.attname::text) || '_seq'::text) > 0 THEN 1 ELSE 0 END AS is_increment, btrim( CASE WHEN t.typelem <> 0::oid AND t.typlen = '-1'::integer THEN 'ARRAY'::text ELSE CASE WHEN t.typtype = 'd'::"char" THEN format_type(t.typbasetype, NULL::integer) ELSE format_type(a.atttypid, NULL::integer) END END, '"'::text) AS column_type FROM pg_attribute a JOIN (pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid) ON a.attrelid = c.oid LEFT JOIN pg_description d ON d.objoid = c.oid AND a.attnum = d.objsubid LEFT JOIN pg_constraint con ON con.conrelid = c.oid AND (a.attnum = ANY (con.conkey)) LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum LEFT JOIN pg_type t ON a.atttypid = t.oid WHERE (c.relkind = ANY (ARRAY['r'::"char", 'p'::"char"])) AND a.attnum > 0 AND n.nspname = 'public'::name ORDER BY c.relname, a.attnum

抱歉，我无法将此查询转换为SQL Server的视图。这是因为该查询涉及到了一些PostgreSQL特定的函数和表达式，这些在SQL Server中并不存在。你可能需要手动重新编写查询以适应SQL Server的语法和函数。以下是一个可能的SQL Server等效查询的示例，供参考：

SELECT 
    t.name AS table_name,
    c.name AS column_name,
    CAST(ep.value AS NVARCHAR(MAX)) AS column_comment,
    CASE
        WHEN c.is_nullable = 0 AND pk.constraint_column_id IS NULL THEN 1
        ELSE 0
    END AS is_required,
    CASE
        WHEN pk.constraint_column_id IS NOT NULL THEN 1
        ELSE 0
    END AS is_pk,
    c.column_id AS sort,
    CASE
        WHEN COLUMNPROPERTY(c.object_id, c.name, 'IsIdentity') = 1 THEN 1
        ELSE 0
    END AS is_increment,
    CASE
        WHEN t.name = 'varchar' AND c.max_length <> -1 THEN t.name + '(' + CAST(c.max_length AS NVARCHAR(MAX)) + ')'
        WHEN t.name = 'nvarchar' AND c.max_length <> -1 THEN t.name + '(' + CAST(c.max_length / 2 AS NVARCHAR(MAX)) + ')'
        ELSE t.name
    END AS column_type
FROM sys.tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
LEFT JOIN sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.is_primary_key = 1
LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND c.column_id = ic.column_id
LEFT JOIN sys.key_constraints pk ON t.object_id = pk.parent_object_id AND ic.index_column_id = pk.unique_index_id
WHERE t.type = 'U'
AND SCHEMA_NAME(t.schema_id) = 'dbo'
ORDER BY t.name, c.column_id

请注意，这只是一个示例查询，具体取决于你的数据库结构和需求，你可能需要进行一些调整和修改。建议在转换之前备份你的数据库，以防出现意外情况。

select tablename from pg_tables where schemaname='public' and tablename not in (SELECT child.relname AS child_table FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace) order by 1;

这是一条 PostgreSQL 数据库查询语句，用于查找指定 schema（在此为 public）下的所有表名，并排除继承关系的表。其具体作用如下：

1. 通过 `pg_tables` 系统目录表，筛选指定 schema（在此为 public）下的所有表名，使用 `schemaname` 和 `tablename` 进行筛选。
2. 使用子查询查找继承关系的表，即父表和子表，使用 `pg_inherits` 系统目录表来查找这个关系。通过 `inhparent` 和 `inhrelid` 可以找到父表和子表的 `oid`。然后使用 `pg_class` 系统目录表找到表的详细信息，包括表名和 schema。最后，使用 `pg_namespace` 系统目录表来查找 schema 的详细信息，并与表的详细信息一起使用以获取完整的表名。
3. 将不在子查询结果中的所有表名按照字母顺序排序，并返回结果。

该查询语句的作用是查找指定 schema 下的所有非继承关系的表名。在数据迁移过程中，可以使用此查询来列出需要进行数据清理和转换的表，以便执行后续的数据迁移操作。