public inbox for [email protected]  

help / color / mirror / Atom feed
From: Greg Sabino Mullane <[email protected]>
To: Lok P <[email protected]>
Cc: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Column type modification in big tables
Date: Thu, 8 Aug 2024 16:35:32 -0400
Message-ID: <CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com> (raw)
In-Reply-To: <CAKna9Vb_mx+dX02XOV6mpr8RFC-5io38kM6=4xRHQj_MUvQ+aQ@mail.gmail.com>
References: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
	<CAD=mzVUmXmkdvvMG30G1=D4Kq3WqnzGo=0ov9JnRCs1p=KJiTQ@mail.gmail.com>
	<CAKna9VZRc4+Vzbt6qPGMCauE84isPtz-wE_KX9AOt7WKfhwjiQ@mail.gmail.com>
	<CAD=mzVUX13ZM16kP4QhY+F5XiLr=ezCXftKOTKA4eUvhphgOJw@mail.gmail.com>
	<CAKna9Vb_mx+dX02XOV6mpr8RFC-5io38kM6=4xRHQj_MUvQ+aQ@mail.gmail.com>

On Thu, Aug 8, 2024 at 2:39 PM Lok P <[email protected]> wrote:

> Can anybody suggest any other possible way here.
>

Sure - how about not changing the column type at all?

> one of the columns from varchar(20) to varchar(2)

ALTER TABLE foobar ADD CONSTRAINT twocharplease CHECK (length(mycol) <= 2)
NOT VALID;

> one of the columns from Number(10,2) to Numeric(8,2)

ALTER TABLE foobar ADD CONSTRAINT eightprecision CHECK (mycol <= 10^8) NOT
VALID;

> two of the columns from varchar(20) to numeric(3)

This one is trickier, as we don't know the contents, nor why it is going to
numeric(3) - not a terribly useful data type, but let's roll with it and
assume the stuff in the varchar is a number of some sort, and that we don't
allow nulls:

ALTER TABLE foobar ADD CONSTRAINT onekorless CHECK (mycol::numeric(3) is
not null) NOT VALID;

You probably want to check on the validity of the existing rows: see the
docs on VALIDATE CONSTRAINT here:

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

Cheers,
Greg


view thread (22+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Column type modification in big tables
  In-Reply-To: <CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox