Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31
All platforms

ybsql Examples ​

This section presents examples of some of the ybsql command-line options.

Connection Options ​

To connect to database premdb on host yb007 as user jamesbond:

me@yb100:~$ ybsql -h yb007 premdb -U jamesbond
Password for user jamesbond: 
ybsql (1.2.2)
Type:  \h for help with SQL commands
      \? for help with ybsql commands
      \g or terminate with semicolon to execute query
      \q to quit

-c Option ​

Use the -c option to run a ybsql backslash command, such as \d, or to run a query:

me@yb100:~$ ybsql premdb bobr -c "\d"
Password for user bobr: 
         List of relations
 Schema |   Name   | Type  |  Owner  
--------+----------+-------+---------
 public | awayteam | table | brumsby
 public | hometeam | table | brumsby
 public | match    | table | brumsby
 public | season   | table | brumsby
 public | team     | table | brumsby
(5 rows)
me@yb100:~$ ybsql premdb bobr -c 'select count(*) from match;'
Password for user bobr: 
 count 
-------
  8606
(1 row)

-e Option ​

Use the -e option to print SQL query statements as part of the output:

me@yb100:~$ ybsql premdb -e
ybsql (1.2.1)
Type "help" for help.

premdb=# select * from team where teamid=21;
select * from team where teamid=21;
 teamid | htid | atid |     name     | nickname | city  |   stadium   | capacity | avg_att 
--------+------+------+--------------+----------+-------+-------------+----------+---------
    21 |   22 |   71 | Leeds United | Whites   | Leeds | Elland Road |    39460 |   0.000
(1 row)

-A Option ​

Use the -A option to return unaligned output:

me@yb100:~$ ybsql premdb -A
ybsql (1.2.2)
Type:  \h for help with SQL commands
      \? for help with ybsql commands
      \g or terminate with semicolon to execute query
      \q to quit

premdb=# select * from team where teamid=21;
teamid|htid|atid|name|nickname|city|stadium|capacity|avg_att
21|22|71|Leeds United|Whites|Leeds|Elland Road|39460|0.000
(1 row)

Combination of -A, -F, -e, and -f options ​

Use this combination of options to run a query from a named file, echo the file in the output, return unaligned output, and set a field separator:

me@yb100:~$ ybsql premdb -A -F ':' -e -f goals_per_season.sql
select t1.season_name, t1.winners, homegoals+awaygoals as total
from
(select season_name, winners, sum(substr(ftscore,1,1)::int) homegoals
from season, match
where season.seasonid=match.seasonid
group by season_name, winners) t1,
(select season_name, winners, sum(substr(ftscore,3,1)::int) awaygoals
from season, match
where season.seasonid=match.seasonid
group by season_name, winners) t2
where t1.season_name=t2.season_name
order by 1,2;
season_name:winners:total
1992-1993:Manchester United:1222
1993-1994:Manchester United:1195
1994-1995:Blackburn Rovers:1195
1995-1996:Manchester United:988
1996-1997:Manchester United:970
1997-1998:Arsenal:1019
1998-1999:Manchester United:959
1999-2000:Manchester United:1060
2000-2001:Manchester United:992
2001-2002:Arsenal:1001
2002-2003:Manchester United:1000
2003-2004:Arsenal:1012
2004-2005:Chelsea:975
2005-2006:Chelsea:944
2006-2007:Manchester United:931
2007-2008:Manchester United:1002
2008-2009:Manchester United:942
2009-2010:Chelsea:1053
2010-2011:Manchester United:1063
2011-2012:Manchester City:1066
2012-2013:Manchester United:1063
2013-2014:Manchester City:1052
(22 rows)

-H option ​

Use the -H option to return the result set in HTML format:

me@yb100:~$ ybsql premdb -H -f total_goals_per_season_avg_per_match.sql
<table border="1">
  <tr>
   <th align="center">season_name</th>
   <th align="center">numteams</th>
   <th align="center">total_goals</th>
   <th align="center">goals_per_match</th>
  </tr>
  <tr valign="top">
   <td align="left">1995-1996</td>
   <td align="right">20</td>
   <td align="right">988</td>
   <td align="right">2.60</td>
  </tr>
  <tr valign="top">
   <td align="left">1996-1997</td>
   <td align="right">20</td>
   <td align="right">970</td>
   <td align="right">2.55</td>
...

-H and -T Options ​

Display the output in HTML format and use -T to set one or more HTML attributes:

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -H -T bgcolor=red
<table border="1" bgcolor=red>
  <tr>
   <th align="center">season_name</th>
   <th align="center">numteams</th>
   <th align="center">total_goals</th>
   <th align="center">goals_per_match</th>
  </tr>
