今天在導(dǎo)出pgsql數(shù)據(jù)庫(kù)字典時(shí)，運(yùn)行sql出現(xiàn)很奇怪的問(wèn)題，報(bào)錯(cuò)內(nèi)容如下：

SQL 錯(cuò)誤 [42601]: ERROR: syntax error at or near "select?"

位置：1

圖1

sql語(yǔ)句如下：

select

a."數(shù)據(jù)庫(kù)名" as 數(shù)據(jù)庫(kù)名,

a."表名" as 表名,

a."表中文描述" as 表中文描述,

b.typname as 表類(lèi)型,

'' as 表創(chuàng)建時(shí)間,

a."字段編號(hào)" as 字段編號(hào),

a."字段名" as 字段名,

a."字段描述" as 字段描述,

a."字段類(lèi)型" as 字段類(lèi)型,

a."數(shù)據(jù)長(zhǎng)度" as 數(shù)據(jù)長(zhǎng)度,

d.adrename as 默認(rèn)值,

a."是否為空字段" as 是否為空字段,

c.indexrelid as 是否索引字段,

a."是否分區(qū)字段" as 是否分區(qū)字段

from (select

pg_namespace.nspname as 數(shù)據(jù)庫(kù)名,

pg_class.relname as 表名,

cast(obj_description(pg_class.relfilenode,'pg_class') as varchar) as 表中文描述,

'' as 表類(lèi)型,

'' as 表創(chuàng)建時(shí)間,

pg_attribute.attnum as 字段編號(hào),

pg_attribute.attname as 字段名,

col_description(pg_attribute.attrelid,pg_attribute.attnum) as 字段描述,

format_type(pg_attribute.atttypid,pg_attribute.atttypmod) as 字段類(lèi)型,

(case when pg_attribute.attlen > 0 then pg_attribute.attlen else pg_attribute.atttypmod - 4 end) as 數(shù)據(jù)長(zhǎng)度,

'' as 默認(rèn)值,

pg_attribute.attnotnull as 是否為空字段,

'' as 是否索引字段,

'0' as 是否分區(qū)字段,

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 -- 引號(hào)更改成自己的數(shù)據(jù)庫(kù)名稱(chēng)

left join pg_type b on a.reltype = b.oid

left join pg_index c on concat(a.attrelid,a.字段編號(hào)) = 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.字段編號(hào)) = concat(d.adrelid,d.adnum)

通過(guò)對(duì)報(bào)錯(cuò)的分析得知在select附件的語(yǔ)法出現(xiàn)錯(cuò)誤。

但是仔細(xì)觀察sql，并不存在sql的語(yǔ)法錯(cuò)誤。之后逐行全選時(shí)查看到如下現(xiàn)象：

圖2

? ? ? ? ? ? ?細(xì)心的同學(xué)會(huì)發(fā)現(xiàn)，在逗號(hào)后面多出了一個(gè)空格。但是在pgsql中運(yùn)行一般的sql語(yǔ)句:如select a.id,? ? ? a.name from table a即便是逗號(hào)后面有很多個(gè)空格，該sql運(yùn)行也不會(huì)出現(xiàn)語(yǔ)法錯(cuò)誤。

解決方法，在將select返回集中的空格都刪除后，該條sql運(yùn)行正常：

圖3

具體導(dǎo)致原因不是很清楚，希望有對(duì)pgsql了解的大神能夠解惑。