Question 187954 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to use DEFAULT when inserting rows into postgres sql database

Consider that I have a table t1 in my postgres database like so:

CREATE TABLE t1 (
    id SERIAL PRIMARY KEY, 
    col_2 TEXT DEFAULT "foo", 
    col_3 TEXT NOT NULL DEFAULT "bar", 
    ...
    col_X INTEGER NOT NULL DEFAULT 42
);

And that I receive a JSON object obj which I unmarshall in a struct ObjStrct:

type ObjStrct {
    id *int32  `json:id,omitempty`, 
    s1 *string `json:s1,omitempty`, 
    s2 *string `json:s2,omitempty`, 
    ...
    nr *int32  `json:nr,omitempty`
}
var obj ObjStrct
err := json.NewDecoder(request.Body).Decode(&obj)
// error handling has been done

Now I want to insert that object into my database:

// connection to db established
row := db.QueryRow(
        `INSERT INTO 
            t1 (col_1, col_2, ..., col_X)
        VALUES
            ($1, $2, ..., $X)
        RETURNING 
            id;`, 
        *obj.s1, *obj.s2, ..., *obj.nr)

The issue is: All those obj.*-values could be nil in which case the sql DEFAULT value should be used.

My question is: How do I achieve this?

  • datatypes from the sql package (sql.Null<TYPE>) do not support the DEFAULT keyword afaik.
  • Functions like func defaultOnNil(v *int32) any { ... } returning "DEFAULT" when v is nil and v instead do not work. See this related post
Tags:

2

Answers


  1. 0

    To use the DEFAULT value of a column an INSERT should exclude the column while inserting.

    This means, you’ll need to process the JSON object, find out which values are nil, and remove them from the query.

    For example, if the JSON object has s2 as nil, then col2 should be removed from your query; it should be modified as:

    row := db.QueryRow(
        `INSERT INTO 
            t1 (col_1, ..., col_X) -- "col2" removed here
        VALUES
            ($1, ..., $X)          -- "$2" removed here
        RETURNING 
            id, col2;`,            -- "col2" added here
        *obj.s1, *obj.s2)

    This is easily done by any ORM that implements Dynamic SQL functionality.

    Login or Signup to reply.
  2. 0

    Another option is send the keyword DEFAULT instead of the value parsed from the json. So:

    row := db.QueryRow(
        `INSERT INTO 
            t1 (col_1, col_2, ..., col_X)
        VALUES
            ($1, $2, ..., $X)
        RETURNING 
            id;`, 
        *obj.s1, DEFAULT, ..., *obj.nr);

    You do not use quotes around DEFAULT, in this usage it is a keyword.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search