Обсуждение: update error

Поиск

Список

Период

Сортировка

update error

От

josep porres

Дата:

24 апреля 2009 г., 11:47:05

Hello,

I am migrating a database to pg and modifying the app.
In that process I found an error and i don't see the reason.

It's happening when executing the next update sentence
Do you see what's wrong?

The server is pg8.3.7 for w32
________________________________________________________________

update talls as t
set t.estat=5, t.d_pag=now(), t.ts_update=now()
where
t.estat in (0,1) and t.data = '02/04/2009'
and t.clau not in ( select p.clau from pendents p
                    where (t.data - p.data) >= 90 )
________________________________________________________
ERROR: column "t" of relation "talls" does not exist
LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now()
            ^

********** Error **********

ERROR: column "t" of relation "talls" does not exist
estat SQL: 42703
Caràcter: 25
_________________________________________________________

the table definitions are these:

CREATE TABLE TALLS (
    DATA        DATE NOT NULL,
    CLAU        VARCHAR(12) NOT NULL,
    CLAU_ABO    VARCHAR(12),
    LLIBRE    INTEGER,
    FULLA       NUMERIC(10) DEFAULT 0,
    NOM         VARCHAR(40),
    ADRECA    VARCHAR(40),
    NUMERCOMPT VARCHAR(15),
    LECACT    NUMERIC(10) DEFAULT 0,
    LECTURA     NUMERIC(10) DEFAULT 0,
    REFORMA     NUMERIC(1) DEFAULT 0,
    MOTIU       NUMERIC(1) DEFAULT 0,
    NOTES       VARCHAR(100),
    D_TALL    DATE,
    D_PAG       DATE,
    D_RECON     DATE,
    TS_UPDATE   TIMESTAMP,
    ESTAT       NUMERIC(2) DEFAULT 0,
    CPARAT    NUMERIC(1) DEFAULT 0,
    NOUCOMPT    VARCHAR(15),
    REINCID     NUMERIC(1) DEFAULT 0,
    CPOSTAL     NUMERIC(5),
    CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU));

CREATE TABLE PENDENTS (
    CLAU    VARCHAR(12),
    DATA    DATE,
    TITULAR   VARCHAR(40),
    BANC    VARCHAR(4),
    SUCURSAL VARCHAR(4),
    COMPTE    VARCHAR(10),
    IMPORT    NUMERIC(10,2),
    L1        VARCHAR(40) DEFAULT '',
    L2        VARCHAR(40) DEFAULT '',
    L3        VARCHAR(40) DEFAULT '',
    L4        VARCHAR(40) DEFAULT '',
    L5        VARCHAR(40) DEFAULT '',
    L6        VARCHAR(40) DEFAULT '',
    L7        VARCHAR(40) DEFAULT '',
    L8        VARCHAR(40) DEFAULT '',
    L9        VARCHAR(40) DEFAULT '',
    L10       VARCHAR(40) DEFAULT '',
    L11       VARCHAR(40) DEFAULT '',
    L12       VARCHAR(40) DEFAULT '',
    L13       VARCHAR(40) DEFAULT '',
    L14       VARCHAR(40) DEFAULT '',
    L15       VARCHAR(40) DEFAULT '',
    L16       VARCHAR(40) DEFAULT '',
    NOM       VARCHAR(40),
    AIGUA     NUMERIC(10,2) DEFAULT 0,
    QS        NUMERIC(10,2) DEFAULT 0,
    LLOGUER   NUMERIC(10,2) DEFAULT 0,
    CANON     NUMERIC(10,2) DEFAULT 0,
    MATERIALS NUMERIC(10,2) DEFAULT 0,
    IVA7    NUMERIC(10,2) DEFAULT 0,
    DESPESES NUMERIC(10,2) DEFAULT 0,
    FIANCA           NUMERIC(10,2) DEFAULT 0,
    DRET             NUMERIC(10,2) DEFAULT 0,
    DATACOBRAMENT    DATE,
    CLAVEGUERAM      NUMERIC(10,2) DEFAULT 0,
    QSCLAVEGUERAM    NUMERIC(10,2) DEFAULT 0,
    DOCUMENT         VARCHAR(10),
    DEVOLUCIO        NUMERIC(1)    DEFAULT 0,
    ESTAT            VARCHAR(1),
    CARTA            NUMERIC(1)    DEFAULT 0,
    ESPECIAL         NUMERIC(1)    DEFAULT 0,
    CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT));

Re: update error

От

Filip Rembiałkowski

Дата:

24 апреля 2009 г., 12:06:39

2009/4/24 josep porres <jmporres@gmail.com>

Hello,

I am migrating a database to pg and modifying the app.
In that process I found an error and i don't see the reason.

It's happening when executing the next update sentence
Do you see what's wrong?

table name / alias is not allowed in SET section.

http://www.postgresql.org/docs/current/static/sql-update.html

> Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

The server is pg8.3.7 for w32
________________________________________________________________

update talls as t
set t.estat=5, t.d_pag=now(), t.ts_update=now()
where
t.estat in (0,1) and t.data = '02/04/2009'
and t.clau not in ( select p.clau from pendents p
                    where (t.data - p.data) >= 90 )
________________________________________________________
ERROR: column "t" of relation "talls" does not exist
LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now()
            ^

********** Error **********

ERROR: column "t" of relation "talls" does not exist
estat SQL: 42703
Caràcter: 25
_________________________________________________________

the table definitions are these:

CREATE TABLE TALLS (
    DATA        DATE NOT NULL,
    CLAU        VARCHAR(12) NOT NULL,
    CLAU_ABO    VARCHAR(12),
    LLIBRE    INTEGER,
    FULLA       NUMERIC(10) DEFAULT 0,
    NOM         VARCHAR(40),
    ADRECA    VARCHAR(40),
    NUMERCOMPT VARCHAR(15),
    LECACT    NUMERIC(10) DEFAULT 0,
    LECTURA     NUMERIC(10) DEFAULT 0,
    REFORMA     NUMERIC(1) DEFAULT 0,
    MOTIU       NUMERIC(1) DEFAULT 0,
    NOTES       VARCHAR(100),
    D_TALL    DATE,
    D_PAG       DATE,
    D_RECON     DATE,
    TS_UPDATE   TIMESTAMP,
    ESTAT       NUMERIC(2) DEFAULT 0,
    CPARAT    NUMERIC(1) DEFAULT 0,
    NOUCOMPT    VARCHAR(15),
    REINCID     NUMERIC(1) DEFAULT 0,
    CPOSTAL     NUMERIC(5),
    CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU));

CREATE TABLE PENDENTS (
    CLAU    VARCHAR(12),
    DATA    DATE,
    TITULAR   VARCHAR(40),
    BANC    VARCHAR(4),
    SUCURSAL VARCHAR(4),
    COMPTE    VARCHAR(10),
    IMPORT    NUMERIC(10,2),
    L1        VARCHAR(40) DEFAULT '',
    L2        VARCHAR(40) DEFAULT '',
    L3        VARCHAR(40) DEFAULT '',
    L4        VARCHAR(40) DEFAULT '',
    L5        VARCHAR(40) DEFAULT '',
    L6        VARCHAR(40) DEFAULT '',
    L7        VARCHAR(40) DEFAULT '',
    L8        VARCHAR(40) DEFAULT '',
    L9        VARCHAR(40) DEFAULT '',
    L10       VARCHAR(40) DEFAULT '',
    L11       VARCHAR(40) DEFAULT '',
    L12       VARCHAR(40) DEFAULT '',
    L13       VARCHAR(40) DEFAULT '',
    L14       VARCHAR(40) DEFAULT '',
    L15       VARCHAR(40) DEFAULT '',
    L16       VARCHAR(40) DEFAULT '',
    NOM       VARCHAR(40),
    AIGUA     NUMERIC(10,2) DEFAULT 0,
    QS        NUMERIC(10,2) DEFAULT 0,
    LLOGUER   NUMERIC(10,2) DEFAULT 0,
    CANON     NUMERIC(10,2) DEFAULT 0,
    MATERIALS NUMERIC(10,2) DEFAULT 0,
    IVA7    NUMERIC(10,2) DEFAULT 0,
    DESPESES NUMERIC(10,2) DEFAULT 0,
    FIANCA           NUMERIC(10,2) DEFAULT 0,
    DRET             NUMERIC(10,2) DEFAULT 0,
    DATACOBRAMENT    DATE,
    CLAVEGUERAM      NUMERIC(10,2) DEFAULT 0,
    QSCLAVEGUERAM    NUMERIC(10,2) DEFAULT 0,
    DOCUMENT         VARCHAR(10),
    DEVOLUCIO        NUMERIC(1)    DEFAULT 0,
    ESTAT            VARCHAR(1),
    CARTA            NUMERIC(1)    DEFAULT 0,
    ESPECIAL         NUMERIC(1)    DEFAULT 0,
    CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT));

--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

Re: update error

От

josep porres

Дата:

24 апреля 2009 г., 12:20:54

ok, it's working without the alias inside the SET

thanks

2009/4/24 Filip Rembiałkowski <plk.zuber@gmail.com>

2009/4/24 josep porres <jmporres@gmail.com>

Hello,

I am migrating a database to pg and modifying the app.
In that process I found an error and i don't see the reason.

