[SQL] All function parameters become NULL if one is?

[Joel Burton]

I have a function that takes two text parameters. If both of these 
are non-null, everything works fine. However, if one of these two 
parameters is null and one is not, the function receives both as null.

CREATE FUNCTION F(TEXT,TEXT) RETURNS TEXT AS '
BEGIN
  RETURN $2;
END;
' LANGUAGE 'plpgsql';

SELECT F(null,'Hello');

returns null.

Is this intentional? Is there a way around this? 
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

Re: [SQL] Cache look up failure

[Joel Burton]

On 29 Nov 2000, at 17:56, Najm Hashmi wrote:

> Hi All, I am trying to insert  a tuple in the tuple, and i am getting
> the follwoing error message:
> 
> fliprdb=# insert into collection(name, artist_id) values('El Baile
> Aleman',2); ERROR:  fmgr_info: function 24011: cache lookup failed
> 
> Can someone help me out here. Thnaks in advance  for your help.
> Najm

Normally, the cache lookup error means you've done something like:

create table
create view on table
drop table
re-create table (perhaps slightly different)
select * from view

since the view calls the table by oid, not by name, it can't find the 
original table it uses.

for you, it sounds like

create function
create table-that-uses-function-somehow
drop function
re-create function
insert into table

Are there triggers on your table? Or rules? (Both of these might 
refer to functions that might have been changed.) Or, perhaps you 
have constraints on your table that call a function that you've re-
created.

If you have a backup of your database (ie pg_dumpall), you can 
grep this file for 24011. This should be the original oid of the 
function that is lost.

Good luck!

--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

[SQL] Rules with Conditions: Bug, or Misunderstanding

[Joel Burton]

Am I misunderstanding how to use rule w/conditionals, or is there a 
bug in this?

--

I love to use Pgsql comments, but find the 'comment on field...' 
language a bit of a pain for documenting a large database at the 
last minute. So, I wrote a query that pulls together all the fields in a 
database, w/descriptions (if any):

create view dev_col_comments as 
select a.oid as att_oid, 
relname, 
attname, 
description 
from pg_class c, 
pg_attribute a left outer join pg_description d on d.objoid=a.oid
where c.oid=a.attrelid
and (c.relkind='r' or c.relkind='v') and c.relname !~ '^pg_'
and attname not in ('xmax','xmin','cmax','cmin','ctid','oid','tableoid')
order by relname, attname;

[This uses pg7.1 syntax; you could rewrite for 7.0 w/o the 'v' for 
views, and using a union rather than outer join.]

This works great. Feeling clever, I wrote two rules, so I could 
update this and create comments. I need two rules, one if this is an 
existing description (becoming an update to pg_description), one if 
this not (becoming an insert to pg_description).

create rule dev_ins as on update to dev_col_comments where 
old.description isnull do instead insert into pg_description ( objoid, 
description) values (old.att_oid, new.description);

create rule dev_upd as on update to dev_col_comments where 
old.description notnull do instead update pg_description set 
description=new.description where objoid=old.att_oid;

This doesn't work: I get a "cannot update view w/o rule" error 
message, both for fields where description was null, and for fields 
where it wasn't null.

If I take out the "where old.description isnull" clause of dev_ins, it 
works fine--but, only, of course, if I am sure to only pick new 
descriptions. Or, if I take out the clause in dev_upd, it works too, 
with the opposite caveat.

Is this a bug? Am I misunderstanding something about the way that 
rule conditions should work? The docs are long but fuzzy on rules 
(they seem to suggest, for instance, that "create rule foo on 
update to table.column" will work, when this is not implemented yet, 
so perhaps the docs are ahead of the implementation?)

Any help would be great!

I do read the pgsql lists, but always appreciate a cc, so I don't miss 
any comments. TIA.

Thanks,

--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

Re: [SQL] Rules with Conditions: Bug, or Misunderstanding

[Joel Burton]

On 29 Nov 2000, at 19:42, Tom Lane wrote:

> "Joel Burton" <[EMAIL PROTECTED]> writes:
> > create rule dev_ins as on update to dev_col_comments where 
> > old.description isnull do instead insert into pg_description (
> > objoid, description) values (old.att_oid, new.description);
> 
> > create rule dev_upd as on update to dev_col_comments where 
> > old.description notnull do instead update pg_description set 
> > description=new.description where objoid=old.att_oid;
> 
> > This doesn't work: I get a "cannot update view w/o rule" error
> > message, both for fields where description was null, and for fields
> > where it wasn't null.
> 

> [... ] I think this will work:
> 
> create rule dev_upd as on update to dev_col_comments do instead
> (
>   insert into pg_description (objoid, description)
> select old.att_oid, new.description WHERE old.description isnull;
>   update pg_description set description=new.description
> where objoid = old.att_oid;
> )

Tom --

Thanks for the help. I had assumed (wrongly) that one could have 
conditional rules, and only if all the conditions fail, that it would go 
to the "cannot update view" end, and didn't realize that there 
*had* to be a single do instead.

In any event, though, the rule above crashes my backend, as do 
simpler versions I wrote that try your CREATE RULE DO INSTEAD ( 
INSERT; UPDATE; ) idea.

What information can I provide to the list to troubleshoot this?

Thanks!


--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

Re: [SQL] trying to pattern match to a value contained in a column

[Joel Burton]

> This makes perfect sense...unfortunately it isn't working...
> 
> I hope this isn't because I am using 6.3 (yes...I know it is very very
> old but this is currently where the data is!)
> 
> here is the query:
> 
> select * from av34s1 where chromat ~~ ('%' || sample || '%');
> 
> 
> ERROR:  parser: syntax error at or near "||"
> 
> I have also tried using LIKE
> 
> samething..
> 
> NOW..
> select * from av34s1 where chromat~sample;
> 
> ERROR:  There is no operator '~' for types 'bpchar' and 'bpchar'
> You will either have to retype this query using an explicit
> cast,
> or you will have to define the operator using CREATE OPERATOR
> 

