[SQL] How NULL is interpreted in Pgsql

[Guru Prasad]

Dear Friends,

I created a table with no 'unique constraints' which is depicted as
follows.

create table junk (id int, name varchar);

Then, i inserted some records into the table. My table contents is listed
below.

 id  | name
-+--
 1001.00 | bolt
 1002.00 | nut
 1003.00 |
   11.00 | screw
 | cutter
 | cutting table

Then, i gave a select query for the above table. The select query is

'select * from junk where id != 11';

To my surprise, it displayed all the records except those which had
'NULL' in the 'id' field.

How is that possible ? (I mean it should displayed all the records except
the matching condition). 

But, if i set PRIMARY KEY constraint to 'junk' table then it displays all
the records (I can't say NULL here, but i can say '0'). 

How NULL is processed in Postgres. 
 
Could any one has some clue.

Regards,
guru.

bk SYSTEMS (P) Ltd.
P . N . G U R U P R A S A D
---




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] error message...

[Justin Clift]

Hi Sven,

There is a startup option, "-N" of how many clients are allowed to be
connected simultaneously.  The default is 32 in the source code, not
sure if SuSE has it changed.

You can also alter the default maximum before compiling it, but I get
the feeling you've installed pre-built packages.

Where does SuSE put it's startup scripts?  In /etc/rc.d/init.d ?  If so,
there should be something there relating to PostgreSQL, and it's this
you should modify.

If it starts PostgreSQL with "pg_ctl" then you'll need to pass the
option -o '-N '.  If it starts it with
"postmaster", then use -N  directly, without the
-o.

The man pages for pg_ctl and postmaster should be of some benefit also.

Regards and best wishes,

Justin Clift

"S.E.Franke" wrote:
> 
> Hi I have Postgres 7.0.3/6 on a Suse Professional 7.1 (kernel 2.4.0)
> machine.
> 
> The database is used very often and I see in the logfile the error
> message: Sorry, too many clients already
> 
> Can I set the number of 'active' clients? And where can I set this? And
> How?
> 
> Thanx in advance!
> 
> Sven Franke
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
 - Indira Gandhi

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] execute client application from PL/pgSql

[Justin Clift]

Hi,

It's exact URL is :

http://www.greatbridge.org/project/phppgadmin/projdisplay.php

For PostgreSQL related stuff like this, you can look them up at :

http://techdocs.postgresql.org/oresources.html

I still have to add a lot of the GreatBridge.org projects to it, as
they're expanding quite nicely.

:-)

Regards and best wishes,

Justin Clift

Jeff MacDonald wrote:
> 
> hi,
> 
> phpPGAdmin is a web based php driven postgresql
> admin tool. not sure of the exact url, try
> google :)
> 
> it has a pg_dump option in it.
> 
> jeff
> 
> On Sat, 12 May 2001, datactrl wrote:
> 
> > Date: Sat, 12 May 2001 10:23:39 +1000
> > From: datactrl <[EMAIL PROTECTED]>
> > To: [EMAIL PROTECTED]
> > Subject: Re: [SQL] execute client application from PL/pgSql
> >
> > Thank You Jeff,
> > What is phpPgAdmin and where can get it?
> >
> > Jack
> >
> > - Original Message -
> > From: "Jeff MacDonald" <[EMAIL PROTECTED]>
> > To: "Jack" <[EMAIL PROTECTED]>
> > Cc: <[EMAIL PROTECTED]>
> > Sent: Saturday, May 12, 2001 2:28 AM
> > Subject: Re: [SQL] execute client application from PL/pgSql
> >
> >
> > > you could hack the pg_dump bit out of phpPgAdmin
> > > i think the license permits it.
> > >
> > > just my 2 cents.
> > >
> > > jeff
> > >
> > > On Wed, 9 May 2001, Jack wrote:
> >
> >
> >
> > ---(end of broadcast)---
> > TIP 4: Don't 'kill -9' the postmaster
> >
> 
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
 - Indira Gandhi

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] system time

[Seema Noor]

is there any function from which i can get system time?


Do You Yahoo!?
Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
or your free @yahoo.ie address at http://mail.yahoo.ie

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

Re: [SQL] system time

[Justin Clift]

select now();

???

+ Justin

Seema Noor wrote:
> 
> is there any function from which i can get system time?
> 
> 
> Do You Yahoo!?
> Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk
> or your free @yahoo.ie address at http://mail.yahoo.ie
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
 - Indira Gandhi

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Case insensitive string comparison?

[Borek Lupoměský]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

   Is there an operator for case insensitive string comparison, or
should I use regular expression matching with ~* '^string$'?

   Bye Borek

