Re: [GENERAL] UPDATE syntax change

Adam Brusselback writes: > --works > UPDATE tst_table > SET (b, c) = ('help me', 'please') > WHERE a = 0; > --does not work > UPDATE tst_table > SET (b) = ('help me') > WHERE a = 0; > So there was a change made, and you now cannot use the multi-column > syntax if you're only updating a single col

Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax fails with single column)

Appreciate the link, didn't come up when I was googling the issue. As you said, a mention in the release notes would have been helpful. Thanks, -Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/p

Re: [GENERAL] UPDATE syntax change (column-list UPDATE syntax fails with single column)

On Mon, Oct 30, 2017 at 12:29:03PM -0400, Adam Brusselback wrote: > I have some queries that were working in 9.6 which suddenly broke when > moving to 10. > > Digging in, the error i'm getting is: ERROR: source for a > multiple-column UPDATE item must be a sub-SELECT or ROW() expression > So the

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

Thanks for the explanation! Can these checks be implemented or the data needed is not there and adding it will only add an overhead for the majority of use cases? 2017-07-19 20:42 GMT+02:00 Tom Lane : > Luca Looz writes: > > After some tests it seems that this happens when the same row is covere

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

Luca Looz writes: > Thanks for the explanation! > Can these checks be implemented or the data needed is not there and adding > it will only add an overhead for the majority of use cases? It's hard to see how to do much better than we're doing without storing more data on-disk than is there now.

Re: [GENERAL] UPDATE column without FK fires other FK triggers constraint check

Luca Looz writes: > After some tests it seems that this happens when the same row is covered by > more than 1 update in the same transaction even without any change. > Is this an expected behavior? Why it happens? Yes, see comment in RI_FKey_fk_upd_check_required: * If the original

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Yasin, thank you for this suggestion, but - On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari wrote: ​​ >> Have you tried with inner block and do nothing on exception; >> > > >> BEGIN >> >> >> .. >> BEGIN >> >> UPDATE words_social >> SET uid = out_uid

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

The convention on these lists is to inline or bottom-post. On Tue, Mar 14, 2017 at 12:07 PM, Yasin Sari wrote: > ​​ > Have you tried with inner block and do nothing on exception; > > ​I suppose that would work - though using an exception path for expected logic is messy - and considerably slower

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Have you tried with inner block and do nothing on exception; BEGIN .. BEGIN UPDATE words_social SET uid = out_uid WHERE uid = ANY(_uids); EXCEPTION WHEN OTHERS THEN --do nothing or write NULL means do nothing

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On Tue, Mar 14, 2017 at 4:55 AM, Alexander Farber wrote: > I wish there would be possibility to add ON CONFLICT DO NOTHING /* and drop > the review */ to the both UPDATE's above, but there is no such thing > described at https://www.postgresql.org/docs/9.5/static/sql-update.html Heikki wanted to

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On Tue, Mar 14, 2017 at 1:25 PM, Alexander Farber < alexander.far...@gmail.com> wrote: > > But this might give me conflicts, because there might be such a PK > already... > > You need to remove the "might" and figure out which ones will and which will not. You can update the ones will not and del

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 01:25 PM, Alexander Farber wrote: Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all, but instead only

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian - On Tue, Mar 14, 2017 at 7:18 PM, Adrian Klaver wrote: > On 03/14/2017 09:00 AM, Alexander Farber wrote: > >> My initial idea has been not to use ON CONFLICT at all, but instead only >> UPDATE the words_reviews records (set "uid" or "author" to out_uid) for >> which NO EXISTS already

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 09:00 AM, Alexander Farber wrote: My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then I am afraid the logic is escap

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

