https://github.com/pganalyze/pg_query

Ruby extension to parse, deparse and normalize SQL queries using the PostgreSQL query parser
https://github.com/pganalyze/pg_query

Keywords from Contributors

activerecord mvc activejob marshalling rubygems feature-flag yarn crash-reporting rack rspec

Last synced: about 2 hours ago
JSON representation

Repository metadata

Ruby extension to parse, deparse and normalize SQL queries using the PostgreSQL query parser

Host: GitHub
URL: https://github.com/pganalyze/pg_query
Owner: pganalyze
License: bsd-3-clause
Created: 2014-05-11T23:58:52.000Z (almost 12 years ago)
Default Branch: main
Last Pushed: 2026-01-27T06:11:35.000Z (about 1 month ago)
Last Synced: 2026-03-01T23:25:37.588Z (1 day ago)
Language: C
Homepage:
Size: 8.53 MB
Stars: 865
Watchers: 17
Forks: 97
Open Issues: 16
Releases: 0
Metadata Files:
- Readme: README.md
- Changelog: CHANGELOG.md
- License: LICENSE

pg_query

This Ruby extension uses the actual PostgreSQL server source to parse SQL queries and return the internal PostgreSQL parsetree.

In addition the extension allows you to normalize queries (replacing constant values with $n) and parse these normalized queries into a parsetree again.

When you build this extension, it builds parts of the PostgreSQL server source (see libpg_query), and then statically links it into this extension.

This may seem like a lot of complexity, but is the only reliable way of parsing all valid PostgreSQL queries.

You can find further examples and a longer rationale here: https://pganalyze.com/blog/parse-postgresql-queries-in-ruby.html

Installation

gem install pg_query

Due to compiling parts of PostgreSQL, installation might take a while on slower systems. Expect up to 5 minutes.

Usage

Parsing a query

PgQuery.parse("SELECT 1")

=> #<PgQuery::ParserResult:0x000000012ec4e9e0
  @query="SELECT 1",
  @tree=<PgQuery::ParseResult:
    version: 160001,
    stmts: [
      <PgQuery::RawStmt:
        stmt: <PgQuery::Node:
          select_stmt: <PgQuery::SelectStmt:
            distinct_clause: [],
            target_list: [
              <PgQuery::Node:
                res_target: <PgQuery::ResTarget:
                  name: "",
                  indirection: [],
                  val: <PgQuery::Node:
                    a_const: <PgQuery::A_Const:
                      ival: <PgQuery::Integer: ival: 1>,
                      isnull: false,
                      location: 7
                    >
                  >,
                  location: 7
                >
              >
            ],
            from_clause: [],
            group_clause: [],
            group_distinct: false,
            window_clause: [],
            values_lists: [],
            sort_clause: [],
            limit_option: :LIMIT_OPTION_DEFAULT,
            locking_clause: [],
            op: :SETOP_NONE,
            all: false
          >
        >,
        stmt_location: 0,
        stmt_len: 0
      >
    ]
  >,
  @warnings=[],
  @tables=nil,
  @aliases=nil,
  @cte_names=nil,
  @functions=nil
>

Modifying a parsed query and turning it into SQL again

This is a simple example for deparse, for more complex modification, use walk!.

parsed_query = PgQuery.parse("SELECT * FROM users")

# Modify the parse tree in some way
parsed_query.tree.stmts[0].stmt.select_stmt.from_clause[0].range_var.relname = 'other_users'

# Turn it into SQL again
parsed_query.deparse
=> "SELECT * FROM other_users"

Parsing a normalized query

# Normalizing a query (like pg_stat_statements in Postgres 10+)
PgQuery.normalize("SELECT 1 FROM x WHERE y = 'foo'")

=> "SELECT $1 FROM x WHERE y = $2"

Extracting tables from a query

PgQuery.parse("SELECT $1 FROM x JOIN y USING (id) WHERE z = $2").tables

=> ["x", "y"]

Extracting columns from a query

PgQuery.parse("SELECT $1 FROM x WHERE x.y = $2 AND z = $3").filter_columns

=> [["x", "y"], [nil, "z"]]

Fingerprinting a query

PgQuery.parse("SELECT 1").fingerprint

=> "50fde20626009aba"

PgQuery.parse("SELECT 2; --- comment").fingerprint

=> "50fde20626009aba"

# Faster fingerprint method that is implemented inside the native C library
PgQuery.fingerprint("SELECT $1")

=> "50fde20626009aba"

Scanning a query into tokens

PgQuery.scan('SELECT 1 --comment')

=> [<PgQuery::ScanResult: version: 160001, tokens: [
<PgQuery::ScanToken: start: 0, end: 6, token: :SELECT, keyword_kind: :RESERVED_KEYWORD>,
<PgQuery::ScanToken: start: 7, end: 8, token: :ICONST, keyword_kind: :NO_KEYWORD>,
<PgQuery::ScanToken: start: 9, end: 18, token: :SQL_COMMENT, keyword_kind: :NO_KEYWORD>]>,
 []]

