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

View returns only 1000 rows. But underlying SQL returns much more.

I use Vertica CE 6.1.2. I've created table "test.TABLE1" and inserted 3123 rows. Afterwards I created view "test2.VIEW1" in another schema - create view TEST2.VIEW1 as select * from test.TABLE1; But "select count(1) from test2.VIEW1" return only 1000 rows. What can it be???

Comments

  • Try running the following statement to check how the view statement is being saved: select * from views where table_name='VIEW1'
  • Hello, Here is the problem. It saved as "SELECT * FROM test.TABLE LIMIT 1000". Could you please advise who can add LIMIT 1000 and possible way to avoid that? Many thanks in advance.
  • My guess would be that the tool you are using is probably returning 100 rows by default, and that may have gotten into your view script. Can you try doing (in vsql or other sql tool): Create or replace view test2.view1 as SELECT * FROM test.TABLE and see the limit clause goes away?
  • Thanks. It's an issue with my tool. vsql creates views properly.

Leave a Comment

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

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