[GENERAL] cannot use column references in default expression?

[Jignesh Shah]

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

[Craig Ringer]

Post by Jignesh Shah
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

[Jignesh Shah]

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

Post by Craig Ringer

Post by Jignesh Shah
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

[Christophe Pettus]

Post by Jignesh Shah
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

Post by Jignesh Shah
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
***@thebuild.com

[Adrian Klaver]

Post by Jignesh Shah
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

Post by Craig Ringer

Post by Jignesh Shah
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.

[Nilesh Govindarajan]

On Fri, May 28, 2010 at 10:50 PM, Jignesh Shah

Post by Jignesh Shah
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