CODEWITHSUNDEEP
sqldatabasevertica
2Big2BeSmall
2Big2BeSmall

Reputation: 1378

Create anonymous Block with Exception Handling in Vertica

I have an issue with Vertica alter column size

I have some columns I want to resize with this command 

alter table TABLE_NAME  alter column  COLUMN_NAME   SET DATA TYPE     varchar(100);

But when executing via some of my Vertica DB I get this error: 

SQL Error [2629] [42701]: [Vertica][VJDBC](2629) ROLLBACK: Column "COLUMN_NAME" is already of type "varchar(100)"
  [Vertica][VJDBC](2629) ROLLBACK: Column "COLUMN_NAME" is already of type "varchar(100)"
    com.vertica.util.ServerException: [Vertica][VJDBC](2629) ROLLBACK: Column "COLUMN_NAME" is already of type "varchar(100)"

But some of my columns already is this size - I have multiple Vertica database some of them, not the same size. I have a huge amount of column I want to resize and align to specific size I don't want to create different SQL's file for each DB.

At my previous experience with ORACLE DB, I would solve Exception Handling with anonymous Blocks easy as that:

BEGIN
  .. code statements go here ..
EXCEPTION
  .. exception handlers go here ..
END;

I was thinking of Creating a Function/Produce at Vetica but is just a huge overkill - since Vertica doesn't support Simple Function writing like PLSQL

Creating a function in Vertica is not possible since it's not supporting SQL commands execution - or select queries as i saw here

How can I create a simple Block to handle and ignore this exception ?

Upvotes: 0

Views: 520

Answers (1)

mauro
mauro

Reputation: 5940

I think you can easily build the script you're looking for using a "SQL generating SQL" approach:

SELECT
    'ALTER TABLE ' || table_schema || '.' || table_name ||
    ' ALTER COLUMN ' || column_name || ' SET DATA TYPE varchar(100);'
FROM 
    v_catalog.columns 
WHERE 
    data_type !='varchar(100)'
;

It will generate in output something like this:

 ALTER TABLE tpch.partsupp ALTER COLUMN ps_partkey SET DATA TYPE varchar(100);
 ALTER TABLE tpch.partsupp ALTER COLUMN ps_suppkey SET DATA TYPE varchar(100);
 ALTER TABLE tpch.partsupp ALTER COLUMN ps_availqty SET DATA TYPE varchar(100);
 ALTER TABLE tpch.partsupp ALTER COLUMN ps_supplycost SET DATA TYPE varchar(100);
 ALTER TABLE tpch.partsupp ALTER COLUMN ps_comment SET DATA TYPE varchar(100);
 ALTER TABLE tpch.customer ALTER COLUMN c_custkey SET DATA TYPE varchar(100);

And, of course you can add all sort of filters and/or change the generated SQL...

EDIT

It looks like OP - for some reason - don't want to create a script. Then just use pipes...

$ vsql -f - <<-EOF | vsql -f -
SELECT
    'ALTER TABLE ' || table_schema || '.' || table_name ||
    ' ALTER COLUMN ' || column_name || ' SET DATA TYPE varchar(100);'
FROM 
    v_catalog.columns 
WHERE 
    data_type !='varchar(100)'
EOF

Upvotes: 0

Related Questions