ysqlsh meta-command examples
Examples of ysqlsh meta-commands
Create a database
Start a ysqlsh session and enter the following command to create and connect to a database:
CREATE DATABASE testdb;
\c testdb;
The following command to create a table shows how a SQL statement can be spread over several lines of input. Notice the changing prompt:
CREATE TABLE my_table (
first integer not null default 0,
second text)
;
testdb=# CREATE TABLE my_table (
testdb(# first integer not null default 0,
testdb(# second text)
testdb-# ;
CREATE TABLE
Look at the table definition:
\d my_table
Table "public.my_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
first | integer | | not null | 0
second | text | | |
Fill the table with data:
INSERT INTO my_table VALUES (1, 'one');
INSERT INTO my_table VALUES (2, 'two');
INSERT INTO my_table VALUES (3, 'three');
INSERT INTO my_table VALUES (4, 'four');
SELECT * FROM my_table;
first | second
-------+--------
1 | one
2 | two
3 | three
4 | four
(4 rows)
Change the prompt
To change the prompt to something more interesting:
\set PROMPT1 '%n@%m %~%R%# '
yugabyte@localhost testdb=#
crosstabview
When suitable, query results can be shown in a crosstab representation with the \crosstabview
command:
SELECT first, second, first > 2 AS gt2 FROM my_table;
first | second | gt2
-------+--------+-----
1 | one | f
2 | two | f
3 | three | t
4 | four | t
(4 rows)
\crosstabview first second
first | one | two | three | four
-------+-----+-----+-------+------
1 | f | | |
2 | | f | |
3 | | | t |
4 | | | | t
(4 rows)
The following example shows a multiplication table with rows sorted in reverse numerical order and columns with an independent, ascending numerical order.
SELECT t1.first as "A", t2.first+100 AS "B", t1.first*(t2.first+100) as "AxB",
row_number() over(order by t2.first) AS ord
FROM my_table t1 CROSS JOIN my_table t2 ORDER BY 1 DESC
\crosstabview "A" "B" "AxB" ord
A | 101 | 102 | 103 | 104
---+-----+-----+-----+-----
4 | 404 | 408 | 412 | 416
3 | 303 | 306 | 309 | 312
2 | 202 | 204 | 206 | 208
1 | 101 | 102 | 103 | 104
(4 rows)
pset
You can display tables in different ways by using the \pset
command:
\pset border 2
Border style is 2.
SELECT * FROM my_table;
+-------+--------+
| first | second |
+-------+--------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
+-------+--------+
(4 rows)
\pset border 0
Border style is 0.
SELECT * FROM my_table;
first second
----- ------
1 one
2 two
3 three
4 four
(4 rows)
\pset border 1
Border style is 1.
\pset format unaligned
Output format is unaligned.
\pset fieldsep ,
Field separator is ",".
\pset tuples_only
Showing only tuples.
SELECT second, first FROM my_table;
one,1
two,2
three,3
four,4
Alternatively, use the short commands:
\a \t \x
Output format is aligned.
Tuples only is off.
Expanded display is on.
SELECT * FROM my_table;
-[ RECORD 1 ]-
first | 1
second | one
-[ RECORD 2 ]-
first | 2
second | two
-[ RECORD 3 ]-
first | 3
second | three
-[ RECORD 4 ]-
first | 4
second | four