Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

My SQL History

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.