...

-o Option ​

Use the -o option to send query results to a file:

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -o gps.txt
brumsby@brumsby:~$ more gps.txt
 season_name | numteams | total_goals | goals_per_match 
-------------+----------+-------------+-----------------
 1995-1996   |       20 |         988 |            2.60
 1996-1997   |       20 |         970 |            2.55
 1997-1998   |       20 |        1019 |            2.68
 1998-1999   |       20 |         959 |            2.52
 ...

-L Option ​

Use the -L option to send query results and the SQL statement itself to a file. Also return the query results to the screen.

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -L gps.txt
 season_name | numteams | total_goals | goals_per_match 
-------------+----------+-------------+-----------------
 1995-1996   |       20 |         988 |            2.60
 1996-1997   |       20 |         970 |            2.55
 1997-1998   |       20 |        1019 |            2.68
 1998-1999   |       20 |         959 |            2.52
 1999-2000   |       20 |        1060 |            2.79
 2000-2001   |       20 |         992 |            2.61
 2001-2002   |       20 |        1001 |            2.63
 2002-2003   |       20 |        1000 |            2.63
 2003-2004   |       20 |        1012 |            2.66
 2004-2005   |       20 |         975 |            2.57
 2005-2006   |       20 |         944 |            2.48
 2006-2007   |       20 |         931 |            2.45
 2007-2008   |       20 |        1002 |            2.64
 2008-2009   |       20 |         942 |            2.48
 2009-2010   |       20 |        1053 |            2.77
 2010-2011   |       20 |        1063 |            2.80
 2011-2012   |       20 |        1066 |            2.81
 2012-2013   |       20 |        1063 |            2.80
 2013-2014   |       20 |        1052 |            2.77
(19 rows)

me@yb100:~$ more gps.txt
********* QUERY **********
select *, (total_goals/380.00)::dec(3,2) as goals_per_match
from (
select season_name, numteams, 
sum(substr(ftscore,1,1)::int)+sum(substr(ftscore,3,1)::int) total_goals
from season, match 
where season.seasonid=match.seasonid and season.seasonid>=4
group by season_name,numteams
) t1 
order by 1;
**************************

 season_name | numteams | total_goals | goals_per_match 
-------------+----------+-------------+-----------------
 1995-1996   |       20 |         988 |            2.60
 1996-1997   |       20 |         970 |            2.55
 1997-1998   |       20 |        1019 |            2.68
 1998-1999   |       20 |         959 |            2.52
 1999-2000   |       20 |        1060 |            2.79
...

-P Option ​

Use the -P option to set the text format of the query output:

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -P format=latex
\begin{tabular}{l | r | r | r}
\textit{season\_name} & \textit{numteams} & \textit{total\_goals} & \textit{goals\_per\_match} \\
\hline
1995-1996 & 20 & 988 & 2.60 \\
1996-1997 & 20 & 970 & 2.55 \\
1997-1998 & 20 & 1019 & 2.68 \\
1998-1999 & 20 & 959 & 2.52 \\
1999-2000 & 20 & 1060 & 2.79 \\
2000-2001 & 20 & 992 & 2.61 \\
2001-2002 & 20 & 1001 & 2.63 \\
2002-2003 & 20 & 1000 & 2.63 \\
2003-2004 & 20 & 1012 & 2.66 \\
2004-2005 & 20 & 975 & 2.57 \\
2005-2006 & 20 & 944 & 2.48 \\
2006-2007 & 20 & 931 & 2.45 \\
2007-2008 & 20 & 1002 & 2.64 \\
2008-2009 & 20 & 942 & 2.48 \\
2009-2010 & 20 & 1053 & 2.77 \\
2010-2011 & 20 & 1063 & 2.80 \\
2011-2012 & 20 & 1066 & 2.81 \\
2012-2013 & 20 & 1063 & 2.80 \\
2013-2014 & 20 & 1052 & 2.77 \\
\end{tabular}

\noindent (19 rows) \\

-A and -R Options ​

Use the -A and -R options to set unaligned output with a special record separator:

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -A -R '--'
season_name|numteams|total_goals|goals_per_match--1995-1996|20|988|2.60--1996-1997|20|970|2.55--1997-1998|20|1019|2.68--1998-1999|20|959|2.52--1999-2000|20|1060|2.79--2000-2001|20|992|2.61--2001-2002|20|1001|2.63--2002-2003|20|1000|2.63--2003-2004|20|1012|2.66--2004-2005|20|975|2.57--2005-2006|20|944|2.48--2006-2007|20|931|2.45--2007-2008|20|1002|2.64--2008-2009|20|942|2.48--2009-2010|20|1053|2.77--2010-2011|20|1063|2.80--2011-2012|20|1066|2.81--2012-2013|20|1063|2.80--2013-2014|20|1052|2.77--(19 rows)