The suggestion works fine w/recent versions; perhaps it's a '6.3 
thing'

Perhaps

SELECT * FROM tbl WHERE chromat::TEXT ~~ ('%' || sample || '%' 
)::TEXT;  

?

Also, upgrading isn't difficult in most cases; you can pg_dumpall and 
upgrade and restore your files. 7.0 has many nice features over the 
6.x series.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

Re: [SQL] How to...

[Joel Burton]

> Hi,
> 
> I'am wondering if it is possible to retrieve the last added
> record of a table?
> I don't think there is a default SQl-query to do so. Is there
> a PostgreSQL way?

What is it you want to do? If you want to find out what auto-
generated ID will be or was inserted, you can use a sequence 
function, like currval() on the sequence.

If you're just working in psql, you're shown the OID of insert as it 
happens. You could SELECT ... WHERE oid= to get the record 
back. Some interfaces (like DBD::Pg) provide functions to get this 
oid, so you could get the record that way.

If you don't mean the last insert period, but rather the last insert 
just to this table, you could add a TIMESTAMP column DEFAULT 
CURRENT_TIMESTAMP and just select the record w/the latest 
timestamp.

HTH,

--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

Re: [SQL] Selecting Most Recent Row

[Joel Burton]

> Ok here is the problem.
> Table: Widgets
> Fields:  Unique_Key, DateStamp, Batch_Number, Instructions.
> 
> Basic Select Statement:
> select Unique_Key from Widgets where Batch_Number>='inputedvalue'
> 
> Problem:
> Some Batch_Numbers might be duplicated over a period of time.  I want
> to select the most recent of these Batch Numbers.

Will DateStamp being the date of insertion? If so, is it that you want 
the record for the most recent (largest) datestamp for each 
batch_number?

something like

SELECT DISTINCT ON (batch_number) unique_key, datestamp, 
batch_number, instructions FROM widgets ORDER BY batch_number, 
datestamp desc;

(sort by batch then by date (last first) and show the first (aka 
'distinct') row, considering only the batch_number for distinctness)

HTH.

--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

Re: [SQL] substring ..

[Joel Burton]

> i want to do this to a datetime field..
> 
> select foo from table where substr(datefoo,1,11) = '2000-12-14';
> 
> it returns no results yet..
> 
> select substr(datefoo,1,11) does return some values that say
> 2000-12-14

Ummm... because '2000-12-14' is a ten-character, not eleven 
character long string. Try substr(datefoo,1,10) and it works for me 
(under 7.1devel).

However, this all seems sloppy. Why not extract the date, and 
compare it as a date?

--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)

Re: [SQL] connecting to postgres server from Access

[Joel Burton]

On Wed, 10 Jan 2001, Markus Wagner wrote:

> Hi,
> 
> I'm running a postgres db server on my linux machine. Someone else would like 
> to build his frontends with MS Access and use the postgres server as backend. 
> He installed the Pg/ODBC-Driver linked to at the pg web site. But when he 
> tries to link in some table he always gets an error message about wrong 
> authentication. I inserted his IP address in the pg_hba.conf file and created 
> a linux user account for him as well as a pg user account within "template1". 
> So he could log in and use "createdb" to create his own db. I tried to set 
> his password within pgaccess, but pgaccess claims about wrong input (""). 
> Leaving the password field empty doesn't help to get the connection.  Then I 
> set his password with "ALTER USER" in pgsql. But there still is no connection 
> from access to pg yet.

First of all, questions about interfacing to PostgreSQL are better
directed to pgsql-interfaces list. You might not get much help posting to
the sql list.


What kind of authorization are you using in pg_hba.conf? (can you post the
line from pg_hba.conf for him)

What error message does he get in Access?

Can he connect to the database from another Linux/Unix machine via
psql? (or, from his Windows machine using a PostgreSQL-compatible command
interface, like isql)?

There's a FAQ on PostgreSQL + Access at www.scw.org/pgaccess. It may help
as well.

Good luck,
-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington

Re: [SQL] connecting to postgres server from Access

[Joel Burton]

On Thu, 11 Jan 2001, Markus Wagner wrote:

> first of all, I looked at the mailing list list at th pg web site and I
> did not find a list named "pgsql-interfaces".

Hmmm. I see it at

  http://www.postgresql.org/users-lounge/index.html

Perhaps you looked elsewhere, or you saw an old mirror? (In any event, you
can get to the pgsql-interfaces archive at 

  http://www.postgresql.org/mhonarc/pgsql-interfaces/

which is great place to catch up on all the ODBC/Access questions of the
past.)

> In pg_hba.conf I added one line at the bottom with the IP of the client:
> 
> host all 134.93.64.47  255.255.255.255   trust
> 
> In Access, after installing the pg db as system DSN source, linking to
> it and selecting a table and attributes, I get:
> 
> "cannot create index for the selected field"
> 
> Then the linked table appears in the tables tab in Access. When trying
> to open it, I get ("Organisation" is the table to be linked):
> 
> "ODBC error:
>  error while executing the query
>  ERROR: Organisation: Permission denied (#1)"

Haven't seen this exact error before.

Are you sure that you found and turned off BOTH read-only options in the
ODBC driver configuration? (I'm just wondering if this "can't
create" error is a strange read-only twist.)

Also, you might try to take the error at face value: try to create an
index on the Org field using psql, and see if you run across any errors.

> I have searched for Windows pg tools but I didn'*t find one.

The nicest Windows tool is pgAdmin, a fantastic tool to administer almost
every aspect of your PG database under Windows. You can find it at
www.greatbridge.org. It can be a *bit* tricky to install, as it has a few
prerequisites, but I think you'd find it worth your time.

Slimmer but less featureful is Zeos Database Explorer, available at

  http://www.zeos.dn.ua/eng/index.html

This is basically a GUI version of psql.

Either of these tools will let you try your query out from the Windows
box, and will help us figure out whether this is a PG problem, an
ODBC problem, an Access problem, etc.

My advice?

Get a Windows querying tool *other* than Access to just test the basics of
the ODBC connection, and post your results from that.

Also, you should compare your ODBC setup to the basic setup described in
the Pg+Access FAQ at www.scw.org/pgaccess, and write to the interfaces-
list with your settings, as well as a small pg_dump of your database so
that people can try to recreate your problem.

Good luck!

JB

[SQL] [Tip] Using list-aggregates for faster batching

[Joel Burton]

I've written a HOWTO on how to create new aggregate functions to
create list (comma lists, HTML lists, etc.). It explains the purpose
of these, and gives an example of how to create one in pgplsql.

The HOWTO is written for the Zope site, but it's not really Zope-
or Python- specific.

http://www.zope.org/Members/pupq/pg_in_aggregates

Hoping someone finds it useful.


Cheers,
-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

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

[SQL] Re: Project Development

[Joel Burton]

On Thu, 5 Apr 2001, Kyle wrote:

> I've done some work on a solution to allow you to drop and rebuild your
> db from scratch relatively painlessly.  It enables you to munge portions
> of the database, drop columns, undermine RI, clobber views, etc. and
> then put the whole thing back together clean, fresh and perfect.
> Pg_dump is focused on doing a drop/restore exactly.  What is needed is a
> way to drop/modify/restore.  That's kind of what my tool enables you to
> do (without the fear that you're going to lose some subtle detail of the
> schema in the process).
> 
> It's a management tool that sits in between Postgres and your
> application.  You store chunks of SQL in a relational format that
> describe your schema exactly.  Then you can call those bits of SQL on
> demand to destroy or build parts of the schema selectively.  Because its
> relational, you can store dependencies so the program knows which parts
> of the DB to rebuild when you clobber something.
> 
> Right now, it is a handful of shell scripts and files, but we're using
> it with good success at ATI.  I have created an outline of how to do it
> with a tcl/tk GUI and actually storing the SQL chunks in a Postgres
> database.
> 
> I want to do some more development on it and then do a GPL release of
> it.  Problem is, I don't seem to find the time to get it all done.  Are
> you interested in collaborating on its development?  Or do you know
> someone who is?

Post it along w/any quick notes about the architecture (about 20 minutes
of your time covering the concepts would probably save me two hours). I'd
be happy to look over it to see if I can help.


-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

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

[SQL] Re: Can anyone explain how this works?

[Joel Burton]

On Mon, 2 Apr 2001 [EMAIL PROTECTED] wrote:

> Hi,
> 
> I recently posted this same question a few weeks back but lost the reply 
> someone kindly sent. The question again how exactly does this query work:
> 
> it will return all attributes and respective data types of a given table':
> 
> select attname, typname
> from pg_class c, pg_attribute a, pg_type t
> where relname = relation_name and
> attrelid = c.oid and
> atttypid = t.oid and
> attnum > 0
> order by attnum;

Understanding a few minutes' worth of the system tables hold will help a
lot here--you can find that in the Developer's Guide.

Eseentially, pg_class hold "classes" (ie tables, views, sequences,
etc.) pg_attribute holds "Attributes" (ie fields). This query joins
togetehr pg_class and pg_Attribute, showing you all attributes for a class
with name = 'relation name'. attnum > 0 is perhaps the only
odd part -- it has to do w/hiding certain system columns of tables that
ordinary users don't realize are there are don't care about.

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: \i command

[Joel Burton]

On Wed, 11 Apr 2001, Najm Hashmi wrote:

> Hi All,
> From pgsql, I try to insert data in table using the \i command. Nothing
> takes place and after this command pgsql is hung... i cant use it any more.
> The same command works with all other tables but this is only one I am having
> problem with.
> I have attached my file with message. Could someone help me out here what is
> the reason for this behaviour.

Works just fine for me (Pg7.1 RC3, Linux).

Can you do manual inserts into the table?
Can you insert just a few records using \i?
Can you vacuum the table?


-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

Re: [SQL] Re: \i command

[Joel Burton]

On Wed, 11 Apr 2001, Najm Hashmi wrote:

> Joel Burton wrote:
> 
> > On Wed, 11 Apr 2001, Najm Hashmi wrote:
> >
> > > From pgsql, I try to insert data in table using the \i command. Nothing
> > > takes place and after this command pgsql is hung... i cant use it any more.
> > > The same command works with all other tables but this is only one I am having
> > > problem with.
> > > I have attached my file with message. Could someone help me out here what is
> > > the reason for this behaviour.
> >
> > Can you do manual inserts into the table?
> > Can you insert just a few records using \i?
> > Can you vacuum the table?
> 
> Hi, No I can't even do manaul insert on that particular table. This is really
> weird. Could this table's internals be corrupted?
>  One  more thing, if I run the same file on another db it works just fine. I am
> just wondering what is wrong with the structure.
> Thank you for replying. Anyone else has an idea about it
> Regards.

The problem is almost certainly not w/your structure at all. Your tables
has probably just become corrupted. Can you vacuum it? Can you dump it
and recreate it?

(You may also want to try running postmaster at a higher level of debug
and checking the logs.) 

Good luck,

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

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

[HACKERS] Re: problem with copy command

[Joel Burton]

On Tue, 10 Apr 2001, Jaruwan Laongmal wrote:

> dear all,
> I currently using postgresql v7.0.3
> when i import text file to table with command "copy tablename from
> '/tmp/a.txt';
> and it shows
> "copy: line 20, Cannot insert a duplicate key into unique index testpri_pk"
> ,then it exits with doing nothing.
> 
> I want to ignore this errors and continue copy the next record. How to do
> that?
> if I don't filter in '/tmp/a.txt' before using copy command.

AFAIK, you can't ignore primary keys, so you can't cheat and get it in,
even for a moment. And if COPY encounters bad data, it ends the
transaction. (Both of these seem like the Right Thing to me, though
perhaps there's an argument for COPY IGNORING ERRORS or something like
that. Ick.)


Either

1) filter /tmp/a.txt to remove duplicates

or 

2) drop your unique index, copy the data, get rid of duplicates, the add
the index again

