CODEWITHSUNDEEP
sqlpostgresqlpostgis
Ayush
Ayush

Reputation: 19

How to execute this query in postgresql database i was in trouble to execute that

How to solve this error, I was in trouble to run that query is anyone please help to execute that query with all the attributes.

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

Error:

ERROR: column "sqkm" does not exist LINE 1: ...able_2156_17 as a SET "area(sqkm)"=newvalues.area(sqkm),"cou... ^

Where area(sqkm) is a column name with double precision data type.

Upvotes: 0

Views: 68

Answers (1)

Jim Jones
Jim Jones

Reputation: 19643

The usage of special characters in object names is allowed - by means of wrapping it up with quotes " -, but quite often it leads to confusion. This example with very strange column names might make things clearer:

Demo: 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

This should fix your 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";

Upvotes: 1

Related Questions