When using the command line clients of MySQL or Postgresql it is often the case that the output of some select statement does not display very well, either because the query returns too many columns or the contents of a column are too long. In such cases it would be really nice to display the query results line by line in a vertical listing instead of the horizontal listing. A similar display can be found in both MySQL and Postgresql. So, let’s assume that you have a table that was created with the following SQL statement:

create table manyColumns(
	id integer primary key,
	column1 varchar(1024),
	column2 varchar(1024),
	column3 varchar(1024),
	column4 varchar(1024),
	column5 varchar(1024)
);

And you have inserted the following line of data in the table

insert into manyColumns values (
	1,
	'Lorem ipsum dolor sit amet, consectetur adipiscing elit.',
	'Integer ullamcorper sodales lectus, sit amet congue quam ullamcorper sed.',
	'Pellentesque pretium scelerisque ullamcorper.',
	'Suspendisse lectus nibh, auctor id scelerisque nec, iaculis eget massa.',
	'Nullam et tellus a sapien posuere faucibus.'
);

MySQL

Let us run a query that returns all the lines (and columns) from table manyColumns. The results in our MySQL command line tool will look something like this:

which is not actually ideal especially when you wish to read the contents and the columns that they belong too.

So there is a MySQL command interpreted by MySQL that modifies the results and displays them one record at a time with the columns listed one per line. So our previous query now becomes:

mysql> select * from manyColumns G

and the output of that query now looks like:

which is much easier to read through. The only thing to remember is that we need to use G as a terminator of every select query for which we wish to display its results in a vertical manner.

PostgreSQL

Let us now run the same query against our manyColumns table in PostgreSQL.

psql> select * from manyColumns;

Again the output of this query is not ideal for reading through the command line prompt. It should look something like:

Now in order to achieve a similar result in PostgreSQL we need to change what is called the “table formatting mode”. This is achieved either with the x on shortcut or by using the pset expanded on. “On” can be ommited in which case the two commands toggle between the On and Off states. The difference between MySQL and PostgreSQL is that when you use the G command this is per statement whereas the x command changes the mode from that point on untill you either switch it back off for your current session.

So, if we actually try it the command that we need to run and the output of the select query will look like: