pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
no value for numeric filed in SQL statement causes an error
4+ messages / 4 participants
[nested] [flat]

* no value for numeric filed in SQL statement causes an error
@ 2001-01-31 14:16  juerg.rietmann@pup.ch
  0 siblings, 2 replies; 4+ messages in thread

From: juerg.rietmann@pup.ch @ 2001-01-31 14:16 UTC (permalink / raw)
  To: pgsql-sql

Hi there

I have a table with char and int fields. From my form  I get no values back
for int fields when they are left blank. This causes the SQL insert to
fail.

field     type
name char
id   int2
city char

insert into table (name,id,city) values ('roger rabbit',,'carrot city')

The default for int/float fields is 0. Since I reuse the code of the
referring page I don't like to have all fields assigned 0 at the beginning.

Who can help ???

Thanks ... jr

============================================
PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

phone: +4141 790 4040
fax: +4141 790 2545
mobile: +4179 211 0315
============================================




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: no value for numeric filed in SQL statement causes an error
@ 2001-01-31 14:35  Ian Harding <iharding@pakrat.com>
  parent: juerg.rietmann@pup.ch
  1 sibling, 0 replies; 4+ messages in thread

From: Ian Harding @ 2001-01-31 14:35 UTC (permalink / raw)
  To: pgsql-sql

juerg.rietmann@pup.ch wrote:

> Hi there
>
> I have a table with char and int fields. From my form  I get no values back
> for int fields when they are left blank. This causes the SQL insert to
> fail.
>
> field     type
> name char
> id   int2
> city char
>
> insert into table (name,id,city) values ('roger rabbit',,'carrot city')
>
> The default for int/float fields is 0. Since I reuse the code of the
> referring page I don't like to have all fields assigned 0 at the beginning.
>
> Who can help ???
>
> Thanks ... jr
>
> ============================================
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
>
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
> ============================================

That is by design, as they say.  You can allow the default to take effect by
changing your field list on the insert to not include id.

insert into table (name,city) values ('roger rabbit','carrot city')

I don't know what your front end is, but you could have it plug a figure into
the offending fields if blank as well.

Ian




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: no value for numeric filed in SQL statement causes an error
@ 2001-01-31 14:43  Michael Fork <mfork@toledolink.com>
  parent: juerg.rietmann@pup.ch
  1 sibling, 0 replies; 4+ messages in thread

From: Michael Fork @ 2001-01-31 14:43 UTC (permalink / raw)
  To: juerg.rietmann@pup.ch; +Cc: pgsql-sql

You can do it one of two ways, dynamically build your queries and leave
out fields that are blank (they will be set to the default value or, if
none is specified, NULL), i.e.:

INSERT INTO table (name, city) VALUES ('roger rabbit', 'carrot city');

or, assign a default value to the id variable, and update the variable if
the form field is not blank, leaving you with:

INSERT INTO table (name, id, city) VALUES ('roger rabbit', <<Default ID
Variable or Form Value Variable>>, 'carrot city');

You cannot put a column with any of the number types (int4, numeric,
etc.) into the field list and leave it blank, a value *must* be specified.  

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Wed, 31 Jan 2001 juerg.rietmann@pup.ch wrote:

> Hi there
> 
> I have a table with char and int fields. From my form  I get no values back
> for int fields when they are left blank. This causes the SQL insert to
> fail.
> 
> field     type
> name char
> id   int2
> city char
> 
> insert into table (name,id,city) values ('roger rabbit',,'carrot city')
> 
> The default for int/float fields is 0. Since I reuse the code of the
> referring page I don't like to have all fields assigned 0 at the beginning.
> 
> Who can help ???
> 
> Thanks ... jr
> 
> ============================================
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
> 
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
> ============================================
> 




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: no value for numeric filed in SQL statement causes an error
@ 2001-01-31 16:20  Michael Davis <mdavis@sevainc.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Michael Davis @ 2001-01-31 16:20 UTC (permalink / raw)
  To: 'juerg.rietmann@pup.ch' <juerg.rietmann@pup.ch>; pgsql-sql

Try: 

insert into table (name,id,city) values ('roger rabbit',NULL,'carrot city')


-----Original Message-----
From:	juerg.rietmann@pup.ch [SMTP:juerg.rietmann@pup.ch]
Sent:	Wednesday, January 31, 2001 7:16 AM
To:	pgsql-sql@postgresql.org
Subject:	no value for numeric filed in SQL statement causes an error

Hi there

I have a table with char and int fields. From my form  I get no values back
for int fields when they are left blank. This causes the SQL insert to
fail.

field     type
name char
id   int2
city char

insert into table (name,id,city) values ('roger rabbit',,'carrot city')

The default for int/float fields is 0. Since I reuse the code of the
referring page I don't like to have all fields assigned 0 at the beginning.

Who can help ???

Thanks ... jr

============================================
PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

phone: +4141 790 4040
fax: +4141 790 2545
mobile: +4179 211 0315
============================================




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-01-31 16:20 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-31 14:16 no value for numeric filed in SQL statement causes an error juerg.rietmann@pup.ch
2001-01-31 14:35 ` Ian Harding <iharding@pakrat.com>
2001-01-31 14:43 ` Michael Fork <mfork@toledolink.com>
2001-01-31 16:20 RE: no value for numeric filed in SQL statement causes an error Michael Davis <mdavis@sevainc.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox