Обсуждение: cannot use column references in default expression?

Could anyone please help me to get rid of following error? I want to set the 'ishuman' column based on the value of 'ID' column but it is not allowing me to do so. Any alternatives?

techdb=> CREATE TABLE Users (
       ID INTEGER,
       isHuman        BOOLEAN NOT NULL
                      DEFAULT (ID IS NULL)
                      CHECK (isHuman = ID IS NULL),
       Name  VARCHAR NOT NULL);
ERROR:  cannot use column references in default expression
techdb=>

Thanks,
Jignesh

On 29/05/2010 1:20 AM, Jignesh Shah wrote:
> Could anyone please help me to get rid of following error? I want to set
> the 'ishuman' column based on the value of 'ID' column but it is not
> allowing me to do so. Any alternatives?

Use a BEFORE trigger to set it.

--
Craig Ringer

Trigger should be the last solution. This used to be working but I think with latest postgresql upgrade, this stopped working. Might be someone around here knows whats going on here.

Thanks,
Jignesh

On May 28, 2010, at 10:41 AM, Jignesh Shah wrote:

> Trigger should be the last solution. This used to be working but I
> think with latest postgresql upgrade, this stopped working. Might be
> someone around here knows whats going on here.

I'm afraid not.  It's never been allowed to have a variable in the
expression for a DEFAULT value.  To quote the manual:

> DEFAULT default_expr
> The DEFAULT clause assigns a default data value for the column whose
> column definition it appears within. The value is any variable-free
> expression (subqueries and cross-references to other columns in the
> current table are not allowed). The data type of the default
> expression must match the data type of the column.
>
A trigger is the appropriate solution in this case.

--
-- Christophe Pettus
    xof@thebuild.com

On 05/28/2010 10:41 AM, Jignesh Shah wrote:
> Trigger should be the last solution. This used to be working but I think
> with latest postgresql upgrade, this stopped working. Might be someone
> around here knows whats going on here.
>
> Thanks,
> Jignesh
>
> On Fri, May 28, 2010 at 11:00 PM, Craig Ringer
> <craig@postnewspapers.com.au>wrote:
>
>> On 29/05/2010 1:20 AM, Jignesh Shah wrote:
>>
>>> Could anyone please help me to get rid of following error? I want to set
>>> the 'ishuman' column based on the value of 'ID' column but it is not
>>> allowing me to do so. Any alternatives?
>>>
>>
>> Use a BEFORE trigger to set it.
>>
>> --
>> Craig Ringer
>>
>

What previous version? I just tried 8.3.5 and it did not work there either.

--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, May 28, 2010 at 10:50 PM, Jignesh Shah
<jignesh.shah1980@gmail.com> wrote:
> Could anyone please help me to get rid of following error? I want to set the
> 'ishuman' column based on the value of 'ID' column but it is not allowing me
> to do so. Any alternatives?
>
> techdb=> CREATE TABLE Users (
>        ID INTEGER,
>        isHuman        BOOLEAN NOT NULL
>                       DEFAULT (ID IS NULL)
>                       CHECK (isHuman = ID IS NULL),
>        Name  VARCHAR NOT NULL);
> ERROR:  cannot use column references in default expression
> techdb=>
>
> Thanks,
> Jignesh
>


I don't know much about the error, but an alternative solution I see
is using Triggers. WIth triggers you can manipulate the value during
INSERT or UPDATE.

--
Nilesh Govindarajan
Facebook: nilesh.gr
Twitter: nileshgr
Website: www.itech7.com