Skip to content

Instantly share code, notes, and snippets.

@JoshCheek
Last active December 13, 2017 19:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save JoshCheek/57de9a5bd3c46848e7685a222e99db87 to your computer and use it in GitHub Desktop.
Save JoshCheek/57de9a5bd3c46848e7685a222e99db87 to your computer and use it in GitHub Desktop.
distinct array postgresql
require 'pg'
db = PG.connect(dbname: 'josh_testing')
# PG.connect(dbname: 'postgres').exec('drop database josh_testing')
# PG.connect(dbname: 'postgres').exec('create database josh_testing')
db.exec 'begin' # throws away changes when process exits
def db.exec(*)
super.to_a
rescue Exception
$!.set_backtrace caller.drop(1)
raise
end
db.exec <<~SQL
do $$
declare
ary integer[];
begin
ary := ARRAY[1, 2, 1];
raise notice 'INITIAL: %', ary;
ary := array_append(ary, 3);
ary := array_append(ary, 2);
raise notice 'WITH DUPLICATES: %', ary;
-- ary := (select array_agg(distinct n) from unnest(ary) as n);
ary := ARRAY(select distinct unnest(ary));
raise notice 'AFTER: %', ary;
end $$ language plpgsql;
SQL
# !> NOTICE: INITIAL: {1,2,1}
# !> NOTICE: WITH DUPLICATES: {1,2,1,3,2}
# !> NOTICE: AFTER: {1,2,3}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment