![5f503b424666f1cb9f29762aed3ae5ba.png](https://i-blog.csdnimg.cn/blog_migrate/5a9dcb1d1f0fab7487d1b3cdcd68dbd4.png)
今天在导出pgsql数据库字典时,运行sql出现很奇怪的问题,报错内容如下:
SQL 错误 [42601]: ERROR: syntax error at or near "select "
位置:1
![94206c74fce25909c240353ed55de824.png](https://i-blog.csdnimg.cn/blog_migrate/569d82453a23db94695d846116502659.jpeg)
sql语句如下:
select
a."数据库名" as 数据库名,
a."表名" as 表名,
a."表中文描述" as 表中文描述,
b.typname as 表类型,
'' as 表创建时间,
a."字段编号" as 字段编号,
a."字段名" as 字段名,
a."字段描述" as 字段描述,
a."字段类型" as 字段类型,
a."数据长度" as 数据长度,
d.adrename as 默认值,
a."是否为空字段" as 是否为空字段,
c.indexrelid as 是否索引字段,
a."是否分区字段" as 是否分区字段
from (select
pg_namespace.nspname as 数据库名,
pg_class.relname as 表名,
cast(obj_description(pg_class.relfilenode,'pg_class') as varchar) as 表中文描述,
'' as 表类型,
'' as 表创建时间,
pg_attribute.attnum as 字段编号,
pg_attribute.attname as 字段名,
col_description(pg_attribute.attrelid,pg_attribute.attnum) as 字段描述,
format_type(pg_attribute.atttypid,pg_attribute.atttypmod) as 字段类型,
(case when pg_attribute.attlen > 0 then pg_attribute.attlen else pg_attribute.atttypmod - 4 end) as 数据长度,
'' as 默认值,
pg_attribute.attnotnull as 是否为空字段,
'' as 是否索引字段,
'0' as 是否分区字段,
pg_attribute.attrelid as attrelid,
pg_class.reltype as reltype
from pg_attribute ,pg_class,pg_namespace where pg_attribute.attrelid = pg_class.oid and
pg_namespace.oid = pg_class.relnamespace and pg_attribute.attnum > 0 and pg_class.relkind = 'r' and
pg_namespace.nspname = 'public') as a -- 引号更改成自己的数据库名称
left join pg_type b on a.reltype = b.oid
left join pg_index c on concat(a.attrelid,a.字段编号) = concat(c.indrelid,c.indkey)
left join (select adrelid,adnum, pg_get_expr(adbin, adrelid) as adrename from pg_attrdef)
as d on concat(a.attrelid,a.字段编号) = concat(d.adrelid,d.adnum)
通过对报错的分析得知在select附件的语法出现错误。
但是仔细观察sql,并不存在sql的语法错误。之后逐行全选时查看到如下现象:
![a5d51454e81ba0ebb08b128f88dafe6b.png](https://i-blog.csdnimg.cn/blog_migrate/ce59524131466b5851c0521c58aa533f.png)
细心的同学会发现,在逗号后面多出了一个空格。但是在pgsql中运行一般的sql语句:如select a.id, a.name from table a即便是逗号后面有很多个空格,该sql运行也不会出现语法错误。
解决方法,在将select返回集中的空格都删除后,该条sql运行正常:
![9e945cfbc60038b1df040af275eb2831.png](https://i-blog.csdnimg.cn/blog_migrate/fd9a1a8915e4f244bcbecb02568d2c46.jpeg)
具体导致原因不是很清楚,希望有对pgsql了解的大神能够解惑。