YugaByte YugaByte
Enterprise
Open Source
Customers
Use Cases
Globally Distributed Applications Real-Time Streaming & Analytics Transactional NoSQL Planet-Scale SQL Distributed Transactional Key-Value DB
Resources
All Resources Docs Blog
Get Started
latest v1.0
  • Introduction
  • Quick Start
    • 1. Install YugaByte DB
    • 2. Create Local Cluster
    • 3. Test YCQL API
    • 4. Test YEDIS API
    • 5. Test YSQL API
    • 6. Run Sample Apps
  • Explore Core Features
    • Cloud Native
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. Observability
      • 4. Orchestration Readiness
    • Transactional
      • 1. ACID Transactions
      • 2. Secondary Indexes
      • 3. JSON Documents
    • High Performance
      • Tunable Reads
    • Planet Scale
      • 1. Global Distribution
      • 2. Auto Sharding
      • 3. Auto Rebalancing
    • PostgreSQL (Beta)
      • 1. Linear Scalability
      • 2. Fault Tolerance
      • 3. JOINs
      • 4. Aggregations
      • 5. Expressions
      • 6. Views
  • Develop
    • Tools
      • cqlsh
      • TablePlus
    • Client Drivers
      • C/C++
      • C#
      • Go
      • Java
      • NodeJS
      • Python
    • Learn App Dev
      • 1. SQL vs NoSQL
      • 2. Data Modeling
      • 3. Data Types
      • 4. ACID Transactions
      • 5. Aggregations
      • 6. Batch Operations
    • Ecosystem Integrations
      • Apache Kafka
      • Apache Spark
      • JanusGraph
      • KairosDB
      • Presto
      • Metabase
    • Real World Examples
      • E-Commerce App
      • IoT Fleet Management
      • Retail Analytics
  • Deploy
    • Checklist
    • Manual Deployment
      • 1. System Configuration
      • 2. Install Software
      • 3. Start YB-Masters
      • 4. Start YB-TServers
      • 5. Verify Deployment
    • Kubernetes
      • Helm Chart
      • Local SSD
    • Docker Swarm
    • Public Clouds
      • Amazon Web Services
      • Google Cloud Platform
      • Microsoft Azure
    • Pivotal Cloud Foundry
    • Enterprise Edition
      • 1. Initial Setup
      • 2. Install Admin Console
      • 3. Configure Admin Console
      • 4. Configure Cloud Providers
  • Secure
    • Security Checklist
    • Authentication
    • Authorization
      • 1. RBAC Model
      • 2. Create Roles
      • 3. Grant Permissions
    • TLS Encryption
      • 1. Prepare Nodes
      • 2. Server-server encryption
      • 3. Client-server encryption
      • 4. Connect to cluster
  • Manage
    • Backup and Restore
      • Backing Up Data
      • Restoring Data
    • Data Migration
      • Bulk Import
      • Bulk Export
    • Change Cluster Config
    • Upgrade Deployment
    • Diagnostics Reporting
    • Enterprise Edition
      • Create Universe - Multi-Zone
      • Create Universe - Multi-Region
      • Edit Universe
      • Edit Config Flags
      • Health Checking and Alerts
      • Node Status & Actions
      • Read Replicas
      • Backup & Restore
      • Upgrade Universe
      • Delete Universe
  • Troubleshoot
    • Troubleshooting Overview
    • Cluster Level Issues
      • YCQL Connection Issues
      • YEDIS Connection Issues
    • Node Level Issues
      • Check Processes
      • Inspect Logs
      • System Stats
    • Enterprise Edition
      • Troubleshoot Universes
  • Architecture
    • Basic Concepts
      • Single Node
      • Universe, Cluster, YB-TServer, YB-Master
      • Sharding
      • Replication
      • Persistence
      • Query Layer
      • Acknowledgements
    • Core Functions
      • Universe Creation
      • Table Creation
      • Write IO Path
      • Read IO Path
      • High Availability
    • Transactions
      • Isolation Levels
      • Single Row Transactions
      • Distributed Transactions
      • Transactional IO Path
  • Comparisons
    • Apache Cassandra
    • MongoDB
    • Redis In-Memory Store
    • FoundationDB
    • Amazon DynamoDB
    • Azure Cosmos DB
    • Google Cloud Spanner
    • Apache HBase
  • API Reference
    • YCQL
      • ALTER KEYSPACE
      • ALTER ROLE
      • ALTER TABLE
      • CREATE INDEX
      • CREATE KEYSPACE
      • CREATE ROLE
      • CREATE TABLE
      • CREATE TYPE
      • DROP INDEX
      • DROP KEYSPACE
      • DROP ROLE
      • DROP TABLE
      • DROP TYPE
      • GRANT PERMISSION
      • GRANT ROLE
      • REVOKE PERMISSION
      • REVOKE ROLE
      • USE
      • INSERT
      • SELECT
      • UPDATE
      • DELETE
      • TRANSACTION
      • TRUNCATE
      • Simple Value
      • Subscript
      • Function Call
      • Operator Call
      • BLOB
      • BOOLEAN
      • MAP, SET, LIST
      • FROZEN
      • INET
      • Integer & Counter
      • Non-Integer
      • TEXT
      • Date & Time Types
      • UUID & TIMEUUID
      • JSONB
      • Date & Time Functions
    • YEDIS
      • APPEND
      • AUTH
      • CONFIG
      • CREATEDB
      • DELETEDB
      • LISTDB
      • SELECT
      • DEL
      • ECHO
      • EXISTS
      • EXPIRE
      • EXPIREAT
      • FLUSHALL
      • FLUSHDB
      • GET
      • GETRANGE
      • GETSET
      • HDEL
      • HEXISTS
      • HGET
      • HGETALL
      • HINCRBY
      • HKEYS
      • HLEN
      • HMGET
      • HMSET
      • HSET
      • HSTRLEN
      • HVALS
      • INCR
      • INCRBY
      • KEYS
      • MONITOR
      • PEXPIRE
      • PEXPIREAT
      • PTTL
      • ROLE
      • SADD
      • SCARD
      • SET
      • SETRANGE
      • SISMEMBER
      • SMEMBERS
      • SREM
      • STRLEN
      • ZRANGE
      • TSADD
      • TSCARD
      • TSGET
      • TSLASTN
      • TSRANGEBYTIME
      • TSREM
      • TSREVRANGEBYTIME
      • TTL
      • ZADD
      • ZCARD
      • ZRANGEBYSCORE
      • ZREM
      • ZREVRANGE
      • PUBSUB
      • PUBLISH
      • SUBSCRIBE
      • UNSUBSCRIBE
      • PSUBSCRIBE
      • PUNSUBSCRIBE
    • YSQL (Beta)
      • DDL Statements
        • CREATE DATABASE
        • CREATE TABLE
        • CREATE VIEW
        • DROP DATABASE
        • DROP TABLE
      • DML Statements
        • INSERT
        • SELECT
      • Datatypes
        • FLOAT
        • INTEGER
        • TEXT
      • Transactions
      • Roles and Permissions
      • Prepared Statements
      • Explain Statement
  • Admin Reference
    • yb-ctl
    • yb-docker-ctl
    • docker-compose
    • yb-master
    • yb-tserver
  • FAQs
    • Product
    • Architecture
    • Enterprise Edition
    • Cassandra Compatibility
