Postgres default values

[Kobus-Smit]

Hi @zapov

When I add a new property with a default value, would it be possible for Revenj to generate the sql to add the column's default value?

bool my_new_col { default c# 'false'; }

ALTER TABLE "my_module"."my_root" ADD COLUMN "my_new_col" BOOL DEFAULT FALSE;

[zapov]

You can use the Postgres specific default, or a generic default, eg:

default 'it => false'; 

or

default postgres 'false';

[Kobus-Smit]

Thanks @zapov , I've tried both and it does not generate the default in the ddl?

  bool new_field_2 { default 'it => false'; }
  bool new_field_3 { default postgres 'false'; }

DO $$ BEGIN
    IF NOT EXISTS(SELECT * FROM "-DSL-".Load_Type_Info() WHERE type_schema = 'my_module' AND type_name = 'my_root' AND column_name = 'new_field_3') THEN
        ALTER TABLE "my_module"."my_root" ADD COLUMN "new_field_3" BOOL;
        COMMENT ON COLUMN "my_module"."my_root"."new_field_3" IS 'NGS generated';
    END IF;
END $$ LANGUAGE plpgsql;

[zapov]

Yes, currently it does it in multistep. Only new PG versions will support improved default. I'll improve the generated SQL then.

[Kobus-Smit]

Multistep is fine but looking at the generated SQL and the database afterwards it did not set the default value.

Only this:

UPDATE "my_module"."my_module" SET "new_field_3" = false WHERE "new_field_3" IS NULL;
ALTER TABLE "my_module"."my_module" ALTER "new_field_3" SET NOT NULL;

I'd expected a

ALTER TABLE "my_module"."my_module" ALTER "new_field_3" SET DEFAULT false;

[zapov]

Well, that update set the default value, but yeah. It doesn't currently set the default constraint. Revenj will not use that db default value, but I guess it could set the default constraint. Currently you'll have to do it yourself in a custom sql script

[Kobus-Smit]

That would be very useful for future rows inserted outside of Revenj. We are currently using a custom script but sometimes forget it and it is nice to be able to recreate the database from the dsl and not rely on external scripts.

[zapov]

I'll look into adding it along with this new PG 11 feature so it's consistent.

Custom SQL scripts are a normal thing, you can't really run away from that in complex setups.

[Kobus-Smit]

Cool thanks @zapov

[zapov]

This should be in the latest release

[Kobus-Smit]

It appears compiler v2.5.7274.29304 generates the default constraint but does not use the default value in the dsl.

For example the default is true in the dsl but false in the generated script:

bool my_new_col { default c# 'true'; }

ALTER TABLE "my_module"."test" ADD COLUMN "my_new_col" BOOL NOT NULL DEFAULT false;

[zapov]

Because you only set it for C#. You can use a expression for all types, eg: default 'it => true'; Or you can use PG specific: default postgres 'true';

[Kobus-Smit]

I've tested and

bool my_new_col { default 'it => true'; }

does not generate the default constraint and does not generate NOT NULL :

ALTER TABLE "my_module"."test" ADD COLUMN "my_new_col" BOOL;

but

bool my_new_col { default c# 'true'; default postgres 'true'; }

generates correct:

ALTER TABLE "my_module"."test" ADD COLUMN "my_new_col" BOOL NOT NULL DEFAULT true;

[zapov]

Ah, right. I guess I didn't want to go into analyzing if default can be applied at that time. So yeah, only PG specific default (or type default) will work

[Kobus-Smit]

Ok cool, I'll try to remember to use the PG specific default.