It's happening when executing the next update sentence
Do you see what's wrong?

table name / alias is not allowed in SET section.

http://www.postgresql.org/docs/current/static/sql-update.html

> Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

The server is pg8.3.7 for w32
________________________________________________________________

update talls as t
set t.estat=5, t.d_pag=now(), t.ts_update=now()
where
t.estat in (0,1) and t.data = '02/04/2009'
and t.clau not in ( select p.clau from pendents p
                    where (t.data - p.data) >= 90 )
________________________________________________________
ERROR: column "t" of relation "talls" does not exist
LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now()
            ^

********** Error **********

ERROR: column "t" of relation "talls" does not exist
estat SQL: 42703
Caràcter: 25
_________________________________________________________

the table definitions are these:

CREATE TABLE TALLS (
    DATA        DATE NOT NULL,
    CLAU        VARCHAR(12) NOT NULL,
    CLAU_ABO    VARCHAR(12),
    LLIBRE    INTEGER,
    FULLA       NUMERIC(10) DEFAULT 0,
    NOM         VARCHAR(40),
    ADRECA    VARCHAR(40),
    NUMERCOMPT VARCHAR(15),
    LECACT    NUMERIC(10) DEFAULT 0,
    LECTURA     NUMERIC(10) DEFAULT 0,
    REFORMA     NUMERIC(1) DEFAULT 0,
    MOTIU       NUMERIC(1) DEFAULT 0,
    NOTES       VARCHAR(100),
    D_TALL    DATE,
    D_PAG       DATE,
    D_RECON     DATE,
    TS_UPDATE   TIMESTAMP,
    ESTAT       NUMERIC(2) DEFAULT 0,
    CPARAT    NUMERIC(1) DEFAULT 0,
    NOUCOMPT    VARCHAR(15),
    REINCID     NUMERIC(1) DEFAULT 0,
    CPOSTAL     NUMERIC(5),
    CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU));

CREATE TABLE PENDENTS (
    CLAU    VARCHAR(12),
    DATA    DATE,
    TITULAR   VARCHAR(40),
    BANC    VARCHAR(4),
    SUCURSAL VARCHAR(4),
    COMPTE    VARCHAR(10),
    IMPORT    NUMERIC(10,2),
    L1        VARCHAR(40) DEFAULT '',
    L2        VARCHAR(40) DEFAULT '',
    L3        VARCHAR(40) DEFAULT '',
    L4        VARCHAR(40) DEFAULT '',
    L5        VARCHAR(40) DEFAULT '',
    L6        VARCHAR(40) DEFAULT '',
    L7        VARCHAR(40) DEFAULT '',
    L8        VARCHAR(40) DEFAULT '',
    L9        VARCHAR(40) DEFAULT '',
    L10       VARCHAR(40) DEFAULT '',
    L11       VARCHAR(40) DEFAULT '',
    L12       VARCHAR(40) DEFAULT '',
    L13       VARCHAR(40) DEFAULT '',
    L14       VARCHAR(40) DEFAULT '',
    L15       VARCHAR(40) DEFAULT '',
    L16       VARCHAR(40) DEFAULT '',
    NOM       VARCHAR(40),
    AIGUA     NUMERIC(10,2) DEFAULT 0,
    QS        NUMERIC(10,2) DEFAULT 0,
    LLOGUER   NUMERIC(10,2) DEFAULT 0,
    CANON     NUMERIC(10,2) DEFAULT 0,
    MATERIALS NUMERIC(10,2) DEFAULT 0,
    IVA7    NUMERIC(10,2) DEFAULT 0,
    DESPESES NUMERIC(10,2) DEFAULT 0,
    FIANCA           NUMERIC(10,2) DEFAULT 0,
    DRET             NUMERIC(10,2) DEFAULT 0,
    DATACOBRAMENT    DATE,
    CLAVEGUERAM      NUMERIC(10,2) DEFAULT 0,
    QSCLAVEGUERAM    NUMERIC(10,2) DEFAULT 0,
    DOCUMENT         VARCHAR(10),
    DEVOLUCIO        NUMERIC(1)    DEFAULT 0,
    ESTAT            VARCHAR(1),
    CARTA            NUMERIC(1)    DEFAULT 0,
    ESPECIAL         NUMERIC(1)    DEFAULT 0,
    CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT));

--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

Re: update error

От

josep porres

Дата:

24 апреля 2009 г., 12:32:29

So, what's the point of the alias in the documentation?

-> http://www.postgresql.org/docs/current/static/sql-update.html

2009/4/24 Filip Rembiałkowski <plk.zuber@gmail.com>

2009/4/24 josep porres <jmporres@gmail.com>

Hello,

I am migrating a database to pg and modifying the app.
In that process I found an error and i don't see the reason.

It's happening when executing the next update sentence
Do you see what's wrong?

table name / alias is not allowed in SET section.

http://www.postgresql.org/docs/current/static/sql-update.html

> Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

The server is pg8.3.7 for w32
________________________________________________________________

update talls as t
set t.estat=5, t.d_pag=now(), t.ts_update=now()
where
t.estat in (0,1) and t.data = '02/04/2009'
and t.clau not in ( select p.clau from pendents p
                    where (t.data - p.data) >= 90 )
________________________________________________________
ERROR: column "t" of relation "talls" does not exist
LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now()
            ^

********** Error **********

ERROR: column "t" of relation "talls" does not exist
estat SQL: 42703
Caràcter: 25
_________________________________________________________

the table definitions are these:

CREATE TABLE TALLS (
    DATA        DATE NOT NULL,
    CLAU        VARCHAR(12) NOT NULL,
    CLAU_ABO    VARCHAR(12),
    LLIBRE    INTEGER,
    FULLA       NUMERIC(10) DEFAULT 0,
    NOM         VARCHAR(40),
    ADRECA    VARCHAR(40),
    NUMERCOMPT VARCHAR(15),
    LECACT    NUMERIC(10) DEFAULT 0,
    LECTURA     NUMERIC(10) DEFAULT 0,
    REFORMA     NUMERIC(1) DEFAULT 0,
    MOTIU       NUMERIC(1) DEFAULT 0,
    NOTES       VARCHAR(100),
    D_TALL    DATE,
    D_PAG       DATE,
    D_RECON     DATE,
    TS_UPDATE   TIMESTAMP,
    ESTAT       NUMERIC(2) DEFAULT 0,
    CPARAT    NUMERIC(1) DEFAULT 0,
    NOUCOMPT    VARCHAR(15),
    REINCID     NUMERIC(1) DEFAULT 0,
    CPOSTAL     NUMERIC(5),
    CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU));

CREATE TABLE PENDENTS (
    CLAU    VARCHAR(12),
    DATA    DATE,
    TITULAR   VARCHAR(40),
    BANC    VARCHAR(4),
    SUCURSAL VARCHAR(4),
    COMPTE    VARCHAR(10),
    IMPORT    NUMERIC(10,2),
    L1        VARCHAR(40) DEFAULT '',
    L2        VARCHAR(40) DEFAULT '',
    L3        VARCHAR(40) DEFAULT '',
    L4        VARCHAR(40) DEFAULT '',
    L5        VARCHAR(40) DEFAULT '',
    L6        VARCHAR(40) DEFAULT '',
    L7        VARCHAR(40) DEFAULT '',
    L8        VARCHAR(40) DEFAULT '',
    L9        VARCHAR(40) DEFAULT '',
    L10       VARCHAR(40) DEFAULT '',
    L11       VARCHAR(40) DEFAULT '',
    L12       VARCHAR(40) DEFAULT '',
    L13       VARCHAR(40) DEFAULT '',
    L14       VARCHAR(40) DEFAULT '',
    L15       VARCHAR(40) DEFAULT '',
    L16       VARCHAR(40) DEFAULT '',
    NOM       VARCHAR(40),
    AIGUA     NUMERIC(10,2) DEFAULT 0,
    QS        NUMERIC(10,2) DEFAULT 0,
    LLOGUER   NUMERIC(10,2) DEFAULT 0,
    CANON     NUMERIC(10,2) DEFAULT 0,
    MATERIALS NUMERIC(10,2) DEFAULT 0,
    IVA7    NUMERIC(10,2) DEFAULT 0,
    DESPESES NUMERIC(10,2) DEFAULT 0,
    FIANCA           NUMERIC(10,2) DEFAULT 0,
    DRET             NUMERIC(10,2) DEFAULT 0,
    DATACOBRAMENT    DATE,
    CLAVEGUERAM      NUMERIC(10,2) DEFAULT 0,
    QSCLAVEGUERAM    NUMERIC(10,2) DEFAULT 0,
    DOCUMENT         VARCHAR(10),
    DEVOLUCIO        NUMERIC(1)    DEFAULT 0,
    ESTAT            VARCHAR(1),
    CARTA            NUMERIC(1)    DEFAULT 0,
    ESPECIAL         NUMERIC(1)    DEFAULT 0,
    CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT));

--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

Re: update error

От

Tom Lane

Дата:

24 апреля 2009 г., 14:15:53

josep porres <jmporres@gmail.com> writes:
> So, what's the point of the alias in the documentation?

You might want the alias elsewhere in the command, in places where it's
actually possible to refer to more than one table.

            regards, tom lane

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: update error

update error

Re: update error

Re: update error

Re: update error

Re: update error