> Explore Core Features > PostgreSQL (Beta) >

Aggregations

Aggregates functions are supported. However, the computation of these aggregations is not yet pushed down to the individual nodes. This is a performance optimization that we expect to be available in future beta releases.

If you haven’t installed YugaByte DB yet, do so first by following the Quick Start guide.

  • macOS
  • Linux
  • Docker

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

$ ./bin/yb-ctl destroy

Start a new local cluster - by default, this will create a 3-node universe with a replication factor of 3.

$ ./bin/yb-ctl create --enable_postgres

2. Run psql to connect to the service

$ ./bin/psql -p 5433 -U postgres
psql (10.3, server 10.4)
Type "help" for help.

postgres=#

3. Create tables and insert data

postgres=> CREATE TABLE multi_types(h bigint, r float, vi int, vs text, PRIMARY KEY (h, r));
postgres=> INSERT INTO multi_types(h, r, vi, vs) VALUES (1, 1.5, 3, 'YugaByte');
INSERT INTO multi_types(h, r, vi, vs) VALUES (2, 3.5, 4, 'YugaByte-1.1');

4. Query data with aggregates

postgres=> SELECT * FROM multi_types;
h |  r  | vi |      vs      
---+-----+----+--------------
 2 | 3.5 |  4 | YugaByte-1.1
 1 | 1.5 |  3 | YugaByte
