Executing SQL Commands With psql on PostgreSQL db in SystemLink Server

SystemLink Server installs psql along with the local PostgreSQL database it installs and uses by default.  You will grab the PostgreSQL port and password from the PostgreSQLDatabase.json config file, then use those to connect with psql to the PostgreSQL database that SystemLink Server is configured to use.  You must run the DOS commands below on the computer hosting the PostgreSQL database that SystemLink Server is configured to use-- connecting with psql to a remote PostgreSQL database requires additional changes to the database configuration.

1. Find the SystemLink Server file that contains the PostgreSQL port and password, and copy them for use in subsequent steps
   C:\ProgramData\National Instruments\Skyline\Config\PostgreSQLDatabase.json
    
       
2. Open a DOS shell on the SystemLink Server computer and switch the directory to the bin folder of the PostgreSQL database it uses
   cd "C:\Program Files\National Instruments\Shared\Skyline\PostgreSQLDatabase\pgsql\bin"
       
   
3. Connect to the PostgreSQL database with the pgsl command that installs by default in that folder, using the port you copied earlier (nisystemlink is the default user name SystemLink Server uses for its connection to the PostgreSQL database-- it rarely changes, but you can double-check in the PostgreSQLDatabase.json file shown above)
   psql -h localhost -p 5433 -U nisystemlink
        
4. Type or paste in the password you copied earlier (without quotes).  You will not see what you type or paste.  Then hit the Enter key.
        
5. You can now interactively type SQL commands to execute.  As a first example, try the built in ANALYZE command for PostgreSQL databases
   ANALYZE "TestMonitor".steps;
        
   
6. As a second example try querying the statistical results that the ANALYZE command placed in the pg_stats table
   SELECT schemaname,tablename,attname,inherited,null_frac,avg_width,n_distinct,correlation FROM pg_stats WHERE schemaname = 'TestMonitor'; 
    
7. As a third example, navigate to Test Insights and select the Test Results table.  Pick a result with steps, then read off the result_id from the last part of the URL in the result page:
   
        
8. Now query the first 20 steps from that result, like this:
   SELECT id,step_id,parent_id,result_id,name FROM "TestMonitor".steps WHERE result_id = '5b64b8f8-050c-40d4-bd4b-2e3b2b3dd93e' LIMIT 20;