The number corresponds to the pg_attribute.attnum value. I followed https://stackoverflow.com/a/656841/52499 to reorder columns. It appears indexes refer to columns using attnum values. In other words, after reordering columns, they started to refer to other columns, which led to the error. To reproduce the issue ( https://gist.github.com/x-yuri/8c207b1ab7a3cbc81b40550a55acecd2 ), create a table: > CREATE TABLE t1 (f1 varchar, f2 varchar, f3 integer) CREATE TABLE CREATE INDEX ON t1 (f2) WHERE f2 IS NOT NULL CREATE INDEX \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+----------+--------- f1 | character varying | | | f2 | character varying | | | f3 | integer | | | Indexes: "t1_f2_idx" btree (f2) WHERE f2 IS NOT NULL Reorder columns: > UPDATE pg_attribute SET attnum = 30001 WHERE attname = 'f2' AND attrelid = 16384 UPDATE 1 UPDATE pg_attribute SET attnum = 30002 WHERE attname = 'f3' AND attrelid = 16384 UPDATE 1 UPDATE pg_attribute SET attnum = 3 WHERE attname = 'f2' AND attrelid = 16384 UPDATE 1 UPDATE pg_attribute SET attnum = 2 WHERE attname = 'f3' AND attrelid = 16384 UPDATE 1 \d t1 Table "public.t1" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+----------+--------- f1 | character varying | | | f3 | integer | | | f2 | character varying | | | Indexes: "t1_f2_idx" btree (f3 COLLATE "default" text_ops) WHERE f3 IS NOT NULL Try to insert a row: > INSERT INTO t1 (f1) VALUES ('f1') ERROR: attribute 2 of type record has wrong type DETAIL: Table has type integer, but query expects character varying. SELECT a.attname, a.attnum FROM pg_attribute a JOIN pg_class c on a.attrelid = c.oid JOIN pg_namespace n on c.relnamespace = n.oid WHERE n.nspname = 'public' AND c.relname = 't1' AND attnum >= 1 ORDER BY attnum attname | attnum ---------+-------- f1 | 1 f3 | 2 f2 | 3 (3 rows) As you can see t1_f2_idx now refers to f3 (in the table it's an integer, in the index a string). At least in this case it's reversible: > UPDATE pg_attribute SET attnum = 30001 WHERE attname = 'f2' AND attrelid = 16384 UPDATE 1 UPDATE pg_attribute SET attnum = 30002 WHERE attname = 'f3' AND attrelid = 16384 UPDATE 1 UPDATE pg_attribute SET attnum = 2 WHERE attname = 'f2' AND attrelid = 16384 UPDATE 1 UPDATE pg_attribute SET attnum = 3 WHERE attname = 'f3' AND attrelid = 16384 UPDATE 1 INSERT INTO t1 (f1) VALUES ('f1') INSERT 0 1

It didn't fail though, because you specified ON CONFLICT DO NOTHING;. The query succeeded in not doing anything since you instructed what it should do in a graceful manner when there is a data conflict. This is logically no different than using a try / catch in an object oriented programming language to gracefully capture an exception and swallow it as opposed to it being thrown as unhandled in the application. If you want the query to fail on data conflicts, then don't gracefully handle it and remove the ON CONFLICT DO NOTHING; clause. For further information, please see the https://www.postgresql.org/docs/current/sql-insert.html specifically the "ON CONFLICT Clause" section where it states: The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error.

That is a trivial application of INSERT ... RETURNING: INSERT INTO parents (display_name) VALUES ('papa') RETURNING rowid; Then you can use the returned value for the next INSERT. You can do all three inserts in a single SQL statement using CTEs: WITH p_ids AS ( INSERT INTO parents (display_name) VALUES ('papa') RETURNING rowid ), c_ids AS ( INSERT INTO clients (parent, display_name) SELECT p_ids.rowid, 'murcielago' FROM p_ids RETURNING rowid ) INSERT INTO datapoints (client, val) SELECT c_ids.rowid, 2.71828 FROM c_ids;

