I'm new to PostgreSQL so sorry if I haven't explained myself clearly!

I've used the below query to find out the max character length of each column in my table. I now want to be able to shorten each column to the largest character length. Can anyone help?

        MAX(char_length(gid)) as column1,
        MAX(char_length(column2)) as column2

The reason I want to do this I want to export to shapefiles and the file size is huge as the majority of the columns are set to 254.

  • 1
    What data types does these columns have now? If they are char(254) alter them to varchar(254). I want to export to shapefiles How do you do it?
    – Akina
    Jul 19 '19 at 8:20
  • What is the current maximum length of the values? If you want to reduce the size, you can run an UPDATE statement, e.g. update the_table set column2 = substr(column2, 1, 20) to shorten the string to 20 characters - but that sounds like a terrible idea. Jul 19 '19 at 8:32
  • @Akina So they're currently varchar if I look in pgAdmin. I load the table into QGIS using the PostGIS database link. From here I can use QGIS to export as a shapefile which the column types are now 254 strings. (I don't know the difference between char & string sorry?) Jul 19 '19 at 8:33
  • @a_horse_with_no_name just out of interest why do you think that sounds like a terrible idea? Jul 19 '19 at 8:35
  • Because you are removing data without checking if it's OK to remove parts of the column value. Especially if the contents has some structure - which I assume as you talk about a "shapes". If you change e.g. the string 'point(1,2)' to 'point(1' what does that give you? Jul 19 '19 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.