时间戳

首页 > 解决方案 > 如何在 postgresql 数据库中执行此查询我无法执行该查询

sql - 如何在 postgresql 数据库中执行此查询我无法执行该查询

问题描述

如何解决此错误，我在运行该查询时遇到了麻烦，请任何人帮助执行该查询的所有属性。

PG_QUERY：-

UPDATE mapschema_127_17.layertable_2156_17 AS a 
SET "area(sqkm)" = newvalues.area(sqkm),
    "county" = newvalues.county,
    "countyfp" = newvalues.countyfp,
    "geoid" = newvalues.geoid,
    "gid" = newvalues.gid,
    "land(sqm)" = newvalues.land(sqm),
    "state" = newvalues.state,
    "statefp" = newvalues.statefp,
    "stusps" = newvalues.stusps,
    "water(sqm)" = newvalues.water(sqm) 
FROM 
    (VALUES ('2813.807491', 'Rich County', '033', '49033', 1, '2664700959', 'Utah', '49', 'UT', '149106532'),
            ('3037.725199', 'Cache County', '005', '49005', 2, '3016627502', 'Utah', '49', 'UT', '21097697'),
            ('8418.300607', 'Duchesne County', '013', '49013', 3, '8379502802', 'Utah', '49', 'UT', '38797805'),
            ('9543.91397', 'Grand County', '019', '49019', 28, '9512361692', 'Utah', '49', 'UT', '31552278'),
            ('18870.630612', 'Tooele County', '045', '49045', 29, '17979556898', 'Utah', '49', 'UT', '891073714')) AS newvalues ("area(sqkm)", "county", "countyfp", "geoid", "gid", "land(sqm)", "state", "statefp", "stusps", "water(sqm)") 
WHERE a.gid = newvalues.gid

错误：

错误：列“sqkm”不存在第 1 行：...able_2156_17 作为 SET“area(sqkm)”=newvalues.area(sqkm)，“cou... ^

wherearea(sqkm)是具有双精度数据类型的列名。

标签： sqlpostgresqlpostgis

解决方案

允许在对象名称中使用特殊字符 - 通过用引号将其包裹起来"- 但通常会导致混淆。这个列名非常奇怪的例子可能会让事情更清楚：

演示：db<>fiddle

CREATE TABLE t ("(id)" int, "#(txt)" text);
INSERT INTO t VALUES (1,'foo');

UPDATE t SET "#(txt)" = newvalues."#(txt)"
FROM (VALUES (1,'bar')) newvalues ("(id)","#(txt)")
WHERE t."(id)" = newvalues."(id)";

SELECT * FROM t;

 (id) | #(txt) 
------+--------
    1 | bar

这应该可以解决您的查询：

UPDATE mapschema_127_17.layertable_2156_17 AS a 
SET "area(sqkm)"=newvalues."area(sqkm)",
    "county"=newvalues."county",
    "countyfp"=newvalues."countyfp",
    "geoid"=newvalues."geoid",
    "gid"=newvalues."gid",
    "land(sqm)"=newvalues."land(sqm)",
    "state"=newvalues."state",
    "statefp"=newvalues."statefp",
    "stusps"=newvalues."stusps",
    "water(sqm)"=newvalues."water(sqm)" 
FROM 
  (VALUES 
    (2813.807491,'Rich County','033','49033',1,'2664700959','Utah','49','UT','149106532'),
    (3037.725199,'Cache County','005','49005',2,'3016627502','Utah','49','UT','21097697'),
    (8418.300607,'Duchesne County','013','49013',3,'8379502802','Utah','49','UT','38797805'),
    (9543.91397,'Grand County','019','49019',28,'9512361692','Utah','49','UT','31552278'),
    (18870.630612,'Tooele County','045','49045',29,'17979556898','Utah','49','UT','891073714')) AS newvalues ("area(sqkm)","county","countyfp","geoid","gid","land(sqm)","state","statefp","stusps","water(sqm)") 
WHERE a."gid" = newvalues."gid";

推荐阅读