Vertica, describe table

Question

I have a vertica database and I need to describe some tables. In MySQL you use describe table_name. And I know that in vertica it's \d table_name. Also I need to do it throw python, but when I make query:

cur.execute("\d table_name")

I get this problem:

vertica_python.errors.VerticaSyntaxError: Severity: ERROR, Message: Syntax error at or near "\", Sqlstate: 42601, Position: 1, Routine: base_yyerror, File: /scratch_a/release/vbuild/vertica/Parser/scan.l, Line: 1004, SQL: '\\d table_name'

Is there another way to get columns and columns type in vertica?

`cur.execute(r'\d table_name')`? https://docs.python.org/2/reference/lexical_analysis.html#string-literals — Benjamin Bannier, Oct 16 '14 at 08:09
Tried different ways. For example in this case: vertica_python.errors.VerticaSyntaxError: Severity: ERROR, Message: Syntax error at or near "\", Sqlstate: 42601, Position: 1, Routine: base_yyerror, File: /scratch_a/release/vbuild/vertica/Parser/scan.l, Line: 1004, SQL: '\\d table_name' — Opperix, Oct 16 '14 at 08:18
Found answer, if you want get columns and types, you can use: select * from COLUMNS where table_name = 'table_name'. You can select by table_schema and other params. My first meeting with vertica, think it will be fun. — Opperix, Oct 16 '14 at 08:50

Guillaume · Accepted Answer · 2018-02-06T05:37:31.563

Why your query fails:

The \d command is a specificity of vsql, this is not valid SQL you can use use via ODBC or JDBC for instance.

You have 3 options. First the one you already put in comment:

SELECT * 
FROM   v_catalog.columns 
WHERE  table_schema='schema' 
       AND table_name='table' 
ORDER  BY ordinal_position;`

The second option is to export the object, which will give you the full create statement, including projections:

SELECT export_objects('', 'schema.table');

The third option is to export the table, which only provides the table definition:

SELECT EXPORT_TABLES('', 'schema.table');

The first set of double quotes says to print the output on STDOUT, the second is the table (or schema or all the objects) you want to export.

NOTE: it seems an `S` need to be added to `TABLE` in the third option, `EXPORT_TABLES` — Aziz Alto, Feb 06 '18 at 00:24

score 0 · Answer 2 · edited Apr 25 '22 at 09:33

0

There are many ways to describe tables in vertica but easiest is like this:

\d table_name;
\dt tablw_name;

edited Apr 25 '22 at 09:33

100rabh

6,156
5
27
41

answered Jul 09 '18 at 12:15

santosh tiwary

598
6
4

score 0 · Answer 3 · answered Feb 22 '20 at 19:30

you can use \d or get it using below dictionary table

select column_name, table_name
from v_catalog.columns
where column_name ilike '<column_name>'
and table_schema ilike '<table_name>'
order by column_name;

you can also add more attributes based on the required information that you need.

Vertica, describe table

3 Answers3