CODEWITHSUNDEEP
sqlpostgresqlforeachtruncate
as.beaulieu
as.beaulieu

Reputation: 484

PostgreSQL truncate existing field and altering to add character limit

I am altering my existing table that is already filled with columns. In my table 'section', I want to set a character limit on the column "name", specifically type VARCHAR(60). However, there has not been a character limit before, and I would like to truncate any existing fields in the name column so that it now matches this restriction before my ALTER script.

I'm still getting several error messages, including in my LEFT statement, which is what I'm using to truncate the string in the "name" column. The LEFT statement is upset how I'm declaring the string to be truncated, whether I put the parameters in parenthesis or not. This is where I'm at so far:

DO $$
DECLARE
    _name text;
    _id uuid;
BEGIN
    FOR _name, _id IN SELECT (name, id) FROM %SCHEMA%.section
    LOOP
        IF (_name > 60)
        THEN
            SET name = LEFT (_name, 60) WHERE id = _id;
        END IF;
    END LOOP;
    RETURN NEW;
END $$;

Once I have this done, I know my ALTER script is very simple:

ALTER TABLE IF EXISTS %SCHEMA%.section ALTER COLUMN name TYPE VARCHAR(60);

Upvotes: 0

Views: 1291

Answers (2)

gahooa
gahooa

Reputation: 137332

You can also make use of the the USING syntax to ALTER TABLE. This allows you to do it as part of the ALTER, rather than as two separate commands.

ALTER TABLE myschema.mytable 
  ALTER COLUMN mycolumn 
    TYPE VARCHAR(60) 
    USING LEFT(mycolumn, 60);

https://www.postgresql.org/docs/9.6/static/sql-altertable.html

Upvotes: 4

Haleemur Ali
Haleemur Ali

Reputation: 28263

use an update query, like this:

UPDATE myschema.mytable
SET name = LEFT(mytable.name, 60)
WHERE LENGTH(mytable.name) > 60

Upvotes: 2

Related Questions