SQL delete and update at the same time
Hi list, the question is in the ens of the Mail. I have a problem with adjacent polygons when converting them to lines, as I would like to only have one line in the boundaries between the former polygons. Now it could be up to 5. I’ve been thinking of: 1. Creating a thin buffer around the line strings with the featureId from the line strings as a new table. 2. Then I should break the line strings to line segments into a new Table where the line segments gets the featureId from the line string. 3. Create a column (edited) in the new line-segment Table with a default value of 0 4. Then delete from the line segments table all segments that are within the buffer, but not the line segments that have the same featureId as the current buffer and edited value must be 0 to be deleted. 5. The line segments that are not deleted in the current buffer shall be updated (edited = 1). 6. Do step 4 and 5 until the all objects in the buffered table are executed. I’ve come this far: DELETE FROM "LineSegments" USING buffered as h WHERE ST_Contains(h.the_geom, " LineSegments".the_geom) AND "LineSegments".edited = 0 AND NOT h.fid=" LineSegments".fid; But how can I set the edited vale = 1 on the objects (line segments) that are not deleted (in the current buffer) at the same time so it won’t be deleted in the next run with an adjacent buffer? Kind regards, Paul
SV: SQL delete and update at the same time
Hi Michael, Thanks for taking interest in my problem. My biggest problem is that I’m not so familiar with SQL, and having problems to see how I can realize your suggestion. I’ve been testing in pgAdmin with 2 tables: linesegments and buffered. linesegments (splitted line strings into segments) fid (integer, the same value for all line segments from the original line) the_geom (Postgis geometry LineString) edited, integer set to 0 (afterwards, 1 = shall be deleted, 2 = not to be deleted) gid (integer, PRIMARY key) buffered (polygons from bluffering original lines, thin buffer fid (integer, the same value as the original line strings) the_geom (Postgis geometry Polygon) gid (integer, PRIMARY key) This is what I’ve tried in pgAdmin4 from reading your comment UPDATE linesegments l set edited = CASE WHEN l.gid IN (SELECT li.gid FROM linesegments li, buffered as b WHERE ST_Contains(b.the_geom, li.the_geom) AND (l.edited = 0 OR l.edited = null) AND NOT b.fid=l.fid) THEN WHEN l.gid IN (SELECT li.gid FROM linesegments li, buffered as b WHERE ST_Contains(b.the_geom, li.the_geom) AND (l.edited = 0 OR l.edited = null) AND b.fid=l.fid) THEN 2 END But the line segments that are within several buffer-polygon is set to 1 and the line segments that are within only one buffer polygon is set to 2. I thought this SQL-command would at least have on segment line set to 2 where there are duplicates. And it is very slow, although I created gist index on the two tables. Can you see what I’m doing wrong? Kind regards, Paul Från: Michael Lewis [mailto:mle...@entrata.com] Skickat: den 24 juni 2020 21:33 Till: Malm, Paul (Operations AIM) Kopia: PostgreSQL General Ämne: Re: SQL delete and update at the same time But how can I set the edited vale = 1 on the objects (line segments) that are not deleted (in the current buffer) at the same time so it won’t be deleted in the next run with an adjacent buffer? You might want to create a temporary table to hold unique identifiers of all records that you want to either delete or edit, and make use of a RETURNING clause to determine which got deleted, then update the records NOT IN that returning clause values (or use EXCEPT). Perhaps that would get you where you want to end up.
psql \copy
Hi. I'm trying to execute this command: C:\P_bin\psql -h 10.233.37.24 -p 5432 -U tester -d test -w -c "\copy public.BadenWuerttemberg_20181011 FROM C:\data\Baden-Wuerttemberg.csv' WITH DELIMITER ';' CSV" Error: relation "public.badenwuerttemberg_20181011" does not exist. What am I Doing wrong? There is a table (badenwuerttemberg_20181011) in database test under the public schema. The table has all the columns, with the right types, as the csv-file. I'm using PostgreSQL 11 Kind regards, Paul
Re: psql \copy
Thanx, it worked! :) 4 juni 2019 kl. 11:00 skrev Pavel Stehule mailto:pavel.steh...@gmail.com>>: Hi út 4. 6. 2019 v 10:50 odesílatel mailto:paul.m...@lfv.se>> napsal: Hi. I’m trying to execute this command: C:\P_bin\psql -h 10.233.37.24 -p 5432 -U tester -d test -w -c "\copy public.BadenWuerttemberg_20181011 FROM C:\data\Baden-Wuerttemberg.csv' WITH DELIMITER ';' CSV" Error: relation "public.badenwuerttemberg_20181011" does not exist. What am I Doing wrong? looks like case sensitive identifier is used. try "\copy public.""BadenWuerttemberg_20181011"" ... Regards Pavel Stehule There is a table (badenwuerttemberg_20181011) in database test under the public schema. The table has all the columns, with the right types, as the csv-file. I’m using PostgreSQL 11 Kind regards, Paul
delimeters psql /CSV
Hi, I have a problem with psql and CSV. C:\Users\an\bin>C:\Tmp\psql -h 10.211.43.22 -p 5432 -U postgres -d aatest -w -c "\copy public.""Bayern"" FROM 'C:\Users\an\test\Bayern.csv' WITH DELIMITER ';' CSV" Error: extra data after expected last column CONTEXT: COPY Bayern, row 1: "48.456;11.8008333;Anglberg/Amper;Industrial plant;722;220;220;2133;0;0;Undefined..." I have a table (Bayern) with all columns in the right format. There is a column-value which has a '/' in the string (Anglberg/Amper). I think that '/' is taken for a delimeter, since when I replace / with 'white space' it works. I use ';' as delimeter. I have UTF-8 encoding in the db. Is there a way around this or do I have to go through all csv files and change '/' to white space? Kind regards, Paul
SV: delimeters psql /CSV
Sorry, I recognized that it was not "/" who caused the problem. I'm doing this in a java program and if I pause the program before running psql.exe and just open the csv file in windows with Excel and close it (without doing anything else) and then let the program run again. Then it works! Could it have something to do with encoding. I have no idea. Kind regards, Paul -Ursprungligt meddelande- Från: Daniel Verite [mailto:dan...@manitou-mail.org] Skickat: den 7 juni 2019 14:03 Till: Malm, Paul (Operations AIM) Kopia: pgsql-general@lists.postgresql.org Ämne: Re: delimeters psql /CSV Paul Malm wrote: > C:\Users\an\bin>C:\Tmp\psql -h 10.211.43.22 -p 5432 -U postgres -d aatest -w > -c "\copy public.""Bayern"" FROM 'C:\Users\an\test\Bayern.csv' WITH > DELIMITER ';' CSV" > Error: extra data after expected last column > CONTEXT: COPY Bayern, row 1: > "48.456;11.8008333;Anglberg/Amper;Industrial > plant;722;220;220;2133;0;0;Undefined..." > > I have a table (Bayern) with all columns in the right format. There is a > column-value which has a '/' in the string (Anglberg/Amper). I think that > '/' is taken for a delimeter, since when I replace / with 'white space' it > works. There's no reason for '/' to be taken as a delimiter. Can you can share the table definition and the offending line as an attachment, so that someone can try to reproduce this with the exact same data? Also mention your PostgreSQL version and the shell the command is invoked from (cmd.exe, powershell, something else?) just in case it matters. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
Help with a selection
Hi, I have a column named col (varchar) col could look like this 902930 902920 902900 903110 913210 913200 ... I would like to remove an object that doesn't end '00' and if there are objects that start with the same 4 charcters and ending with 00. All objects ending with 00 shall remain. All object not ending with 00 shall remain of there is no object starting with the same 4 characters and ending with 00 The result of the col table should be: 902900 903110 913200 903110 is not removed because there is no 903100 913210 is removed because there is a 913200 902930 and 902920 are removed because there is 902900 I hope you understand the logic , perhaps there is no logic in my explanation. Thanks in advance, Paul
Memory alloc exception
Hi list, I get this error when running a SQL statement in my Java application. ERROR: Invalid memory alloc request size 1683636507 Location: File: d:\pginstaller.auto\postgres.windows-x64\src\backend\utils\mmgr\mcxt.c, Routine: MemoryContextAlloc, Line: 779 Server SQLState: XX000 I think it has something with to do with PostgreSQL This is how I had my postgrsql.conf: shared_buffers = 128MB #huge_pages = try #temp_buffers = 8MB #max_prepared_transactions = 0 #work_mem = 6MB work_mem = 64MB #maintenance_work_mem = 64MB maintenance_work_mem = 64MB Then I changed to this postgrsql.conf: shared_buffers = 4096MB #huge_pages = try #temp_buffers = 8MB #max_prepared_transactions = 0 #work_mem = 6MB work_mem = 256MB #maintenance_work_mem = 64MB maintenance_work_mem = 256MB And got this error instead: Org.postgresql.util.PSQLException: AnI/O error occurred while sending to the backend. Any ideas? Kind regards, Paul
