My SQL History

[Deleted User]

This tip was authored by Jim Knicely.

In vsql, you can use the \s meta-command to view your command-line history:

Example:

dbadmin=> CREATE TABLE some_table (c1 INT, c2 VARCHAR(10));
CREATE TABLE

dbadmin=> INSERT INTO some_table SELECT 1, 'TEST1';
OUTPUT
--------
      1
(1 row)

dbadmin=> ALTER TABLE some_table ADD COLUMN c3 int;
ALTER TABLE

dbadmin=> UPDATE some_table SET c3 = 1 WHERE c1 = 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> \s
CREATE TABLE some_table (c1 INT, c2 VARCHAR(10));
INSERT INTO some_table SELECT 1, 'TEST1';
ALTER TABLE some_table ADD COLUMN c3 int;
UPDATE some_table SET c3 = 1 WHERE c1 = 1;
COMMIT;
\s

If using some other Vertica SQL client, such as DbVisualizer, you can also get a history of your current session’s SQL commands like this:

dbadmin=> SELECT time, request_type, substr(request, 1, 150) request FROM dc_requests_issued WHERE session_id = current_session() ORDER BY TIME DESC;
             time              | request_type |                                                                   request
-------------------------------+--------------+---------------------------------------------------------------------------------------------------------------------------------------------
2018-03-19 08:34:26.266058-04 | QUERY        | SELECT time, request_type, request FROM dc_requests_issued WHERE session_id = current_session() ORDER BY TIME DESC;
2018-03-19 08:30:26.149521-04 | TRANSACTION  | COMMIT;
2018-03-19 08:30:23.820284-04 | QUERY        | UPDATE some_table SET c3 = 1 WHERE c1 = 1;
2018-03-19 08:30:03.525298-04 | DDL          | ALTER TABLE some_table ADD COLUMN c3 int;
2018-03-19 08:29:52.14367-04  | QUERY        | INSERT INTO some_table SELECT 1, 'TEST1';
2018-03-19 08:29:52.04373-04  | DDL          | CREATE PROJECTION public.some_table AS  SELECT * FROM public.some_table ORDER BY  c1,c2 SEGMENTED BY hash( c1,c2) ALL NODES  KSAFE;
2018-03-19 08:29:41.695105-04 | DDL          | CREATE TABLE some_table (c1 INT, c2 VARCHAR(10));
(7 rows)

Have Fun!