cannot use column references in default expression?

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

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 Fri, May 28, 2010 at 11:00 PM, Craig Ringer
<craig@postnewspapers.com.au>wrote:

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