I am trying to debug a really strange problem.
One of the parts of my database that used to work correctly suddenly stopped working. I have reduced the problem to this:
First of all: those are my databases
users: id(int), email(var).....
calendar : id(int) , user_id(int)(fk->users(id))
calendar_field : id(int), text(text), rating(int), calendar_id(int)(fk-> calendar(id)), week_number(int)
This is your creation script:
USERS
-- Table: public.users
-- DROP TABLE public.users;
CREATE TABLE public.users
(
id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
email character varying(254) COLLATE pg_catalog."default" NOT NULL,
password character varying(60) COLLATE pg_catalog."default" NOT NULL,
refreshtoken character varying COLLATE pg_catalog."default",
birth_date date,
first_name character varying COLLATE pg_catalog."default",
second_name character varying COLLATE pg_catalog."default",
years_to_live integer,
register_date date,
death_date date,
weeks_to_live integer,
CONSTRAINT users_pkey PRIMARY KEY (id),
CONSTRAINT users_email_unique UNIQUE (email)
)
TABLESPACE pg_default;
ALTER TABLE public.users
OWNER to postgres;
CALENDAR:
-- Table: public.calendar
-- DROP TABLE public.calendar;
CREATE TABLE public.calendar
(
id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
user_id integer,
CONSTRAINT calendar_pkey PRIMARY KEY (id),
CONSTRAINT "userId" FOREIGN KEY (user_id)
REFERENCES public.users (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
)
TABLESPACE pg_default;
ALTER TABLE public.calendar
OWNER to postgres;
CALENDAR_FIELD
-- Table: public.calendar_field
-- DROP TABLE public.calendar_field;
CREATE TABLE public.calendar_field
(
id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
text text COLLATE pg_catalog."default",
rating integer,
calendar_id integer NOT NULL,
week_number integer,
CONSTRAINT "CalendarField_pkey" PRIMARY KEY (id),
CONSTRAINT "calendarField_calendar_id_fk_calendar_id" FOREIGN KEY (calendar_id)
REFERENCES public.calendar (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
)
TABLESPACE pg_default;
ALTER TABLE public.calendar_field
OWNER to postgres;
What is the problem there? My program generates the following data in the database:
users:
- id: 58
- email: email1@email.com
- password: password
- refreshtoken: hash....
- birth_date: 2008-08-08
- first_name: first
- second_name: second
- years_to_live: 100
- register_date: 2020-05-12
- death_date: 2097-12-08
- weeks_to_live 5218
calendar:
- id: 64
- user_id: 58
calendar_field
-id: 462640
-text: notext
-rating: 0
-calendar_id: 64
-week_number: 1
And when I try to do this I select
SELECT * from calendar_field cf join calendar c on (c.user_id = cf.calendar_id) where week_number='1' and user_id='58';
It doesn't return rows, when I remember perfectly how it used to return some rows
It is not the curious fact, it is that when I insert the data manually, filling each field 1 by 1 with pgadmin, for example with this:
users:
- id: 58
- email: email2@email.com
- password: password
- refreshtoken: token
- birth_date: null
- first_name: null
- second_name: null
- years_to_live: null
- register_date: null
- death_date: null
- weeks_to_live null
calendar:
- id: 65
- user_id: 77
calendar_field
-id: 3000
-text: sd
-rating: 0
-calendar_id: 65
-week_number: 1
And then I throw it, it returns 0 rows too
SELECT * from calendar_field cf join calendar c on (c.user_id = cf.calendar_id) where week_number='1' and user_id='77';
(At the time of writing the above selection doesn't work, but sometimes after inserting the data manually it used to work)
And with both data inserted starting
SELECT * from calendar_field cf join calendar c on (c.user_id = cf.calendar_id) ;
it will also return 0 rows. What's going on?