Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DROP TABLE IF EXISTS contacts;
CREATE TEMP TABLE contacts
as
select *
from (
VALUES
(1::int, 'abc'::VARCHAR),
(2::int, 'Aac'::VARCHAR),
(3::int, 'bvC'::VARCHAR),
(4::int, 'Bvc'::VARCHAR),
(5::int, 'Avc'::VARCHAR),
(6::int, 'BV'::VARCHAR),
(7::int, 'FR'::VARCHAR),
(8::int, 'dddD'::VARCHAR),
(9::int, 'WWW'::VARCHAR),
(10::int, 'WWW'::VARCHAR),
(11::int, 'WWW'::VARCHAR),
(12::int, 'WWW'::VARCHAR),
(13::int, 'WWW'::VARCHAR),
(14::int, 'WWW'::VARCHAR),
(15::int, 'WWW'::VARCHAR),
(16::int, 'WWW'::VARCHAR),
(17::int, 'WWW'::VARCHAR)
) as t (id, name);
SELECT * FROM generate_series(ascii('A'), ascii('Z'));
-- SELECT * FROM generate_series(ascii('A'), ascii('Z')) ABC GROUP BY ABC
select ascii('A');
select chr(65);
select substring('Abc' from 1 for 1);
select upper('a');
SELECT ABC, array_agg(contacts.* ORDER BY contacts.name, contacts.id)
FROM generate_series(ascii('A'), ascii('Z')) AS ABC INNER JOIN contacts
ON ascii(upper(substring(contacts.name FROM 1 FOR 1))) = ABC
GROUP BY ABC
ORDER BY ABC ASC;
-- PAGINATION
SELECT ABC, json_agg(contacts.*)
FROM generate_series(ascii('A'), ascii('Z')) AS ABC INNER JOIN (SELECT contacts.* from contacts ORDER BY contacts.name, contacts.id LIMIT 3 OFFSET 1) as contacts
ON ascii(upper(substring(contacts.name FROM 1 FOR 1))) = ABC
GROUP BY ABC
ORDER BY ABC ASC;
-- coalesce
SELECT ABC, coalesce(json_agg(contacts.*) FILTER (WHERE contacts.name NOTNULL), '{}')
FROM generate_series(ascii('A'), ascii('Z')) AS ABC LEFT JOIN (SELECT contacts.* from contacts ORDER BY contacts.name, contacts.id LIMIT 3 OFFSET 1) as contacts
ON ascii(upper(substring(contacts.name FROM 1 FOR 1))) = ABC
GROUP BY ABC
ORDER BY ABC ASC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.