Thread: ...
Hi,
I have a table in postgresql db with the following description.
- Table: vyapar_user
CREATE TABLE vyapar_user (
loginid varchar(150) NOT NULL,
name varchar(150) NOT NULL,
"password" varchar(32) NOT NULL,
"type" varchar(10) NOT NULL,
address varchar(100) NOT NULL,
phone varchar(15),
email varchar(20),
creationdate date DEFAULT date('now'::text),
expriydate date DEFAULT (date('now'::text) + 360),
CONSTRAINT vyapar_user_pk PRIMARY KEY (loginid)
) WITH OIDS;
If i try inserting NULL values from backend, then it gives me an
error. But thru php, its adds null values without any errors. Any idea
how to solve this issue.....
On Mon, 15 Sep 2003, S.P.Vimala wrote:
> Hi,
>
> I have a table in postgresql db with the following description.
>
> - Table: vyapar_user
> CREATE TABLE vyapar_user (
> loginid varchar(150) NOT NULL,
> name varchar(150) NOT NULL,
> "password" varchar(32) NOT NULL,
> "type" varchar(10) NOT NULL,
> address varchar(100) NOT NULL,
> phone varchar(15),
> email varchar(20),
> creationdate date DEFAULT date('now'::text),
> expriydate date DEFAULT (date('now'::text) + 360),
> CONSTRAINT vyapar_user_pk PRIMARY KEY (loginid)
> ) WITH OIDS;
>
>
> If i try inserting NULL values from backend, then it gives me an
> error. But thru php, its adds null values without any errors. Any idea
> how to solve this issue.....
I'll need to see some sample code. In my experience, PHP cannot insert
NULLS into not null fields.
Are you sure you're not inserting '' and thinking of it as NULL? They're
not the same.
I think u are right, My code goes like this.......
This is what i do in my html page
$result = insert_user($userloginid,$username,$password,$usertype,$txtAddress,
$phoneno,$email);
The function insert_user is like this.
function insert_user($loginid,$username,$password,$usertype,$txtAddress,$phoneno,$email){
if($dbhandle=get_dbconnection())
{
$query="insert into vyapar_user values('". $loginid .
"','" . $username . "','" . $password . "','" . $usertype . "','" .
$txtAddress . "','" . $phoneno . "','" . $email . "')";
$result=pg_exec($dbhandle,$query);
if(!result)
return null;
else
return $result;
}
}
I need to prevent '' as well as ' ' entry into the db which are also null
values may be not theoritically but in practice they are....
so any suggestions?
> On Mon, 15 Sep 2003, S.P.Vimala wrote:
>
> > Hi,
> >
> > I have a table in postgresql db with the following description.
> >
> > - Table: vyapar_user
> > CREATE TABLE vyapar_user (
> > loginid varchar(150) NOT NULL,
> > name varchar(150) NOT NULL,
> > "password" varchar(32) NOT NULL,
> > "type" varchar(10) NOT NULL,
> > address varchar(100) NOT NULL,
> > phone varchar(15),
> > email varchar(20),
> > creationdate date DEFAULT date('now'::text),
> > expriydate date DEFAULT (date('now'::text) + 360),
> > CONSTRAINT vyapar_user_pk PRIMARY KEY (loginid)
> > ) WITH OIDS;
> >
> >
> > If i try inserting NULL values from backend, then it gives me an
> > error. But thru php, its adds null values without any errors. Any idea
> > how to solve this issue.....
>
> I'll need to see some sample code. In my experience, PHP cannot insert
> NULLS into not null fields.
>
> Are you sure you're not inserting '' and thinking of it as NULL? They're
> not the same.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
Sure, you just need a constraint:
create table test (info text constraint "no blanks" check (info<>'' and info<>' ') not null);
CREATE TABLE
insert into test (info) values ('');
ERROR: new row for relation "test" violates CHECK constraint "no blanks"
I'm not sure how to add constraints to already existing tables, so you
might wanna do this in a test table, move your data over, then rename
tables to swap in the new table.
On Tue, 16 Sep 2003, S.P.Vimala wrote:
>
>
> I think u are right, My code goes like this.......
>
>
> This is what i do in my html page
> $result = insert_user($userloginid,$username,$password,$usertype,$txtAddress,
> $phoneno,$email);
>
> The function insert_user is like this.
>
> function insert_user($loginid,$username,$password,$usertype,$txtAddress,$phoneno,$email){
> if($dbhandle=get_dbconnection())
> {
> $query="insert into vyapar_user values('". $loginid .
> "','" . $username . "','" . $password . "','" . $usertype . "','" .
> $txtAddress . "','" . $phoneno . "','" . $email . "')";
> $result=pg_exec($dbhandle,$query);
> if(!result)
> return null;
> else
> return $result;
> }
> }
>
>
> I need to prevent '' as well as ' ' entry into the db which are also null
> values may be not theoritically but in practice they are....
>
> so any suggestions?
>
>
>
> > On Mon, 15 Sep 2003, S.P.Vimala wrote:
> >
> > > Hi,
> > >
> > > I have a table in postgresql db with the following description.
> > >
> > > - Table: vyapar_user
> > > CREATE TABLE vyapar_user (
> > > loginid varchar(150) NOT NULL,
> > > name varchar(150) NOT NULL,
> > > "password" varchar(32) NOT NULL,
> > > "type" varchar(10) NOT NULL,
> > > address varchar(100) NOT NULL,
> > > phone varchar(15),
> > > email varchar(20),
> > > creationdate date DEFAULT date('now'::text),
> > > expriydate date DEFAULT (date('now'::text) + 360),
> > > CONSTRAINT vyapar_user_pk PRIMARY KEY (loginid)
> > > ) WITH OIDS;
> > >
> > >
> > > If i try inserting NULL values from backend, then it gives me an
> > > error. But thru php, its adds null values without any errors. Any idea
> > > how to solve this issue.....
> >
> > I'll need to see some sample code. In my experience, PHP cannot insert
> > NULLS into not null fields.
> >
> > Are you sure you're not inserting '' and thinking of it as NULL? They're
> > not the same.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if your
> > joining column's datatypes do not match
> >
>
>
> I'm not sure how to add constraints to already existing tables, so you > might wanna do this in a test table, move your data over, then rename > tables to swap in the new table. ALTER TABLE ... ADD CONSTRAINT
Attachment
I used java script to validate before passing values it to the backend.
The options of adding constraints to the backend is a good idea. I did
thru alter constraint. Thanks...
-vimala/.
On Tue, 16 Sep 2003, scott.marlowe wrote:
> Sure, you just need a constraint:
>
> create table test (info text constraint "no blanks" check (info<>'' and info<>' ') not null);
> CREATE TABLE
> insert into test (info) values ('');
> ERROR: new row for relation "test" violates CHECK constraint "no blanks"
>
> I'm not sure how to add constraints to already existing tables, so you
> might wanna do this in a test table, move your data over, then rename
> tables to swap in the new table.
>
> On Tue, 16 Sep 2003, S.P.Vimala wrote:
>
> >
> >
> > I think u are right, My code goes like this.......
> >
> >
> > This is what i do in my html page
> > $result = insert_user($userloginid,$username,$password,$usertype,$txtAddress,
> > $phoneno,$email);
> >
> > The function insert_user is like this.
> >
> > function insert_user($loginid,$username,$password,$usertype,$txtAddress,$phoneno,$email){
> > if($dbhandle=get_dbconnection())
> > {
> > $query="insert into vyapar_user values('". $loginid .
> > "','" . $username . "','" . $password . "','" . $usertype . "','" .
> > $txtAddress . "','" . $phoneno . "','" . $email . "')";
> > $result=pg_exec($dbhandle,$query);
> > if(!result)
> > return null;
> > else
> > return $result;
> > }
> > }
> >
> >
> > I need to prevent '' as well as ' ' entry into the db which are also null
> > values may be not theoritically but in practice they are....
> >
> > so any suggestions?
> >
> >
> >
> > > On Mon, 15 Sep 2003, S.P.Vimala wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a table in postgresql db with the following description.
> > > >
> > > > - Table: vyapar_user
> > > > CREATE TABLE vyapar_user (
> > > > loginid varchar(150) NOT NULL,
> > > > name varchar(150) NOT NULL,
> > > > "password" varchar(32) NOT NULL,
> > > > "type" varchar(10) NOT NULL,
> > > > address varchar(100) NOT NULL,
> > > > phone varchar(15),
> > > > email varchar(20),
> > > > creationdate date DEFAULT date('now'::text),
> > > > expriydate date DEFAULT (date('now'::text) + 360),
> > > > CONSTRAINT vyapar_user_pk PRIMARY KEY (loginid)
> > > > ) WITH OIDS;
> > > >
> > > >
> > > > If i try inserting NULL values from backend, then it gives me an
> > > > error. But thru php, its adds null values without any errors. Any idea
> > > > how to solve this issue.....
> > >
> > > I'll need to see some sample code. In my experience, PHP cannot insert
> > > NULLS into not null fields.
> > >
> > > Are you sure you're not inserting '' and thinking of it as NULL? They're
> > > not the same.
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 9: the planner will ignore your desire to choose an index scan if your
> > > joining column's datatypes do not match
> > >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
On Tue, 16 Sep 2003, Rod Taylor wrote: > > I'm not sure how to add constraints to already existing tables, so you > > might wanna do this in a test table, move your data over, then rename > > tables to swap in the new table. > > ALTER TABLE ... ADD CONSTRAINT For some unknown reason, that part of the alter table under \h eluded me when I was doing this... Well, it WAS like 6am, and I hadn't had any coffee. Think I'll take a nap under my desk now. :-)