Vertica Quick Tip: My SQL History

This blog post 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, i.e. 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!