PostgreSQL – SQL – Aggregate Functions – Day 01

Background

Let us take a quick look at a couple of Aggregate functions available in PostgreSQL.

DDL

Region


-- Drop table
-- DROP TABLE us.region;
CREATE TABLE us.region (
	"name" varchar(60) NOT NULL,
	CONSTRAINT region_pk PRIMARY KEY (name)
);

State


-- Drop table
-- DROP TABLE us.state;
CREATE TABLE us.state (
	"name" varchar(60) NOT NULL,
	nameabbrev bpchar(2) NOT NULL,
	region varchar(60) NOT NULL,
	CONSTRAINT state_pk PRIMARY KEY (name),
	CONSTRAINT fk_state_region
               FOREIGN KEY (region)
               REFERENCES us.region
               (
                 name
               )
);

CREATE UNIQUE INDEX state_nameabbrev_idx
ON us.state
USING btree
(
   nameabbrev
)
;

DML

Region


truncate us."region" cascade
;

insert into us."region"
(
  	  "name"
)
values
  	    ('Northeast')
  	  , ('Southeast')
  	  , ('Midwest')
  	  , ('Southwest')
  	  , ('West')	

;

commit; 

State

truncate us."state"
;

insert into us."state"
(
  	  "name"
	, "nameabbrev"
	, "region"
)
values
	-- North East
  	  ('Maine'	, 'ME'	, 'Northeast')
	, ('Rhode Island', 'RI', 'Northeast')
	, ('New Jersey', 'NJ', 'Northeast')
	, ('Delaware', 'DE', 'Northeast')
	, ('Maryland', 'MD', 'Northeast')

	-- South East
  	, ('West Virginia'	, 'WV'	, 'Southeast')
	, ('Virginia', 'VI', 'Southeast')
	, ('Kentucky', 'KY', 'Southeast')
	, ('Tennessee', 'TN', 'Southeast')
	, ('South Carolina', 'SC', 'Southeast')
	, ('North Carolina', 'NC', 'Southeast')
	, ('Georgia', 'GA', 'Southeast')
	, ('Alabama', 'AL', 'Southeast')

	-- Mid West
  	, ('Kansas'	, 'KS'	, 'Midwest')
  	, ('Nebraksa'	, 'NE'	, 'Midwest')
  	, ('South Dakota'	, 'SD'	, 'Midwest')
  	, ('North Dakota'	, 'ND'	, 'Midwest')  	

	-- South West
  	, ('Texas'	, 'TX'	, 'Southwest')
	, ('Oklahoma', 'OK', 'Southwest')
	, ('New Mexico', 'NM', 'Southwest')
	, ('Arizona', 'AZ', 'Southwest')	

	-- West
  	, ('Colorado'	, 'CO'	, 'West')
	, ('Wyoming', 'WY', 'West')
	, ('Montana', 'MT', 'West')
	, ('Idaho', 'ID', 'West')
	, ('Washington', 'WA', 'West')
	, ('Oregon', 'OR', 'West')
	, ('Utah', 'UT', 'West')
	, ('Nevada', 'NV', 'West')
	, ('California', 'CA', 'West')
	, ('Alaska', 'AK', 'West')
	, ('Hawaii', 'HI', 'West')	

;

commit;

Query

array_agg

Code


select 

          tblUSR.name
            as "region"

        , array_agg
            (
                tblUSS.name
            )
                as "stateAsArray"

from   us.state tblUSS

inner join us.region tblUSR

        on tblUSS.region = tblUSR.name

group by
    tblUSR.name

order by
    tblUSR.name 

Output

Output.DBeaver

state.region.array.01.20190809.0915AM

Output.Azure Data Studio

state.region.array.01.20190809.0919AM

array_to_string

Code


select
          tblUSR.name
            as "region"

        , array_to_string
            (
                array_agg
                (

                    tblUSS.name

                    ORDER BY
                        tblUSS.name
                )
                , ', '
            ) as "state"

from   us.state tblUSS

inner join us.region tblUSR

        on tblUSS.region = tblUSR.name

group by
    tblUSR.name

order by
    tblUSR.name 

Output

Output.DBeaver

state.region.arrayToSTring.01.20190809.0917AM

Output.Azure Data Studio

state.region.array.01.20190809.0921AM

Error

Dbeaver

Exception

java.lang.NullPointerException

java.lang.NullPointerException
	at org.jkiss.dbeaver.model.DBUtils.getEntityFromMetaData(DBUtils.java:1804)
	at org.jkiss.dbeaver.model.DBUtils.getEntityFromMetaData(DBUtils.java:1792)
	at org.jkiss.dbeaver.model.impl.jdbc.data.JDBCCollection.makeCollectionFromArray(JDBCCollection.java:197)
	at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCArrayValueHandler.getValueFromObject(JDBCArrayValueHandler.java:62)
	at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreArrayValueHandler.getValueFromObject(PostgreArrayValueHandler.java:82)
	at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreArrayValueHandler.getValueFromObject(PostgreArrayValueHandler.java:1)
	at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCComplexValueHandler.fetchColumnValue(JDBCComplexValueHandler.java:50)
	at org.jkiss.dbeaver.ext.postgresql.model.data.PostgreArrayValueHandler.fetchColumnValue(PostgreArrayValueHandler.java:49)
	at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:49)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataReceiver.fetchRow(ResultSetDataReceiver.java:122)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.fetchQueryData(SQLQueryJob.java:755)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:484)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:407)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:146)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:405)
	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:849)
	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2776)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:98)
	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:146)
	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:96)
	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:102)
	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Summary

PostgreSQL offers solid tooling for SQL data aggregation.

Unfortunate, Dbeaver throws an exception when it tries to display a PostgreSQL column that is an array.

The exception is java.lang.NullPointerException.

One thought on “PostgreSQL – SQL – Aggregate Functions – Day 01

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s