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

Question

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.

Does the column sqkmexist in the table mapschema_127_17.layertable_2156_17? — Jim Jones
– Jim Jones, Commented Aug 12, 2021 at 10:41
The column name is area(sqkm) and it is exist in the table mapschema_127_17.layertable_2156_17 @JimJones — Ayush
– Ayush, Commented Aug 12, 2021 at 10:45
That's a very "unorthodox" column name :) Could you add the CREATE TABLE statement? — Jim Jones
– Jim Jones, Commented Aug 12, 2021 at 10:46
And then don't make everything a string. I suppose that '2813.807491', 'Rich County', '033', '49033', 1, '2664700959', 'Utah', '49', 'UT', '149106532' is supposed to be 2813.807491, 'Rich County', '033', 49033, 1, 2664700959, 'Utah', 49, 'UT', 149106532? Or are all these columns really strings? — Thorsten Kettner
– Thorsten Kettner, Commented Aug 12, 2021 at 11:26

Jim Jones · Accepted Answer · 2021-08-12 11:13:41Z

1

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";

edited Aug 12, 2021 at 11:13

answered Aug 12, 2021 at 10:53

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ayush Over a year ago

I try this but i got this error-

ERROR:  column "area(sqkm)" is of type double precision but expression is of type text LINE 5: SET "area(sqkm)"=newvalues."area(sqkm)",

Jim Jones Over a year ago

@Ayush that's a totally different issue. You're inserting a text in a double precision column.. and it is not allowed. Can you add the create table statement?

Jim Jones Over a year ago

@Ayush I just edited the query with the modification of "area(sqkm)" .

Ayush Over a year ago

I tried this with column data type text and it works, But it doesn't work with my data, can you please suggest something that i can work with double precision or some other data types also with this query.

Jim Jones Over a year ago

@Ayush have you tried my last edit? I removed the ' single quotes around the values of "area(sqkm)"

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related