Postgres Set column default value as empty-CodePudding

How can I set a default empty value when I create a column? The table workflow_job_template_id is a INTEGER type and should be empty by default.

How come other tables have DEFAULT NOT NULL but no value? I want the new column to be NOT NULL but empty in the default value.

ALTER TABLE main_workflowjobnode 
  ADD COLUMN "workflow_job_template_id" INTEGER NOT NULL DEFAULT='';

ERROR:  syntax error at or near "="
LINE 2: ...LUMN "workflow_job_template_id" INTEGER NOT NULL DEFAULT='';

Here is the table structure:

                                                                  Table "public.main_workflowjobnode"
          Column           |           Type           | Collation | Nullable |                     Default                      | Storage  | Stats target | Description
--------------------------- -------------------------- ----------- ---------- -------------------------------------------------- ---------- -------------- -------------
 id                        | integer                  |           | not null | nextval('main_workflowjobnode_id_seq'::regclass) | plain    |              |
 created                   | timestamp with time zone |           | not null |                                                  | plain    |              |
 modified                  | timestamp with time zone |           | not null |                                                  | plain    |              |
 job_id                    | integer                  |           |          |                                                  | plain    |              |
 unified_job_template_id   | integer                  |           |          |                                                  | plain    |              |
 workflow_job_id           | integer                  |           |          |                                                  | plain    |              |
 char_prompts              | text                     |           | not null |                                                  | extended |              |
 inventory_id              | integer                  |           |          |                                                  | plain    |              |
 ancestor_artifacts        | text                     |           | not null |                                                  | extended |              |
 extra_data                | text                     |           | not null |                                                  | extended |              |
 survey_passwords          | text                     |           | not null |                                                  | extended |              |
 do_not_run                | boolean                  |           | not null |                                                  | plain    |              |
 all_parents_must_converge | boolean                  |           | not null |                                                  | plain    |              |
 identifier                | character varying(512)   |           | not null |                                                  | extended |              |

CodePudding user response：

Try removing the not null condition and setting it NULL by default

CodePudding user response：

I suggest just using the NULL value to represent "missing" in your workflow_job_template_id column. NULL semantically means "unknown" in the SQL language. What you are currently trying to do is not even valid:

ALTER TABLE main_workflowjobnode 
    ADD COLUMN "workflow_job_template_id" INTEGER NOT NULL DEFAULT = '';

This isn't valid because an integer column cannot store a string, including empty string, as a value.