Re: [GENERAL] Insert large number of records

On 20 September 2017 at 22:55, Job wrote: > One further question: within a query launched on the MASTER table where i > need to scan every table, for exaple to search rows locatd in more partitions. > In there a way to improve "parallel scans" between more table at the same > time or not? > I no

R: [GENERAL] Insert large number of records

scan in the master table is Always sequential, descending into the partitions. Thank you again, F Da: Alban Hertroys [haram...@gmail.com] Inviato: mercoledì 20 settembre 2017 17.50 A: Job Cc: pgsql-general@postgresql.org Oggetto: Re: [GENERAL] In

Re: [GENERAL] Insert large number of records

On 20 September 2017 at 07:42, Job wrote: > We use a "temporary" table, populated by pg_bulkload - it takes few minutes > in this first step. > Then, from the temporary table, datas are transferred by a trigger that copy > the record into the production table. > But *this step* takes really lots

Re: [GENERAL] Insert large number of records

On Tuesday, September 19, 2017, Job wrote: > and would not care about table partitioning (COPY command fire > partitioned-table triggers). You might want to write a script that inserts directly into the partitions and bypass routing altogether. Insert into ... select from ... is your only opti

R: [GENERAL] Insert large number of records

Dear Alban, thank you for your precious reply, first of all. >> On 19 Sep 2017, at 15:47, Job wrote: >> >> Hi guys, >> >> we need to insert from a table to another (Postgresql 9.6.1) a large amount >> of data (about 10/20 millions of rows) without locking destination table. >> Pg_bulkload is th

Re: [GENERAL] Insert large number of records

> On 19 Sep 2017, at 15:47, Job wrote: > > Hi guys, > > we need to insert from a table to another (Postgresql 9.6.1) a large amount > of data (about 10/20 millions of rows) without locking destination table. > Pg_bulkload is the fastest way but it locks the table. > > Are there other ways? >

[GENERAL] Insert large number of records

Hi guys, we need to insert from a table to another (Postgresql 9.6.1) a large amount of data (about 10/20 millions of rows) without locking destination table. Pg_bulkload is the fastest way but it locks the table. Are there other ways? Classic "COPY" from? Thank you! F -- Sent via pgsql-gene

Re: [GENERAL] INSERT INTO: string with apostrophe

On Thu, Jun 29, 2017 at 1:36 PM, Rich Shepard wrote: > On Thu, 29 Jun 2017, Cachique wrote: > > That is correct. You can double the single quotes. Another way is to use >> the E'...' syntax (i.e., E'O\'Brien'). Or you can use the quote_* >> functions ( >> https://www.postgresql.org/docs/current/s

Re: [GENERAL] INSERT INTO: string with apostrophe

On Thu, 29 Jun 2017, Cachique wrote: That is correct. You can double the single quotes. Another way is to use the E'...' syntax (i.e., E'O\'Brien'). Or you can use the quote_* functions ( https://www.postgresql.org/docs/current/static/functions-string.html) Walter, Thanks for confirming. R

[GENERAL] INSERT INTO: string with apostrophe

The syntax for inserting data into a table uses single quotes to identify strings. When I have a string such as O'Brien do I double the single quotes within the string (i.e., 'O''Brien') or is there another way to include such strings? Rich -- Sent via pgsql-general mailing list (pgsql-gener

Re: [GENERAL] insert on conflict

> On Jun 27, 2017, at 3:30 PM, Peter Geoghegan wrote: > > On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu wrote: >> so how is it working in fact ? Isn't it working like looping in the >> IVEE.dim_company and for each company_id if the record does have a >> correspondent in csischema.dim_comp

Re: [GENERAL] insert on conflict

On Tue, Jun 27, 2017 at 12:40 PM, armand pirvu wrote: > so how is it working in fact ? Isn't it working like looping in the > IVEE.dim_company and for each company_id if the record does have a > correspondent in csischema.dim_company then update csischema.dim_company set > company_name = EXCLUDED

[GENERAL] insert on conflict

Hi Got question birstdb=# \d csischema.dim_company Table "csischema.dim_company" Column |Type | Modifiers -+-+--- company_id | integer | not null company_name| ch

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

Ronny Abraham schrieb am 15.05.2017 um 19:25: 4. Insert 10,000 rows to JSON, execution time (sec): 122.855001211 5. Insert 10,000 rows to JSONB, execution time (sec): 122.128999233 What’s interesting is that inserting to JSONB is slightly faster than inserting to JSON. A difference in 0.7

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

On Mon, May 15, 2017 at 12:02 PM, Ronny Abraham wrote: > 4. Insert 10,000 rows to JSON, execution time (sec): > 5. Insert 10,000 rows to JSONB, execution time (sec): > > What’s interesting is that inserting to JSONB is slightly faster than > inserting to JSON. With those times, only explanation i

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

each row), execution time (sec): 118.248999119 Thanks Ronny From: Dmitry Dolgov [mailto:9erthali...@gmail.com] Sent: Monday, May 15, 2017 3:35 PM To: Ronny Abraham Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB On 15 May 2017 at 19:25

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

On 15 May 2017 at 19:25, Ronny Abraham wrote: > > What’s interesting is that inserting to JSONB is slightly faster than inserting to JSON. > > Maybe that’s because my JSON has a flat structure (no nesting), or maybe I am doing something else wrong? I assume it's because your json documents (10 fi

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

Here are the attachments. From: Ronny Abraham Sent: Monday, May 15, 2017 1:03 PM To: 'pgsql-general@postgresql.org' Subject: Insert performance and disk usage in JSON vs JSONB Hello all, I am trying to decide whether to use JSON or JSONB to store my application data. From what I read so far ab

[GENERAL] Insert performance and disk usage in JSON vs JSONB

Hello all, I am trying to decide whether to use JSON or JSONB to store my application data. >From what I read so far about JSON vs JSONB: Performance - JSON is faster for inserts since it only odes JSON format verification, vs JSONB which

Re: [GENERAL] INSERT and ON CONFLICT

On Fri, 10 Mar 2017, Brian Dunavant wrote: I believe the following test should answer your question. Thank you, Brian. It does answer my question. Regards, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgres

Re: [GENERAL] INSERT and ON CONFLICT

I believe the following test should answer your question. db=# create table test ( a integer not null unique ); CREATE TABLE db=# insert into test values (1); INSERT 0 1 db=# insert into test values (1); ERROR: duplicate key value violates unique constraint "test_a_key" DETAIL: Key (a)=(1) alr

[GENERAL] INSERT and ON CONFLICT

I'm filling a table with rows and have the first batch successfully inserted. When I add more rows there may be some that already exist in the table and I would prefer that they be ignored and the insert process continue. The syntax page for INSERT suggests that ON CONFLICT DO NOTHING is exac

Re: [GENERAL] insert - on conflict question

On Wed, Feb 1, 2017 at 4:15 PM, Johann Spies wrote: > How do I formulate the on conflict do update-section of this query? When > I try set title=q.title, q is unknown. When I try and change 'title' in > the select-part to something else and try title=ti I get the message that > ti cannot be use

[GENERAL] insert - on conflict question

How do I formulate the on conflict do update-section of this query? When I try set title=q.title, q is unknown. When I try and change 'title' in the select-part to something else and try title=ti I get the message that ti cannot be used in this part of the query. INSERT INTO wos_2017_1.article (

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

Hi, I did: pkill -x postgres so it should send SIGTERM. Regards Pupillo ​​

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

On Tue, Dec 6, 2016 at 2:44 AM, Tom DalPozzo wrote: > Hi, > about SSD light: > > I guessed it was WAL -> actual db files data traffic. It explains why the > light stops blinking after shutting down the server (I did it via kill > command) . > Do you kill with -15 (the default) or -9? And whic

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

Hi, about SSD light: I guessed it was WAL -> actual db files data traffic. It explains why the light stops blinking after shutting down the server (I did it via kill command) . But if so, I expected the light to restart blinking after restarting the server (in order to continue WAL->db activity). R

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

On Fri, Dec 2, 2016 at 9:40 AM, Tom DalPozzo wrote: > Hi, > I've two tables, t1 and t2, both with one bigint id indexed field and one > 256 char data field; t1 has always got 1 row, while t2 is increasing as > explained in the following. > > My pqlib client countinously updates one row in t1

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

I tried to tune some parameters without appreciable changes in this behaviour. I tried to play with: checkpoint timeout wal size shared buffers commit delay checkpoijnt completion target No meaningful info found in the log file. Regards 2016-12-04 4:02 GMT+01:00 Tomas Vondra : > On Fri, 2016-

Re: [GENERAL] INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries

Justin Pryzby writes: > I'm implementing unique indices to allow "upsert" (and pg_repack and..), but > running into a problem when the table has >830 columns (we have some tables > which are at the 1600 column limit, and have previously worked around that > limit using arrays or multiple tables).

Re: [GENERAL] INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries

> On Dec 4, 2016, at 9:32 AM, Justin Pryzby wrote: > > Our application INSERTs data from external sources, and infrequently UPDATEs > the previously-inserted data (currently, it first SELECTs to determine whether > to UPDATE). > > I'm implementing unique indices to allow "upsert" (and pg_repack

[GENERAL] INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries

Our application INSERTs data from external sources, and infrequently UPDATEs the previously-inserted data (currently, it first SELECTs to determine whether to UPDATE). I'm implementing unique indices to allow "upsert" (and pg_repack and..), but running into a problem when the table has >830 column

[GENERAL] INSERT ON CONFLICT of "wide" table: target lists can have at most 1664 entries

Our application INSERTs data from external sources, and infrequently UPDATEs the previously-inserted data (currently, it first SELECTs to determine whether to UPDATE). I'm implementing unique indices to allow "upsert" (and pg_repack and..), but running into a problem when the table has >830 column

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

On Fri, 2016-12-02 at 13:45 -0800, Adrian Klaver wrote: > > On 12/02/2016 09:40 AM, Tom DalPozzo wrote: > > > > > > Hi, > > I've two tables, t1 and t2, both with one bigint id indexed field > > and > > one 256 char data field; t1 has always got 1 row, while t2 is > > increasing as explained

Re: [GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

On 12/02/2016 09:40 AM, Tom DalPozzo wrote: Hi, I've two tables, t1 and t2, both with one bigint id indexed field and one 256 char data field; t1 has always got 1 row, while t2 is increasing as explained in the following. My pqlib client countinously updates one row in t1 (every time target

[GENERAL] INSERT - UPDATE throughput oscillating and SSD activity after stopping the client

Hi, I've two tables, t1 and t2, both with one bigint id indexed field and one 256 char data field; t1 has always got 1 row, while t2 is increasing as explained in the following. My pqlib client countinously updates one row in t1 (every time targeting a different row) and inserts a new row in

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Wed, May 4, 2016 at 1:39 AM, Maxim Boguk wrote: >> This sounds like a known issue, fixed in 9.6, where the pages used for >> the pending list do not eligible for recycling until the table is >> vacuumed. Autovacuum does not run on insert only tables, so they just >> accumulate as empty pages

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Wed, May 4, 2016 at 3:45 AM, Jeff Janes wrote: > On Tue, May 3, 2016 at 3:09 AM, Maxim Boguk wrote: > > Hi, > > > > I started with empty table with index over > > custom_fields | jsonb > > field > > defined as: > > "idx_learners_custom_fields" gin (custom_fields) > > Globally gin_pending

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Tue, May 3, 2016 at 3:09 AM, Maxim Boguk wrote: > Hi, > > I started with empty table with index over > custom_fields | jsonb > field > defined as: > "idx_learners_custom_fields" gin (custom_fields) > Globally gin_pending_list_limit set to 2MB. > Database version is 9.5.2. > > Now question:

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Tue, May 3, 2016 at 1:35 PM, Maxim Boguk wrote: > > > On Tue, May 3, 2016 at 8:09 PM, Maxim Boguk wrote: > >> Hi, >> >> I started with empty table with index over >> custom_fields | jsonb >> field >> defined as: >> "idx_learners_custom_fields" gin (custom_fields) >> Globally gin_pending_

Re: [GENERAL] Insert only table and size of GIN index JSONB field.

On Tue, May 3, 2016 at 8:09 PM, Maxim Boguk wrote: > Hi, > > I started with empty table with index over > custom_fields | jsonb > field > defined as: > "idx_learners_custom_fields" gin (custom_fields) > Globally gin_pending_list_limit set to 2MB. > Database version is 9.5.2. > > Now question

[GENERAL] Insert only table and size of GIN index JSONB field.

Hi, I started with empty table with index over custom_fields | jsonb field defined as: "idx_learners_custom_fields" gin (custom_fields) Globally gin_pending_list_limit set to 2MB. Database version is 9.5.2. Now question: If table populated with 1M records in single transaction then the final

Re: [GENERAL] Insert data in two columns same table

On Wed, Mar 16, 2016 at 9:34 PM, drum.lu...@gmail.com wrote: > The problem is that I need to do that at the same time, because of a > constraint: > > Mark your constraint as deferrable, and then defer the constraints within your transaction.

Re: [GENERAL] Insert data in two columns same table

> "drum.lu...@gmail.com" hat am 17. März 2016 um 02:34 > geschrieben: > > > I'm trying to insert data from TABLE A to TABLE B. > > 1 - Select billable_id from dm.billable > 2 - Select mobiuser_id from ja_mobiusers > 3 - Insert the billable_id and the mobiuser_id to the dm.billables_links > ta

Re: [GENERAL] Insert data in two columns same table

> > > assuming those two queries 1 and 2 return multiple rows, which rows of > junk.wm_260_billables2 match up with what rows of public.ja_mobiusers ? > > your schema is very poorly defined. I think you need to take a class in > relational database design and usage, or read a good book on

Re: [GENERAL] Insert data in two columns same table

> > > Hi Andreas! > > Well... > > There are two tables that I need to get data from(dm.billables / > public.ja_mobiusers), and a third table (dm.billables_links) that I need to > insert data from those two tables. lets start from here. you have 2 tables: test=*# select * from source1; i ---

Re: [GENERAL] Insert data in two columns same table

On Wed, Mar 16, 2016 at 6:49 PM, Andreas Kretschmer wrote: > > > > "drum.lu...@gmail.com" hat am 17. März 2016 um > 02:34 > > geschrieben: > > > > > > I'm trying to insert data from TABLE A to TABLE B. > > > > 1 - Select billable_id from dm.billable > > 2 - Select mobiuser_id from ja_mobiusers >

Re: [GENERAL] Insert data in two columns same table

> > > > I see a lot of other problems: you have 3 independet tables. Your 2 queries > (selects) returns 2 independet results, you can't use that for insert into > the > 3rd table. And i think, you are looking for an update, not insert. So you > have > to define how your tables are linked together (

Re: [GENERAL] Insert data in two columns same table

On 18 March 2016 at 03:23, Adrian Klaver wrote: > On 03/16/2016 07:07 PM, drum.lu...@gmail.com wrote: > >> >> >> >> >> I see a lot of other problems: you have 3 independet tables. Your 2 >> queries >> (selects) returns 2 independet results, you can't use that for >> insert into th

Re: [GENERAL] Insert data in two columns same table

On 3/16/2016 7:07 PM, drum.lu...@gmail.com wrote: *1 -* select the billable_id: (SELECT1) SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%Alisha%' *2 -* select the mobiuser_id: (SELECT2) SELECT id FROM public.ja_mobiusers WHERE name_first LIKE 'Alisha%' AND name_last LIKE 'Da

[GENERAL] Insert data in two columns same table

I'm trying to insert data from TABLE A to TABLE B. 1 - Select billable_id from dm.billable 2 - Select mobiuser_id from ja_mobiusers 3 - Insert the billable_id and the mobiuser_id to the dm.billables_links table. *FYI -* It has to be in the same transaction because the mobiuser_id must go to the

Re: [GENERAL] Insert data in two columns same table

On 03/16/2016 07:07 PM, drum.lu...@gmail.com wrote: I see a lot of other problems: you have 3 independet tables. Your 2 queries (selects) returns 2 independet results, you can't use that for insert into the 3rd table. And i think, you are looking for an update, not insert.

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

Hi Rafal: On Tue, Jul 21, 2015 at 12:43 PM, Rafal Pietrak wrote: > W dniu 21.07.2015 o 09:34, Francisco Olarte pisze: >> In this case I think you are mixing vouchers with voucher-numbers. IMO >> you could get a better dessign by using an auxiliary table and not >> nullifying the number after been

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

On 21 July 2015 at 11:43, Rafal Pietrak wrote: > On the other hand, the "ON CONFLICT RETRY" has a nice feature for an > application programmer (like myself) that it leaves us free of the > implementation of the re-issue of an INSERT. One database-schema > designer does that for all of us. > > But

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

Franscisco, W dniu 21.07.2015 o 09:34, Francisco Olarte pisze: > Hi Rafal: > > On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak wrote: >> Regarding the last point. Usually, I implement one-time used vouchers as >> rows in table like: >> CREATE TABLE (voucher int not null, consumed bool, expire tim

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

Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak wrote: > Regarding the last point. Usually, I implement one-time used vouchers as > rows in table like: > CREATE TABLE (voucher int not null, consumed bool, expire timestamp not > null default timestamp_pl_interval(now()::timestamp, '2 > mi

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

On 7/20/2015 7:01 AM, Geoff Winkless wrote: Some web research suggests that random sequences are not great for indexes because of the resultant "keyspace fragmentation". I'm assuming that means a low number of nodes in the btree leafs, so an increase in memory usage for the index? that su

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

Hi Rafal: On Mon, Jul 20, 2015 at 3:33 PM, Rafal Pietrak wrote: > 3. there are methods (like cryptographic "random" sequence), which > guarantee no conflicts. So one should resort to that. > Regarding the last point. Usually, I implement one-time used vouchers as > rows in table like: > CREATE TA

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

Hi Igor: On Mon, Jul 20, 2015 at 4:56 PM, Igor Neyman wrote: > Well, there is a caveat. > If I create table and couple indexes like this: .. > and populate them: > and then check the size of the indexes: > for "select pg_relation_size('U1')" I get 2834432 > while " select pg_relation_size('U2')"

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

Hi Alvaro. On Mon, Jul 20, 2015 at 4:07 PM, Alvaro Herrera wrote: >> Some web research suggests that random sequences are not great for indexes >> because of the resultant "keyspace fragmentation". I'm assuming that means >> a low number of nodes in the btree leafs, so an increase in memory usage

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

On Mon, Jul 20, 2015 at 7:01 AM, Geoff Winkless wrote: > On 20 July 2015 at 14:33, Rafal Pietrak wrote: > >> If I'm not mistaken, the conclusions from posts in this thread are: >> >> 3. there are methods (like cryptographic "random" sequence), which >> guarantee no conflicts. So one should resor

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

Geoff Winkless wrote: > On 20 July 2015 at 14:33, Rafal Pietrak wrote: > > > If I'm not mistaken, the conclusions from posts in this thread are: > > > > 3. there are methods (like cryptographic "random" sequence), which > > guarantee no conflicts. So one should resort to that. > > > > > Some web

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

On 20 July 2015 at 15:07, Alvaro Herrera wrote: > Not sure what type of indexes would be affected by that problem, but I > don't think Postgres' btrees would be. > I admit it's not really my area.​ ​Take it up with Drew Blas, I guess :) https://blog.starkandwayne.com/2015/05/23/uuid-primary-ke

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

Geoff Winkless wrote: > On 20 July 2015 at 14:33, Rafal Pietrak wrote: > > > If I'm not mistaken, the conclusions from posts in this thread are: > > > > 3. there are methods (like cryptographic "random" sequence), which > > guarantee no conflicts. So one should resort to that. > > > > > Some web

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

On 20 July 2015 at 14:33, Rafal Pietrak wrote: > If I'm not mistaken, the conclusions from posts in this thread are: > > 3. there are methods (like cryptographic "random" sequence), which > guarantee no conflicts. So one should resort to that. > > Some web research suggests that random sequences

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

If I'm not mistaken, the conclusions from posts in this thread are: 1. recognizing of a "RETRY" action, as a separate case of "ON CONFLICT" transaction continuation is not generally appreciated. 2. I shouldn't expect any "hidden corruption/performance" obstacles when simply re-attempting of an IN

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

For this purpose, I have seen it recommended to use a UUID instead of a randomly generated integer. I do this myself for production applications and over millions of records I have yet to log a conflict. Also, as stated above, you could create a plpgsql function which would achieve exactly what you

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

Hi Daniel: On Sun, Jul 19, 2015 at 9:03 PM, Daniel Verite wrote: > For SERIAL, it's too obvious to guess what is the next one, > so malicious people could claim access codes or vouchers > they don't own. Why don't you use encryption? Specifically only on the external side. You use a serial in th

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

Melvin Davidson wrote: > Aside from Tom Lane's comments, it seems to me you are reinventing the wheel > by generating random values for keys. Why not just use UUID > http://www.postgresql.org/docs/9.5/static/datatype-uuid.html > or serial > http://www.postgresql.org/docs/9.5/static/datatyp

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

Rafal Pietrak wrote: > CREATE TABLE vouchers (a_voucher bigint PRIMARY KEY default > (random()*10)::bigint, issued date default now(), .); Generators of truly unique pseudo-random values provide a better ground for this. Consider for example: https://wiki.postgresql.org/wiki/

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

Aside from Tom Lane's comments, it seems to me you are reinventing the wheel by generating random values for keys. Why not just use UUID http://www.postgresql.org/docs/9.5/static/datatype-uuid.html or serial http://www.postgresql.org/docs/9.5/static/datatype-numeric.html#DATATYPE-SERIAL? Wouldn't

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

On 07/19/2015 08:04 AM, Rafal Pietrak wrote: W dniu 19.07.2015 o 16:33, Adrian Klaver pisze: On 07/19/2015 06:47 AM, Rafal Pietrak wrote: Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: On 19 July 2015 at 11:30, Rafal Pietrak [---] Although "a random" can duplicate its pr

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

W dniu 19.07.2015 o 16:33, Adrian Klaver pisze: > On 07/19/2015 06:47 AM, Rafal Pietrak wrote: >> Hi, >> >> W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: >>> On 19 July 2015 at 11:30, Rafal Pietrak > Although "a random" can duplicate its previous values, "my random(s)" >> (which are created fo

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

On 07/19/2015 06:47 AM, Rafal Pietrak wrote: Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: On 19 July 2015 at 11:30, Rafal Pietrak mailto:ra...@ztk-rp.eu>> wrote: when I have to invent/introduce additional features/columns/attributes (like a key in addition to a sequence),

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

Hi, W dniu 19.07.2015 o 14:10, Geoff Winkless pisze: > On 19 July 2015 at 11:30, Rafal Pietrak > wrote: > > when I have to invent/introduce additional > features/columns/attributes (like a key in addition to a sequence), > which are not required by the design,

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

On 19 July 2015 at 11:30, Rafal Pietrak wrote: > when I have to invent/introduce additional > features/columns/attributes (like a key in addition to a sequence), > which are not required by the design, but necessary for implementation) > is a workaround (almost by definition). > I'm sorry that y

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

Hi, W dniu 19.07.2015 o 10:27, Geoff Winkless pisze: > On 19 July 2015 at 09:11, Rafal Pietrak > wrote: > > I'm not particularly fond of using functions to accessing RDBMS instead > of tables. > > And I'm not particularly fond of "workarounds". > > > Use a

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

On 19 July 2015 at 09:11, Rafal Pietrak wrote: > I'm not particularly fond of using functions to accessing RDBMS instead > of tables. > > And I'm not particularly fond of "workarounds". > Use a combination of factors (a sequence ID and the key) for your authorization. So in the extremely unlikel

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

Hi, W dniu 19.07.2015 o 09:33, Charles Clavadetscher pisze: [---] >> 2. with current (as of 9.5) implementation I think I can always "ON CONFLICT >> DO NOTHING", and retry the INSERT from application level. > > An UPSERT is "try an INSERT and if there is a conflict, do nothing or UPDA

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

Hello > I've just started to read through postgres-9.5 "what's new" ... before giving > it > a try. The "insert ... on conflict do update" is particularly atractive to > me; but I > was wondering why it does not cover the third usage scenario of action that a > programmer may need for a PK confl

[GENERAL] INSERT ... ON CONFLICT DO UPDATE

Hello, I've just started to read through postgres-9.5 "what's new" ... before giving it a try. The "insert ... on conflict do update" is particularly atractive to me; but I was wondering why it does not cover the third usage scenario of action that a programmer may need for a PK conflict during in

Re: [GENERAL] INSERT a real number in a column based on other columns OLD INSERTs

On Wed, Jun 24, 2015 at 5:52 PM, Adrian Klaver wrote: > On 06/23/2015 11:20 PM, litu16 wrote: > >> >> So, this is what I have made so far... >> >> *CREATE OR REPLACE FUNCTION timelog() >>RETURNS trigger AS >> $BODY$ >> DECLARE >> t_ix real; >> n int; >> >> BEG

Re: [GENERAL] INSERT a real number in a column based on other columns OLD INSERTs

On 06/23/2015 11:20 PM, litu16 wrote: In PostgreSQL I have this table... (there is a primary key serial column in the most left side "stmtserial" which is not shown in this image) in the table above, all columns are entered via querrys

[GENERAL] INSERT a real number in a column based on other columns OLD INSERTs

In PostgreSQL I have this table... (there is a primary key serial column in the most left side "stmtserial" which is not shown in this image) in the table above, all columns are entered via querrys, except the "time_index" which I is au

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Adrian Klaver wrote: > On 06/20/2015 12:41 PM, Charles Clavadetscher wrote: >> I just made a short test with the code provided. As Bill mentioned the >> moment when the trigger is fired is essential. >> I made a test with both before (worked) and after (did not work because >> the row was already i

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

On 06/21/2015 12:28 PM, litu16 wrote: Thanks again to all Thanks Charles, fortunately I only need to do INSERTS in my database, however I know I have to read postgreSQL documentation, my respect to programmers who have to learn one language after another, after another, an so on. I just know pyth

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Thanks again to all Thanks Charles, fortunately I only need to do INSERTS in my database, however I know I have to read postgreSQL documentation, my respect to programmers who have to learn one language after another, after another, an so on. I just know python and other shorter scripting languague

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Just a final note. If your trigger needs to handle updates or deletes, then you may need to use OLD or both (OLD and NEW) and return OLD instead of NEW, depending on what you want to achieve. You also may return null to avoid the action to be performed, but this only works with "before" trigger

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Hi The two things have nothing in common. With NEW.time_index = t_ix you set the field with a value and with return you return the record (with the modified field) to make the insert. You have to return NEW, because you have a trigger function. The function must return a record of the same typ

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Hi, thanks yes I was using AFTER, but it only works with BEFORE so finally I got it to work. thanks to all Im just still wondering why here... *BEGIN IF NEW.time_type = 'Start' THEN SELECT t.time_index FROM table_ebscb_spa_log02 t WHERE t.fn_name = NEW.fn_name AND t.time_type = 'Start' ORDE

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Yes I have tried with t_ix = 1; or t_ix := 1; I get the same result, it just doesn't do anything, I have also tried with AFTER and BEFORE, when I use BEFORE I get this error ERROR: cannot return non-composite value from function returning composite type CONTEXT: PL/pgPSQL function on_ai_mytable()

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

On 06/20/2015 12:41 PM, Charles Clavadetscher wrote: Hello I just made a short test with the code provided. As Bill mentioned the moment when the trigger is fired is essential. I made a test with both before (worked) and after (did not work because the row was already inserted and the returned n

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

Hello I just made a short test with the code provided. As Bill mentioned the moment when the trigger is fired is essential. I made a test with both before (worked) and after (did not work because the row was already inserted and the returned new row is ignored). The assignment (= or :=) does

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

On Sat, 20 Jun 2015 10:44:21 -0700 (MST) litu16 wrote: > In PostgreSQL I have this table... (there is a primary key in the most left > side "timestamp02" which is not shown in this image) > > in the table above, all columns are entered via querrys, except the > "time_index" which I would like to

Re: [GENERAL] INSERT a number in a column based on other columns OLD INSERTs

On 06/20/2015 10:44 AM, litu16 wrote: In PostgreSQL I have this table... (there is a primary key in the most left side "timestamp02" which is not shown in this image) in the table above, all columns are entered via querrys, except the "time_index" which I would like to be filled automatically vi

[GENERAL] INSERT a number in a column based on other columns OLD INSERTs

In PostgreSQL I have this table... (there is a primary key in the most left side "timestamp02" which is not shown in this image) in the table above, all columns are entered via querrys, except the "time_index" which I would like to be filled automatically via a trigger each time each row is filled

Re: [HACKERS] [GENERAL] Insert result does not match record count

Did this every go any further? I wrote some data transformation script at work, and after seeing "with count -2017657667" (and similar) in my scripts log I got a bit worried. seeing else where were folks just run a full on count(*) later to check counts but that is even MORE time and I was think

Re: [GENERAL] INSERT to partitioned table doesn't return row count

Kris Deugau wrote > David G Johnston wrote: >> Going from recent memory this particular behavior complaint has now come >> up >> three times in the past six months - the main complaint previously is >> that >> given an insert trigger for the partition you have to copy, not move, the >> insert to th

Re: [GENERAL] INSERT to partitioned table doesn't return row count

David G Johnston wrote: >> http://www.postgresql.org/docs/9.3/interactive/sql-select.html >> >> table_name >> >> The name (optionally schema-qualified) of an existing table or >> view. If ONLY is specified before the table name, only that table is >> scanned. If ONLY is not specified, the ta

Re: [GENERAL] INSERT to partitioned table doesn't return row count

Adrian Klaver-4 wrote > On 12/03/2014 11:19 AM, Kris Deugau wrote: >> Is it possible to return the number of rows inserted to a partitioned >> table set up as per the examples on >> http://www.postgresql.org/docs/9.1/interactive/ddl-partitioning.html, in >> the same way as if you inserted directly

  1   2   3   4   5   6   7   >