My initial idea has been not to use ON CONFLICT at all, but instead only UPDATE the words_reviews records (set "uid" or "author" to out_uid) for which NO EXISTS already such a record with PK (uid, author)... and then DELETE the rest (as I can't update them without a conflict) but I haven't figured

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 08:38 AM, Alexander Farber wrote: Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: On 03/14/2017 07:23 AM, Alexander Farber wrote: in _uids array I have all user ids of player. I want to merge his or her

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Yes, Adrian - On Tue, Mar 14, 2017 at 3:57 PM, Adrian Klaver wrote: > On 03/14/2017 07:23 AM, Alexander Farber wrote: > >> >> in _uids array I have all user ids of player. >> >> I want to merge his or her data, including reviews, to a single user id: >> out_uid. >> >> So I make a copy of related

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 07:23 AM, Alexander Farber wrote: Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has Alright I see that you are setting

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Hi Adrian, in _uids array I have all user ids of player. I want to merge his or her data, including reviews, to a single user id: out_uid. So I make a copy of related words_reviews records (where this user has been rated or this user has rated someone) and then re-INSERT ON CONFLICT DO NOTHING t

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

On 03/14/2017 06:52 AM, Alexander Farber wrote: I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid,

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

Sorry, missed the last DELETE: DELETE FROM words_reviews WHERE author <> out_uid AND author = ANY(_uids);

Re: [GENERAL] UPDATE ... ON CONFLICT DO NOTHING

I have come up with the following (when trying to merge array _uids to a single out_uid): -- try to copy as many reviews of this user as possible INSERT INTO words_reviews ( uid, author, nice,

Re: [GENERAL] update error with serializable

On Fri, Jan 20, 2017 at 11:27 AM, Rob Sargent wrote: > On 01/20/2017 10:05 AM, Kevin Grittner wrote: >> https://www.postgresql.org/message-id/flat/d8joa0eh9yw@dalvik.ping.uio.no#d8joa0eh9yw@dalvik.ping.uio.no > Configurable or dynamic? Wouldn't something related to tuples per page (and

Re: [GENERAL] update error with serializable

On 01/20/2017 10:05 AM, Kevin Grittner wrote: On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: I've two threads countinuously updataing rows in the same table. Each one does: BEGIN, UPDATE,UPDATECOMMIT There can't be two active transactions updating the same row (my bug apart but I d

Re: [GENERAL] update error with serializable

On Fri, Jan 20, 2017 at 4:44 AM, Tom DalPozzo wrote: > I've two threads countinuously updataing rows in the same table. > Each one does: BEGIN, UPDATE,UPDATECOMMIT > There can't be two active transactions updating the same row (my > bug apart but I don't think so). > I'm using default_transac

Re: [GENERAL] update records to have occurance number

HI: You can use windows functions. https://www.postgresql.org/ docs/9.5/static/tutorial-window.html specifically row_number(). https://www.postgresql.org/ docs/9.5/static/tutorial-window.html I'm assuming that your ordering is based on lname and then fname. The query for getting 'number' is san

Re: [GENERAL] update records to have occurance number

> On 25 Oct 2016, at 17:06, Mark Lybarger wrote: > > I want to update a table to have the value of the occurrence number. For > instance, I have the below table. I want to update the number column to > increment the count of last name occurrences, so that it looks like this: > > first last

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B wrote: > 2016-09-28 10:25 GMT+13:00 Patrick B : > >> >> Actually I can't use name_last or name_first because some of the rows > have name_last/name_first = null > > I'm inserting more columns that I shown: > > CREATE TABLE >> public.not_monthly >>

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 10:25 GMT+13:00 Patrick B : > > > 2016-09-28 10:11 GMT+13:00 Kevin Grittner : > >> On Tue, Sep 27, 2016 at 3:33 PM, Patrick B >> wrote: >> >> > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into >> table_2.c_id >> > - This is the problem.. how can I get the inserted id fro

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 10:11 GMT+13:00 Kevin Grittner : > On Tue, Sep 27, 2016 at 3:33 PM, Patrick B > wrote: > > > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into > table_2.c_id > > - This is the problem.. how can I get the inserted id from STEP2 and put > it > > into c_id respecting the ord

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B wrote: > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into table_2.c_id > - This is the problem.. how can I get the inserted id from STEP2 and put it > into c_id respecting the order? For DML you need to think of the data as being unordere

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 9:23 GMT+13:00 Kevin Grittner : > On Tue, Sep 27, 2016 at 2:59 PM, Patrick B > wrote: > > [sel is a relation which can have multiple rows; the fact that it > is being generated in a CTE isn't relevant for purposes of the > error.] > > UPDATE table_2 SET c_id = > ( > SE

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 2:59 PM, Patrick B wrote: [sel is a relation which can have multiple rows; the fact that it is being generated in a CTE isn't relevant for purposes of the error.] UPDATE table_2 SET c_id = ( SELECT c_id FROM sel ORDER BY c_id ) WHE

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Tue, Sep 27, 2016 at 1:10 PM, Igor Neyman wrote: > > > Patrick, > > > > You need to explain your problems in more “coherent” way, David suggested > one. > > If you aren’t willing, people will stop responding to your request, they > are not obligated to read your mind. > > > ​I'll put it this

Re: [GENERAL] Update two tables returning id from insert CTE Query

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick B Sent: Tuesday, September 27, 2016 4:00 PM To: David G. Johnston Cc: pgsql-general Subject: Re: [GENERAL] Update two tables returning id from insert CTE Query 2016-09-28 8:54 GMT+13:00

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 8:54 GMT+13:00 David G. Johnston : > On Mon, Sep 26, 2016 at 9:06 PM, Patrick B > wrote: > >> >> I'm doing this now: >> >> >> sel AS ( >>> SELECT i.id AS c_id >>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) >>> i >>> JOIN rows s USING (rn) >>> ) >>> UPDATE

Re: [GENERAL] Update two tables returning id from insert CTE Query

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B wrote: > > I'm doing this now: > > > sel AS ( >> SELECT i.id AS c_id >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i >> JOIN rows s USING (rn) >> ) >> UPDATE table_2 SET c_id = >> ( >> SELECT c_id >> FROM sel >> ORDER

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-28 0:29 GMT+13:00 Vitaly Burovoy : > On 9/26/16, Patrick B wrote: > > 2016-09-27 16:22 GMT+13:00 Patrick B : > > I'm doing this now: > > > > sel AS ( > >> SELECT i.id AS c_id > >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) > >> i > >> JOIN rows s USING (rn) >

Re: [GENERAL] Update two tables returning id from insert CTE Query

On 9/26/16, Patrick B wrote: > 2016-09-27 16:22 GMT+13:00 Patrick B : > I'm doing this now: > > sel AS ( >> SELECT i.id AS c_id >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) >> i >> JOIN rows s USING (rn) >> ) >> UPDATE table_2 SET c_id = >> ( >> SELECT c_id >> FR

Re: [GENERAL] Update two tables returning id from insert CTE Query

On 9/26/16, Patrick B wrote: > Hi guys, > > I've got 2k rows in a table: ... > So I'd imagine now I would do the update? How can I update table_2.c_id > with the ins_table_1.id value? > I'm using Postgres 9.2 > > Thanks > Patrick > Hello, It is not possible to change one row more than once by

Re: [GENERAL] Update two tables returning id from insert CTE Query

2016-09-27 16:22 GMT+13:00 Patrick B : > Hi guys, > > I've got 2k rows in a table: > >> CREATE TABLE >> public.not_monthly >> ( >> id BIGINT DEFAULT "nextval"('"id_seq"'::"regclass") NOT NULL, >> clientid BIGINT, >> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING

Re: [GENERAL] UPDATE OR REPLACE?

> >>> In my particular situation the case I care about is when the > result > >> of an UPDATE is two identical rows. All I really want is a DISTINCT > >> option. > >> > >> Assuming I am following correctly what you want is that the result > of > >> an UPDATE not be two identical rows. > > > > Corre

Re: [GENERAL] UPDATE OR REPLACE?

On 09/01/2016 05:08 PM, dandl wrote: In my particular situation the case I care about is when the result of an UPDATE is two identical rows. All I really want is a DISTINCT option. Assuming I am following correctly what you want is that the result of an UPDATE not be two identical rows. Corre

Re: [GENERAL] UPDATE OR REPLACE?

> > In my particular situation the case I care about is when the result > of an UPDATE is two identical rows. All I really want is a DISTINCT > option. > > Assuming I am following correctly what you want is that the result of > an UPDATE not be two identical rows. Correct. In practice I don't car

Re: [GENERAL] UPDATE OR REPLACE?

On 09/01/2016 07:37 AM, dandl wrote: Sqlite has options to handle an update that causes a duplicate key. Is there anything similar in Postgres? This is not an UPSERT. The scenario is an UPDATE that changes some key field so that there is now a duplicate key. In Sqlite this handled as: UPDAT

Re: [GENERAL] UPDATE OR REPLACE?

> > Sqlite has options to handle an update that causes a duplicate key. > Is > > there anything similar in Postgres? > > This is not an UPSERT. The scenario is an UPDATE that changes some > key > > field so that there is now a duplicate key. In Sqlite this handled > as: > > UPDATE OR IGNORE table S

Re: [GENERAL] UPDATE OR REPLACE?

On Thu, Sep 1, 2016 at 12:10 PM, dandl wrote: > Sqlite has options to handle an update that causes a duplicate key. Is > there anything similar in Postgres? > This is not an UPSERT. The scenario is an UPDATE that changes some key > field so that there is now a duplicate key. In Sqlite this handl

Re: [GENERAL] UPDATE OR REPLACE?

On Thu, Sep 1, 2016 at 12:10 PM, dandl wrote: > Sqlite has options to handle an update that causes a duplicate key. Is there > anything similar in Postgres? > This is not an UPSERT. The scenario is an UPDATE that changes some key field > so that there is now a duplicate key. In Sqlite this handled

Re: [GENERAL] Update statement results in Out of memory

Thanks for all for the good feedback, I realize that there's no problem with the configuration, and I assume that the full update statement is a bad decision by itself, so I made a batch process with single updates statements which commits every 50 single operation. What I can discover now is tha

Re: [GENERAL] Update statement results in Out of memory

You could check the max number of points in your geometries : SELECT max(ST_NumPoints(geom)) FROM ... Of course you could still have invalid / abberant geometry, which you could also check (ST_IsValid, St_IsSimple). You could solve both those hypotheses if you could perform your buffer by batch.

Re: [GENERAL] Update statement results in Out of memory

Running a multi-million row update will take a long time. It's possible you've exposed a memory leak in ST_Buffer (the older your version of GEOS, the more likely that is) but it's also possible you're just running a really long update. I find for batch processing purposes that creating fresh table

Re: [GENERAL] Update statement results in Out of memory

On 07/06/2016 02:13 AM, Ivan Bianchi wrote: Hello, I am trying to update a column using a PostGIS ST_Buffer function into a table of 4.257.769 rows, but after 6 hours, an /Out of memory/ error appears and the kernel starts killing processes until a /Kerne

Re: [GENERAL] Update multiple rows in a table with different values

Hi Tom, Thanks for your suggestion. I got it working: CREATE OR REPLACE FUNCTION s.updatefunc1(BigInt[], BigInt[]) RETURNS void as $$ BEGIN FOR i IN array_lower($1, 1) .. array_upper($1, 1) LOOP update s.t1 SET c3 = $2[i] WHERE c2 = $1[i]; END LOOP; END; $$ LAN

Re: [GENERAL] Update multiple rows in a table with different values

shankha writes: > PREPARE updatearrayplan(BigInt[], BigInt[]) AS > for i in size($1) > DO > update s.t1 > SET c3 = $2[$i] > WHERE c2 = $1[$i] > END FOR > In this prepared statement I am just trying to explain the algorithm. > I do not kn

Re: [GENERAL] Update multiple rows in a table with different values

Hi Adrian, I am using Postgres version 9.3. PREPARE updatearrayplan(BigInt[], BigInt[]) AS for i in size($1) DO update s.t1 SET c3 = $2[$i] WHERE c2 = $1[$i] END FOR In this prepared statement I am just trying to explain the algorithm. I

Re: [GENERAL] Update multiple rows in a table with different values

On Fri, Jul 1, 2016 at 10:26 AM, shankha wrote: > > PREPARE updatearrayplan(BigInt[], BigInt[]) AS > for i in size($1) > DO > update s.t1 > SET c3 = $2[$i] > WHERE c2 = $1[$i] > END FOR > > EXECUTE updatearrayplan({20, 30}, {275,

Re: [GENERAL] Update multiple rows in a table with different values

On 07/01/2016 07:26 AM, shankha wrote: Greetings, I have the following schema: CREATE TABLE "s"."t1" ( "c1" BigSerial PRIMARY KEY, "c2" BigInt NOT NULL, "c3" BigInt ) WITH (OIDS=FALSE); Unless you have a very old version of Postgres, OIDS=FALSE is the de

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi, What if we have a chain of slaves (master->slave->sec slave)? And if queries would run on each of them? or a config like master ->slave1 ->slave2 Is it ok to enable feedback on both slaves ? -- View this message in context: http://postgresql.nabble.com/Upd

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, May 12, 2016 at 9:22 PM, Viswanath wrote: > Hi, > Thank you for the explanation. > The slave queries are mostly reporting queries,which sometimes would take > 30+ running time due to complex joins and criteria. We haven't tried running > these queries on master before splitting, and it is

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi, Thank you for the explanation. The slave queries are mostly reporting queries,which sometimes would take 30+ running time due to complex joins and criteria. We haven't tried running these queries on master before splitting, and it is possible that there will be heavy updates and deletes on mast

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, May 12, 2016 at 11:14 AM, Viswanath wrote: > Hi Jeff, > Yes I am turning off autovacuum for experimental purpose only. I was > curious to know what is causing the queries to be killed when the autovacuum > is not running. I guess it had to be the pruning of HOT update chains like > you ha

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi Jeff, Yes I am turning off autovacuum for experimental purpose only. I was curious to know what is causing the queries to be killed when the autovacuum is not running. I guess it had to be the pruning of HOT update chains like you have mentioned. Also I have already tried changing the parameter

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

Hi, I am using postgres 9.5 Yes,I restarted the server after changing the autovacuum to off. Also verified that autovacuum process is not running. standby db configurations: (mostly default conf only) hot_standby = on max_standby_archive_delay = 30s max_standby_streaming_delay = 30s wal_receiver_s

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, May 12, 2016 at 6:37 AM, Viswanath wrote: > Hi, > I have the following configurations. > > On master: > autovacuum=off > vacuum_defer_cleanup_age=0 > > On slave: > hot_standby_feedback = off > max_standby_streaming_delay = 30s > > Now when I try to run a long query on slave and do some up

Re: [GENERAL] Update or Delete causes canceling of long running slave queries

On Thu, 12 May 2016 21:56 Viswanath, wrote: > Hi, > I have the following configurations. > > On master: > autovacuum=off > Have you changed it recently or it has always been off? In case you changed it recently, a restart would be needed for this setting to take effect. vacuum_defer_cleanup_age

Re: [GENERAL] Update field to a column from another table

The problem was a trigger in my DB, when I disabled it the data started to be updated. Lucas

Re: [GENERAL] Update field to a column from another table

On 04/21/2016 11:52 AM, drum.lu...@gmail.com wrote: So when I run: UPDATE ja_jobs t2 SET time_job = t1.time_job FROM junk.ja_test t1 WHERE t2.id = t1.id AND t2.time_job IS DISTINCT FROM t1.time_job; I get: UPDATE 2202 So I check the data

Re: [GENERAL] Update field to a column from another table

On Thursday, April 21, 2016, drum.lu...@gmail.com wrote: > So when I run: > > UPDATE ja_jobs t2 >> SET time_job = t1.time_job >> FROM junk.ja_test t1 >> WHERE t2.id = t1.id >> AND t2.time_job IS DISTINCT FROM t1.time_job; > > > I get: > > UPDATE 2202 > > So I check the data by doing: > > select *

Re: [GENERAL] Update field to a column from another table

So when I run: UPDATE ja_jobs t2 > SET time_job = t1.time_job > FROM junk.ja_test t1 > WHERE t2.id = t1.id > AND t2.time_job IS DISTINCT FROM t1.time_job; I get: UPDATE 2202 So I check the data by doing: select * FROM public.ja_jobs WHERE id = 14574527 And the "time_job" field is null

Re: [GENERAL] Update field to a column from another table

Please don't top-post. > > -Original Message- > > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of > > drum.lu...@gmail.com > > Sent: Donnerstag, 21. April 2016 07:10 > > To: Postgres General > > Subject: [GENERAL] Update field to a col

Re: [GENERAL] Update field to a column from another table

Hi This could work: UPDATE public.ja_jobs SET time_job = a.tj FROM ( SELECT id AS rid, clientid AS cid, time_job AS tj FROM junk.ja_jobs_23856 ) AS a WHERE a.rid = id AND a.cid = clientid; In the subselect a you need to rename the column names to avoid ambiguity. Here is a

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 17:11, s d escribió: On 26 February 2016 at 21:02, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: El 26/02/16 a las 16:49, s d escribió: On 26 February 2016 at 20:42, Leonardo M. Ramé mailto:l.r...@griensu.com>

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 21:02, Leonardo M. Ramé wrote: > > > El 26/02/16 a las 16:49, s d escribió: > >> On 26 February 2016 at 20:42, Leonardo M. Ramé > > wrote: >> >> >> >> >> Then try to do the update on the remote db directly. >> >> In the meantime co

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 16:49, s d escribió: On 26 February 2016 at 20:42, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: Then try to do the update on the remote db directly. In the meantime could you provide the table and trigger definitions? I don't understand why th

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 20:42, Leonardo M. Ramé wrote: > > > El 26/02/16 a las 16:33, s d escribió: > >> On 26 February 2016 at 20:19, Leonardo M. Ramé > > wrote: >> >> >> >> El 26/02/16 a las 16:18, s d escribió: >> >> >> On 26 February 2016 at 20:02, Leonar

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 16:33, s d escribió: On 26 February 2016 at 20:19, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: El 26/02/16 a las 16:18, s d escribió: On 26 February 2016 at 20:02, Leonardo M. Ramé mailto:l.r...@griensu.com>

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 20:19, Leonardo M. Ramé wrote: > > > El 26/02/16 a las 16:18, s d escribió: > >> >> On 26 February 2016 at 20:02, Leonardo M. Ramé > > wrote: >> >> >> El 26/02/16 a las 15:55, John R Pierce escribió: >> >> On 2/26/2016 10:29 AM, Leonar

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 16:18, s d escribió: On 26 February 2016 at 20:02, Leonardo M. Ramé mailto:l.r...@griensu.com>> wrote: El 26/02/16 a las 15:55, John R Pierce escribió: On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: Hi, I created a Postgres_FDW table (TABLE_A) and

Re: [GENERAL] Update foreign table with trigger

On 26 February 2016 at 20:02, Leonardo M. Ramé wrote: > > El 26/02/16 a las 15:55, John R Pierce escribió: > >> On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: >> >>> Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on >>> that table. >>> >>> As TABLE_A has a trigger, and the

Re: [GENERAL] Update foreign table with trigger

El 26/02/16 a las 15:55, John R Pierce escribió: On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on that table. As TABLE_A has a trigger, and the trigger does an insert on another table (TABLE_B), I had to create another fo

Re: [GENERAL] Update foreign table with trigger

On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on that table. As TABLE_A has a trigger, and the trigger does an insert on another table (TABLE_B), I had to create another foreign table called TABLE_B, that's ok. that tr

Re: [GENERAL] update inside function does not use the index

That solves my problem. Thanks!! Best regards Johannes Am 16.11.2015 um 18:19 schrieb Tom Lane: > Adrian Klaver writes: >> On 11/16/2015 08:03 AM, Johannes wrote: In every loop I execute an update with a where LIKE condition, which relates to my current cursor position: FOR i IN S

Re: [GENERAL] update inside function does not use the index

A function seams to be atomic for the analyze command (or?) EXPLAIN ANALYZE select my_function(); returns no inner query plan, just the costs, rows and width Am 16.11.2015 um 17:57 schrieb Adrian Klaver: > EXPLAIN ANALYZE select ... your_function(...); signature.asc Description: OpenPGP d

Re: [GENERAL] update inside function does not use the index

No, i did a mistake while simplifying it. It should be FOR i IN SELECT id, level, path_names||'%' as path_names from x LOOP update x set path_ids[i.level] = i.id where path_names like i.path_names; Sorry. Best regards Johannes Am 16.11.2015 um 15:10 schrieb Adrian Klaver: > On 11/16/2015 05:56

Re: [GENERAL] update inside function does not use the index

This helps me to understand for these common table expressions better. Thanks. This looks more elegant than the cursor variant. Limiting the cte to 10 records the update query needs 1.8 seconds. But the cursor variant ( 10 records ) was finished in 0.7 seconds. I guess it is faster, because behind

Re: [GENERAL] update inside function does not use the index

Adrian Klaver writes: > On 11/16/2015 08:03 AM, Johannes wrote: >>> In every loop I execute an update with a where LIKE condition, which >>> relates to my current cursor position: >>> FOR i IN SELECT id, level_ids, path_names||'%' as path_names from x LOOP >>> update x set path_ids[i.level] = id w

Re: [GENERAL] update inside function does not use the index

On 11/16/2015 08:03 AM, Johannes wrote: Ccing list No, i did a mistake while simplifying it. It should be FOR i IN SELECT id, level, path_names||'%' as path_names from x LOOP update x set path_ids[i.level] = i.id where path_names like i.path_names; So what do you see if you do?: EXPLAIN AN

Re: [GENERAL] update inside function does not use the index

Johannes schrieb am 16.11.2015 um 14:56: > I have problems with a self written function, which does not use the > index, which takes very long (500 ms per update). > > The pl/pgsql function iterates over a select resultset with a cursor. > In every loop I execute an update with a where LIKE condit

Re: [GENERAL] update inside function does not use the index

On 11/16/2015 05:56 AM, Johannes wrote: Dear List, I have problems with a self written function, which does not use the index, which takes very long (500 ms per update). The pl/pgsql function iterates over a select resultset with a cursor. In every loop I execute an update with a where LIKE co

Re: [GENERAL] UPDATE an updatable view

> Just for the sake of completeness... > > If the value (empname in the above example) can be NULL, the compare does not work, because > > SELECT NULL = NULL > > returns NULL which is treated as FALSE. > > But I am sure you know this :-) > > > HTH, > > Ladislav Lenart > > __

Re: [GENERAL] UPDATE an updatable view

On 27.8.2015 18:35, David Nelson wrote: >>> So in the UPDATE statement, I only provided a value for last_user. >>> But the first test of the trigger function tests for a NULL value of >>> NEW.empname. Since I did not provide one, I was expecting it to be >>> NULL and an exception to be thrown. A

Re: [GENERAL] UPDATE an updatable view

On Fri, Aug 28, 2015 at 6:39 AM, Ladislav Lenart wrote: > > Hello. > > > On 27.8.2015 18:35, David Nelson wrote: > >>> So in the UPDATE statement, I only provided a value for last_user. But the > >>> first test of the trigger function tests for a NULL value of > >>> NEW.empname. Since > >>> I did

Re: [GENERAL] UPDATE an updatable view

On Thu, Aug 27, 2015 at 1:21 PM, Jerry Sievers wrote: > > David Nelson writes: > > >>> So in the UPDATE statement, I only provided a value for last_user. But the > >>> first test of the trigger function tests for a NULL value of > >>> NEW.empname. Since > >>> I did not provide one, I was expectin

Re: [GENERAL] UPDATE an updatable view

Hello. On 27.8.2015 18:35, David Nelson wrote: >>> So in the UPDATE statement, I only provided a value for last_user. But the >>> first test of the trigger function tests for a NULL value of >>> NEW.empname. Since >>> I did not provide one, I was expecting it to be NULL and an exception to >>> be

Re: [GENERAL] UPDATE an updatable view

David Nelson writes: >>> So in the UPDATE statement, I only provided a value for last_user. But the >>> first test of the trigger function tests for a NULL value of >>> NEW.empname. Since >>> I did not provide one, I was expecting it to be NULL and an exception to >>> be thrown. Am I just misunde

Re: [GENERAL] UPDATE an updatable view

>> So in the UPDATE statement, I only provided a value for last_user. But the >> first test of the trigger function tests for a NULL value of >> NEW.empname. Since >> I did not provide one, I was expecting it to be NULL and an exception to >> be thrown. Am I just misunderstanding how things work? I

Re: [GENERAL] UPDATE an updatable view

On 08/27/2015 09:07 AM, David Nelson wrote: Good morning all, I am creating an updatable view on a set of tables, and just ran into unexpected (or more likely misunderstood) behavior with the UPDATE statement. If an attribute is not explicitly listed in the UPDATE statement, the NEW value appear

Re: [GENERAL] Update using non-existent fields does not throw an error

Thanks very much. Now that you've explained it, it should have been obvious. RobR -Original Message- From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at] Sent: Monday, March 16, 2015 12:21 PM To: Rob Richardson; pgsql-general@postgresql.org Subject: RE: Update using non-existent fields d

Re: [GENERAL] Update using non-existent fields does not throw an error

Rob Richardson wrote: > An update query is apparently succeeding, even though the query refers to > fields that do not exist. > Here’s the query: > > update inventory set > x_coordinate = (select x_coordinate from bases where base = '101'), > y_coordinate = (select y_coordinate from bases where b

Re: [GENERAL] Update using non-existent fields does not throw an error

On 16 March 2015 at 17:02, Rob Richardson wrote: > Greetings! > > > > An update query is apparently succeeding, even though the query refers to > fields that do not exist. Here’s the query: > > > > update inventory set > > x_coordinate = (select x_coordinate from bases where base = '101'), > > y_

  1   2   3   4   5   6   >