The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

information_schema support for ANSI SQL Support

simon_aubert_bdsimon_aubert_bd Community Edition User

According to
"In relational databases, the information schema (information_schema) is an ANSI-standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database"

It appears Vertica does not support information_schema (but v_catalog, while it would be great to follow the norm. i would add, it does not require a lot of job to produce an information_schema schema and then some views in it.

Best regards,



  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2019

    Hah! says:

    RDBMSs which do not support information_schema include:

    Apache Derby
    Microsoft Access
    IBM Informix
    IBM DB2
    Oracle Database
    Sybase ASE
    Sybase SQL Anywhere

    And here...

  • simon_aubert_bdsimon_aubert_bd Community Edition User
    edited December 2019

    Hello @Jim_Knicely

    First of all, thanks for your answer. Yeah, a lot of RDBMS don't care about the SQL norm, including Oracle or, in a more related topic, MonetDB. This means for dataprep/dataviz/etl... etc developers to hard code according to database. As an example, Alteryx or Qlik Sense are really not easy to use with Monetdb or Firebird. On the other hand, one of the two reasons that explain the success of PSQL is to be really compliant with SQL norm. Even Apache Hive support information_schema in the last releases.
    But then, I don't understand your point ? Don't you have higher standards than your competitors ? ;)

    Best regards,

  • Bryan_HBryan_H Vertica Employee Administrator

    Few thoughts here: it looks like information_schema appears in SQL-92, but I don't see it exactly in my SQL-99 reference.
    Since most RDBMS internally implement information_schema as views over internal system-specific catalog, it should be straightforward to do this for Vertica. There exists some scripts to create information_schema for Oracle, for example.
    There is a Stack Overflow commenting reasons why information_schema is NOT commonly used anymore (source

    The Information_Schema views only show objects that are compatible with the SQL-92 standard. This means there is no information schema view for even quite basic constructs such as indexes (These are not defined in the standard and are left as implementation details.) Let alone any SQL Server proprietary features.

    For Vertica, this would mean system-specific objects like projections, UDx, etc. would not appear in information_schema
    Not all information_schema are "standard" anyways; MySQL, MS SQL, Snowflake all state in their respective documentation that the information_schema implementation has proprietary features either added or not listed, or unsupported features removed.
    Is there a specific use case for which information_schema is needed? If so, I can look into adapting the Oracle scripts from SourceForge to create a basic information_schema implementation, though it would help to know the use case so I can also write some tests.

  • simon_aubert_bdsimon_aubert_bd Community Edition User

    Hello @Bryan_H,
    Thanks for your very complete answer. I really appreciate the effort you put in it.
    1. "Since most RDBMS internally implement information_schema as views over internal system-specific catalog, it should be straightforward to do this for Vertica. "
    I didn't want to suggest how to implement it but that's exactly what I had in mind : a script that execute at install /upgrade of the database to create the schema and the views based on the existing v_catalog.

    2."For Vertica, this would mean system-specific objects like projections, UDx, etc. would not appear in information_schema"
    Yep and I don't think it's an issue at all.

    3."Is there a specific use case for which information_schema is needed?"
    A database comparaison to be sure all the tables are imported with a very simple "standard" query? That's what come first. Tableau and Alteryx have both hard-coded routine for Vertica, so I don't have a better use case right now. However, I'm pretty sure there are several examples it would help. With a few google search I find it :

    And every time you gain compliance, you add value to the product. Especially if it's straightforward, to me, it's a no-brainer.

    Best regards,


  • We use a number or DB products in our organisation of which Vertica is one. Many of them (including Vertica) are PostGres based.
    We have written simple tools to put databases under source control, auto-document the DB on a nightly basis, and also to create deployment pipelines. The PostGres commonality means that there is minimal coding difference needed to make the tools work with the different platforms. The inclusion of information_schema views would increase this commonality.
    One of the biggest challenges in building (or buying) tools is putting together the pseudo-business case for them. Unfortunately a development team may not be good at selling the benefits of tooling in a way that wins hearts and minds of those who have to pay for them. Commonality becomes a force multiplier

  • simon_aubert_bdsimon_aubert_bd Community Edition User

    Hello @Bryan_H
    Is there some update on this topic?
    Best regards,

  • marcothesanemarcothesane - Select Field - Administrator

    I'm not aware of any - and the workaround could be doable, though.
    I would opt for a minimal set of these tables:

    • tables
    • views
    • columns
    • primary key constraints - and their columns
    • foreign key constraints - and their columns

    And then, I'd CREATE SCHEMA information_schema myself - and add the views as I know they are expected from the queries that you use regularly. You'll find a match for all of the above in the v_catalog system schema of Vertica.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file