or

2) 

Assuming your table you're importing to is

  CREATE TABLE names (lname text, fname text, primary key (lname,
fname) );

Create another table w/o the primary key:

 CREATE TABLE import (lname text, fname text);

copy to *this* table, then copy from this table to the names table,
ignoring duplicates in the import:

 SELECT distinct fname, lname into names from import;

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: enumerating rows

[Joel Burton]

On Wed, 11 Apr 2001, Kovacs Zoltan wrote:

> > Here is a method which is fairly cumbersome, but will do what you want.
> > (Whether what you want is useful, is another matter.  The row numbers
> > have no meaning except to delineate which row is printed after which; they
> > bear no relation to their order in the table.)
> Thanks, Oliver! Are you sure there is no other (more
> convenient) solution? I don't think this is a part of the SQL standard but
> it could be a PostgreSQL extension. Hm?

If you don't need the numbers in PostgreSQL, but in the output, could you
pipe your query results through `cat -b`, which will add line numbers?

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: DB porting questions...

[Joel Burton]

On Wed, 11 Apr 2001, Diehl, Jeffrey wrote:

> Hi all,
> 
> I'm in the final stages of migrating from mysql to postgres and have a few
> more questions...
> 
> 1)
> I have a table:
>   create table a (
>   t   timestamp not null,
>   ...
>   );
> 
> I'm thinking that I can define a.t as not null default=now().  But will this
> work?  That is, will it update a.t when I modified a given record?
> 
> 
> 2)
> I have another table:
>   create table b (
>   id  int not null AUTO_INCREMENT,
>   ... 
>   );
> 
> To reproduce this behavior, I believe I need to use a sequence.  The problem
> is that I have a lot of data to import into this table.  How do I import the
> old data without colliding with the new sequence numbers?

1)

DEFAULT values only apply when *adding* a record, not modifying it, so,
no, "DEFAULT now()" (or more portably, DEFAULT CURRENT_TIMESTAMP) won't
change on updates. (I can't imagine any database that does do this for
DEFAULT values!)

If you want to track modifications, you want a trigger to watch for
updates. Look in /contrib/spi/moddatetime for help.

At my org, our important tables have

CREATE TABLE ... (
  ...
  addby  varchar(32) not null default current_user,
  addat  timestamp not null default current_timestamp,
  chgby  varchar(32) not null default current_user,
  chgat  timestamp not null default current_timestamp
);

and then add the triggers to track change times/users.
  
2)

You can use a sequence directly, most people would simply say

CREATE TABLE b (
  id  SERIAL NOT NULL PRIMARY KEY
  ...
);

If you old data in, that's fine. You can set the start for the sequence
after the importing so that the sequence starts w/the first new number
with SELECT SETVAL('b_id_seq', xxx), where xxx is the number for it to
begin new id numbers.


-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

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

[SQL] Re: Triggers on SELECT

[Joel Burton]

On Thu, 12 Apr 2001, Lonnie Cumberland wrote:

> Hello All,
> 
> I have been reading on the uses of the Triggers from within the PL/pgSQL
> language and have seen that you can set up triggers for such things as UPDATE,
> and INSERT.
> 
> I was just wondering why there was no trigger mechanicism for the SELECT
> statement?

You could use a RULE instead -- you might be able to get what you want
this way.

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: DROP TABLE in transaction

[Joel Burton]

On Thu, 12 Apr 2001, David Olbersen wrote:

> On Thu, 12 Apr 2001, Peter Eisentraut wrote:
> 
> > Because DROP TABLE removes the table file on disk, and you can't roll back
> > that.  Actually, in 7.1 you can.  ;-)
> 
> Well I understand that it's being taken from the disk, but why does that action
> have to be done *right now*?
> Why can't it be postponed until I type 'commit;' ?
> 
> I wonder how much time this addition would have saved those of us who type
> quickly and use the tab-completion too much :)

If one were inclined to do this sort of thing, it might even make sense
to argue that DROP TABLE hides the table (sets an attrib so that it
doesn't show, query planner doesn't see it, etc.); it should actually be
removed from disk when the database on VACUUM.


-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: Postgresql to Access

[Joel Burton]

On Thu, 19 Apr 2001, Mateusz Mazur wrote:

> Hello.
> 
> Could you help me? I have database in psql and my boss want to have this
> base also is MS Access (only like client - main base will be psql). He wants
> to use access like viewer to psql base. What should I do.

You can create a front-end using linked tables. Any good Access book will
walk through the basics of linked tables.

PostgreSQL-specific stuff is in a FAQ at www.scw.org/pgaccess


-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: rules

[Joel Burton]

On Thu, 26 Apr 2001, [iso-8859-1] Martín Marqués wrote:

> Is it posible to make a rule execute more then one query?
> 
> Something like:
> 
> CREATE RULE rule_name AS ON INSERT TO table1
> DO INSTEAD
> INSERT INTO table2 VALUES
> (new.value1,new.value2)
> INSERT INTO table3 VALUES
> (x,y)

test=# \h create rule
Command: CREATE RULE
Description: Defines a new rule
Syntax:
CREATE RULE name AS ON event
TO object [ WHERE condition ]
DO [ INSTEAD ] action

where action can be:

NOTHING
|
query
|
( query ; query ... )
|
[ query ; query ... ]

ie

CREATE RULE snog AS ON UPDATE TO foo DO INSTEAD ( ...; ... );

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: random rows