(2 rows)

Run another query.

postgres=> SELECT avg(vi), sum(h + r) FROM multi_types;
avg         | sum 
--------------------+-----
 3.5000000000000000 |   8
(1 row)

6. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

$ ./bin/yb-ctl destroy

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

$ ./bin/yb-ctl destroy

Start a new local cluster - by default, this will create a 3-node universe with a replication factor of 3.

$ ./bin/yb-ctl create --enable_postgres

2. Run psql to connect to the service

$ ./bin/psql -p 5433 -U postgres
psql (10.3, server 10.4)
Type "help" for help.

postgres=#

3. Create tables and insert data

postgres=> CREATE TABLE multi_types(h bigint, r float, vi int, vs text, PRIMARY KEY (h, r));
postgres=> INSERT INTO multi_types(h, r, vi, vs) VALUES (1, 1.5, 3, 'YugaByte');
INSERT INTO multi_types(h, r, vi, vs) VALUES (2, 3.5, 4, 'YugaByte-1.1');

4. Query data with aggregates

postgres=> SELECT * FROM multi_types;
h |  r  | vi |      vs      
---+-----+----+--------------
 2 | 3.5 |  4 | YugaByte-1.1
 1 | 1.5 |  3 | YugaByte
(2 rows)

Run another query.

postgres=> SELECT avg(vi), sum(h + r) FROM multi_types;
avg         | sum 
--------------------+-----
 3.5000000000000000 |   8
(1 row)

6. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

$ ./bin/yb-ctl destroy

1. Setup - create universe

If you have a previously running local universe, destroy it using the following.

$ ./yb-docker-ctl destroy

Start a new local cluster - by default, this will create a 3-node universe with a replication factor of 3.

$ ./yb-docker-ctl create --enable_postgres

2. Run psql to connect to the service

$ docker exec -it yb-postgres-n1 /home/yugabyte/postgres/bin/psql -p 5433 -U postgres
psql (10.3, server 10.4)
Type "help" for help.

postgres=#

3. Create tables and insert data

postgres=> CREATE TABLE multi_types(h bigint, r float, vi int, vs text, PRIMARY KEY (h, r));
postgres=> INSERT INTO multi_types(h, r, vi, vs) VALUES (1, 1.5, 3, 'YugaByte');
INSERT INTO multi_types(h, r, vi, vs) VALUES (2, 3.5, 4, 'YugaByte-1.1');

4. Query data with aggregates

postgres=> SELECT * FROM multi_types;
h |  r  | vi |      vs      
---+-----+----+--------------
 2 | 3.5 |  4 | YugaByte-1.1
 1 | 1.5 |  3 | YugaByte
(2 rows)

Run another query.

postgres=> SELECT avg(vi), sum(h + r) FROM multi_types;
avg         | sum 
--------------------+-----
 3.5000000000000000 |   8
(1 row)

6. Clean up (optional)

Optionally, you can shutdown the local cluster created in Step 1.

$ ./yb-docker-ctl destroy
Explore Core Features
JOINs
Explore Core Features
Expressions
Have a technical question?
Slack StackOverflow
Found a bug or have an enchancement request?
GitHub
YugaByte
Contact Us
Copyright © 2017-2019 YugaByte, Inc. All rights reserved.