Skip to content

Instantly share code, notes, and snippets.

View check_unaccent.sql
SELECT unaccent('Tiếng Việt có dấu')
@handuy
handuy / unaccent.sql
Created Jul 10, 2018
Cài đặt extension unaccent
View unaccent.sql
CREATE EXTENSION unaccent
@handuy
handuy / select_5000_noti.sql
Last active Jun 8, 2018
Select 5000 bản ghi từ bảng noti --> mất 2 secs 15 msec
View select_5000_noti.sql
select * from noti where sender = 1015;
select * from noti where sender = 1640;
select * from noti where sender = 1641;
select * from noti where sender = 1016;
select * from noti where sender = 1001;
select * from noti where sender = 1002;
select * from noti where sender = 1003;
select * from noti where sender = 1004;
select * from noti where sender = 1005;
select * from noti where sender = 1006;
@handuy
handuy / insert_5000_records_to_noti.sql
Last active Jun 8, 2018
Có 5000 thông báo mới --> insert 5000 records vào bảng noti --> mất 175 ms
View insert_5000_records_to_noti.sql
insert into noti (sender, receiver, content, status, time) values (1001, 201, 'Vestibulum ac est lacinia nisi venenatis tristique.', false, '2018-05-21 13:16:49');
insert into noti (sender, receiver, content, status, time) values (1002, 202, 'Donec posuere metus vitae ipsum.', false, '2018-05-16 23:13:54');
insert into noti (sender, receiver, content, status, time) values (1003, 203, 'Donec dapibus.', false, '2018-05-28 11:51:08');
insert into noti (sender, receiver, content, status, time) values (1004, 204, 'Maecenas pulvinar lobortis est.', false, '2018-06-03 01:49:02');
insert into noti (sender, receiver, content, status, time) values (1005, 205, 'Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.', false, '2018-05-03 08:06:04');
insert into noti (sender, receiver, content, status, time) values (1006, 206, 'Duis at velit eu est congue elementum.', false, '2018-05-09 15:51:32');
insert into noti (sender, receiver, content, status, time) values (1007, 207, 'Morbi odio odio,
@handuy
handuy / insert_1000_records_to_noti.sql
Last active Jun 8, 2018
Có 1000 thông báo mới trong hệ thống --> insert 1000 bản ghi mới vào bảng noti --> mất 80 ms
View insert_1000_records_to_noti.sql
insert into noti (sender, receiver, content, status, time) values (1, 101, 'Morbi vel lectus in quam fringilla rhoncus.', false, '2018-05-24 11:22:23');
insert into noti (sender, receiver, content, status, time) values (2, 102, 'Nam congue, risus semper porta volutpat, quam pede lobortis ligula, sit amet eleifend pede libero quis orci.', true, '2018-05-24 18:29:06');
insert into noti (sender, receiver, content, status, time) values (3, 103, 'Curabitur convallis.', false, '2018-05-10 02:07:20');
insert into noti (sender, receiver, content, status, time) values (4, 104, 'Proin interdum mauris non ligula pellentesque ultrices.', true, '2018-06-06 21:39:57');
insert into noti (sender, receiver, content, status, time) values (5, 105, 'Nulla ac enim.', false, '2018-05-10 20:08:04');
insert into noti (sender, receiver, content, status, time) values (6, 106, 'Ut tellus.', false, '2018-05-04 05:48:19');
insert into noti (sender, receiver, content, status, time) values (7, 107, 'Vestibulum ac est lacinia nisi venenatis
@handuy
handuy / select_1000_noti.sql
Last active Jun 8, 2018
Select 1000 bản ghi từ bảng noti --> mất 388 ms
View select_1000_noti.sql
select * from noti where sender = 1;
select * from noti where sender = 2;
select * from noti where sender = 3;
select * from noti where sender = 4;
select * from noti where sender = 5;
select * from noti where sender = 6;
select * from noti where sender = 7;
select * from noti where sender = 8;
select * from noti where sender = 9;
select * from noti where sender = 10;
@handuy
handuy / Get_tag.sql
Created Jun 7, 2018
Từ một mảng các tag id lấy ra thông tin chi tiết từng phẩn tử tag
View Get_tag.sql
Bảng posts có cột tag_ids là một mảng các id link đến bảng tag
Với 1 mảng tag có id là [1,2,3], cần lấy ra một mảng các thông tin cụ thể của từng tag id
Cách 1:
SELECT temp_post.Id, temp_post.author_name, temp_post.avatar, temp_post.title,
json_agg(temp_tag.detail) AS tag
FROM
(
SELECT posts.Id AS Id, users.full_name AS author_name, users.avatar AS avatar, posts.title AS title, posts.tag_ids AS tag_ids
FROM posts, users
@handuy
handuy / row_to_json.sql
Created Jun 7, 2018
Lấy dữ liệu của từng cột cho vào JSON
View row_to_json.sql
SELECT row_to_json(t)
FROM (
SELECT * FROM tags WHERE id IN ('1', '2')
) t
You can’t perform that action at this time.