[Joel Burton]

On Thu, 26 Apr 2001, Jie Liang wrote:

> 
> How I can return random N rows from my select stmt?
> like:
> e.g. what my selectee is a set of 1000 rows, I want randomly
> pickup 100 of 1000.

Interesting problem.

You might get much better responses than this, but, two ideas
that might be workable:

 * use a WHERE clause that checks random() > .88 . This should 
   give you, on average, about 120 rows out of 1000, and you
   can add LIMIT 100 to ensure that you get only 100. But you're
   still biased toward the start of the list. (Or, remove the
   LIMIT 100, use > .9, but there's no guarantee you'll get 100--
   you'll get more or less than that.

 * have a plpgsql routine that gets 100 random records,
   and copy these into a temporary table (since plpgsql can't
   return a recordset.) Query against this table.

Or, when all else fails:

 * do it in your front end (Python/Perl/PHP/Pwhatever).


If you get better ideas, and they aren't cc'd to the list, please do so.

HTH,
-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] RE: "correct" sorting.

[Joel Burton]

On Thu, 3 May 2001, Gerald Gutierrez wrote:

> Hi folks,
> 
> say i have a text field with teh values
> 
> 1,2,3,10,20,30,1a,1b,2a,2b
> 
> and i want to sort it so i get,
> 
> 1
> 1a
> 1b
> 2
> 2a
> 2b
> 3
> 10
> 20
> 30
> 
> is there anyway to do that with postgresql ?
> below is what actually happens.
> 
> jeff=> select * from foo order by var1;
>  var1
> --
>  1
>  10
>  1a
>  1b
>  2
>  20
>  2a
>  2b
>  3
>  30
>  3a
>  3b
> (12 rows)

Hmmm... howzabout



create a function order_val(text) returning an integer, which is
equal to the the input, coerced into an integer (for simple things, like
10, 20, etc.), but equal to 10.01 for 10a, 10.02 for 10b, 10.25 for 10z.
(pl/perl, pl/tcl, or pl/python might be a quicker choice for this than
pl/pgsql)

You could then

SELECT id FROM tbl ORDER BY order_val(id);

And you could even index on order_val(id), so that it runs a bit faster.



-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: create table

[Joel Burton]

On Sun, 29 Apr 2001, LeoDeBeo wrote:

> can anybody explain me the syntax of Create Table documentation??

This doc is much improved in the more recent PG create table help.

Check out the online 7.1 Reference Manual, and there's a much nicer CREATE
TABLE grammar.

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

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

[SQL] Re: Get the tables names?

[Joel Burton]

On Fri, 20 Jul 2001, Dado Feigenblatt wrote:

> Magnus Landahl wrote:
> 
> >Hi everybody!
> >
> >Is it possible to get the names of all tables in the database with a sql
> >query??
> >
> >Best regards,
> >
> >Magnus
> >
> >
> >
> >---(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
> >
> >
> >
> Not sure if this is the best way, but it works.
> SELECT relname , relowner from pg_class where relkind = 'r';
> 
> The only thing is that this includes system tables.
> So if you want to strip those you need to
> SELECT relname , relowner FROM pg_class WHERE relkind = 'r' and 
> relowner != 26;
> 
> Is user postgres always 26? Maybe you have to find that out first.

system tables all ~ '^pg', which is probably a better check than
user=postgresql.


hth,
-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] RE: Database Design Question

[Joel Burton]

On Fri, 27 Jul 2001, Jimmie Fulton wrote:

> I'm by no means a crack database designer, but I do have my ideas on this
> subject.  I prefer that every table has a unique integer (auto-incrementing)
> primary key.  Why?  Consistency.  With the alternative, some tables may not
> have a clear-cut candidate for a unique id.  In your example, you had
> "customer".  How many "John Smith"s would it take before we decide that is
> not a good identifier.  On the other hand, some tables would have perfectly
> logical identifiers.  Part numbers, SSNs  So, you would need to create
> some tables with integer primary keys, and others would have some other
> natural identifier.  That to me is inconsistent.  Every table should be,
> IMHO, predictable in it's definition of a primary key.  I don't even have to
> guess what the names of my primary keys are either because the are all named
> _ID.  Always.  I've only come up with these thoughts on my own,
> and have not extensively tried the other way, so I'd be interested in
> hearing other's ideas for the other side.

For large scale databases, there are theories aplenty about proper naming,
etc., and these should be be investigated. For small/simple databases,
this might be overkill in complexity and learning curve.

I teach a series of classes on small-scale database design for nonprofit
organizations, and recommend some simple rules:

 * for tables where there is no candidate key (ie a Person table where
there is no SSN, etc.), use the table name + "id", and use a SERIAL-type.

 * for tables where there is a candidate key, and that candidate key meets
all the usual criteria (always present, always unique, changes very
rarely), use the table name + "code", and use the appropriate type
(text/int/whatever), UNLESS

 * there exists a very common name for this piece of info. Rather than
calling a SSN a "personcode" (in the above example), call it the SSN

 * always put the primary key first in the table

Why not always use a SERIAL integer? It's a pain when a perfectly good
value exists. For example, in a table keeping track of US states, their
capitals, and governors, does it really make sense to code Maryland as
"45", when a memorable, unique, unlikely-to-change code already exists
("md")? Using a random number when a real-world code could do only forces
your user to do that lookup themselves.

[apologies to the international readers: Maryland is a state in the USA,
and "MD" is the postal code abbreviation for it]

I think that you could make some basic rules that would give you a system
that is intuitive and easy-to-rememeber, _without_ sacrificing clarity for
rigidity.

hth,
-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[SQL] Re: Get name of columns in a table

[Joel Burton]

On Fri, 27 Jul 2001, [iso-8859-1] María Elena Hernández wrote:

>  Is it possible to get thecoluns names of an a table in the database with a sql
> query??