- -- 

=
BOREK LUPOMESKYUsti nad Labem, Czech Republic, Europe
WWW: http://www.volny.cz/borekl/  PGP keyid: B6A06AEB
==[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: Get GPG key at http://www.volny.cz/borekl/pgp.txt

iD8DBQE7ASWbA6dWI7agausRAj4JAKCk5MCYlsLEjf+IR5ZZfy6ypDNG7QCeJj2n
eyPHuxTW+WQGyeo4SuvZvsw=
=p+pz
-END PGP SIGNATURE-


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

Re: [SQL] working with stored procedures (fwd)

[Guru Prasad]

> regy1'> output_rec := user_rec;

Here you can't assign the entire record (user_rec) into a variable. You
can assign either one of the following.

1. output_rec := user_rec.fname;
2. output_rec := user_rec.lname;
3. output_rec := user_rec.fname || user_rec.lname (This will concantenate
the two fields of same data type. I am not sure whether you will require
this).

I hope this will solve your problem.

Regards,
guru.

bk SYSTEMS (P) Ltd.
P . N . G U R U P R A S A D
---



On Mon, 14 May 2001, Radhika Vutukuru wrote:

> Hi ,
> I am trying to create a stored procedure that returns a user defined type. The 
>following piece of code shows the way I am trying to do it.
> regy1=> create function regyuser_rec(int) returns varchar as ' declare
> regy1'> v_userid alias for $1;
> regy1'> output_rec varchar(400);
> regy1'> user_rec record;
> regy1'> 
> regy1'> begin
> regy1'> 
> regy1'> select fname ,lname into user_rec from regyuser
> regy1'>  where userid = v_userid;
> regy1'> output_rec := user_rec;
> regy1'> return output_rec;
> regy1'> end;
> regy1'> ' language 'plpgsql';
> CREATE
> regy1=> select regyuser_rec(2);
> ERROR:  Attribute 'user_rec' not found
> 
> Does a postgresql function returns a user defined object?
> If so, could I have an example.
> I am new to postgresql, and was trying to figure out what's wrong with my code.Could 
>some one help me.
> Thanks,
> Radhika.
> 




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] How NULL is interpreted in Pgsql

[Tom Lane]

Guru Prasad <[EMAIL PROTECTED]> writes:
> 'select * from junk where id != 11';
> To my surprise, it displayed all the records except those which had
> 'NULL' in the 'id' field.

This is correct per spec: NULL is not a value and it doesn't act like one.
See any discussion of SQL NULLs --- Bruce's book talks about this IIRC,
or we've been over the turf more than once in the mailing list archives.

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] varbit question

[Laurent Duperval]

Hi,

I thought I posted this but I don't see it on the server so let's try
again:

I have an Oracle script that I want to convert to pgsql. I've got a sed
script that does most of the mappings but it doesn't look like raw is
supported. In the docs, there is mention of varbit(n) but if I use the
(n) argument, the script fails. I can only use varbit alone. How do I get
around that?

Also, will ResultSet.getBytes() (I'm talking Java here) return an array
of bytes with a field defined as varbit? How large will the field be?

Thanks,

L


-- 
Laurent Duperval 

Voisin, vous seriez une vraie lumière... si on avait ouvert l'interrupteur.
 -Lefuneste

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] system time

[Jason Earl]

Try using:

SELECT now()

It should do what you want.  If not there are a whole
pile of date functions.  Look them up in the Users
Guide.

Hope this is helpful.
Jason

--- Seema Noor <[EMAIL PROTECTED]> wrote:
> is there any function from which i can get system
> time?
> 
>

