<p dir="auto"><strong>Migrated issue, originally created by Marcin Barczyński (<a href="https://github.com/marcinbarczynski">@marcinbarczynski</a>)</strong></p>
<p dir="auto">I would like to aggregate two columns from a single table with <code class="notranslate">array_agg</code>. Here is the code showing that mapping types in output rows works fine with psycopg2, but can't be done with SQLAlchemy:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import BigInteger, Column, func, tuple_, ARRAY
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import ARRAY as PG_ARRAY

import psycopg2

engine = create_engine(PG_URI, echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()
class Demo(Base):
    __tablename__ = 'demo'

    key = Column(BigInteger, primary_key=True)
    val1 = Column(BigInteger, primary_key=True)
    val2 = Column(BigInteger, primary_key=True)


session.execute(&quot;DROP TABLE IF EXISTS demo CASCADE&quot;)
session.execute(&quot;CREATE TABLE demo AS SELECT 1 AS key, gen AS val1, gen + 1 AS val2 FROM generate_series(1, 5) gen&quot;)
session.commit()

conn = psycopg2.connect(PG_URI)
cur = conn.cursor()
cur.execute(&quot;SELECT array_agg(ARRAY[val1, val2]) FROM demo GROUP BY key&quot;)
print cur.fetchone()[0]  
# prints list: [[1, 2], [2, 3], [3, 4], [4, 5], [5, 6]]

q = session.query(func.array_agg(ARRAY(tuple_(Demo.val1, Demo.val2)))).group_by(Demo.key)
# fails with AttributeError: 'ARRAY' object has no attribute 'type'"><pre class="notranslate"><code class="notranslate">from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import BigInteger, Column, func, tuple_, ARRAY
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import ARRAY as PG_ARRAY

import psycopg2

engine = create_engine(PG_URI, echo=True)
Session = sessionmaker(bind=engine)
session = Session()

Base = declarative_base()
class Demo(Base):
    __tablename__ = 'demo'

    key = Column(BigInteger, primary_key=True)
    val1 = Column(BigInteger, primary_key=True)
    val2 = Column(BigInteger, primary_key=True)


session.execute("DROP TABLE IF EXISTS demo CASCADE")
session.execute("CREATE TABLE demo AS SELECT 1 AS key, gen AS val1, gen + 1 AS val2 FROM generate_series(1, 5) gen")
session.commit()

conn = psycopg2.connect(PG_URI)
cur = conn.cursor()
cur.execute("SELECT array_agg(ARRAY[val1, val2]) FROM demo GROUP BY key")
print cur.fetchone()[0]  
# prints list: [[1, 2], [2, 3], [3, 4], [4, 5], [5, 6]]

q = session.query(func.array_agg(ARRAY(tuple_(Demo.val1, Demo.val2)))).group_by(Demo.key)
# fails with AttributeError: 'ARRAY' object has no attribute 'type'
</code></pre></div>
<p dir="auto">I tried to use <code class="notranslate">tuple_</code>, but then string is returned instead of list:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="q = session.query(func.array_agg(tuple_(Demo.val1, Demo.val2))).group_by(Demo.key)
print type(q.first()[0]) 
# prints string: '{&quot;(1,2)&quot;,&quot;(2,3)&quot;,&quot;(3,4)&quot;,&quot;(4,5)&quot;,&quot;(5,6)&quot;}'"><pre class="notranslate"><code class="notranslate">q = session.query(func.array_agg(tuple_(Demo.val1, Demo.val2))).group_by(Demo.key)
print type(q.first()[0]) 
# prints string: '{"(1,2)","(2,3)","(3,4)","(4,5)","(5,6)"}'
</code></pre></div>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">I think it might be better to just get the array detection to do the right thing</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="from sqlalchemy import *
from sqlalchemy.dialects.postgresql import array
from sqlalchemy.dialects import postgresql


c1 = column('foo', Integer)

expr = array([c1])

print expr.compile(dialect=postgresql.dialect())

# fails
function = func.array_agg(expr)

# fails
function = func.array_agg(expr, type_=ARRAY(Integer))"><pre class="notranslate"><code class="notranslate">from sqlalchemy import *
from sqlalchemy.dialects.postgresql import array
from sqlalchemy.dialects import postgresql


c1 = column('foo', Integer)

expr = array([c1])

print expr.compile(dialect=postgresql.dialect())

# fails
function = func.array_agg(expr)

# fails
function = func.array_agg(expr, type_=ARRAY(Integer))
</code></pre></div>