"psql -E" will show the SQL commands that psql internally uses to display
tables, database, etc. Once in psql, use "\d table_name" to see fields in
a table and the SQL behind that.

-- 
Joel Burton   <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington


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

[Joel Burton]

On Mon, 15 Oct 2001, Stefan Lindner wrote:

> Is there any way to get system maintained keys from postgres? e.g. to
> have a table with a primary key column (varchar or int) and let postgres
> chose the next unique value for this column?

\h CREATE SEQUENCE  will give syntax, or look up SEQUENCES and SERIAL data
type in the documentation.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant


---(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] CREATE RULE ON UPDATE/DELETE

[Joel Burton]

On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote:

> Can a rule see the where statement in a query which it has been
> triggered by? or is it simply ignored?? what happens?
>

Looking over your question, I wanted to clarify the problem a bit, so:
(cleaned up example a bit from Aasmund)


-- set up tables

drop view normal;
drop view dbl;
drop table raw;

CREATE TABLE raw (id INT PRIMARY KEY, name TEXT );
INSERT INTO raw VALUES(1, 'a');
INSERT INTO raw VALUES(2, 'b');
INSERT INTO raw VALUES(12, 'c');
INSERT INTO raw VALUES(15, 'd');
INSERT INTO raw VALUES(14, 'e');


-- set up two views: "normal", a simple view,
-- and "dbl", which shows id * 2

-- create basic rules to allow update to both views

CREATE VIEW normal AS SELECT * FROM raw;

CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET
id = NEW.id, name = NEW.name WHERE OLD.id = id;

CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw;

CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
id = NEW.id, name = NEW.name WHERE OLD.id = id;


-- now test this

UPDATE normal SET id = id + 10 where id > 10;  -- works fine

UPDATE dbl SET id = id + 10 where id > 10;-- above shows UPDATE 0
  -- even though there are ids > 10

UPDATE dbl SET id = id + 10;  -- UPDATE 1; shows table
SELECT * FROM dbl;-- inconsistencies: two "a"s
SELECT * FROM raw;



The issue is that there are no IDs over 10 that have another ID that is
exactly their value, so the first update to "dbl" does nothing.

The second time, w/o the ID>10 restriction, it finds 1(a), and double
that, 2(b), and adds 10; getting confused about which record to edit.

Is this the best way to interpret this? Is this a bug?


-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant


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

http://archives.postgresql.org

Re: [SQL] oid's in views.

[Joel Burton]

On Mon, 22 Oct 2001, Josh Berkus wrote:

> Each significant data table contains one column, the first column,
> called "usq", for "universal sequence".  This usq field may or may not
> be the primary key for the table, but does have a unique index.  The usq
> is populated by a single sequence "universal_sq" which is shared between
> tables, thus allowing all tables usq uniqueness between them.
>
> This strategy has allowed me to write a number of functions which are
> table-agnostic, needing only the usq to do their job (such as a function
> that creates modification hisotry).

Josh --

Good example! I'll bet a lot of PG users may have never realized that you
can use the same sequence across several tables.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant


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

Re: [SQL] nvl() function

[Joel Burton]

On Tue, 16 Oct 2001, Steven Dahlin wrote:

> I am trying to find the equivalent in Postgresql to the Oracle sql function
> nvl().  With nvl() you give two parameters.  The first may be a field/column
> or variable.  If the value is not null then it is returned by the function.
> For example the with query below if the :ClientParameter is passed then only
> those rows which have a clientdesc matching the parameter are returned.  If
> the :ClientParameter is null then those rows which have clientdesc =
> clientdesc are returned (all rows):
>
> selectclientid,
>  clientdesc
>  from clients
>  where   ( clientdesc = nvl( :ClientParameter, clientdesc ) )
>
> I have looked thru all the documentation I could find but nowhere were any
> built-in SQL functions delineated.  Does anyone know where the documentation
> can be found?

COALESCE is the SQL standard name for this. You'll find details in the
documentation, in the Conditional Expressions section (4.10). Copy at:

http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-conditional.html

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant


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

[Joel Burton]

On Sat, 13 Oct 2001, Timothy J Hitchens wrote:

> It's been a while since I used postgresql but today I have converted one
> of my web apps but with one small problem.  I goto do a group as
> designed and executed in mysql and I get told that this and this must be
> part of the aggreate etc I am puzzled and wonder if someone could bring
> me up to stratch with grouping in postgresql this is my current sql:
>
> SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid;
>
> Result:
>
> Attribute telemetry.rpvuid must be GROUPed or used in an aggregate
> function
>
>
> Oh then if I include rpvuid I get you must include this field and on it
> goes.

Normally, GROUP BY is used to group up records to look at an aggregate.
For example, if you have this table of your friends:

CREATE TABLE friends (
  friend TEXT,
  country_code CHAR(2),
  income FLOAT,
);

I could get a count of how many friends lived in each country by:

SELECT country_code, COUNT(*) FROM friends GROUP BY country_code;

Or I could get the average amount of money made by friends in each country
with:

SELECT country_code, avg(income) FROM friends GROUP BY country_code;


In other words, when you GROUP BY, you're looking for an aggregate
(a function that is applied to a group and returns a single value,
such as average, minimum, maximum, count, etc.)

Can you be more specific about what you're actually trying to accomplish?



---(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] Multiple Parameters to an Aggregate Function

[Joel Burton]

On Tue, 16 Oct 2001, Anthony Bouvier wrote:

> I can create an AGGREGATE but from what I can tell, the format of the
> sfunc can only have two parameters like so:
>
> sfunc_name(type,type)
>
> Where the first parameter is what was passed before, and the second
> parameter is the 'new' info to do stuff to.  Is it not possible to do
> something similar to?:
>
> sfunc_name(type,type,type)
>
> So that I can pass the url and name to the AGGREGATE (so it can in turn
> pass it to the sfunc)?  Where the sfunc could be something like so:
>
> CREATE FUNCTION link_agg (text,text,text)
> RETURNS text AS '
>   return $_[0] . qq[http://www.domain.com/$_[0]";
> class="body_link">$_[1]];
> ' LANGUAGE 'plperl';
>
> Because then I gain benefit of a stored procedure and cut the SQL in the
> script down to:
>
>   SELECT link_agg(url,name) FROM link;
>
> Which will return the entire list at once, instead of needing the script
> to loop through multiple fetches.

I have a techdoc about using aggregate functions to create faster
web looping at

  http://www.zope.org/Members/pupq/pg_in_aggregates

It was written w/examples in DTML, Zope's scripting language, rather than
in Perl/DBI, but you should be able to easily follow it.

Essentially, what you want to end up with is something like this:

SELECT make_into_li ( make_into_text ( url, name ) );

where make_into_text is the aggregate, and make_into_text is the
formatting function.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant


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

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

Re: [SQL] Min and Max

[Joel Burton]

On Fri, Nov 29, 2002 at 10:55:54AM -0800, Sergio Oshiro wrote:
> Hello, everybody!
> 
> I've trouble to make a "simple"(?) query...
> 
> The following table is an example:
> 
> table: children
> id_father | child_name | child_age
> --++
> 1 | John   | 2
> 1 | Joe| 3
> 1 | Mary   | 4
> 1 | Cristine   | 4
> 2 | Paul   | 1
> 2 | Stephany   | 2
> 2 | Raul   | 5
> 
> How can I get the rows of the children name and its "father" such that
> they have the min child_ages? I expect the following rows as result:
> 
> id_father | child_name | child_age
> --++
> 1 | John   | 2
> 2 | Paul   | 1
> 
> The same for the max child_ages...
> 
> id_father | child_name | child_age
> --++
> 1 | Mary   | 4
> 1 | Cristine   | 4
> 2 | Raul   | 5

select distinct on (id_father) * from children order by id_father,
child_age;

will give your results

select distinct on (id_father) * from children order by id_father,
child_age desc;

will give the oldest children, but it doesn't list both mary and
christine -- it arbitrarily lists mary (you could add child_name to sort
order so it wouldn't be abitrary, but it still won't list both).


this is a weird use of distinct on, though, and perhaps cheating. a
canonical, if slower solution (and one that fixes the tie for oldest
child) is:


select id_father, 
   child_name, 
   child_age 
from   children c1 
where  not exists (select * 
   from   children c2 
   where  c1.id_father=c2.id_father 
 and  c2.child_age > c1.child_age);

swap the '>' to '<' for youngest.

- J.
-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

---(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] Accumulative Queries?

[Joel Burton]

On Sat, Nov 30, 2002 at 02:40:08PM -0800, Benjamin Smith wrote:
> Let's say you have a table of Financial transactions: 
> 
> Create table checks ( 
> id serial, 
> number varchar, 
> to varchar, 
> amount real, 
> date integer 
> ); 
> 
> (date is an epoch timestamp) 
> 
> And you want to get a listing of checks 
> 
> "SELECT * FROM checks ORDER BY date ASC"; 
> 
> but you also want to have an accumulative field that adds up the amount field as the 
>results are returned, so you might see results like: 
> 
> id number to amount date balance
> 1 0 Deposit -100 12344 100
> 2 100 Jack 40 123455 60
> 3 101 Bob 20 123345 40 
> 4 102 VOID 0 0 40
> 5 103 Harold 11 123488 29
> 
> Is this possible using only SQL? 
> 
> Also, assuming you have checks year round, how might you get results only in March 
>that have totals consistent for the time frame while taking into account all the 
>other checks in Jan and Feb? 

create table checks (
id serial primary key,
num varchar unique,
"to" varchar,
amt real,
date date
);
insert into checks (num, "to", amt, date) values
   (0,'deposit',100,'2002-01-01');
insert into checks (num, "to", amt, date) values
   (0,'jack',40,'2002-02-01');
insert into checks (num, "to", amt, date) values
   (101,'jack',40,'2002-02-01');
insert into checks (num, "to", amt, date) values
   (102,'bob',20,'2002-02-01');
insert into checks (num, "to", amt, date) values
   (103,'VOID',0,'2002-02-01');
insert into checks (num, "to", amt, date) values
   (104,'jenny',10,'2002-03-01');
insert into checks (num, "to", amt, date) values
   (104,'raul',10,'2002-03-02');
insert into checks (num, "to", amt, date) values
   (105,'raul',10,'2002-03-02');


select *,
   ( select sum(amt) 
 from   checks c2 
 where  c2.id<=c1.id as c2) 
from checks c1;

will give you the full accounting. To get just March, put a
where-date-between clause in both the outer and inner queries.

This will run slowly, though, for many transactions. Either consider:

* "closing" an account every month/quarter/year/whenever will the
  aggregate-so-far, and having your query use that, and do the math from
  that point onwards

* store the running balance in the table, and use triggers to keep it up
  to date for inserts/updates/deletes

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

---(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] Combining queries while preserving order in SQL - Help!

[Joel Burton]

On Mon, Dec 02, 2002 at 01:46:38PM -0500, Casey Allen Shobe wrote:
> Hi there,
> 
> I need to do the following in one SQL query:
> 
> select field1, field2, field3, field4, field5 from table where field6 < 5
> order by field1
> 
> And a totals line which shows the sum for each column.
> 
> The important part is that I need to preserve the order by of the first query.
> 
> Is there any way to do this in one query?

create table foo (a int, b int, c int );
insert into foo values (1,2,3);
insert into foo values (4,5,6);

select '' as label, 
   * 
from   foo 
union all 
select 'TOTAL', 
   sum(a), 
   sum(b),
   sum(c) 
from   foo 
order by 1,2;