Walking the parse tree

For generalized use, PgQuery provides walk! as a means to recursively work with the parsed query.

This can be used to create a bespoke pretty printer:

parsed_query = PgQuery.parse "SELECT * FROM tbl"
parsed_query.walk! { |node, k, v, location| puts k }

More usefully, this can be used to rewrite a query. For example:

parsed_query.walk! do |node, k, v, location|
  next unless k.eql?(:range_var) || k.eql?(:relation)
  next if v.relname.nil?
  v.relname = "X_" + v.relname
end

parsed_query.deparse

There are some caveats, and limitations, in this example.

First, some of the tree nodes are frozen. You can replace them, but you cannot modify in place.

Second, table rewriting is a bit more nuanced than this example. While this will rewrite the table names, it will
not correctly handle all CTEs, or rewrite columns with explicit table names.

Supported Ruby Versions

Currently tested and officially supported Ruby versions:

CRuby 3.0
CRuby 3.1
CRuby 3.2
CRuby 3.3
CRuby 3.4

Not supported:

JRuby: pg_query relies on a C extension, which is discouraged / not properly supported for JRuby
TruffleRuby: GraalVM does not support sigjmp, which is used by the Postgres error handling code (pg_query uses a copy of the Postgres parser & error handling code)

Developer tasks

Update libpg_query source

In order to update to a newer Postgres parser, first update libpg_query to the new Postgres version and tag a release.

Once that is done, follow the following steps:

Update LIB_PG_QUERY_TAG and LIB_PG_QUERY_SHA256SUM in Rakefile
Run rake update_source to update the source code
Commit the Rakefile and the modified files in ext/pg_query to this source tree and make a PR

Resources

See libpg_query for pg_query in other languages, as well as products/tools built on pg_query.

Original Author

Lukas Fittl

Special Thanks to

Jack Danger Canty, for significantly improving deparsing

License

PostgreSQL server source code, used under the PostgreSQL license.
Portions Copyright (c) 1996-2023, The PostgreSQL Global Development Group
Portions Copyright (c) 1994, The Regents of the University of California

All other parts are licensed under the 3-clause BSD license, see LICENSE file for details.
Copyright (c) 2015, Lukas Fittl lukas@fittl.com
Copyright (c) 2016-2023, Duboce Labs, Inc. (pganalyze) team@pganalyze.com

Owner metadata

Name: pganalyze
Login: pganalyze
Email: team@pganalyze.com
Kind: organization
Description: PostgreSQL Performance Monitoring
Website: https://pganalyze.com/
Location: San Francisco, CA
Twitter:
Company:
Icon url: https://avatars.githubusercontent.com/u/3105953?v=4
Repositories: 54
Last ynced at: 2026-02-27T19:46:01.070Z
Profile URL: https://github.com/pganalyze

GitHub Events

Total

Delete event: 4
Pull request event: 24
Fork event: 9
Issues event: 17
Watch event: 67
Issue comment event: 60
Push event: 26
Pull request review comment event: 7
Pull request review event: 19
Create event: 8

Last Year

Delete event: 2
Pull request event: 15
Fork event: 6
Issues event: 14
Watch event: 34
Issue comment event: 36
Push event: 17
Pull request review comment event: 7
Pull request review event: 13
Create event: 5

Committers metadata

Last synced: 6 days ago

Total Commits: 529
Total Committers: 47
Avg Commits per committer: 11.255
Development Distribution Score (DDS): 0.361

Commits in past year: 24
Committers in past year: 6
Avg Commits per committer in past year: 4.0
Development Distribution Score (DDS) in past year: 0.292

Name	Email	Commits
Lukas Fittl	l**s@f**m	338
Jack Danger Canty	g**b@j**m	32
Mehmet Emin KARAKAŞ	e**0@g**m	24
Herwin	h**w@h**l	18
James Silberbauer	j**l@t**t	14
Olle Jonsson	o**n@g**m	10
Markus Ecker	me@i****t	8
Tassos Bareiss	t**s@g**m	8
Stan Hu	s**u@g**m	7
Herwin Weststrate	h**n@q**l	5
Chris Winslett	c**s@c**o	4
Maciek Sakrejda	m**a@g**m	4
Roi Avinoam	a**r@g**m	4
msepga	1****a	4
Shimpei Makimoto	m**o@t**n	3
Stefan Merettig	s**g@n**g	3
dependabot[bot]	4****]	3
Himanshu Garg	h**s@g**m	3
Sean Dick	s**n@s**t	3
Keiko Oda	k**3@g**m	3
Arturo Herrero	a**o@g**m	3
Sean Linsley	c**e@s**m	2
Lukas Fittl	l**l@d**l	2
xiaohui	6****h	1
jcoleman	j**n@g**m	1
seph	s**h@k**o	1
Yuki Nishijima	y**a@g**m	1
Vladimir Támara Patiño	v**a@p**g	1
Tam Chau	c**m@g**m	1
PikachuEXE	g**t@p**t	1
and 17 more...

