Vertica THROW_ERROR function not working?

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Vertica THROW_ERROR function not working?

Post by Josh » Tue Mar 01, 2016 3:36 pm

Hi all,

I am confused by how to use the THROW_ERROR function which was introduced in Vertica 7.2.x.

Documentation:

https://my.vertica.com/docs/7.2.x/HTML/ ... _ERROR.htm

I am trying to re-create the example, but I do not get the same results:

Code: Select all

dbadmin=> select version();
              version
------------------------------------
 Vertica Analytic Database v7.2.1-4
(1 row)

dbadmin=> create table table_name (foo int);
CREATE TABLE

dbadmin=> select foo, throw_error('The column "foo" has been renamed "bar". Please try the new column name.') from table_name;
 foo | throw_error
-----+-------------
(0 rows)

dbadmin=> alter table table_name rename foo to bar;
ALTER COLUMN

dbadmin=> select foo, throw_error('The column "foo" has been renamed "bar". Please try the new column name.') from table_name;
ERROR 2624:  Column "foo" does not exist
I was expecting to see the USER GENERATED ERROR message. Am I missing something obvious?

Thanks!
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1768
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Vertica THROW_ERROR function not working?

Post by JimKnicely » Tue Mar 01, 2016 6:25 pm

Hi,

Since your table was empty, I tried inserting a row, to see how it worked...

Code: Select all

dbadmin=> \d table_name;
                                  List of Fields by Tables
Schema |   Table    | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+------------+--------+------+------+---------+----------+-------------+-------------
public | table_name | bar    | int  |    8 |         | f        | f           |
(1 row)
 
dbadmin=> insert into table_name values (1);
OUTPUT
--------
      1
(1 row)
 
dbadmin=> commit;
COMMIT
 
dbadmin=> select * from table_name;
bar
-----
   1
(1 row)
 
dbadmin=> select foo, throw_error('The column "foo" has been renamed "bar". Please try the new column name.') from table_name;
ERROR 2624:  Column "foo" does not exist
No difference :(

So, the example in the documentation is a bit misleading because it implies that it’ll throw the error if the "foo" column was renamed (overriding the system generated error message). The internal error message supersedes the user defined error message.

In the next example I referenced the renamed column “bar” in the table in the SELECT statement:

Code: Select all

 
dbadmin=> truncate table table_name;
TRUNCATE TABLE
 
dbadmin=> select bar, throw_error('The column "foo" has been renamed "bar". Please try the new column name.') from table_name;
bar | throw_error
-----+-------------
(0 rows)
 
dbadmin=> insert into table_name values (1);
OUTPUT
--------
      1
(1 row)
 
dbadmin=> select bar, throw_error('The column "foo" has been renamed "bar". Please try the new column name.') from table_name;
ERROR 7137:  USER GENERATED ERROR: The column "foo" has been renamed "bar". Please try the new column name.
I also tried inserting a few more rows:

Code: Select all

dbadmin=> select * from table_name;
bar
-----
   1
   1
   0
   0
(4 rows)
 
dbadmin=> select 1/bar from table_name;
ERROR 3117:  Division by zero
 
dbadmin=> select 1/bar, throw_error('Division by zero') from table_name;
ERROR 3117:  Division by zero
The THROW_ERROR function only generates one error message, not one for each row. So I guess a possible use case would be to test for specific data situations...

Example:

Code: Select all

dbadmin=> select bar, case when bar = 0 then throw_error('bar has one or more rows containing a value of 0') end from table_name;
ERROR 7137:  USER GENERATED ERROR: bar has one or more rows containing a value of 0
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”