Last active
December 13, 2017 19:28
-
-
Save JoshCheek/57de9a5bd3c46848e7685a222e99db87 to your computer and use it in GitHub Desktop.
distinct array postgresql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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