-s Option ​

Use the -s option to process query input in single-step mode:

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -s
***(Single step mode: verify command)*******************************************
select *, (total_goals/380.00)::dec(3,2) as goals_per_match
from (
select season_name, numteams, 
sum(substr(ftscore,1,1)::int)+sum(substr(ftscore,3,1)::int) total_goals
from season, match 
where season.seasonid=match.seasonid and season.seasonid>=4
group by season_name,numteams
) t1 
order by 1;
***(press return to proceed or enter x and return to cancel)********************
...

-t Option ​

Remove header and footer information from result sets with the -t option:

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -t
 1995-1996   |       20 |         988 |            2.60
 1996-1997   |       20 |         970 |            2.55
 1997-1998   |       20 |        1019 |            2.68
 1998-1999   |       20 |         959 |            2.52
 1999-2000   |       20 |        1060 |            2.79
 2000-2001   |       20 |         992 |            2.61
 2001-2002   |       20 |        1001 |            2.63
 2002-2003   |       20 |        1000 |            2.63
 2003-2004   |       20 |        1012 |            2.66
 2004-2005   |       20 |         975 |            2.57
 2005-2006   |       20 |         944 |            2.48
 2006-2007   |       20 |         931 |            2.45
 2007-2008   |       20 |        1002 |            2.64
 2008-2009   |       20 |         942 |            2.48
 2009-2010   |       20 |        1053 |            2.77
 2010-2011   |       20 |        1063 |            2.80
 2011-2012   |       20 |        1066 |            2.81
 2012-2013   |       20 |        1063 |            2.80
 2013-2014   |       20 |        1052 |            2.77

--expanded Option ​

Use the --expanded table format for query output:

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql --expanded
-[ RECORD 1 ]---+----------
season_name     | 1995-1996
numteams        | 20
total_goals     | 988
goals_per_match | 2.60
-[ RECORD 2 ]---+----------
season_name     | 1996-1997
numteams        | 20
total_goals     | 970
goals_per_match | 2.55
-[ RECORD 3 ]---+----------
season_name     | 1997-1998
numteams        | 20
total_goals     | 1019
goals_per_match | 2.68
...

-z Option ​

Display output in unaligned format (-A), suppress header/footer information (-t), and use the -z option (zero-byte separator):

me@yb100:~$ ybsql premdb -f total_goals_per_season_avg_per_match.sql -A -z -t
1995-1996209882.60
1996-1997209702.55
1997-19982010192.68
1998-1999209592.52
1999-20002010602.79
2000-2001209922.61
...

-X and -a Options ​

Turn off the .ybsqlrc startup file with -X and use -a to echo all of the commands in a script as they run.

me@yb100:~$ ybsql yellowbrick -X -a -f /home/premdb/premdb.ddl
drop database premdb;
DROP DATABASE
create database premdb;
CREATE DATABASE
\c premdb
You are now connected to database "premdb" as user "premdb1".
drop table season;
ybsql:/home/premdb/premdb.ddl:7: ERROR:  table "season" does not exist
drop table team;
ybsql:/home/premdb/premdb.ddl:8: ERROR:  table "team" does not exist
drop table hometeam;
ybsql:/home/premdb/premdb.ddl:9: ERROR:  table "hometeam" does not exist
drop table awayteam;
ybsql:/home/premdb/premdb.ddl:10: ERROR:  table "awayteam" does not exist
drop table match;
ybsql:/home/premdb/premdb.ddl:11: ERROR:  table "match" does not exist
create table season(seasonid smallint, season_name character(9), numteams smallint, winners varchar(30)) distribute replicate;
CREATE TABLE
create table team(teamid smallint, htid smallint, atid smallint, name varchar(30), nickname varchar(20), city varchar(20), stadium varchar(50), capacity int, avg_att dec(5,3)) distribute replicate;
CREATE TABLE
create table hometeam(htid smallint, name varchar(30)) distribute replicate;
CREATE TABLE
create table awayteam(atid smallint, name varchar(30)) distribute replicate;
CREATE TABLE
create table match(seasonid smallint, matchday timestamp, htid smallint, atid smallint, ftscore char(3), htscore char(3)) distribute on(seasonid);
CREATE TABLE
...