How do I fill in empty table rows that reference a different table column ID?
I want to fill in blank notification parameters for each user that already exists. Identifiers (PK) are automatically generated by Hibernate in every table. Here is the user table:
CREATE TABLE lottery_user (
id int8 not null,
email varchar(255) not null,
password varchar(255) not null,
notification_settings_id int8,
role varchar (50) default 'USER',
registry_date TIMESTAMP default now(),
primary key (id)
ADD CONSTRAINT FK_USER_TO_NOTIFICATION_SETTINGS
FOREIGN KEY (notification_settings_id) REFERENCES notification_settings
);
And here is the notification_settings table that I need to populate for users not populating them.
CREATE TABLE notification_settings (
id int8 not NULL ,
test1_events bool DEFAULT TRUE ,
test2_events bool DEFAULT TRUE ,
test3_events bool DEFAULT TRUE ,
test4_events bool DEFAULT TRUE ,
PRIMARY KEY (id)
);
Basically, I need to use "INSERT INTO notification_settings (test1_events, test2_events, test3_events, test4_events) VALUES (True, True, True, True)"
something similar to this. And, of course, the condition should be something like "where these lines are empty for users". I can't seem to get the syntax.
BIG NOTE: The SQL code is for presentation, so you can have an idea of what tables I have. I just need to get the INSERT script to the right. The tables work fine, you just need to generate notification_settings values for existing users.
Another note: Using Flyway, so it's not only sleep mode. If it is related to anything.
source to share
I will answer my own question, how did I deal with this. First, I insert ids into notification_settings
id, then I get those ids and set them to the lottery_user
FK ( notification_settings_id
) table . Then I just remove the unneeded ids. Yes, not perfect, but it works.
INSERT INTO notification_settings (id) select lu.id from lottery_user lu where lu.id not in(select ns.id from notification_settings ns);
update lottery_user lu set notification_settings_id = (select ns.id from notification_settings ns where ns.id = lu.id) where lu.notification_settings_id is null;
delete from notification_settings ns where not exists (select * from lottery_user lu where lu.notification_settings_id = ns.id);
Also, a script to change the sequence for new Lottery_user objects.
do $$
declare maxid int;
begin
select max(id) from lottery_user into maxid;
IF maxid IS NOT NULL THEN
EXECUTE 'ALTER SEQUENCE notification_settings_seq START with '|| maxid;
END IF;
end;
$$ language plpgsql;
source to share
Since your foreign key constraint goes from notification_settings to user, the "where these lines are empty for user X" condition does not apply to your schema. On the other hand - "I want to fill in empty notification settings for each user that already exists" can be done using the construction insert...select
:
set @rank=0
select @maxid = max(id) from notification_settings
insert into notification_settings (id)
select @maxid + @rank:=@rank+1 as rank
from user
where notification_settings_id is null
I wonder how to get these newly generated ids back into the custom table. Homework for next time :)
source to share