(you wouldn't need the label column to sort by, except that a might
contain negative numbers, so the sum might be _less_ than some/all of
the numbers. by first sorting on this junk column, we can force the
totals at the bottom).

p.s. don't forget the "union __all__", otherwise you'll get rid of
duplicate entries in the your table.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

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

Re: [SQL] Query for filtering records

[Joel Burton]

On Tue, Dec 03, 2002 at 11:01:33AM -0800, eric soroos wrote:
> I'm having trouble subtracting groups from other groups. 
> 
> 
> I've got a data model that has the following essential features:
> 
> create table contacts (num int, properties);
> create table groups (groupNum int, contactNum int);
> 
> Where not all contacts will be in a group, some groups will contain most contacts, 
>and there will be something like hundreds of groups and tens of thousands of 
>contacts.  I allow people to build groups using criteria, which I need to 
>programatically translate to sql.  
> 
> One somewhat common pattern is:
> 
> Select all contacts in group a, who have property b, and who aren't in groups 
>c,d,e,f...
> 
> My first shot was subqueries:
> 
> select num, p1,p2 ... from contacts 
> inner join groups using (contacts.num=groups.contactNum)
> where groups.groupNum=a
> and contact.p3=b
> and not num in (select contactNum from groups where groupNum=c)
> and not num in (select contactNum from groups where groupNum=d)
> and not num in (select contactNum from groups where groupNum=e)
> and not num in (select contactNum from groups where groupNum=f)
> 
> This is  slow.  agonizingly so. 

I'd say so!

Something like:

SELECT * ...
 FROM ...
 WHERE NOT IN (SELECT contactnum FROM groups WHERE groupnum='c' or
 groupnum='d' OR ... )

is bound to be _much_ faster!

And even better is

SELECT *
 FROM ... contacts c1
 WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or
 groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum)


EXISTS is almost always faster in PG.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

---(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] Accent-insensitive

[Joel Burton]

On Fri, Dec 06, 2002 at 09:33:10PM +0100, Cédric Dufour (public) wrote:
> Use the 'to_ascii' function to convert your string to the "no accent" ASCII
> equivalent before accomplishing your comparison
> 
> SELECT foo FROM table WHERE ( to_ascii(foo) ILIKE to_ascii('caractères
> accentués') );
> 
> This does not work with all database locale (LATIN1 is OK, but LATIN9 is
> not).
> 
> I was actually wondering if this is efficient enough or if there is any more
> efficient method to accomplish this...

I'd think that something like:

CREATE FUNCTION lower_ascii (text) RETURNS text AS '
  BEGIN
RETURN lower(to_ascii($1));
  END
' language 'plpgsql';

CREATE INDEX table_lower_ascii ON table(lower_ascii(field));

would perform better, since we can now use this index, whereas we
couldn't do this with ILIKE to_ascii(...).

Also, not sure it's a good idea to use ILIKE simply to get
lower-case-matching. If the user string ends with '%', for instance, it
will match everything-starting-with, which is probably not what the user
meant. Better the check against lower().

There might be a better way specifically-oriented toward
de-accentuation; this is just generic PG advice.

- J.
-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

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

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

Re: [SQL] CURRENT_TIMSTAMP

[Joel Burton]

On Mon, Dec 02, 2002 at 11:41:33AM -0500, Raymond Chui wrote:
> I created a column, dada type timstamp with time zone
> and with default CURRENT_TIMSTAMP
> it shows me the default is
> 
> default ('now'::text)::timstamp(6) with time zone
> 
> Then when I insert a row, the default timestamp value is
> 
> -mm-dd HH:MM:ss.m+00
> 
> where m is milliseconds.
> How do I make default only -mm-dd HH:MM:ss+00 ?
> Thank Q!

The problem isn't CURRENT_TIMESTAMP, it's your table definition.
If you create the field as timestamp(0) [in 7.3, that's timestamp(0)
with time zone, since the default has swung to no-TZ], it will keep
track of just HMS. Or put in other values for 0 for more granularity on
seconds.

Of course, you can always store the specific time and select it out
w/less resolution (using the time/date functions). In some cases, this
might be a better solution.

- J.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

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

Re: [SQL] Accent-insensitive

[Joel Burton]

On Sat, Dec 07, 2002 at 07:06:45PM -0300, Pedro Igor wrote:
> Thanks, you know if some possible release would have some internal to deal
> with this ?
> 
> Abraços
> Pedro Igor
> >
> > CREATE FUNCTION lower_ascii (text) RETURNS text AS '
> >   BEGIN
> > RETURN lower(to_ascii($1));
> >   END
> > ' language 'plpgsql';
> >
> > CREATE INDEX table_lower_ascii ON table(lower_ascii(field));

Pedro --

Please keep conversations on the list -- other people may know things I
don't (actually, they certainly will!), and it allows other people to
follow the conversation.

As for this being internal, I have no idea -- you could submit it as a
suggestion.

Given how easy it is to implement in plpgsql, I suspect this kind of thing will
stay out of the internals. Keep in mind that if you define this function
in your template1 database, you can have it created automatically in all
new databases you create, so it's one less thing to worry about.

HTH.

-- 

Joel BURTON  |  [EMAIL PROTECTED]  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

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

[SQL] Can I create a function that returns many records?

[Joel Burton]

I'd like to create a function that outs the results of a select query. 
It might work like this:

SELECT METAPHONE('jennifer foobar');
persid | name
-
  1 | jennifer fubar
 10 | gennifer foobar

[I already have the metaphone comparing working, it's the returning 
the SELECt that needs help.]

Working through the documentation, I see examples using CREATE 
FUNCTION METAPHONE(text) RETURNS setof tblPerson AS 'SELECT 
* FROM tblPerson' LANGUAGE 'sql',  but this returns only a single 
numeric value (that doesn't seem to correspond to anything.)

Is there a way to do this? Any help would be very appreciated.



--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)