> Do You Yahoo!?
> Get your free @yahoo.co.uk address at
> http://mail.yahoo.co.uk
> or your free @yahoo.ie address at
> http://mail.yahoo.ie
> 
> ---(end of
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
[EMAIL PROTECTED]


__
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/

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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Select between two connections

[cbell]

Hello everyone, I would like to perform a Select statement that accesses

tables and finds matching fields in two separate databases.

 For example, I have two connections to two separate database objects on
the same
Postgres server.  I would like to retrieve information from a table in
one database based on some information from tables in a different
database.

Has anyone done this before, can it be done?

Thanks in advance,

Chris.




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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Please dont flame

[Olivier PRENANT]

Sorry to bother,

Just making sure my newsfeed works

-- 
Olivier PRENANT Tel:+33-5-61-50-97-00 (Work)
Quartier d'Harraud Turrou   +33-5-61-50-97-01 (Fax)
31190 AUTERIVE  +33-6-07-63-80-64 (GSM)
FRANCE  Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Case insensitive string comparison?

[Karel Zak]

On Tue, May 15, 2001 at 02:48:24PM +0200, Borek Lupoměský wrote:
>Is there an operator for case insensitive string comparison, or
> should I use regular expression matching with ~* '^string$'?

 possibility:

- use upper() / lower() inside query

- regex operators: ~* or !~* 

 - case insensitive 'like'

Karel

-- 
 Karel Zak  <[EMAIL PROTECTED]>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

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

http://www.postgresql.org/users-lounge/docs/faq.html

[SQL] Re: how to remove ?

[Per-Olof Pettersson]

Hi

Perhaps you have sent you commands in the subject-line.
All commands should be sent by putting them in the body of the message.

Best regards
Per-Olof Pettersson

>> Original Message <<

On 2001-05-14, 06:56:48, [EMAIL PROTECTED] ("Subhramanya Shiva") wrote 
regarding how to remove ?:


> How to remove from this mailing list ?


> > "Marc Lamothe" <[EMAIL PROTECTED]> writes:
> > > The subnet_number column is a varchar(16) which I assume you can compare
> > > with a text data type, which is what host() returns.
> >
> > Are you on a pre-7.1 Postgres release?  host() is buggy before 7.1 ---
> > it includes a trailing null in its output, which it shouldn't oughta
> > have done.  You can't see the null from outside the system, but it
> > manages to mess up text comparisons anyway.
> >
> > BTW, you should consider using inet or cidr datatype for that column
> > rather than varchar...
> >
> > regards, tom lane
> >
> > ---(end of broadcast)---
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> >


> --
> Subhramanya Shiva, Programmer
> Archean InfoTech pvt.Ltd.
> Hyderabad, India
> http://www.archeanit.com



> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Re: Select between two connections

[Per-Olof Pettersson]

Hi

I think one way of accomplishing this is to use ODBC and thus an 
ODBC-client for the selects.
There does not seem to be any support for it in Postgresql. (If not in 
later versions).

Though I am not very experienced with it so someone more experienced 
should perhaps answer the question.

Hopes this helps a little though.

Best regards
Peo

>> Original Message <<

On 2001-05-15, 22:15:38, [EMAIL PROTECTED] (cbell) wrote regarding 
Select between two connections:


> Hello everyone, I would like to perform a Select statement that accesses

> tables and finds matching fields in two separate databases.

>  For example, I have two connections to two separate database objects on
> the same
> Postgres server.  I would like to retrieve information from a table in
> one database based on some information from tables in a different
> database.

> Has anyone done this before, can it be done?

> Thanks in advance,

> Chris.




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

> http://www.postgresql.org/users-lounge/docs/faq.html

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] Re: How NULL is interpreted in Pgsql

[Per-Olof Pettersson]

Hi

In the condition "where id != 11" you state that the value should differ 
from null.
For a value to be different than 11 it has to be a value in the first 
place.

So in reality you state two things:
1. id must be a value
2. id must differ from 11

It is nothing specific with Postgresql but common to most(all) relational 
databases using SQL.

To get the desired result you can add "OR id IS NULL".

Result:
select * from junk where id != 11 OR id IS NULL;


Best regards
Per-Olof Pettersson

>> Original Message <<

On 2001-05-15, 10:16:22, [EMAIL PROTECTED] (Guru Prasad) wrote regarding 
How NULL is interpreted in Pgsql:


> Dear Friends,

> I created a table with no 'unique constraints' which is depicted as
> follows.

> create table junk (id int, name varchar);

> Then, i inserted some records into the table. My table contents is listed
> below.

>  id  | name
> -+--
>  1001.00 | bolt
>  1002.00 | nut
>  1003.00 |
>11.00 | screw
>  | cutter
>  | cutting table

> Then, i gave a select query for the above table. The select query is

> 'select * from junk where id != 11';

> To my surprise, it displayed all the records except those which had
> 'NULL' in the 'id' field.

> How is that possible ? (I mean it should displayed all the records except
> the matching condition).

> But, if i set PRIMARY KEY constraint to 'junk' table then it displays all
> the records (I can't say NULL here, but i can say '0').

> How NULL is processed in Postgres.

> Could any one has some clue.

> Regards,
> guru.

> bk SYSTEMS (P) Ltd.
> P . N . G U R U P R A 
S A D
> 
---




> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [SQL] Auto incrementing an integer

[Thomas Swan]

Sylte wrote:

>How do I construct a datatype that autoincrement in postgreSQL?
>
Use the SERIAL data type instead of an INT4.Serial is an integer 
(INT4) that's default value is base on a sequence.

for example...

create table foo (
   my_id serial not null,
   my_data text
);




---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl