My SQL History
![[Deleted User]](https://us.v-cdn.net/6029397/uploads/defaultavatar/nD0LWW9MQTB29.jpg)
[Deleted User]
Administrator
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!
0