Обсуждение: ERROR: invalid input syntax for integer: ""

Hi all,
I get invalid input syntax when UPDATEing my radacct table:

freedsl=# \d radacct
                                             Table "public.radacct"
         Column        |           Type           |                          Modifiers
----------------------+--------------------------+-------------------------------------------------------------
  radacctid            | bigint                   | not null default nextval('radacct_radacctid_seq'::regclass)
  acctsessionid        | character varying(32)    | not null
  acctuniqueid         | character varying(32)    | not null
  username             | character varying(253)   |
  groupname            | character varying(253)   |
  realm                | character varying(64)    |
  nasipaddress         | inet                     | not null
  nasportid            | character varying(15)    |
  nasporttype          | character varying(32)    |
  acctstarttime        | timestamp with time zone |
  acctstoptime         | timestamp with time zone |
  acctsessiontime      | bigint                   |
  acctauthentic        | character varying(32)    |
  connectinfo_start    | character varying(50)    |
  connectinfo_stop     | character varying(50)    |
  acctinputoctets      | bigint                   |
  acctoutputoctets     | bigint                   |
  calledstationid      | character varying(50)    |
  callingstationid     | character varying(50)    |
  acctterminatecause   | character varying(32)    |
  servicetype          | character varying(32)    |
  xascendsessionsvrkey | character varying(10)    |
  framedprotocol       | character varying(32)    |
  framedipaddress      | inet                     |
  acctstartdelay       | integer                  |
  acctstopdelay        | integer                  |
Indexes:
     "radacct_pkey" PRIMARY KEY, btree (radacctid)

The query is:


UPDATE radacct
  SET
   AcctStopTime = ('2009-09-25 10:35:44'::timestamp - '0'::interval),
   AcctSessionTime = CASE WHEN '' = '' THEN   (EXTRACT(EPOCH FROM ('2009-09-25 10:35:44'::TIMESTAMP WITH TIME ZONE -
AcctStartTime::TIMESTAMPWITH TIME ZONE   - '0'::INTERVAL)))::BIGINT ELSE '' END, 
   AcctInputOctets = (('0'::bigint << 32) + '0'::bigint),
   AcctOutputOctets = (('0'::bigint << 32) + '0'::bigint),
   AcctTerminateCause = '',
   AcctStopDelay = 0,
   FramedIPAddress = NULLIF('', '')::inet,
   ConnectInfo_stop = ''
  WHERE AcctSessionId = ''
   AND UserName = 'mihamina'
   AND NASIPAddress = '0.0.0.0'
   AND AcctStopTime IS NULL




I cannot find the problem....
Any hints?
Thank you.

--
       Architecte Informatique chez Blueline/Gulfsat:
    Administration Systeme, Recherche & Developpement
                                    +261 34 29 155 34

09/25/2009 01:55 PM, Grzegorz Jaśkiewicz:
> and what's the error message ?

   ERROR: invalid input syntax for integer: ""

(I put it in the email subject, not very clever from me)

--
       Architecte Informatique chez Blueline/Gulfsat:
    Administration Systeme, Recherche & Developpement
                                    +261 34 29 155 34

In response to Rakotomandimby Mihamina :
> Hi all,
> I get invalid input syntax when UPDATEing my radacct table:
>
> freedsl=# \d radacct
>                                             Table "public.radacct"
>         Column        |           Type           |
>         Modifiers
> ----------------------+--------------------------+-------------------------------------------------------------
>  radacctid            | bigint                   | not null default
> ...
>  acctsessiontime      | bigint                   |
> ...
>   AcctSessionTime = CASE WHEN '' = '' THEN   (EXTRACT(EPOCH FROM
>   ('2009-09-25 10:35:44'::TIMESTAMP WITH TIME ZONE -
>   AcctStartTime::TIMESTAMP WITH TIME ZONE   - '0'::INTERVAL)))::BIGINT ELSE
>   '' END,

test=*# select ''::int;
ERROR:  invalid input syntax for integer: ""


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

Rakotomandimby Mihamina <mihamina@gulfsat.mg> writes:
> I get invalid input syntax when UPDATEing my radacct table:

It's the ELSE '' here:

>    AcctSessionTime = CASE WHEN '' = '' THEN   (EXTRACT(EPOCH FROM ('2009-09-25 10:35:44'::TIMESTAMP WITH TIME ZONE -
AcctStartTime::TIMESTAMPWITH TIME ZONE   - '0'::INTERVAL)))::BIGINT ELSE '' END, 

FWIW, 8.4 will give you a syntax error pointer in cases like this.

            regards, tom lane