Re: [SQL] SQL design question: null vs. boolean values

[j.random.programmer]

Hi:

> > (A) 
> > I have three radio boxes in the user form
> > 
> > field_foo
> > []yes  
> > []no   
> > []unknown
> > ...
> > field_foo  char(1)  not null check 
> >   (field_foo in 'y', 'n', 'u')
> >  OR
> > field_foo char(1) check (field_foo in 'y', 'n')
> > 

> Option 1 - the value is known, the user made a
> choice and it was to 
> click the "unknown" box. The box could be labelled
> "marmalade" just as easily.

I see what you are saying. It's "known" that the user 
actually selected something (the choice they selected 
just happened to have a label "unknown"). 

NULL would be apprpriate if they had selected
nothing at all, right ?

However, if a choice is required (meaning the front
end 
html form cannot be submitted without some selection 
at least), then couldn't we fold unknown into NULL ?

i.e.,:

user choice
yes  -> 'y'
no   -> 'n'
unknown -> null

Since it's guaranteed that the user will always 
choose something ?

In fact, this is as you say similar to:

user choice
yes   -> 'y'
no -> 'n'
marmalade-> null

I ran into another issue while designing my front end
html form.

--
field_foo
[ ] yes 
[ ]  no

if you answered "yes" in field_foo above, you must 
enter detail here

foo_detail
[  ]
---

This is a little tricky to capture in the database.
The issue 
is that the nullability of one column depends AT
RUNTIME 
on the value of another column (NOT at design time). 

I ended up doing something like

create table xyz
(
field_foo   char(1) check (field_foo in 'y', 'n'),
foo_detail varchar(255),
check (
  case 
when field_foo='y' and foo_detail is null 
then false 
else true 
  end
  )
); 

The constraint uses a case that *requires* some value
foo_detail if field_foo is 'y'.

I don't know whether this is the recommended
way to do the above or I'm making things too
complicated.. Any other opinions/suggestions ? 

Best regards,

--j





__ 
Do you Yahoo!? 
Yahoo! Mail - You care about security. So do we. 
http://promotions.yahoo.com/new_mail

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] SQL design question: null vs. boolean values

[PFC]

create table xyz
(
field_foo   char(1) check (field_foo in 'y', 'n'),
foo_detail varchar(255),
check (
  case
when field_foo='y' and foo_detail is null
then false
else true
  end
  )
);
A simpler check would be :
CHECK(
(field_foo = 'y' AND foo_detail IS NOT NULL)
OR  ( (field_foo = 'n' OR field_foo IS NULL) AND foo_detail IS NULL)
)
	Which means " field_foo can be y, n, or NULL, and foo_detail should be  
null except if field_foo is 'y' "

	Also, IMHO, the Y/N/unknown case should have three values, NULL meaning  
'the user has not answered this question'. Because if you insert a blank  
row in the table and fill it afterwards, you'll know if it was 'answered  
unknown' or 'unanswered'.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] pgmirror

[Theo Galanakis]

Title: RE: [SQL] pgmirror





Thanks for your response, however can you please translate, my Greek is not the best.


-Original Message-
From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]] 
Sent: Friday, 14 January 2005 6:57 PM
To: Theo Galanakis
Subject: Re: [SQL] pgmirror



O Theo Galanakis έγραψε στις Jan 14, 2005 :


> 
> Hi,
>   I need some help getting dbMirror working. I have installed the 
> contrib RPM package for Redhat Linux. However it hasn't copied all the 
> necessary files for dbMirror. I could only find pending.so.


Mhn to kaneis me apt-get,rpm,pkg-add,portinstall, (put your fav pckg 
sys here) etc...


Apla phgaine sto contrib dir:


cd contrib/dbmirror
gmake ; gmake install


Epishs exw grapsei ena feature wste to dbmirror na kanei
kai conditional replication.
Opoios endiaferetai edw to exw!


> 
>   I have tried coping the other files from : 
> http://developer.postgresql.org/docs/pgsql/contrib/dbmirror/
> 
> 
>   then running the MirrorSetup.sql, however it returned the following 
> error :
> 
>   psql:MirrorSetup.sql:52: ERROR:  could not find function "nextval"
> in file   "/usr/lib/pgsql/pending.so"
> 
>   So I assume I need to compile pending.c. I need some help in doing 
> so?
> 
>   Im running RedHat Linux ES3 with Postgres 7.4.5.
> 
> Regards,
>   Theo
> 
> 
> 
> 
> 
> 
> __
> This email, including attachments, is intended only for the addressee 
> and may be confidential, privileged and subject to copyright.  If you 
> have received this email in error, please advise the sender and delete 
> it.  If you are not the intended recipient of this email, you must not 
> use, copy or disclose its content to anyone.  You must not copy or 
> communicate to others content that is confidential or subject to 
> copyright, unless you have the consent of the content owner.


-- 
-Achilleus

Re: [SQL] TEXT type blob extraction in ecpg

[KÖPFERL Robert]

I think don't really unterstand your intend.
However there exists a type text in SQL and Postgres which is a text of
unspecified length and used like a SQL-String. Thus quoted with '

So when you earlier were able to gain the content of that field. It should
be no problem to quote it.

> -Original Message-
> From: none none [mailto:[EMAIL PROTECTED]
> Sent: Montag, 10. Jänner 2005 18:43
> To: pgsql-sql@postgresql.org
> Subject: [SQL] TEXT type blob extraction in ecpg
> 
> 
> 
> Does any one know how to properly extract a TEXT type blob 
> (undefined char 
> length) in ecpg (Linux system)?  We are converting our SQL 
> from Informix to 
> PostGres.  Informix had a specific struct defined and I 
> believe handled the 
> dynamic memory allocation, but I cannot find any guidance on 
> this issue for 
> PostGres.  Anyone have any suggestions?
> 
> Thanks,
> 
> Tom
> 
> 
> 
> ---(end of 
> broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html
> 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html