handuy

SELECT row_to_json(t)
FROM (
  SELECT * FROM tags WHERE id IN ('1', '2')
) t

handuy

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

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

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

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

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

CREATE EXTENSION unaccent

handuy

SELECT unaccent('Tiếng Việt có dấu')

handuy

á a
à a
ã a
ạ a
ả a
A a
Á a
À a
à a
Ạ a

handuy

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS posts;

CREATE TABLE users (id int, name text, age int);

CREATE TABLE posts (id int, title text);

SELECT create_reference_table('users');