1

I want to check the MAX(LENGTH) of all VARCHAR columns of my Redshift table, so I have an idea of a better limitation I could put on the column sizes. (Right now all columns are VARCHAR(65535))

I can write a query like:

SELECT MAX(LENGTH(col1)), MAX(LENGTH(col2)), ...
FROM my_table

But isn't there a way I could write my query so it basically says "apply this for every column"? I've tried the answer from this post but it seems it only applies to classic PostgreSQL

totooooo
  • 1,050
  • 1
  • 12
  • 32
  • 1
    you are correct there is no way to do this in redshift - you would have tgenerate that somewhere else then run it on redshift – Jon Scott Apr 15 '19 at 16:13

1 Answers1

2

You can use the following SQL the generate your select 

select sql from (
select 1 o, 'select ' sql
union
select 2, 'max(length('+attname+')),'
from pg_class c
join pg_attribute a on c.oid = a.attrelid 
where relname = '<your_table>'
and attnum > 0
union 
select 3, 'from <your_table>'
)
order by o

The output will look like this

select 
max(length(col1)),
max(length(col2)),
...
max(length(coln)),   -- <- remove the last comma
from <your_table>

You can run this sql to get all max lengths from your table

Please let me know if this helps you.

demircioglu
  • 3,069
  • 1
  • 15
  • 22
  • nice hack, the line numbers appear in some SQL clients though: ```1 select 2 max(length(col1)), 3 max(length(col2)), 4 max(length(col3)), ...``` – totooooo Apr 15 '19 at 18:54
  • I have added those to construct the sql in proper order, You can run only the mid section deleting 2 to create a list of columns and add 'select' and 'from..' to the output. If this answered your question can you mark it as correct, thanks! – demircioglu Apr 15 '19 at 20:24
  • what I mean is that my SQL client displays the row number for each of the results of the query (no matter what query is run), this makes the trick unusable on it. Fortunately I have another client on which it works fine – totooooo Apr 15 '19 at 21:01