Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE users( id serial PRIMARY KEY, name varchar(64) );
CREATE TABLE books( id serial PRIMARY KEY, name varchar(64) );


CREATE TABLE users_books( id serial, user_id integer REFERENCES users(id) NOT NULL,
book_id integer REFERENCES books(id),
 stock integer NOT NULL
);

CREATE TABLE



CREATE TABLE



CREATE TABLE


INSERT INTO users(name) VALUES('Ryan');
INSERT INTO users(name) VALUES('Michelle');

INSERT INTO books(name) VALUES('Some book Part 1');
INSERT INTO books(name) VALUES('syche');


INSERT INTO users_books(user_id,book_id, stock) VALUES(1,1,100);
INSERT INTO users_books(user_id,book_id, stock) VALUES(1,2,200);
INSERT INTO users_books(user_id,book_id, stock) VALUES(2,1,200);

SELECT ub.*,us.name AS user_name, book.name AS book_name FROM users_books ub 
INNER JOIN users us ON ub.user_id = us.id
INNER JOIN books book ON ub.book_id = book.id;

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1




id
user_id
book_id
stock
user_name
book_name


1
1
1
100
Ryan
Some book Part 1

2
1
2
200
Ryan
syche

3
2
1
200
Michelle
Some book Part 1



SELECT 3


with data(d) as (VALUES ('
{
      "user_id": 1,
      "name" : "Ryan",
      "books" : [
      {
        "book_id" : 1,
        "stock" : 500
      },
      {
        "book_id" : 2,
        "stock" : 500
      },
      {
        "name": "My new book 1",
        "stock" : 500
      }

      ]

}'::jsonb)
), normalized_data as (
    select (d ->> 'user_id')::int as user_id,
       book_id, stock, name
    FROM data
    JOIN LATERAL (SELECT * FROM jsonb_to_recordset(d -> 'books') 
                    as books(book_id int, stock int, name text)
    ) sub ON TRUE
), update_existing as (
  UPDATE users_books set stock = normalized_data.stock
  FROM normalized_data
  WHERE users_books.user_id = normalized_data.user_id
    AND users_books.book_id = normalized_data.book_id
    AND normalized_data.book_id IS NOT NULL
), insert_new_book as (
INSERT INTO books (name)
  SELECT name from normalized_data
  WHERE book_id IS NULL
  RETURNING id, name
)
INSERT INTO users_books (user_id, book_id, stock)
SELECT user_id, id, stock
FROM insert_new_book
JOIN normalized_data ON normalized_data.name = insert_new_book.name;



INSERT 0 1


SELECT ub.*,us.name AS user_name, book.name AS book_name FROM users_books ub 
INNER JOIN users us ON ub.user_id = us.id
INNER JOIN books book ON ub.book_id = book.id;


id
user_id
book_id
stock
user_name
book_name


3
2
1
200
Michelle
Some book Part 1

4
1
3
500
Ryan
My new book 1

1
1
1
500
Ryan
Some book Part 1

2
1
2
500
Ryan
syche



SELECT 4

id	user_id	book_id	stock	user_name	book_name
1	1	1	100	Ryan	Some book Part 1
2	1	2	200	Ryan	syche
3	2	1	200	Michelle	Some book Part 1