[SQL] lower/upper functions and strings in searches

[Gregory S. Williamson]

I am obviuously doing some newbie trick, and I ordinarily would spend time browing the 
archives, but the archives.postgresql.org site seems to be absurdly slow.

This is 7.3.3 on a linux box.

I have a bunch of data with state, city, county and country names. When our 
application does a search for an exact match:

select * from gx_geotowns where l_state = 'NM';
  I get back a lot of rows of cities in New Mexico, as expected.

If I try:

select * from gx_geotowns where upper(l_state) = upper('nm');

I get back:
-+---+--++--+--
(0 rows)

I've used other databases in which a similar statement worked as exepected:
select * from clients
 where upper(client_name) = upper("Some client or otheR");

And it finds the row(s) in question ...

I just know I'm overlooking some real obvious thing but for some reason this eludes 
me. I could see if the search was very slow (the function returns type "text" and the 
indexed columns are of type CHAR().

If someone could offer help I would appreciate it,

Thanks,

Greg Williamson
DBA GlobeXplorer LLC

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

Re: [SQL] production parameters

[Gregory S. Williamson]

You might find some things of use in these:




Greg W.
DBA GLobeXplorer LLC

-Original Message-
From:   chester c young [mailto:[EMAIL PROTECTED]
Sent:   Sat 9/13/2003 11:10 AM
To: sql pgsql
Cc: 
Subject:[SQL] production parameters

What is the best source doc for postgresql setup in a production
environment?  I have read
- giving it a big chuck of shmem
- os not marking data files as accessed or modified
but cannot find the doc.

thanks,
Chester

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

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

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




---(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] COPY command

[Gregory S. Williamson]

This sounds as if the last character in each line might be a delimiter (a ",") which 
is standard for data unloaded from some sources; if this is the case try removing it 
and your data should load. Or you may have a comma in a character field and that is 
throwing off the count for the line in question ?

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent:   Tue 1/6/2004 10:19 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:[SQL] COPY command

Hi,


  i try to load a file into temporary table but it gives me an error . do 
u have any idea/solutions ??  the command that i've running as below :


dwnc=# copy biosadm.custdo_temp
dwnc-# from '/home/bios/customer_data/CustomerDO_new.CSV'
dwnc-# WITH DELIMITER ',' ;

ERROR:  copy: line 141, Extra data after last expected column

FYI, my file was in comma delimiter type (csv) . My table structure as 
following :

dwnc-# \dbiosadm.custdo_temp
 Table "biosadm.custdo_temp"
  Column  | Type  | Modifiers
--+---+---
 dono | character varying(13) |
 dodate   | date  |
 custname | character varying(70) |
 custlo   | character varying(40) |
 attnto   | character varying(80) |


Please guide me . thanks



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

   http://archives.postgresql.org

Re: [SQL] Database diagram

[Gregory S. Williamson]

ERWin is likely to be expensive, judging by the cost for an Informix license. It is 
also a serious tool; has its quirks but can be used to reverse engineer as well as 
define a database with detailed support for triggers and the like.

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   Denis [mailto:[EMAIL PROTECTED]
Sent:   Mon 1/19/2004 11:01 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:Re: [SQL] Database diagram


Hi Ganesan,

You can try ERWin (by CA).

It creates Logical data model of the database. (though.. i haven't used
it..)

HTH

Thanx

Denis

- Original Message -
From: "Ganesan Kanavathy" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, January 20, 2004 12:08 PM
Subject: [SQL] Database diagram


> I have a postgres database with many tables.
>
> How do I create database diagram? Are there any free tools available to
> create database diagram from pgsql database?
>
> Regards,
> Ganesan
>
>
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend



---(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




---(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

[SQL] function definition documentation

[Gregory S. Williamson]

I am having an impossible time porting some simple Stored Procedures from Informix to 
postgres. The documentation is almost self defeating.

Are there any better descriptions of how to define functions that return several 
tuples to a wide variety of calling programs (e.g. I can't count on my users running 
the psql command line tool, but rather coldfusion, jsp etc.) ?

Thanks !

Greg Williamson
DBA
GlobeXplorer LLC

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

Re: [SQL] function definition documentation

[Gregory S. Williamson]

Thanks muchly for the excellent tip. Lots of useful references there.

I seem to have battled through this thicket -- onwards !

Greg

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 23, 2004 12:09 AM
To: Gregory S. Williamson; [EMAIL PROTECTED]
Subject: Re: [SQL] function definition documentation


On Tuesday 23 March 2004 02:16, Gregory S. Williamson wrote:
> I am having an impossible time porting some simple Stored Procedures from
> Informix to postgres. The documentation is almost self defeating.
>
> Are there any better descriptions of how to define functions that return
> several tuples to a wide variety of calling programs (e.g. I can't count on
> my users running the psql command line tool, but rather coldfusion, jsp
> etc.) ?

You might want to look at the "Set Returning Functions" at the techdocs site:
http://techdocs.postgresql.org

Most of your problems are down to quoting issues and misleading error 
statements.

-- 
  Richard Huxton
  Archonet Ltd

---(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] begin update ... syntax error

[Gregory S. Williamson]

try a semicolon after the begin ?

begin;

commit;

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Kemin Zhou [mailto:[EMAIL PROTECTED]
Sent:   Sat 4/10/2004 2:32 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:[SQL] begin update ... syntax error
I was trying to speed up a simple update query

fri=# begin
fri-# update tabA set nobegin=tmp.nobegin, noend=tmp.noend
fri-# from tmp
fri-# where tabA.acc=tmp.acc;
ERROR:  syntax error at or near "update" at character 7

The same query can be run with no problem if not starting with BEGIN.

Does mean that BEGIN cannot preceed UPDATE?

Or I am making some obvious miskate?

Kemin




**
Proprietary or confidential information belonging to Ferring Holding SA or to one of 
its affiliated companies may be contained in the message. If you are not the addressee 
indicated in this message (or responsible for the delivery of the message to such 
person), please do not copy or deliver this message to anyone. In such case, please 
destroy this message and notify the sender by reply e-mail. Please advise the sender 
immediately if you or your employer do not consent to e-mail for messages of this 
kind. Opinions, conclusions and other information in this message represent the 
opinion of the sender and do not necessarily represent or reflect the views and 
opinions of Ferring.
**


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

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




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

Re: [SQL] feature request ?

[Gregory S. Williamson]

Programming languages, perhaps, but clearly not uncommon in SQL ... Informix certainly 
allows a column to be of type boolean but with a value of NULL for given rows (unless 
precluded by a not-null constraint). Should we question integers, which can be 
positive, negative, or -- gasp ! -- NULL ?

I don't see what your point is. That SQL is wrong ? Or that SQL is not "C" ? Or that 
SQL is not a "programming language" ?

"?Que purposa sirve tanto comedia ?  Quien inventan tab miseria ?"

Greg Williamson
DBA
GLobeXplorer LLC

-Original Message-
From:   sad [mailto:[EMAIL PROTECTED]
Sent:   Wed 6/23/2004 10:01 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:Re: [SQL] feature request ?
On Wednesday 23 June 2004 21:12, you wrote:
> Sad,
>
> > since BOOL expression has three possible values: TRUE,FALSE,NULL
> > plpgsql IF control structure should have three alternate blocks:
> > THEN,ELSE,NULL
> >
> > shouldn't it ?
>
> No, why?
>
> How would you construct a tri-valued IF/THEN? Doesn't seem too likely
> to me, as well as being different from every other programming language in
> existance ...

Three valued BOOLEAN is already different "from every other programming
language in existance"



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

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




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

Re: [SQL] Queries across multiple database

[Gregory S. Williamson]

Although postgres itself doesn't seem to have a thing such as Informix' "synonym" 
which allows more-or-less transparent access to a table in a remote database, there is 
a utility in contrib directory of the postgres source called dblink, which allows 
access to different instance of postgres by opening connections. I have only played 
with it for testing and have no idea of its limitations, but it might serve your needs.

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From: Garth Thompson [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 30, 2004 3:54 PM
To: [EMAIL PROTECTED]
Subject: [SQL] Queries across multiple database



I am in the process of migrating the databases for an application from mysql
to postgres.  The application is uses several different databases.  From
what I have read, postgres still does not support queries across multiple
databases.  If this is still true, does anyone have any tricks to get around
this?  I'm sure this is a common problem for people migrating from mysql.
The obvious answer is to just migrate everything into one larger postgres
database, but I would like to avoid this if possible - many a line of could
would need to be touched if this was done.

Thanks,
Garth


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

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

   http://archives.postgresql.org

Re: [SQL] Sorry too many conecctions

[Gregory S. Williamson]

In the root directory in which postgres stores the data is a file:

postgresql.conf

Edit the file with a text editor (vi / ed / etc.) and change :

max_connections = ###
to
max_connections = 1000
 (or more since other applications or a DBA may need to connect as well)

and also change 

shared_buffers = ###
 to be at least (max_connections * 2), so change this to at least 2000 for 1000 
simulataneous connections. More connections use more shared memory so you want to have 
some limits to the total number.

Save the file changes.

Stop and restart the postgres instance (reload doesn't work for these settings).

Hope this helps,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Javier Tesis Tolosa Trabajo [mailto:[EMAIL PROTECTED]
Sent:   Wed 7/21/2004 8:08 AM
To: [EMAIL PROTECTED]
Cc: 
Subject:[SQL] Sorry too many conecctions
hi everybody
I speak english a little,Sorry.

I have A problem whith de connections from a Aplicatio Java, guive  me a 
Error "Sorry too many clients",I think what is becuase I don't close 
connecction to the DB postgresql, I do aprox. 1000 conecction into statement 
"while" What can Do?
thakns

_
Charla con tus amigos en línea mediante MSN Messenger: 
http://messenger.latam.msn.com/


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




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Copy command freezes but INSERT works fine with trigger oninsert.

[Gregory S. Williamson]

The copy command will run as a single transaction -- all succeed or all fail, I think, 
so if you interrupt it no rows will be loaded. The inserts -- unless wrapped into a 
transaction with a "begin; ... commit;" block will each be a single transaction.

Check you postgres log file for the time when they copy was runnint --- you should see 
it writing something like this as it does its logging:
2004-08-18 09:56:26 LOG:  removing transaction log file "00220089"
2004-08-18 09:56:26 LOG:  removing transaction log file "00220087"
...

In and of itself I don't see why the trigger would stop copy (although performance 
might be an issue), but I am rather unacquainted with triggers in postgres, so perhaps 
someone more knowledgable could comment.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Artimenko Igor [mailto:[EMAIL PROTECTED]
Sent:   Fri 8/27/2004 1:16 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:[SQL] Copy command freezes but INSERT works fine with trigger 
oninsert. 
I tried to add up records 2 different ways: 
1.  using command [copy messageinfo (user_id,  receivedtime, filename,  
sendersstring, subject,
hasattachments,  groupmsg,  msgsize,  version ) FROM '/meridtemp/messageinfo.dat';] 
from psql
2.  using simple INSERT INTO messageinfo ( user_id ) VALUES( 1728::int8 );

In 2nd case it worked but not in 1st one. Why? 
Later I did an experiment & repeated it few times. After copy command is running for a 
while  from
within psql and trying to insert 60,000 records I interrupted it by pressing CTRL+C 
few times.
Each time I had different line within addmsgcountSync where it stopped. It tells me 
that “copy”
command does not freeze on one particular statement but it did not insert a single 
record.

For this table messageinfo I have trigger:
CREATE TRIGGER msgInjector AFTER INSERT ON messageinfo FOR EACH ROW
EXECUTE PROCEDURE addmsgcountSync();
 
CREATE OR REPLACE FUNCTION addmsgcountSync() RETURNS TRIGGER AS 
'
DECLARE 
 currentTime injector.lastreceivedtime%TYPE;
 vlastreceivedtime injector.lastreceivedtime%TYPE;
 userIdRec RECORD;
 vID messageinfo.user_id%TYPE;
 injectorCursor CURSOR ( tt int8 ) FOR SELECT lastreceivedtime FROM injector WHERE 
injector.id =
tt::int8 ;

BEGIN
vID = NEW.user_id;
IF ( vID IS NOT NULL ) THEN
-- Find out lastrecievedtime we need cursor
OPEN injectorCursor( vID );
FETCH injectorCursor INTO userIdRec;
vlastreceivedtime = userIdRec.lastreceivedtime;
CLOSE injectorCursor;
currentTime = CURRENT_TIMESTAMP;
IF vlastreceivedtime < currentTime THEN
vlastreceivedtime = currentTime;
END IF;
-- To make sure time of last message is not newer than 
lastreceivedtime time
IF vlastreceivedtime < NEW.receivedtime THEN
vlastreceivedtime = NEW.receivedtime;
END IF;
-- Stopes copy command but not insert one ?
UPDATE injector SET addmsgcount = addmsgcount + 1, lastreceivedtime = 
vlastreceivedtime WHERE
injector.id = vID::int8;
END IF;
RETURN NULL;
END;
'
  LANGUAGE 'plpgsql';



=
Thanks a lot
Igor Artimenko
I specialize in 
Java, J2EE, Unix, Linux, HP, AIX, Solaris, Progress, Oracle, DB2, Postgres, Data 
Modeling




__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

---(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 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] explain analyze results are different for each iteration

[Gregory S. Williamson]

Are you forcing stuff out of cache (both postgres and OS) ? I've found that this can 
make a huge difference with some queries ... not sure that this would make it have 
different plans, though.

My ill-informied $0.02 worth ... fwiw

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Robert Davis [mailto:[EMAIL PROTECTED]
Sent:   Tue 9/14/2004 6:21 PM
To: [EMAIL PROTECTED]
Cc: 
Subject:[SQL] explain analyze results are different for each iteration
I'm trying to benchmark some complex sql queries.  One query, in 
particular, is causing problems -- its cost values can vary from 228 
to 907, its Total Runtimes from 60 ms to 5176 ms.  The query plans 
show that the optimizer is choosing different plans for different 
iterations of the same query.  Does anyone have any idea what's 
going on here or maybe how I can force Postgres to prefer the faster 
plan?

The query plans are very long (11 tables joined) but I can include 
them if it might help.  Thanks for any advice,

Roberto

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

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




---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Comparing two (largish) tables on different servers

[Gregory S. Williamson]

This is probably a silly question.

Our runtime deployment of database servers (7.4) involves some 
redundant/duplicate databases. In order to compare tables (about 5 gigs each) 
on different servers I unload the things (takes a while etc.), sort them with a 
UNIX sort and then do a cksum on them.

Is there any way to do this from inside postgres that anyone knows of ? I 
looked through the manual and the contrib stuff and didn't see much ...  

Thanks,

Greg Williamson
DBA
GlobeXplorer LLC
  

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

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

Re: [SQL] NULLS and string concatenation

[Gregory S. Williamson]

Someone on this list provided me with a rather elegant solution to this a few 
weeks ago:

CREATE OR REPLACE FUNCTION text_concat_nulls_with_an_embedded_space(text, text) 
RETURNS text AS 'SELECT CASE WHEN $1 IS NULL THEN $2 WHEN $2 IS NULL THEN $1 
ELSE $1 || '' '' || $2 END' LANGUAGE sql;

CREATE OPERATOR ||~ (PROCEDURE = text_concat_nulls_with_an_embedded_space, 
LEFTARG = text, RIGHTARG = text);

And I call it as:
SELECT (trim(s_directio) ||~ trim(s_house) ||~ trim(s_post_dir) ||~ 
trim(s_street) ||~ trim(s_suffix)) as street ... (yadda yadda)

Deals quite neatly with the NULLs in some of the columns.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent:   Fri 11/19/2004 9:53 AM
To: Don Drake; [EMAIL PROTECTED]
Cc: 
Subject:Re: [SQL] NULLS and string concatenation
On Fri, Nov 19, 2004 at 11:45:43AM -0600, Bruno Wolff III wrote:
> On Fri, Nov 19, 2004 at 11:12:38 -0600, Don Drake <[EMAIL PROTECTED]> wrote:
> > 
> > I was able to work around the problem by using COALESCE (and casting
> > variables since it wants the same data types passed to it).
> 
> This is what you should do.

If you don't mind using a non-standard feature, another possibility
would be to create an operator similar to || that COALESCEs NULLs
into empty strings.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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




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

   http://archives.postgresql.org

Re: [SQL] commit in plpgsql function?

[Gregory S. Williamson]

I don't think you can do transactions within a procedure in postgres, at least 
in version 7.x, which I am most familiar with. Page 579 on the postgres 7.4 
manual says:
"Functions and trigger procedures are always executed within a transaction 
established by an outer query --- they cannot start or commit transactions, 
since PostgreSQL does not have nested transactions."

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Dennis Sacks [mailto:[EMAIL PROTECTED]
Sent:   Tue 12/21/2004 9:34 AM
To: [EMAIL PROTECTED]
Cc: 
Subject:[SQL] commit in plpgsql function?
Hi,

I am converting oracle stored procedures to plpgsql. There are several 
of the oracle procedures where a parameter vCommit is passed into the 
procedure and:

if vCommit = 1
   then
   commit;
   do_something_commit(vdate);
   else
   do_something(vdate);
   end if;

does this make sense in plpgsql? Does it make sense to do a commit in 
plpgsql?

Thanks,

Dennis

---(end of broadcast)---
TIP 8: explain analyze is your friend




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

Re: [SQL] commit inside plpgsql function

[Gregory S. Williamson]

Already been answered (by me) -- you can't do this in postgres 7.x or earlier 
-- procedures may not have begins/commits or rollbacks. Version 8.0 *may* be 
different -- I don't have it installed yet; it does allow for some nesting of 
transactions but I don't know if this is allowed. The 8.0 documentation on user 
defined procedures would probably say.

Greg Williamson
DBA
GlobeXplorer LLC



-Original Message-
From:   Dennis Sacks [mailto:[EMAIL PROTECTED]
Sent:   Tue 12/21/2004 9:21 AM
To: pgsql-sql@postgresql.org
Cc: 
Subject:[SQL] commit inside plpgsql function
Hi,

I am converting oracle stored procedures to plpgsql. There are several 
of the oracle procedures where a parameter vCommit is passed into the 
procedure and:

if vCommit = 1
then
commit;
do_something_commit(vdate);
else
do_something(vdate);
end if;

does this make sense in plpgsql? Does it make sense to do a commit in 
plpgsql?

Thanks,

Dennis

---(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

Re: [SQL] how can I query for unset timestamps

[Gregory S. Williamson]

Bret --
A test for NULL uses the IS statement, e.g.:

SELECT * FROM tablefoo WHERE last_timestamp IS NULL;

HTH,

Greg Williamson
DBA
GlobeXPlorer LLC

-Original Message-
From:   Bret Hughes [mailto:[EMAIL PROTECTED]
Sent:   Tue 2/22/2005 1:09 PM
To: postgresql sql list
Cc: 
Subject:[SQL] how can I query for unset timestamps
I have a table that I recently altered by adding several timestamp
columns.  I would like to query the table for those rows for which the
timestamp has not been set but am unable to find a way to do so.  any
tips ?  in psql the column appears empty and char_length returns
nothing.

Some of what I have tried 

"" -> not valid

char_length(last_timestamp) > 0  -> returns the rows with the time
stamps

where NOT char_length(last_timestamp) > 0  -> returns 0 rows

where NOT (char_length(last_timestamp) > 0 ) -> returns 0 rows

where last_timestamp = NULL;  -> 0 rows

this is kicking my butt, and starting to piss me off so if any one has
tips I would appreciate it.

Bret




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

!DSPAM:421b9fba271461201672223!





---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] dblink versus schemas. What to use in this case?

[Gregory S. Williamson]

Robert,

A couple of possible issues -- 

Running two different databases (on the same server) implies more use of system 
resources, but may be slightly more robust (i.e. one could go down but the 
other would still work). dblink is certainly slower than refering to a table in 
a schema, but it seems to work reasonably well, as least in talking between two 
databases on the same server (I've not really tested it between servers but it 
would obviously be slower depending on one's network).

If you want to enforce referential integrity then a schema is the way to go; 
schemas have permissions so it should be possible to lock out unwanted users 
almost as effectively as if there were two databases.

I have a database that uses schemas fairly heavily (in a postgres 7.4 
installation) and I have had to edit my restore scripts -- partly because the 
script is confused by all of the ALTER statements I needed, and partly to get 
schema restored in the corect order. Version 8 may be better but might still 
need some manual editing of the restore script. I used schemas to simplify 
scripts and maitain references.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   KÖPFERL Robert [mailto:[EMAIL PROTECTED]
Sent:   Wed 2/23/2005 6:33 AM
To: pgsql-sql@postgresql.org
Cc: 
Subject:[SQL] dblink versus schemas. What to use in this case?
Hi all,

I have got two database schemas. They're rather independend. Thus they are
in two databases. However there is one function that needs access to the
other database.

As I found out, I have two choices:
*Using schemas and put the schemas tighter together (via interdependencies).
Dumping distinct schemas is possible, however quistionable if a restore will
work with the dependencies.
*Using dblink. Dblink gives me a loose binding of the two databases. Some of
us care about the 'contrib' status of dblink. Speed (connect, query,
disconnect may sloww down) and it's deadlock resolv capabilities. However
the deadlock thingy is just a question of interest.


What should I do?
to make one fcn of one DB access another DB's tables/fcns



Thanks

---(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

!DSPAM:421c94cc83679760939685!





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

Re: [SQL] delphi access question?

[Gregory S. Williamson]

Something is not translating CHAR values correctly -- all the fields that you 
"see" are not char values.

Have no idea off hand *why* this would be ... character encoding differences 
maybe ?

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   Joel Fradkin [mailto:[EMAIL PROTECTED]
Sent:   Wed 3/30/2005 12:15 PM
To: pgsql-sql@postgresql.org
Cc: [EMAIL PROTECTED]
Subject:[SQL] delphi access question?
One of my applications is in Delphi 5.
I just went to change it over to Postgres (from MSSQL).
I am using odbc and something a bit odd is happening.
I can run a sql statement ok, even in sql builder I see all the fields.
But the returned result set appears to be missing some of the fields.
So my table is
CREATE TABLE tbltranslations
(
  transnumber int4 NOT NULL,
  clientnum char(4) NOT NULL,
  lastran timestamp,
  lastupdated timestamp,
  firstrowhasheading char(1),
  fixed_delimited char(1),
  tblname varchar(50),
  delimeter char(1),
  textqualifier char(1),
  active bool,
  direction char(1),
  client_filename varchar(250),
  ftp_account int4,
  fixedlenghthascomma char(1),
  ftp_path varchar(250),
  ftp_filename varchar(50),
  fieldname_forid_on_insert varchar(50)
)
but only fields
  transnumber 
  lastran
  lastupdated
  active
  and ftp_account
show up as fields I can add to the result.
Any ideas?


Joel Fradkin
 
 



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

   http://archives.postgresql.org

!DSPAM:424b0a12126562811677690!





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

Re: [SQL] using pg_tables and tablename in queries

[Gregory S. Williamson]

Yasir --

You wrote:

>If I generate a temporary table instead of returning the results how
>long will that table exist for?  Excuse the OOP terminology but would
>it be correct to create a 'Singleton' to access the temporary table,
>where if it exists and is less than 30 minutes old use that one,
>otherwise drop the table and recreate it?

In 8.0:
"Temporary tables are automatically dropped at the end of a session, or 
optionally at the end of the current transaction (see ON COMMIT below).
Existing permanent tables with the same name are not visible to the current 
session while the temporary table exists, unless they are referenced with 
schema-qualified names. Any indexes created on a temporary table are 
automatically temporary as well."

So a temp table would persist as long as the originating session, but only that 
session could see it.

Not sure if this helps or not ...

Greg Williamson
DBA
GlobeXplorer LLC



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

   http://www.postgresql.org/docs/faq

Re: [SQL] Help with simple SQL query?

[Gregory S. Williamson]

Joost --

You are correct in stating that the problem is that the subquery returns more 
than 1 row -- try using the NOT IN syntax ... it is not likely to be very 
efficient but at least avoids the syntax error:

select order_id from order, orderline
where order_id = parent_order_id
and order_price NOT IN (select sum(orderline_price) from orderline group by
parent_order_id)

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   [EMAIL PROTECTED] on behalf of Joost Kraaijeveld
Sent:   Wed 10/5/2005 4:35 AM
To: Pgsql-sql@postgresql.org
Cc: 
Subject:[SQL] Help with simple SQL query?
Hi,

I have 2 tables with the following columns:

order: order_id, order_price
orderline: parent_order_id, orderline_price 

I want all orders order where _price <> sum(orderline_price).

What is wrong with the following query:

select order_id from order, orderline
where order_id = parent_order_id
and order_price <> (select sum(orderline_price) from orderline group by
parent_order_id)


It reports "ERROR:  more than one row returned by a subquery used as an
expression" which seems right (the select sum() returns multiple rows?),
but I cannot get query right. Can someone help?

TIA

-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: [EMAIL PROTECTED]
web: www.askesis.nl 



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

!DSPAM:4343bb5c106941059188129!





---(end of broadcast)---
TIP 1: 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] Como ejecutar una funcion insert en plpgsql....

[Gregory S. Williamson]

Fernando --

I am not sure about the first question -- my spansih is rusry.

postgres does force all column, table and schema names (I think) to lower case 
(there have been recent long discussions about this on this list IIRC). If you 
want to preserve case put the column name in double quotes:
"ItemID" =  ItemID
ItemID   =  itemid

This applies both to the creation of a table and when referring to that table's 
columns.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   [EMAIL PROTECTED] on behalf of Fernando Garcia
Sent:   Wed 11/9/2005 6:22 AM
To: pgsql-sql@postgresql.org
Cc: 
Subject:[SQL] Como ejecutar una funcion insert en plpgsql
Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql
para ver si inserta correctamente en la tabla asociada, pero con execute me
da un erroralguien me puede decir como lo hago
 yo trabajo con el editor postgresql manager pro..
 Gracias...
 Ahh otra cosa: en otra consulta que realice pero que es un select al
principio me daba un error porque no encontraba la columna "ItemID" la cual
porsupuesto existia, entonces a la columna le quite las mayusculas de su
nombre quedando "itemid" y ya no me salio el error.en postgres los
nombres de tablas y columnas deben ser siempre con minuscula


!DSPAM:437202c363872044317020!




---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Como ejecutar una funcion insert en plpgsql....

[Gregory S. Williamson]

Fernando --

I have forwarded your message to the postgres SQL list where it started -- it 
is good practice to "cc" the list in general so that others can contribute / 
learn.

If you could provide the version of postgres you are using that might help, as 
there differences between 7.x and 8.x that might matter.

I am at the very end of my day and too tired to be coherent -- perhaps someone  
else can provide an elegant snippet of code ?

If there's no response in the next few hours I'll give it try.

ZzzzZZzzz

G


-Original Message-
From:   Fernando Garcia [mailto:[EMAIL PROTECTED]
Sent:   Thu 11/10/2005 5:23 AM
To: Gregory S. Williamson
Cc: 
Subject:Re: [SQL] Como ejecutar una funcion insert en plpgsql
thanks very much, jeje, my english its very rusry...
 i try explain to you what i can to do in the first question.remember I
try!
 Well, I have one function what insert one record in one tabe (EJ:
adduser(name,email,password))
 How can I execute this function to prove this insertion..???
 Welll, you understand somethin in muy very bad english!!!
 OK Thanks anywere..


 On 11/9/05, Gregory S. Williamson <[EMAIL PROTECTED]> wrote:
>
>
> Fernando --
>
> I am not sure about the first question -- my spansih is rusry.
>
> postgres does force all column, table and schema names (I think) to lower
> case (there have been recent long discussions about this on this list IIRC).
> If you want to preserve case put the column name in double quotes:
> "ItemID" = ItemID
> ItemID = itemid
>
> This applies both to the creation of a table and when referring to that
> table's columns.
>
> HTH,
>
> Greg Williamson
> DBA
> GlobeXplorer LLC
>
> -Original Message-
> From: [EMAIL PROTECTED] on behalf of Fernando Garcia
> Sent: Wed 11/9/2005 6:22 AM
> To: pgsql-sql@postgresql.org
> Cc:
> Subject: [SQL] Como ejecutar una funcion insert en plpgsql
> Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql
> para ver si inserta correctamente en la tabla asociada, pero con execute
> me
> da un erroralguien me puede decir como lo hago
> yo trabajo con el editor postgresql manager pro..
> Gracias...
> Ahh otra cosa: en otra consulta que realice pero que es un select al
> principio me daba un error porque no encontraba la columna "ItemID" la
> cual
> porsupuesto existia, entonces a la columna le quite las mayusculas de su
> nombre quedando "itemid" y ya no me salio el error.en postgres los
> nombres de tablas y columnas deben ser siempre con minuscula
>
>
> 
>
>
>
>


!DSPAM:43734556223684784314229!




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

Re: [SQL] Como ejecutar una funcion insert en plpgsql....

[Gregory S. Williamson]

Fernando --

It is best to always copy to the list -- more eyes means a better chance of 
help.

You can use native SQL to define a database (once connected to the "template1" 
database issue a "CREATE DATABASE foo;" command), or use one of the pgAdmin 
option, IIRC -- I don't have it installed on this computer and haven't used it 
recently. I don't remember if they have a GUI for creating tables -- I almost 
always use SQL directly.

In answer to you question about creating a function to do inserts, I have a 
very simple test case.

I create a table:
gex_runtime=# create table users (
   user_name text,  -- postgres' easiest string variable is text
   user_email text,
   user_pwd text);

This is obviously quite simple -- no index or serial ids, etc., which I almost 
always use, nor a timestamp or any other goodies. But enough to show the idea I 
hope.

Then I create a simple function in the plpgsql language. After you create the 
database you need to run a "createlang plpgsql" at a command prompt; pgAdmin 
surely has a way of creating language support in a database but I don't 
remember what that method is. 

Remove/comment out the NOTICE lines when you are done debugging (? depulgar ? 
IIRC from my days in Nicaragua long ago) as they will always appear in the logs 
etc.

This function doesn't do any sanity checking, but you could make it check for 
obvious errors like missing data; a NULL value passed to this function will 
cause undesirable results because of a " = " test that would need to be able to 
be an IS NULL test.

The only check this function makes is to see if we already have the name, email 
and password; if we do the function returns an integer value of 1; if it 
suceeds it will return a 0.

Remember that you can't put a transaction into a function (well, not in 
postgres 7.4, which is where this example comes from), although you can use 
savepoints I think in the latest releasesl check the manuals for guidance on 
transactions and functions for details.

BEGIN;
CREATE OR REPLACE FUNCTION insert_a_user(TEXT,TEXT,TEXT)
  RETURNS INTEGER AS '
DECLARE p_u_name ALIAS FOR $1;
   p_u_email ALIAS FOR $2;
   p_u_pwd ALIAS FOR $3;
sp_retval INTEGER;
BEGIN
RAISE NOTICE ''doing insert user for %'', p_u_name;
-- check here for bad data, etc. 
sp_retval = (SELECT 1 FROM users WHERE user_name = p_u_name AND 
user_email = p_u_email AND user_pwd = p_u_pwd);
RAISE NOTICE ''got back test val of %'', sp_retval;
IF (sp_retval >= 1) THEN
RETURN(sp_retval);
ELSE
   INSERT INTO users VALUES(p_u_name, p_u_email, p_u_pwd);
   RAISE NOTICE ''done with insert'';
   RETURN(0); 
END IF;
END;
' LANGUAGE 'plpgsql';
COMMIT;

And when I test it:

gex_runtime=# select * from insert_a_user('Joe Major', '[EMAIL PROTECTED]', 
'apassWord?');
NOTICE:  doing insert for Joe Major
NOTICE:  got back test val of 
NOTICE:  done with insert
 insert_a_user
---
 0
(1 row)

And the row is in the table:

gex_runtime=# select * from users;
 user_name |user_email |  user_pwd
---+---+
 Joe Major | [EMAIL PROTECTED] | apassWord?
(1 row)

Try it again -- should fail:

gex_runtime=# select * from insert_a_user('Joe Major', '[EMAIL PROTECTED]', 
'apassWord?');
NOTICE:  doing insert user for Joe Major
NOTICE:  got back test val of 1
 insert_a_user
---
 1
(1 row)

gex_runtime=# select * from users;
 user_name |user_email |  user_pwd
---+---+
 Joe Major | [EMAIL PROTECTED] | apassWord?
(1 row)

And sure enough -- just the original record.

Add another user:

gex_runtime=# select * from insert_a_user('Mary Contrary', '[EMAIL PROTECTED]', 
'only4me!');
NOTICE:  doing insert user for Mary Contrary
NOTICE:  got back test val of 
NOTICE:  done with insert
 insert_a_user
---
 0
(1 row)

gex_runtime=# select * from users;
   user_name   |user_email |  user_pwd
---+---+
 Joe Major | [EMAIL PROTECTED] | apassWord?
 Mary Contrary | [EMAIL PROTECTED]  | only4me!
(2 rows)

HTH,

Greg W.

ps be sure to send questions, etc. to the list as a whole so that more people 
have a chance to see the question and answer, and because any one individual 
might be gone for a while.

-Original Message-
From:   Fernando Garcia [mailto:[EMAIL PROTECTED]
Sent:   Thu 11/10/2005 6:23 AM
To: Gregory S. Williamson
Cc: 
Subject:Re: [SQL] Como ejecutar una funcion insert en plpgsql
ok man dont worry I use postgresql 8.0.3.. other thing, I have a
postgresql DataB

Re: [SQL] Sequential scan where Index scan expected (update)

[Gregory S. Williamson]

Perhaps it is clogged with dead tuples -- has it been vacuumed recently with 
enough FSM space ?

It seems unlikely but maybe try an explict cast for the thing_id call, e.g.
explain update xx_thing_event set thing_color='foo' where
thing_event_id=1::bigint;

It may also be that 5842 rows is enough that the planner decides it is faster 
to do a sequential scan that the busier index scan (read index, get data row, 
versus just reading all the necessary pages in one faster sequential scan).

If you set the sequential scan parameter in the config file and reload postgres 
does the same query get faster ? (not suggesting this for real runtime use but 
it can be useful to diagnose issues).

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of Bryce Nesbitt
Sent:   Thu 3/2/2006 11:28 PM
To: pgsql-sql@postgresql.org
Cc: 
Subject:[SQL] Sequential scan where Index scan expected (update)
I'm getting sequential scans (and poor performance), on scans using my
primary keys.  This is an older postgres.
Can anyone help figure out why?


demo=# \d xx_thing
 Table "public.xx_thing"
 Column  |Type | Modifiers
-+-+---
 thing_id  | bigint  | not null
thing_model   | character varying(128)  |
 thing_color   | character varying(128)  |
 thing_year| integer |
Indexes:
"xx_thing_pkey" primary key, btree (thing_id)


demo=# analyze verbose xx_thing_event;
INFO:  analyzing "public.xx_thing_event"
INFO:  "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated
total rows


demo=# explain update xx_thing_event set thing_color='foo' where
thing_event_id=1;
 QUERY PLAN
-
 Seq Scan on xx_thing_event  (cost=0.00..5842.48 rows=1 width=110)
   Filter: (thing_event_id = 1)
(2 rows)



demo=# select * from version();
 version
--
 PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
(1 row)


---(end of broadcast)---
TIP 1: 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

!DSPAM:4407f048253644359117518!





---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] Truncate and Foreign Key References question

[Gregory S. Williamson]

This is in postgres 8.1:
 PostgreSQL 8.1.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 
20041017 (Red Hat 3.4.2-6.fc3)


 I've got a table in one schema (work.client_collect_rates) which has an FK 
constraint with a table, content.collections_l (definitions shown below). 
There's about 500 entries currently in my collections_l table.

I need to wipe out the contents of the collections_l table nightly and refresh 
it from a remote master source. (Don't ask ... long & sordid history)

As the sequence below shows, I dropped the FK constraint successfully, but when 
I run TRUNCATE collections_l it says:

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "client_collect_rates" references "collections_l" via foreign 
key constraint "$2".
HINT:  Truncate table "client_collect_rates" at the same time.

This truncation of the second table is *not* an option, but since the 
constraint "$2" is clearly gone, I am wondering what in the name of sweet 
apples is going on ? Is this a bug ? Have a developed premature senility ?

Any clues for the clueless would be gratefully accepted!

TIA,

Greg Williamson
DBA
GlobeXplorer LLC


billing=# \d work.client_collect_rates
  Table "work.client_collect_rates"
Column |  Type   | Modifiers
---+-+---
 contract_id   | integer | not null
 collection_id | integer | not null
 rate  | numeric |
 break_1   | numeric |
 rate_1| numeric |
 break_2   | numeric |
 rate_2| numeric |
 break_3   | numeric |
 rate_3| numeric |
 break_4   | numeric |
 rate_4| numeric |
Indexes:
"clnt_colrate_ndx" UNIQUE, btree (contract_id, collection_id)
Foreign-key constraints:
"$1" FOREIGN KEY (contract_id) REFERENCES client_contracts(contract_id)
"$2" FOREIGN KEY (collection_id) REFERENCES 
content.collections_l(collect_id)

billing=# \d content.collections_l
   Table "content.collections_l"
Column|  Type  | Modifiers
--++
 collect_id   | integer| not null
 owner| integer|
 collection_name  | character(50)  |
 begin_date   | date   |
 end_date | date   |
 pos_accuracy | integer|
 res_accuracy | integer|
 loc_code | character(30)  |
 color| integer| default 0
 category_id  | integer|
 is_mosaic| integer| not null default 0
 detail_metadata_view | character varying(255) |
 jdbc_url | character varying(255) |
 jdbc_driver  | character varying(255) |
Indexes:
"collections_l_pkey" PRIMARY KEY, btree (collect_id)
"collect_own_ndx" btree ("owner", collect_id)

billing=# alter table work.client_collect_rates drop constraint "$2";
ALTER TABLE

billing=# \d work.client_collect_rates
  Table "work.client_collect_rates"
Column |  Type   | Modifiers
---+-+---
 contract_id   | integer | not null
 collection_id | integer | not null
 rate  | numeric |
 break_1   | numeric |
 rate_1| numeric |
 break_2   | numeric |
 rate_2| numeric |
 break_3   | numeric |
 rate_3| numeric |
 break_4   | numeric |
 rate_4| numeric |
Indexes:
"clnt_colrate_ndx" UNIQUE, btree (contract_id, collection_id)
Foreign-key constraints:
"$1" FOREIGN KEY (contract_id) REFERENCES client_contracts(contract_id)

(Note that the "$2" FK is gone...)

billing=# truncate content.collections_l;
ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "client_collect_rates" references "collections_l" via foreign 
key constraint "$2".
HINT:  Truncate table "client_collect_rates" at the same time.


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL]Linked List

[Gregory S. Williamson]

Ben,

The pgsql function is compiled and wouldn't know how to handle a table name as 
a variable.

If you rewrite the SQL to use the 'EXECUTE' statement I think you could do 
this, something along the lines of (untested):

EXECUTE ''INSERT INTO '' || quote_ident(tmplist) || '' (SELECT * FROM links 
WHERE p=x)'';

HTH,

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of Ben K.
Sent:   Sun 4/30/2006 6:29 PM
To: Ray Madigan
Cc: Pgsql-Sql-Owner; Marc G. Fournier; pgsql-sql@postgresql.org
Subject:Re: [SQL]Linked List

> I have a table that I created that implements a linked list.  I am not an
> expert SQL developer and was wondering if there are known ways to traverse
> the linked lists.  The table contains many linked lists based upon the head
> of the list and I need to extract all of the nodes that make up a list.  The
> lists are simple with a item and a link to the history item so it goes kind
> of like:
>
> 1, 0
> 3, 1
> 7, 3
> 9, 7
> ...

I missed "The table contains many linked lists", so wanted to do another 
try. I guess there should be a better way, but what if you do this?

1)

Assuming your table has two columns (n int, p int), do

create table tmplist (n int, p int);

2)

drop function traverse(integer);
create or replace function traverse (integer)
returns integer as
$$
   declare
 x int;
   begin
 x := $1;
 while x is not null loop
   select n into x from linkedlist where p = x;
   insert into tmplist (select * from links where p=x);
-- or do any processing
 end loop;
 return 1 ;
   end;
$$
language plpgsql;

3)

select traverse(0);
select * from tmplist;
0 - 1 - 4 - 8 - 12 ...


delete from tmplist;
select traverse(2);
select * from tmplist;
2 - 3 - 5 - 6 - ...

(where 0 or 2 is the heads of the linked lists in the table, which you 
want to traverse)

I'd appreciate any insight if there's a better way but somehow it was not 
possible to return setof int from within while loop whereas it was 
possible from within a for loop. I didn't find a way to deliver the 
templist table name as argument. (Somehow there seemed to be a bug(?) 
there)



Regards,

Ben K.
Developer
http://benix.tamu.edu

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

   http://www.postgresql.org/docs/faq

!DSPAM:445564c2225761179214242!





---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Logging in Stored Procedure

[Gregory S. Williamson]

Well, perhaps the NOTICE functionality, e.g.

RAISE NOTICE ''report id of % for date %'', rpt_rec.report_id, 
rpt_rec.report_s_date;

This will show on the terminal if running from a console, and also writes to 
the log, IIRC. See the documentation for your version for details.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of Aaron Bono
Sent:   Wed 7/12/2006 4:28 PM
To: Postgres SQL language list
Cc: 
Subject:[SQL] Logging in Stored Procedure

OK, maybe a stupid simple question but, how do you add logging to a stored
procedure?  My procedure is running but the results I get are not complete
and I need to see better what is going wrong.  The tool I use has a debugger
but the debugger is barfing at a particular line that, when run without the
debugger, works with no errors so I know there is something wrong with the
debugger.

Thanks,
Aaron

-- 
==
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==


!DSPAM:44b58e5178651804284693!




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

   http://www.postgresql.org/docs/faq

Re: [SQL] Evaluation of if conditions

[Gregory S. Williamson]

Daniel,

AFAIK there is no short-circuiting of evaluations in postgres and I don't think 
you can depend on the order they appear in to determine the order in which they 
are checked, although more knowledgable people may have better info than I ... 
so the rewritten form is the way to go.

Greg Williamson
DBA
GlobeXplorer LLC

-Original Message-
From:   [EMAIL PROTECTED] on behalf of Daniel CAUNE
Sent:   Wed 9/6/2006 3:32 PM
To: pgsql-sql@postgresql.org
Cc: 
Subject:[SQL] Evaluation of if conditions

Hi,

How does the IF statement evaluate conditions?  Does it evaluate conditions
following their declaration order from left to right?  In case of
or-conditions, does the IF statement stop evaluating conditions whenever a
first or-condition is true?

The following snippet seems to be invalid, which let me think that PL/PGSQL
evaluates all the conditions:

  IF (TG_OP = 'INSERT') OR
 (OLD.bar = ...) THEN
statement
  END IF;

Should be rewritten as (for example):

  IF (TG_OP = 'INSERT') THEN
statement
  ELSIF (OLD.bar = ...) THEN
statement
  END IF;


Regards,

--
Daniel


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=44ff5167171391789821027&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:44ff5167171391789821027!
---






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

   http://www.postgresql.org/docs/faq

Re: [SQL] files or DataBase

[Gregory S. Williamson]

As Jim N. already pointed out, extra layers do have a cost in time, and for 
some things straight disk is way faster.

A few points to consider (you don't give enough details for me to tell if this 
is true for you or not) ...

a) ACID -- what databases do best, making sure that everything that was changed 
is changed uniformly, etc. This can be hard to manage in a file system, 
although its not impossible, depending on what you are doing.

b) how much traffic ?  With small datasets most requests will find an already 
cached file and won't have to go to disk; this is true of databases as well If, 
OTH, you have either lots of requests for data that is rarely seen, or you have 
more data than can be cached, disk becomes a bigger issue.

c) Indexing and searching can be faster in a database (especially for 
complicated data sets) -- that's the payoff for the overhead of extra disk 
space for the indexes. Simple schemas can be implemented easily enough on a 
file system but rapidly become unworkable if you have data that needs to be 
addressed different ways (e.g. by name, and by date, etc.)

d) Update frequency -- databases do a better jopb of making sure that everyone 
sees a consistant selection; update a database with 10,000 items in a 
transaction, and everyone outside the transaction sees the old 10,000 items as 
a consistant whole; the transaction commits and instantly everyone will see the 
new 10,000. With a file system it can be hard to do this (although, again, 
there are ways). The more volatile the data the more I'd lean toward a 
database, personally. But again, without details of what you want to do it is 
very hard to say much other than generalities.

HTH,

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of tomcask o_o
Sent:   Thu 9/28/2006 3:37 PM
To: pgsql-sql@postgresql.org
Cc: 
Subject:[SQL] files or DataBase

Hi

in advance, sorry for my english.

in a Web server  as is the best option?

to accede to db to show the contents  or to accede to static files modified
by scripts when the content of db has been modified.

That the habitual thing is to work directly with db and to give back to the
results mounting the page then.

But I believe that serious much more fast if they were directly the files
and on the other hand in the server of local way executed scripts whom the
changes of the files in a aux table verified, and published the files that
have undergone changes.

that to both consider the yield of the server in the diferents scenes.

Greetings and thanks.



---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=451c4dbb67491336712104&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:451c4dbb67491336712104!
---




---(end of broadcast)---
TIP 1: 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] [GENERAL] How to split a table?

[Gregory S. Williamson]

A crude approach would be to add a column to the original table; then update 
that based on the rand() call:

update foo set i_am_a_60 = 1 where (rand() <= 0.60);
create table foo_60 as select * from foo where i_am_a_60 = 1;
create table foo_40 as select * from foo where i_am_a_60 <> 1;

The CASE condition might be usable as well but I haven't puzzled it out ...

G


-Original Message-
From:   [EMAIL PROTECTED] on behalf of A. Kretschmer
Sent:   Tue 10/17/2006 2:12 AM
To: pgsql-sql@postgresql.org; [EMAIL PROTECTED]
Cc: 
Subject:Re: [SQL] [GENERAL] How to split a table?

am  Tue, dem 17.10.2006, um  1:53:35 -0700 mailte Gregory S. Williamson 
folgendes:
> Perhaps something like:
> 
> CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60);

Then we have 2 tables: one with 100% data and one with around 60% ;-)
If the table contains a primary key you can delete simple the copied
records from the origin table.

(delete from origin where pk in (select pk from copy);


> 
> -Original Message-

Please, no top-posting with fullquote below.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45349cb0275391789821027&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:45349cb0275391789821027!
---






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

Re: [SQL] [GENERAL] How to split a table?

[Gregory S. Williamson]

Perhaps something like:

CREATE TABLE foo2 AS SELECT * FROM foo WHERE (rand() <= 0.60);

?

HTH,

Greg Williamson
DBA
GlobeXplorer LLC


-Original Message-
From:   [EMAIL PROTECTED] on behalf of Andreas Kretschmer
Sent:   Tue 10/17/2006 1:34 AM
To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org; [EMAIL 
PROTECTED]
Cc: 
Subject:Re: [SQL] [GENERAL] How to split a table?

Felix Zhang <[EMAIL PROTECTED]> schrieb:

> Hi,
>  
> I want to split a table to 2 small tables. The 1st one contains 60% records
> which are randomly selected from the source table.
> How to do it?

Why do you want to do this?


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."(unknow)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


---
Click link below if it is SPAM [EMAIL PROTECTED]
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=4534936b271274356172766&[EMAIL
 PROTECTED]&retrain=spam&template=history&history_page=1"
!DSPAM:4534936b271274356172766!
---






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