Thread: 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
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
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:Use a BEFORE trigger to set it.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?
--
Craig Ringer
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