logo
down
shadow

jsonb and primary/foreign keys: which performs better in PostgreSQL?


jsonb and primary/foreign keys: which performs better in PostgreSQL?

By : user2955075
Date : November 22 2020, 10:38 AM
fixed the issue. Will look into that further All values involved in a PRIMARY KEY or FOREIGN KEY constraint must be stored as dedicated columns (best in normalized form). Constraints and references do not work for values nested inside a json / jsonb column.
As for the rest of the data: it depends. Having them inside a jsonb (preferably) value carries the well-known advantages and disadvantages of storing unstructured document-type data.
code :


Share : facebook icon twitter icon
primary and foreign keys in postgresql - total beginner

primary and foreign keys in postgresql - total beginner


By : Simon.W
Date : March 29 2020, 07:55 AM
wish help you to fix your issue One entity that is missing from your list is branch (of the company). The table definitions are then:
code :
CREATE TABLE country (
  id         integer NOT NULL PRIMARY KEY,
  name       varchar NOT NULL,
  population integer NOT NULL
);

CREATE TABLE city (
  id         integer NOT NULL PRIMARY KEY,
  name       varchar NOT NULL,
  population integer NOT NULL,
  country    integer NOT NULL REFERENCES country(id)
);
CREATE TABLE company (
  id         integer NOT NULL PRIMARY KEY,
  name       varchar NOT NULL
);

CREATE TABLE branch (
  company    integer NOT NULL REFERENCES company(id),
  city       integer NOT NULL REFERENCES city(id),
  name       varchar,
  employees  integer NOT NULL,
  PRIMARY KEY (company, city)
);
SELECT
  company.name AS company,
  branch.name AS branch,
  city.name AS city,
  branch.employees,
  city.population AS city_pop,
  country.population AS country_pop
FROM company
JOIN branch ON branch.company = company.id
JOIN city ON city.id = branch.city
JOIN country ON country.id = city.country
ORDER BY company.name, branch.name;
Storing foreign keys in postgresql jsonb a bad idea?

Storing foreign keys in postgresql jsonb a bad idea?


By : sazzum
Date : March 29 2020, 07:55 AM
may help you . You need to take into consideration following things about JSONB type:
queries will be more complex; if you even feel comfortable with all JSONB functions (they are more about SELECT statements), UPDATE operation will still be tricky - please consider @Erwin's answer regarding this. size overhead; for small docs it will not really matter, but at scale you might hit a wall. Just compare pg_column_size() results for your case. limited indexing support; you will have no luck when you perform search in array by element (functions like json_array_elements() etc). Default GIN index supports queries with the (CONTAINS) @>, (EXISTS)?, (EXISTS ALL)?& and (EXISTS ANY)?| operators, so you need to be careful with the queries you are using. jsonb supports btree and hash indexes. You can check details here.
Use JSONB for postgres columns other than primary and foreign keys

Use JSONB for postgres columns other than primary and foreign keys


By : mushwonk
Date : March 29 2020, 07:55 AM
should help you out It turns out you're on to something here.
The major points of using a relational database.
code :
create table customers (
    id text primary key
);

create table products (
    id text primary key,
    title text,
    sales_rank integer,
    "group" text,
    category text,
    subcategory text,
    similar_ids text[]
);

create table reviews (
    customer_id text references customers(id),
    product_id text references products(id),
    "date" timestamp,
    rating integer,
    votes integer,
    helpful_votes integer
);
create table customers (
    id text primary key
);

create table products_jb (
    id text primary key,
    fields jsonb
);

create table reviews_jb (
    customer_id text references customers(id),
    product_id text references products_jb(id),
    fields jsonb
);
create table reviews_jsonb (
    review jsonb
);
test=> select round(avg(r.rating), 2)
from reviews r
join products p on p.id = r.product_id
where p.category = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 138.631 ms
test=> select round(avg((review#>>'{review,rating}')::numeric),2)
test-> from reviews_jsonb
test-> where review #>>'{product,category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 380.697 ms
test=> select round(avg((r.fields#>>'{rating}')::numeric),2)
from reviews_jb r
join products_jb p on p.id = r.product_id
where p.fields#>>'{category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 192.333 ms
test=> create index products_category on products(category);
CREATE INDEX
Time: 491.969 ms

test=> select round(avg(r.rating), 2)
from reviews r
join products p on p.id = r.product_id
where p.category = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 128.212 ms
test=> create index on reviews_jsonb using gin(review);
CREATE INDEX
Time: 25253.348 ms
test=> select round(avg((review#>>'{review,rating}')::numeric),2)
from reviews_jsonb
where review #>>'{product,category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 363.222 ms
test=> create index on products_jb using gin(fields);
CREATE INDEX
Time: 3654.894 ms
test=> create index on reviews_jb using gin(fields);
CREATE INDEX
Time: 3237.534 ms
test=> select round(avg((r.fields#>>'{rating}')::numeric),2)
from reviews_jb r
join products_jb p on p.id = r.product_id
where p.fields#>>'{category}' = 'Home & Garden';
 round 
-------
  4.59
(1 row)

Time: 183.679 ms
PostgreSQL: Foreign Key to a table with 3 Primary Keys on PgAdmin

PostgreSQL: Foreign Key to a table with 3 Primary Keys on PgAdmin


By : demaxman
Date : March 29 2020, 07:55 AM
may help you . You cannot create that foreign key constraint because there is no unique constraint on units(category_id).
For a foreign key you need a PRIMARY KEY or UNIQUE constraint that is defined on exactly the target columns. This constraint identifies the target row.
code :
ALTER TABLE units
   ADD UNIQUE (category_id);
POSTGRESQL Foreign Key Referencing Primary Keys of two Different Tables

POSTGRESQL Foreign Key Referencing Primary Keys of two Different Tables


By : DSK
Date : March 29 2020, 07:55 AM
help you fix your problem There's more than one way to do this in PostgreSQL. Personally, I prefer this way.
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org