Committer domains:

Issue and Pull Request metadata

Last synced: 3 days ago

Total issues: 58
Total pull requests: 119
Average time to close issues: 8 months
Average time to close pull requests: 26 days
Total issue authors: 51
Total pull request authors: 29
Average comments per issue: 2.95
Average comments per pull request: 1.02
Merged pull request: 98
Bot issues: 0
Bot pull requests: 4

Past year issues: 8
Past year pull requests: 19
Past year average time to close issues: 2 days
Past year average time to close pull requests: 12 days
Past year issue authors: 8
Past year pull request authors: 7
Past year average comments per issue: 2.13
Past year average comments per pull request: 0.95
Past year merged pull request: 12
Past year bot issues: 0
Past year bot pull requests: 0

More stats: https://issues.ecosyste.ms/repositories/lookup?url=https://github.com/pganalyze/pg_query

Top Issue Authors

kduraiswami (3)
ghost (2)
skpravien (2)
directionless (2)
ankane (2)
EQuincerot (2)
mfechner (1)
Willianvdv (1)
mhenrixon (1)
feliperaul (1)
aight8 (1)
jdenquin (1)
harto (1)
imcvampire (1)
TomNaessens (1)

Top Pull Request Authors

lfittl (55)
Tassosb (8)
stanhu (6)
olleolleolle (5)
dependabot[bot] (4)
msakrejda (4)
msepga (4)
chaadow (3)
keiko713 (3)
purcell (2)
owst (2)
mfechner (2)
ankane (2)
seanlinsley (2)
vtamara (2)

Top Issue Labels

question (5)

Top Pull Request Labels

dependencies (4)
enhancement (1)

Package metadata

Total packages: 14
Total downloads:
- rubygems: 131,880,182 total
Total docker downloads: 874,110,422
Total dependent packages: 33 (may contain duplicates)
Total dependent repositories: 1,005 (may contain duplicates)
Total versions: 256
Total maintainers: 4

gem.coop: pg_query

Parses SQL queries using a copy of the PostgreSQL server query parser

Homepage: https://github.com/pganalyze/pg_query
Documentation: http://www.rubydoc.info/gems/pg_query/
Licenses: BSD-3-Clause
Latest release: 6.2.2 (published about 1 month ago)
Last Synced: 2026-03-01T23:01:45.970Z (1 day ago)
Versions: 77
Dependent Packages: 0
Dependent Repositories: 0
Downloads: 63,631,747 Total
Docker Downloads: 437,053,164
Rankings:
- Dependent repos count: 0.0%
- Dependent packages count: 0.0%
- Average: 0.182%
- Docker downloads count: 0.272%
- Downloads: 0.457%
Maintainers (2)
- lfittl
- seanlinsley

gem.coop: gitlab-pg_query

Parses SQL queries using a copy of the PostgreSQL server query parser

Homepage: http://github.com/pganalyze/pg_query
Documentation: http://www.rubydoc.info/gems/gitlab-pg_query/
Licenses: BSD-3-Clause
Latest release: 2.0.4 (published almost 5 years ago)
Last Synced: 2026-03-02T03:33:11.484Z (1 day ago)
Versions: 3
Dependent Packages: 0
Dependent Repositories: 0
Downloads: 2,306,924 Total
Docker Downloads: 2,047
Rankings:
- Dependent repos count: 0.0%
- Dependent packages count: 0.0%
- Average: 1.072%
- Docker downloads count: 2.105%
- Downloads: 2.184%
Maintainers (1)
- stanhu

rubygems.org: pg_query

Parses SQL queries using a copy of the PostgreSQL server query parser

Homepage: https://github.com/pganalyze/pg_query
Documentation: http://www.rubydoc.info/gems/pg_query/
Licenses: BSD-3-Clause
Latest release: 6.2.2 (published about 1 month ago)
Last Synced: 2026-03-02T02:31:56.257Z (1 day ago)
Versions: 77
Dependent Packages: 25
Dependent Repositories: 964
Downloads: 63,634,587 Total
Docker Downloads: 437,053,164
Rankings:
- Docker downloads count: 0.337%
- Downloads: 0.504%
- Dependent packages count: 0.924%
- Dependent repos count: 1.048%
- Average: 1.327%
- Stargazers count: 2.136%
- Forks count: 3.016%
Maintainers (2)
- seanlinsley
- lfittl