I'm using postgres 13.3, and I already have an existing table with a GENERATED column such as:

CREATE TABLE test_table (
    id uuid NOT NULL DEFAULT uuid_generate_v4(),
    the_column_to_alter bool NOT NULL GENERATED ALWAYS AS (2 > 1) STORED,
    CONSTRAINT test_table2_pkey PRIMARY KEY (id)

But I've now changed my mind, and I want this to instead just be a regular non-generated column that I can edit manually...

  • But I want to avoid having to DROP and re-create the column
  • And I want to retain the existing values that are already in the column at the moment

Is this possible? What would the ALTER query be to do this for the the_column_to_alter column?


Here's the command for the example table in the original post:

ALTER TABLE test_table ALTER COLUMN the_column_to_alter DROP EXPRESSION;

The postgres manual page is here: https://www.postgresql.org/docs/current/sql-altertable.html

  • 2
    @Vérace: it's right there in the manual Jun 26 at 15:24
  • @a_horse_with_no_name Yeah I'd actually read the page before posting, but for some reason it still wasn't very clear to me. I thought it was about something else, and also that EXPRESSION was actually a placeholder (rather than literally just the word itself). It likely would have been much more obvious to me if it was on its own page... there's a lot of stuff you can do with ALTER TABLE, might be easier to not miss things if it was broken up a bit more, if not separate pages, at least some more headings in the page.
    – LaVache
    Jun 27 at 6:14
  • ALTER TABLE ... ALTER COLUMN ... DROP EXPRESSION is defined by SQL standart. We could make the syntax of this command more obvious (drop generated or something else). But in choosing between the standard syntax and your own, it is better to use what is in the standard. (I am one of the contributors to this feature in postgresql 13).
    – Melkij
    Jun 27 at 9:28

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.