You probably want something like this: WITH result_query AS ( SELECT id, arrive_date, arrive_location, thing_type, thing_count FROM ... -- ? ) INSERT INTO preprocess_things (arrive_date, arrive_location, data) -- ① SELECT r.arrive_date -- ② , r.arrive_location , jsonb_build_object(r.thing_type, r.thing_count) -- ③ FROM result_query r ON CONFLICT (arrive_date, arrive_location) DO UPDATE SET data = data || EXCLUDED.data; -- ④ ① Always provide a target column list for persisted code. ② The main issue: you have to SELECT from the CTE to bring in the computed values. Your original syntax also switched target and source. Have another look at the https://www.postgresql.org/docs/current/sql-insert.html . ③ Assuming you meant jsonb_build_object(r.thing_type, r.thing_count). What you had there didn't work. Also, no cast: jsonb_build_object() already returns jsonb. ④ My guess. What you had, didn't work. Note the use of the EXCLUDED key word to refer to the row initially proposed for insertion.

Per the documentation ( https://www.postgresql.org/docs/12/sql-createpolicy.html ) (read once more now with the clear question in mind), both the insert and select policies are read by postgres when using the insert ... returning command combination. The following policies did the trick: -- using with check for new records create policy "Api can insert" on core.users for insert to api with check (true); -- using using for existing records create policy "Api can select (before current_user is set)" on core.users for select to api using (true); create policy "Api can do anything when current_user_id" on core.users for all -- where overlaps with other policies, think "or" to api using(current_user_id() = id) with check (current_user_id() = id); Note: I had tried something similar prior to drafting this post, but only succeeded this-time-round by properly using using and with check. The former is specific to new records (i.e., used in insert and update commands), while the latter is specific to existing records (i.e., used for select and delete commands).

In theory what I want should look like this: | StudentID | StaffID | Phone_Model | Class | | --------- | ------- |------------ | ----- | | 1 | 1 | iPhone7 | S201 | 2 | 3 | iPhone11 | S203 Is there something missing from my SQL statement? You are missing relation between Staff and Student. You need a column StaffID int in the Student table. create table Staff ( StaffID int , Class varchar(10), TotalPhonesConfiscated int ); insert into Staff values (1,'S201',1), (2,'S202',0), (3,'S203',1); create table Student ( StudentID int , Phone_Model varchar(20), StaffID int ); insert into Student values (1,'iPhone7',1), (2,'Samsung',3); create table Phones ( StudentID int, StaffID int , Phone_Model varchar(20), Class varchar(10)); Then you can insert the data: INSERT INTO Phones ( StudentID, StaffID, Phone_Model, Class ) SELECT StudentID, Student.StaffID, Phone_Model, Class FROM Student INNER JOIN Staff on Student.StaffID=Staff.StaffID WHERE TotalPhonesConfiscated >= 1; select * from Phones; Would result on: Result: StudentID StaffID Phone_Model Class 1 1 iPhone7 S201 2 3 Samsung S203

INSERT INTO Nodes (`Key`, ParentId) SELECT @key, @parentid WHERE NOT EXISTS ( SELECT NULL FROM Nodes WHERE `Key` @key AND ParentId @parentid ) @key and @parentid are placeholders for the values to be inserted. So if the row with the values to be inserted already exists then the subquery detects this, and none rows is inserted.

It's a coincidence that your query even works. Normally, you cannot use an alias in the GROUP BY clause of a query--you have to use the actual column name. For example, If you try to run this simple query you'll get the error Invalid column name 'ObjectName' when you try to execute the query (note that it will parse successfully, but only error on execution): SELECT ObjectName = OBJECT_NAME(object_id), NumberColumns = COUNT(*) FROM sys.columns GROUP BY ObjectName; To fix this, you'd either use the non-aliased column name, or add in a CTE or subquery to force the layer of abstraction & make it so that the query engine understands the alias. In your query, you coincidentally use the same name for the shredded column from the OPENJSON...WITH and the computed alias in your select. This obscures the real issue. If you change the name you're using to come out of the JSON shredding & the reference in your SELECT, but continue to use the Alias in your GROUP BY, you'll get an Invalid column error message: SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN FROM OPENJSON(@json) WITH ( Bin VARCHAR(20) '$.Bin', StockCode VARCHAR(30) '$.StockCode', Qty DECIMAL(18,6) '$.Qty', PutDateText VARCHAR(20) '$.PutDate', --changed this (and reference above) LPN VARCHAR(50) '$.LPN' ) WHERE Bin <> 'DEFAULT' GROUP BY StockCode, Bin, PutDate, LPN; As an aside, now that we've changed some of the labels, we can see that the functional equivalent of your original query is actually this: SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN FROM OPENJSON(@json) WITH ( Bin VARCHAR(20) '$.Bin', StockCode VARCHAR(30) '$.StockCode', Qty DECIMAL(18,6) '$.Qty', PutDateText VARCHAR(20) '$.PutDate', LPN VARCHAR(50) '$.LPN' ) WHERE Bin <> 'DEFAULT' GROUP BY StockCode, Bin, PutDateText, LPN; The "quick" way to make this work is not using the alias, and just using the direct column name (in this case, a formula using the CAST() function: SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDateText AS DATE) as PutDate, GETDATE() as VerDate, LPN FROM OPENJSON(@json) WITH ( Bin VARCHAR(20) '$.Bin', StockCode VARCHAR(30) '$.StockCode', Qty DECIMAL(18,6) '$.Qty', PutDateText VARCHAR(20) '$.PutDate', LPN VARCHAR(50) '$.LPN' ) WHERE Bin <> 'DEFAULT' GROUP BY StockCode, Bin, CAST(PutDateText AS DATE), LPN Personally, I'd use a CTE to shred the JSON into a tabular format, then construct my query using the CTE to do the aggregates. This feels a bit "cleaner" to me and allows me to use the alias names wherever I want. This has an advantage of not having to repeat function calls & keep them in perfect unison between SELECT & GROUP BY: WITH JsonTable AS ( SELECT Bin, StockCode, Qty, CAST(PutDate AS DATE) as PutDate, LPN FROM OPENJSON(@json) WITH ( Bin VARCHAR(20) '$.Bin', StockCode VARCHAR(30) '$.StockCode', Qty DECIMAL(18,6) '$.Qty', PutDate VARCHAR(20) '$.PutDate', LPN VARCHAR(50) '$.LPN' ) ) INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN) SELECT Bin, StockCode, SUM(Qty) as Qty, PutDate, GETDATE() AS VerDate, LPN FROM JsonTable WHERE Bin <> 'DEFAULT' GROUP BY StockCode, Bin, PutDate, LPN; But you could also use an inline subquery in much the same way. INSERT INTO warehouse (Bin, StockCode, Qty, PutDate, VerDate, LPN) SELECT Bin, StockCode, Sum(Qty) as Qty, CAST(PutDate AS DATE) as PutDate, GETDATE() as VerDate, LPN FROM ( SELECT Bin, StockCode, Qty, CAST(PutDate AS DATE) as PutDate, LPN FROM OPENJSON(@json) WITH ( Bin VARCHAR(20) '$.Bin', StockCode VARCHAR(30) '$.StockCode', Qty DECIMAL(18,6) '$.Qty', PutDate VARCHAR(20) '$.PutDate', LPN VARCHAR(50) '$.LPN' ) ) AS x WHERE Bin <> 'DEFAULT' GROUP BY StockCode, Bin, PutDate, LPN;

The first table has 120 columns, and each primary key has one row The "Primary Key", by definition, uniquely identifies each row. I suspect that's not what you mean here. the second table has 5 columns, and each primary key has 1000 rows Then it cannot be the "Primary Key" of the second table. I assume you mean that the second table has 1000 rows for each corresponding [Primary] Key in the first table. ... two ways to update the database ... One is on the API level where only one primary key is updated. Another one ... where all primary keys are updated. The Primary Key of any record should be generated / stored when the record is first created and should remain unchanged for the entire lifetime of that record, until the record is finally destroyed. Nothing should update Primary Key values. It's a hugely expensive operation, will lock up the database while it's happening and, to all intents and purposes, it's a complete waste of time. If you're using a surrogate, numeric id for each record to make tying things together in the database "easier", then that value should never, ever change (and no-one, outside the database, should ever, ever see it). Every change to the database is logged, so effectively deleting and recreating the table, by "rewriting" every record is going to hammer your disk system, with an image of every deleted record and then every inserted record. That's going to be a lot of disk activity and CPU load and record/table locking and Application performance impact - for something that you probably shouldn't be doing at all. An analogy: Consider the effect if your bank decided to renumber everybody's accounts whenever someone else closed their account ... That's the sort of thing we're talking about here.

The problem like due to the subquery: FROM ( SELECT DISTINCT (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val UNION SELECT DISTINCT (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val ) v producing rows with same name but different precision or commission_precision. You'll need to fix it so it produces only one row for each name - according to your requirements. There may be different cases: rows with same base_name but different base_precision/base_commision_precision. rows with same quote_name but different quote_precision/quote_commision_precision. rows where base_name matches quote_name of other rows (and different precision/commision_precision). Other points: the columns do not need to be in parantheses. the following code part looks to be doing totally nothing: ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name WHERE a.name <> EXCLUDED.name

What you discovered is collectively called Pessimistic Locking or Mutual Exclusion (mutex). For it to work we need to choose an object to lock on. It's possible to lock on some record in DB (select ... for update) or you can ask PostgreSQL to create such object outside of tables (pg_advisory_xact_lock()). While pg_advisory_xact_lock() is a valid solution it's specific to PostgreSQL - and therefore it should be considered as the last resort. First solution that you should think of is finding records that we can lock on inside of existing tables. E.g. in your case it seems like you only append data and therefore you could lock on the oldest record with select ... for update. If this isn't practical (e.g. because it's possible that the table is completely empty), then yes - pg_advisory_xact_lock() would be a good choice.

I would definitely suggest modifying your PK to use OPTIMIZE_FOR_SEQUENTIAL_KEY (as suggested by https://dba.stackexchange.com/users/32281/erik-darling in https://dba.stackexchange.com/questions/305074/how-to-resolve-insert-contention-in-azure-sql-database?noredirect=1#comment595567_305074 . https://www.erikdarlingdata.com/optimize_for_sequential_key/enabling-optimize-for-sequential-key/ is pretty straightforward. This makes it a relatively low-effort, low-impact starting point. ALTER INDEX PK_ChangeLog ON dbo.ChangeLog SET(OPTIMIZE_FOR_SEQUENTIAL_KEY = ON); This feature was made specifically to address the scenario you're seeing--so before re-clustering the table and fighting those higher-effort, higher-risk changes, using the purpose built setting first is the right starting point! There's no guarantee it will be a silver bullet to solve your problem, but it should help. Pam Lahoud, from the SQL Server Product Group wrote a great https://techcommunity.microsoft.com/t5/sql-server-blog/behind-the-scenes-on-optimize-for-sequential-key/ba-p/806888 on the feature. From Pam's article: With last page insert contention, as the number of insert threads increases, the queue for the page latch increases which in turn increases latency. Throughput will also decrease, but if something slows down one of the threads that is holding the latch, this can trigger a convoy and throughput suddenly falls off a cliff. ... When using this option, you may not see a decrease in PAGELATCH waits, in fact you may even see an increase in waits with a new wait type called BTREE_INSERT_FLOW_CONTROL. Despite these waits, you should be able to achieve much better throughput, and scale to a much higher number of concurrent threads, without hitting the proverbial cliff. If you are still seeing limited throughput after enabling that feature, you may then need to resort to changing your indexing strategy to eliminate that "last page contention" when concurrent inserts are all clamoring to write to the end of the table.

Since something needs to first populate the table variable that is then passed to the stored procedure, unless the procedure is doing more than what you posted, why not populate the table directly? Skipping the use of the table variable and sproc?

That is not the normal way to use IODKU. Instead of ... UPDATE foo = i1.stuff ... do ... UPDATE foo = VALUES(foo), ... What is the Unique key in the table?

The implementation may be: -- books table CREATE TABLE Ebook (ebook_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255) UNIQUE); -- tags table CREATE TABLE Tags (tag_id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(255) UNIQUE); -- adjacency table PRAGMA foreign_keys = ON; CREATE TABLE TagsToEbooks (ebook_id INT, tag_id INT, PRIMARY KEY (ebook_id, tag_id), FOREIGN KEY (ebook_id) REFERENCES Ebook (ebook_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (tag_id) REFERENCES Tags (tag_id) ON DELETE CASCADE ON UPDATE CASCADE); -- Trigger which will delete unused tags after book deletion CREATE TRIGGER delete_unused_tags AFTER DELETE ON Ebook BEGIN DELETE FROM Tags WHERE NOT EXISTS ( SELECT NULL FROM TagsToEbooks WHERE Tags.tag_id = TagsToEbooks.tag_id); END; https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=3240437678fe642477e75f07a30affb6

Function insert may, for example, be determined as shown in the demonstration programme below#include <iostream> struct ListMem { int listvalue; ListMem* next = 0; }; class List; class Iterator { public: void setValue(int a); void next(); int value(); bool equal(Iterator& other); private: ListMem* item = 0; friend class List; }; class List { public: void push(int a); Iterator begin(); Iterator end(); bool insert( int a, size_t n = 0 ); private: ListMem* cur = 0; ListMem* head = 0; }; void List::push( int a ) { head = new ListMem { a, head }; } bool List::insert( int a, size_t n ) { if ( n == 0 ) { push( a ); } else { ListMem **current = &head; while ( *current != nullptr &amp;&amp; --n ) current = &amp;( *current )-&gt;next; if ( n == 0 ) { ListMem *next = new ListMem { a, ( *current )-&gt;next }; ( *current )-&gt; next = next; } } return n == 0; } ;int main() { const int N = 10; List list; for ( int i = 0; i &lt; N; i++ ) list.insert( i, i ); return 0; }

Try this:insert into public."PARAMETER_NAME"(name,short_name) select _name, _short_name where not exists(select 1 from public."PARAMETER_NAME" where short_name = _short_name and coalesce(name,'')=coalesce(_name,'') ) returning id into id_param_name; Second option: To make the impossible repetition of records a unique index. Unfortunately, in postgress, NULL records are not included in the index and uniqueness is not checked for them. To address this problem, a functional index should be made:create unique index public."PARAMETER_NAME_UNIQ" on public."PARAMETER_NAME"(_short_name, coalesce(name,'')); After which your insertion can be written as follows:BEGIN insert into public."PARAMETER_NAME"(name,short_name) values(_name,_short_name) returning id into id_param_name; EXCEPTION WHEN unique_violation THEN -- Ничего не делаем, либо сообщаем об ошибке дублирования и т.п. END;

If in the table chat unique id c AUTO_INCREMENT, after addition, you should be available insertId♪//Отправка сообщения в базу данных var query = connection.query( 'INSERT INTO chat SET ?', post, function(err, result) { console.log('Добавлено сообщение с id = ' + result.insertId); }); https://github.com/mysqljs/mysql#getting-the-id-of-an-inserted-row ♪

One request for a lie, but in your case, a functions. last_insert_id()update table MYTABLE set outer_id = id where id = last_insert_id()

You've got ajax in parallel with the ajax's departure of the uniform, and the page is overloaded before the answer comes to ajax. Whatever happens is standard (not ajax) submit forms:$("#send").click(function() { var dannie = $("form").serialize(); $.ajax({ ... }); return false; // !!! }); Or on recommendation @ Other : $("#send").click(function(event) { event.preventDefault(); // !!! var dannie = $("form").serialize(); $.ajax({ ... }); }); Also:$("form").submit(function(event) { event.preventDefault(); // !!! var dannie = $("form").serialize(); $.ajax({ ... }); });