Query AACT with pSQL

The most straight forward way to access the content of ClinicalTrials.gov is by using the free postgreSQL command line query tool, pSQL, to connect to and query the AACT database. This tool is part of the PostgreSQL software package that can be downloaded for free from this site. (Note: AACT is an open source project, based entirely on open source tools; the goal is to make this information as freely accessible as possible to everyone who can make use of it.)

Install PostgreSQL Database Platform

The PostgreSQL package (open source and freely available) includes a number of nice tools. If you intend to access the public AACT database hosted in the cloud, the only PostgreSQL tool you need is psql. If you would like to download a static copy of AACT to use on your local machine, you'll need the full postgreSQL package including the tools to create and populate the database (createdb & pg_restore). These tools are part of the standard PostgreSQL package, so once installed, you will be able to create your own AACT database.

AACT is currently running on version 11.5 of PostgreSQL; it's recommended you install this version or higher. You many download PostgreSQL from the PostgreSQL Download Site or this Enterprise DB site.

We've provided a few screenshots and basic instructions here to help guide you through the installation process.

Launch pSQL

Once PostgreSQL is installed on your local machine, you can launch the query tool by entering the command: psql from a terminal session. To access the AACT cloud-hosted database, open a terminal window and enter the psql command with the following connection credentials.

  
        ->  PGPASSWORD=your AACT password psql --host aact-db.ctti-clinicaltrials.org --port=5432 --username=(sign up/in to get a username) --dbname=aact
     
      psql (9.5.3, server 11.5)
      SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
      Type "help" for help.
     
      aact=# _

The aact=# prompt indicates that you're in a pSQL console session and can enter SQL queries against the AACT database. A simple example:

aact=# select count(*) from studies; count -------- 387486 (1 row) aact=# _ As of 08/24/21

Important note: To exit pSQL, enter the (not entirely intuitive) command: \q and hit return.

      
       aact=>  \q
         
        -> _