All Questions
Tagged with many-to-many postgresql
37 questions
2
votes
2
answers
97
views
Many-to-many: select an entity by matching relationship
I have a many to many relationship implemented:
CREATE TABLE public.message (
id BIGSERIAL PRIMARY KEY,
name varchar(40) UNIQUE NOT NULL
);
CREATE TABLE public.package(
id BIGSERIAL ...
- 23
0
votes
1
answer
41
views
Postgres trigger to update records of several parent tables based on values of a many-to-many join table referencing those parent table records
I have a PostgreSQL 15 join table schema.m2mjointable to link records of several parent tables (schema.table_a, schema.table_b, ...) with other records of those same parent tables:
id
ref_table
...
- 384
0
votes
1
answer
23
views
Alerting system relationships and STI in PostgreSQL
I'm no expert in DBMS and trying to build up an alerting system that can attach itself to many tables, so I figured asking the question would help my architecture.
The thing that may confuse me a ...
- 101
0
votes
1
answer
43
views
Possible options for SQL and NoSQL combination for social media data
I have searched this forum for an answer but could not find anything relevant. We are designing database schemas for a social media app with posts, groups, users and chats etc and it looks like we ...
- 101
2
votes
2
answers
604
views
Design many-to-many join table with additional column
I have product and documentation tables and want to hava a many-to-many relationship between them with an additional condition.
The tables look like this:
create table product (
id bigserial primary ...
- 21
3
votes
2
answers
881
views
Ensuring that a combination of records in a many to many relationship is unique
We want to enforce that a combination of an entity is unique in a join table.
Suppose we have the following many to many relationship between widgets and combinations
widgets
-------
widget_id
w1 ...
- 131
1
vote
0
answers
377
views
Many to Many triangle between three tables
I am having three entities:
Student, Group, Class. All of them have many-to-many relationship:
One student can attend many classes, and many classes may be assigned to one user.
One student can be a ...
- 11
2
votes
1
answer
2k
views
Multiple tables for many-to-many mappings or one table with multiple columns?
I'm using Postgres 14 and I have a tags table with a many-to-many relationship with other entities such as posts, comments, pages, etc. I've currently been representing these with tables like ...
- 123
1
vote
1
answer
310
views
Filter table lookup by array value, but returning groupby unfiltered
I have multiple lookup tables and each need to be filtered by an array of values:
select
array_agg(joined_properties.name) as properties,
items.name
from items
left join (
select properties.name, ...
0
votes
0
answers
436
views
Many-to-many relationship vs one-to-many relationships
Recently I read an article about building a tag table for better seraching post by tags. The author suggested using one-to-many relationship with post table -> tag table with tag as text[] type. ...
- 101
5
votes
3
answers
15k
views
Single foreign key for referencing one of multiple tables
Currently I have a schema that looks like this:
create table person(
id uuid primary key default gen_random_uuid() not null,
);
create table car(
id uuid primary key default gen_random_uuid() ...
- 153
0
votes
1
answer
80
views
newbie question - how to list people who has not email address related to him/her? [closed]
Sorry for my newbie question: I would like to list all people even those who has no email address in case of using many-to-many relation with superclass-sublclass inheritance.
Please see SQL Fiddle: ...
- 11
1
vote
1
answer
257
views
What is the proper way to validate that a many-to-many relationship will not surpass a certain limit?
In a Postgres DB, I have a relationship as follows:
A Group table has a column userEntranceLimits.
A UserEntrance table has a column userId and a column groupId.
The count of a UserEntrance's ...
- 11
0
votes
0
answers
34
views
Many-To-Many Relation by storing comma-separated ids in a column? [duplicate]
Is it OK to sometimes instead of making a join table for a many-to-many relation, store the ids of entity A in a column of entity B? It would look something like this:
// A
id | name
---------
10 | ...
- 101
0
votes
0
answers
89
views
Filling a database (from a temporary database) with a Many to Many relation
I have a temporary table that I fill in using a CSV file.
CREATE TEMP TABLE temp_products(
product_marketplace_name varchar,
product_link varchar,
product_name varchar,
...
- 21
7
votes
1
answer
1k
views
Recursivly get a Tree Via self joined table
Using other questions here and Postgresql documentation I've managed to build a many-to-many self joined table.
However adding a WHERE clause is giving me trouble.
Problem:
A Category can have many ...
- 171
0
votes
1
answer
447
views
How to perform schema migration from array to many-to-many to lookup table?
I currently am storing values in a column of type array like follows (this is just an example):
CREATE TABLE blog_posts(
id INTEGER PRIMARY KEY,
blog_title TEXT,
tags TEXT[]
);
With the ...
- 101
0
votes
1
answer
53
views
What is the proper way to relate many to many?
I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
7
votes
3
answers
15k
views
Query "all of" across many-to-many relation
Imagine a setup of three tables, User, Group, and UserGroup, where UserGroup consists of simple a foreign key to each of User and Group tables.
User
----
id
name
Group
-----
id
name
UserGroup
------...
- 307
2
votes
1
answer
113
views
Unique sets of IDs from table
I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified):
CREATE ...
1
vote
1
answer
37
views
How to fetch those records who appears in many records in many to many relationship in PostgreSQL?
I have the following three tables:
create table users(id int primary key, name varchar);
create table groups(id int primary key, name varchar);
create table user_groups(uid int references users(id),...
1
vote
1
answer
494
views
Better way to select a matching subset of rows?
DB Fiddle link. I have a many to many table like this:
CREATE TABLE house_to_cats (
id SERIAL PRIMARY KEY,
house_id INTEGER,
cat_id INTEGER
);
-- house with cats 1 and 2: too small
INSERT INTO ...
- 699
2
votes
2
answers
7k
views
How to prevent deadlocks in many-to-many insert/update trigger function?
I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point.
I have a tweet table that receives thousands of records per second. One of the columns ...
- 401
1
vote
1
answer
11k
views
A question about foreign keys and many to many relationships
My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school ...
- 78
2
votes
1
answer
3k
views
Efficiently return two aggregated arrays from a m:n table
I have a table for a many-to-many relationship over a users table to represent a follow relationship between users:
CREATE TABLE users (
id text PRIMARY KEY,
username text NOT NULL
);
CREATE ...
- 193
3
votes
1
answer
7k
views
Using JSONB Column or Another Table to Save Relationsips
I tried to search here thoroughly but did not find any answers.
I have a PostgreSQL database, which has two main tables:
documents
users
These two tables have different relations. A user can:
...
4
votes
5
answers
723
views
Get members of a given group from N:M relationship
I have this N:M relationship:
CREATE TABLE auth_user (
id integer NOT NULL PRIMARY KEY,
username character varying(150) NOT NULL UNIQUE
);
CREATE TABLE auth_group (
id integer NOT NULL ...
- 1,561
1
vote
2
answers
728
views
Select Items that has one item but not the other
I have three tables:
Table1:
ID Name
1 Item1
2 Item2
3 Item3
Table2:
ID Name Type
1 Logistics One
2 Human Resource Two
3 Finance ...
- 532
1
vote
0
answers
433
views
Multiple many-to-many database relations
SO told me to ask here, so this is just copy-pasted from over there
We're making a database of anime. Every anime can be listed as having been made by an arbitrary number of studios, and every studio ...
- 13
9
votes
5
answers
3k
views
Mutually exclusive many-to-many relationships
I have a table containers that can have a many-to-many relationships to several tables, let's say those are plants, animals and bacteria. Each container can contain an arbitrary number of plants, ...
- 1,250
0
votes
1
answer
54
views
Selecting union of results of dataset-producing function when applied element-wise to an array
This problem is somewhat an academic curiosity. I'm attempting to implement a SQL (i.e. not plpgSQL) function which takes an array of input data, transforms each entry to a set of zero or more rows ...
- 153
1
vote
1
answer
51
views
Access articles from a table if user belongs to qualified group
The tables below are myuser & mygroup, where each user has membership to 1 or more groups via an M2M table membership.
The articles are accessible by users if one of the condition is satisfied
1)...
- 43
3
votes
1
answer
3k
views
Getting duplicate results in many to many query
I have designed a bookstore app with these tables. There's a book with an M2M relation to author. Lastly, there's table reader which keeps track of which user has read the book i.e. an entry in the ...
- 43
1
vote
1
answer
383
views
UPDATE n:m relation in view as array (operations)
Environment
I came up with a view and trigger based solution to modify rows in multiple underlying tables in a Postgres 9.5.3 database. This works reasonable using Qt's model view programming. The ...
- 255
11
votes
5
answers
35k
views
Build JSON object from one-to-many relationship data in a single query?
I have a PostgreSQL 9.5.3 DB with tables like this:
container
id: uuid (pk)
... other data
thing
id: uuid (pk)
... other data
container_thing
container_id: uuid (fk)
...
- 255
4
votes
1
answer
4k
views
How to make unique check without creating heavy index in postgres
I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and ...
- 143
4
votes
0
answers
2k
views
Postgres: Arrays instead of intermediate tables for many-to-many relations
I have a PostgreSQL 9.3.7 database scheme representing a directed graph of vertices and edges with some many-to-many relations. I'm only ever interested in getting all vertices that a given vertex ...
- 1,031