Thread: Indicating DEFAULT values in INSERT statement

Indicating DEFAULT values in INSERT statement

From
Postgres User
Date:
Hi,

According to the docs, the DEFAULT keyword lets you explicitly insert a field's default value in an INSERT statement.

From a db function, I'd like to force the use of default when an input parameter is null.  This syntax looks correct but fails to compile.

Any suggestions? 

INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status, 
        Case When p_created_ts Is Not Null Then p_created_ts Else DEFAULT End
);

I tried replacing the Case statement as follows, but it fails also:  Coalesce(p_created_ts, DEFAULT)

Thanks

Re: Indicating DEFAULT values in INSERT statement

From
Diego Augusto Molina
Date:
  Hi, when posting SQL it may be a good practice to post your PG
version so that answers may be more accurate and better fit your
needs. In this case, I don't think you'll be able to do what you are
trying to, because as of my understanding the "DEFAULT" is not part of
an expression but a keyword itself which is to be written *instead* of
an expression. I get your point and seems pretty fair to me, but if
I'm right you don't have access to the default value of the column
from within an expression.
  Assuming you're using the latest stable version of PostgreSQL (9.0
as of today), you can check the following page for an online reading
reference of the INSERT statement:
http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
  If you look carefully, you'll see that for the value of a column you
may write: the "DEFAULT" keyword [exclusive or] an expression (which
includes specific values).
  The  [exclusive or] is deduced because of the pipe (`|') between the
two choices which are embraced by curly braces.
  So, if you were able to use the "DEFAULT" keyword inside an
expression, instead of having the "{ expression | DEFAULT }" syntax
you would have the "expression" syntax, which would include the case
of having just "DEFAULT" as an expression. It's just a matter of
understanding the syntax.

  Cheers!

--
Diego Augusto Molina
diegoaugustomolina@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html

Re: Indicating DEFAULT values in INSERT statement

From
Postgres User
Date:
Thanks for the previous posts. I am in fact running 9.0 'nix and am unable to find a way to embed DEFAULT within an expression.  
I can always rewrite the function to call EXECUTE but that's not a very good solution in this case because many of our db functions are code generated.

On Tue, Aug 9, 2011 at 4:47 PM, Diego Augusto Molina <diegoaugustomolina@gmail.com> wrote:
 Hi, when posting SQL it may be a good practice to post your PG
version so that answers may be more accurate and better fit your
needs. In this case, I don't think you'll be able to do what you are
trying to, because as of my understanding the "DEFAULT" is not part of
an expression but a keyword itself which is to be written *instead* of
an expression. I get your point and seems pretty fair to me, but if
I'm right you don't have access to the default value of the column
from within an expression.
 Assuming you're using the latest stable version of PostgreSQL (9.0
as of today), you can check the following page for an online reading
reference of the INSERT statement:
http://www.postgresql.org/docs/9.0/interactive/sql-insert.html
 If you look carefully, you'll see that for the value of a column you
may write: the "DEFAULT" keyword [exclusive or] an expression (which
includes specific values).
 The  [exclusive or] is deduced because of the pipe (`|') between the
two choices which are embraced by curly braces.
 So, if you were able to use the "DEFAULT" keyword inside an
expression, instead of having the "{ expression | DEFAULT }" syntax
you would have the "expression" syntax, which would include the case
of having just "DEFAULT" as an expression. It's just a matter of
understanding the syntax.

 Cheers!

--
Diego Augusto Molina
diegoaugustomolina@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html

Re: Indicating DEFAULT values in INSERT statement

From
Diego Augusto Molina
Date:
This is a bit hacky, but it may work (I haven't tried it yet). If it
works let us know and if it doesn't then maybe we can debug it and get
something useful, or move on to another solution.

<code>
 INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status,
        Case When p_created_ts Is Not Null Then p_created_ts Else
        ( -- You may want to be sure the field has a default value.
                SELECT d.adsrc -- or should it be d.adbin?
                        FROM
                                pg_catalog.pg_attribute a INNER JOIN
                                pg_catalog.pg_attrdef d ON (a.attnum = d.adnum)
                        WHERE
                                a.attname = 'created_ts' AND
                                a.attrelid = 'public.test'::REGCLASS AND
                                d.adrelid = 'public.test'::REGCLASS
        ) End
);
</code>

Well (thinking it thoroughly) it won't work at all as is. It will just
put the source code of the default expression but you would need to
*interpret* it first.
Looked for a way to do this (without `EXECUTE') but couldn't find it. Sorry.

--
Diego Augusto Molina
diegoaugustomolina@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html

Re: Indicating DEFAULT values in INSERT statement

From
Postgres User
Date:
Good idea but you're right, if the default value is an expression such as a the next serial value, it will need to be executed first.  If Execute doesn't return the interpreted value, I don't know of a way to make it work..

On Tue, Aug 9, 2011 at 5:35 PM, Diego Augusto Molina <diegoaugustomolina@gmail.com> wrote:
This is a bit hacky, but it may work (I haven't tried it yet). If it
works let us know and if it doesn't then maybe we can debug it and get
something useful, or move on to another solution.

<code>
 INSERT INTO public.test
(
userid, object_id, user_notes, object_status, created_ts
)
VALUES
(
p_userid, p_obj_id, p_user_notes, p_obj_status,
       Case When p_created_ts Is Not Null Then p_created_ts Else
       ( -- You may want to be sure the field has a default value.
               SELECT d.adsrc -- or should it be d.adbin?
                       FROM
                               pg_catalog.pg_attribute a INNER JOIN
                               pg_catalog.pg_attrdef d ON (a.attnum = d.adnum)
                       WHERE
                               a.attname = 'created_ts' AND
                               a.attrelid = 'public.test'::REGCLASS AND
                               d.adrelid = 'public.test'::REGCLASS
       ) End
);
</code>

Well (thinking it thoroughly) it won't work at all as is. It will just
put the source code of the default expression but you would need to
*interpret* it first.
Looked for a way to do this (without `EXECUTE') but couldn't find it. Sorry.

--
Diego Augusto Molina
diegoaugustomolina@gmail.com

ES: Por favor, evite adjuntar documentos de Microsoft Office. Serán
desestimados.
EN: Please, avoid attaching Microsoft Office documents. They shall be discarded.
LINK: http://www.gnu.org/philosophy/no-word-attachments.html

Re: Indicating DEFAULT values in INSERT statement

From
Vincent Veyron
Date:
Le mardi 09 août 2011 à 15:57 -0700, Postgres User a écrit :

>
>
> From a db function, I'd like to force the use of default when an input
> parameter is null.

May be something like this :


CREATE TABLE users (
    id bigint NOT NULL,
    username text NOT NULL,
    is_active boolean DEFAULT false
);


CREATE FUNCTION new_user (text, boolean default 't') RETURNS SETOF users
AS $$

INSERT INTO users(username, is_active) VALUES($1, $2) RETURNING *;

$$ LANGUAGE SQL;

 select new_user('no_status_defined');
         new_user
--------------------------
 (10,no_status_defined,t)
(1 ligne)


As you can see, in the case where the second parameter is absent, the
default set by the function (true) is used.

http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-SQL-PARAMETER-DEFAULTS

It won't work if NOT NULL is set on the field.


--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique


Re: Indicating DEFAULT values in INSERT statement

From
Vincent Veyron
Date:
Le mardi 09 août 2011 à 15:57 -0700, Postgres User a écrit :

>
>
> From a db function, I'd like to force the use of default when an input
> parameter is null.

May be something like this :


CREATE TABLE users (
    id serial NOT NULL,
    username text NOT NULL,
    is_active boolean DEFAULT false
);


CREATE FUNCTION new_user (text, boolean default 't') RETURNS SETOF users
AS $$

INSERT INTO users(username, is_active) VALUES($1, $2) RETURNING *;

$$ LANGUAGE SQL;

 select new_user('no_status_defined');
         new_user
--------------------------
 (10,no_status_defined,t)
(1 ligne)


As you can see, in the case where the second parameter is absent, the
default set by the function (true) is used.

http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-SQL-PARAMETER-DEFAULTS

It won't work if NOT NULL is set on the field.


--
Vincent Veyron
http://marica.fr/
Logiciel de gestion des sinistres et des contentieux pour le service juridique