Re: [SQL] writing a function to mimic mysql last_insert_id
Hi Beth,
Here's a function I use to do the same sort of thing - but you need to
supply it with a table name to get it - in the case of standard inserts
it's pretty easy since it's always the third word (so if you're using an
abstraction layer it's easy to change).
It can be written a lot better but it works for me and it was my first
function so :P
This works for 7.1 and the 7.2 series.. but it doesn't cope with errors
very well:
timesheets=# SELECT last_insert_id('task');
NOTICE: Error occurred while executing PL/pgSQL function last_insert_id
NOTICE: line 12 at for over select rows
ERROR: task_taskid_seq.currval is not yet defined in this session
Tweaks appreciated :) I probably don't need half the variables in there but
I haven't revisited it since I got it working.
CREATE FUNCTION "last_insert_id" (character varying) RETURNS text AS '
DECLARE
tbl ALIAS FOR $1;
idxnme TEXT;
idval RECORD;
idv TEXT;
seq RECORD;
seqname TEXT;
BEGIN
FOR seq IN SELECT substring(substring(d.adsrc for 128),
strpos(substring(d.adsrc for 128),''\\'''')+1, (strpos(substring(d.adsrc
for 128),''\\''::'') - strpos(substring(d.adsrc for 128),''\\'''')-1)) as
seq_name FROM pg_attrdef d, pg_class c WHERE c.relname = tbl::text AND
c.oid = d.adrelid AND d.adnum = 1 LOOP
seqname=seq.seq_name;
END LOOP;
FOR idval IN SELECT currval(seqname) AS id LOOP
idv := idval.id;
END LOOP;
RETURN idv;
END;
' LANGUAGE 'plpgsql';
Chris.
---(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] Case Sensitive "WHERE" Clauses?
>On Thursday 26 September 2002 19:54, Jordan Reiter wrote:
> > Are string comparisons in postgresql case sensitive?
>
>Yes, unless you specify otherwise.
>
>Are you sure you are using the right database? I can
>reproduce similar results, but only like this:
You're using MySQL in these examples .. not Postgres :)
(FYI - Just tried this with 7.3beta and I got the same results as everyone
else .. it is case sensitive).
Chris.
>mysql> create temporary table foo (ch char(2), vc varchar(2));
>Query OK, 0 rows affected (0.12 sec)
>
>mysql> insert into foo values ('aa','AA');
>Query OK, 1 row affected (0.02 sec)
>
>mysql> select * from foo where ch = 'aa';
>+--+--+
>| ch | vc |
>+--+--+
>| aa | AA |
>+--+--+
>1 row in set (0.01 sec)
>
>mysql> select * from foo where ch = 'AA';
>+--+--+
>| ch | vc |
>+--+--+
>| aa | AA |
>+--+--+
>1 row in set (0.00 sec)
>
>mysql> select * from foo where vc = 'aa';
>+--+--+
>| ch | vc |
>+--+--+
>| aa | AA |
>+--+--+
>1 row in set (0.00 sec)
>
---(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] [GENERAL] pgcrypto-crypt
AKHILESH GUPTA wrote:
how do we compare the existing data in the table with the entered one?
same way as anything else, for example:
select * from users where passwd=md5('my_password');
is there any other function which we can use here for both cases
encryption as well as for decryption at the script as well as database
level
why do you need it encrypted?
Please do reply-all - you will get a quicker response (from me and the
list(s) might have suggestions I don't).
On 4/6/06, *chris smith* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>>
wrote:
On 4/6/06, AKHILESH GUPTA <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
> dear all,
> i want to encrypt and decrypt one of the fields in my table
(i.e-password
> field)
> i have searched and with the help of pgcrypto package, using
function
> "crypt", i am able to encrypt my data,
> but there is nothing which i found to decrypt that same data,
> plz anybody give me the function to decrypt that encrypted
value.
The crypt function can't be decrypted (whether it's in postgresql or
anywhere else).
Crypt is meant to be used for passwords and such that you don't need
to reverse (you only compare against).
--
Postgresql & php tutorials
http://www.designmagick.com/
--
Thanks & Regards,
Akhilesh
DAV Institute of Management
Faridabad(Haryana)
GSM:-(+919891606064)
(+911744293789)
"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
--
Postgresql & php tutorials
http://www.designmagick.com/
---(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] Bit by "commands ignored until end of transaction block" again
Joshua Tolley wrote: On Thu, Jul 23, 2009 at 02:04:53AM -0400, Glenn Maynard wrote: On Thu, Jul 23, 2009 at 1:31 AM, Richard Huxton wrote: - Let me use SAVEPOINT outside of a transaction, You are never outside a transaction. All queries are executed within a transaction. "Transaction block", then, if you insist. I think this is the root of your problem - all queries are within a transaction so either: 1. You have a transaction that wraps a single statement. If you get an error then only that statement was affected. 2. You have an explicit BEGIN...COMMIT transaction which could use a savepoint. Savepoints can only be used inside transaction blocks. My function has no idea whether it's being called inside a transaction block. From inside a transaction block, my function would need to call SAVEPOINT/RELEASE SAVEPOINT. If it's not in a transaction block, it needs to call BEGIN/COMMIT instead. SAVEPOINT will fail with "SAVEPOINT can only be used in transaction blocks". Have you tried this? I expect if you give it a shot, you'll find you don't actually have this problem. Really, everything is always in a transaction. Each statement is in it's own transaction, but the problem (as I understand it) is that you're in this sort of situation: psql -d dbname .. # select now(); now --- 2009-07-23 17:04:21.406424+10 (1 row) Time: 2.434 ms (csm...@[local]:5432) 17:04:21 [test] # savepoint xyz; ERROR: SAVEPOINT can only be used in transaction blocks (csm...@[local]:5432) 17:04:25 [test] You haven't explicitly started a transaction, therefore savepoints won't work. Django (it seems) just issues queries with no knowledge of (and no way to support) them. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] getting extract to always return number of hours
Hi,
I'm trying to get extract() to always return the number of hours between
two time intervals, ala:
=> create table t1(timestart timestamp, timeend timestamp);
=> insert into t1(timestart, timeend) values ('2010-01-01 00:00:00',
'2010-01-02 01:00:00');
=> select timeend - timestart from t1;
?column?
1 day 01:00:00
(1 row)
to return 25 hours.
I couldn't see anything in the docs and can't work out a way to do it.
Any suggestions?
Thanks!
--
Postgresql & php tutorials
http://www.designmagick.com/
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] getting extract to always return number of hours
Chris wrote:
Hi,
I'm trying to get extract() to always return the number of hours between
two time intervals, ala:
=> create table t1(timestart timestamp, timeend timestamp);
=> insert into t1(timestart, timeend) values ('2010-01-01 00:00:00',
'2010-01-02 01:00:00');
=> select timeend - timestart from t1;
?column?
1 day 01:00:00
(1 row)
to return 25 hours.
I ended up with
select extract('days' from x) * 24 + extract('hours' from x) from
(select (timeend - timestart) as x from t1) as y;
mainly because t1 is rather large so I didn't want to run the end -
start calculation multiple times.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] indexes
Seb wrote: Hi, I have some views and queries that take a bit too long to return, so perhaps some judicious indexes might help, but I don't know much about how to use them. The PostgreSQL manual has a good section on indexes, but I can't find guidance on (unless I missed something): o How to decide what columns need an index? I wrote something a little while ago about this: http://www.designmagick.com/article/16/ (comments welcome!) o Should all foreign keys have an index? Not necessarily, you might just want the db to enforce the restriction but not actually use the data in it. For example, keep a userid (and timestamp) column of the last person to update a row. You may need it to say "aha - this was last changed on this date and by person X", but you'll never generally use it. If you never have a where clause with that column, no need to index it. If you're using it in a join all the time, then yes it would be better to index it. o Naming conventions? That comes down to personal or project preference - there's no particular convention used anywhere. o Does PostgreSQL use available indexes that can be useful in any query, without the user having to do anything in particular? Yes - though just because an index is present doesn't mean postgres will use it, in some cases it's better for it to ignore the index altogether and use some other method to perform your query. -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] postgre2postgre
F. wrote: Hello, I am trying to migrate from postgresql-8.1.11-1.el5_1.1 (i386) to postgresql-8.3.4-1.fc10.x86_64. But I can not. Database uses ltree and tsearch and the problem seems to be this. I am using, pg_dump in first computer and psql in second computer to execute script. First error: psql:informatica.sql:24: ERROR: no se encuentra la función «gtsvector_in» en el archivo «/usr/lib64/pgsql/tsearch2.so» Anyone know any way to migrate? tsearch2 became a built in module, there is doco on the website about how to handle this: http://www.postgresql.org/docs/8.3/static/tsearch2.html -- Postgresql & php tutorials http://www.designmagick.com/ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Trigger
Craig May wrote: > > Could someone send me a quick example of a trigger. Hope this helps. Chris Ryan <<< Clip below and execute to create example >>> -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update the last_updated -- field on insert and updates. -- create function trigger_last_updated() returns opaque as 'begin new.last_updated := now(); return new; end;' language 'plpgsql'; -- -- TABLE: test_tbl -- -- DESCRIPTION: -- A simple table to test my trigger -- create table test_tbl ( some_field varchar(10), last_updated timestamp not null default now() ); -- -- TRIGGER: trigger_insert_update_test_tbl -- -- DESCRIPTION: -- This is the trigger called on insert and updates of all the table that -- has the last_updated field. It will use the function trigger_last_updated -- The cool thing here is the function doesn't make specific reference to the -- table so you could create a different trigger for each table with the field -- last_updated and use the same function. -- create trigger trigger_insert_update_test_tbl before insert or update on test_tbl for each row execute procedure trigger_last_updated();
Re: [SQL] Trigger
Chris Ryan wrote: > > Craig May wrote: > > > > Could someone send me a quick example of a trigger. > > Hope this helps. > > Chris Ryan > -- snipped code -- I am so sorry but you may have noticed my email client wrapped lines it shouldn't have. I have attached the file this time. Chris Ryan -- -- FILE: trigger_example.sql -- -- DESCRIPTION: -- This file shows the basics of creating a table with a trigger -- -- Chris Ryan <[EMAIL PROTECTED]> 09/06/2000 -- -- GENERAL DISCLAIMER: -- Please feel free to use this in any way you see fit to copy, modify, -- redistribute, etc.. I provide not warranty of the code nor may I be held -- responsible for it's use/misuse should something bad happen including -- intentional or acts of god. -- -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update the last_updated -- field on insert and updates. -- create function trigger_last_updated() returns opaque as 'begin new.last_updated := now(); return new; end;' language 'plpgsql'; -- -- TABLE: test_tbl -- -- DESCRIPTION: -- A simple table to test my trigger -- create table test_tbl ( some_field varchar(10), last_updated timestamp not null default now() ); -- -- TRIGGER: trigger_insert_update_test_tbl -- -- DESCRIPTION: -- This is the trigger called on insert and updates of all the table that -- has the last_updated field. It will use the function trigger_last_updated -- The cool thing here is the function doesn't make specific reference to the -- table so you could create a different trigger for each table with the field -- last_updated and use the same function. -- create trigger trigger_insert_update_test_tbl before insert or update on test_tbl for each row execute procedure trigger_last_updated();
[SQL] two tables - foreign keys referring to each other...
Hey to all, I got two tables, linked to each other. How can I tell the first CREATE TABLE (institute_t) to wait to check the foreign key for the second table??? just like "hold on a little bit... you'll receive your admin_t" :-) ? I thoght DEFERRABLE, DEFERRED and transaction with BEGIN/COMMIT take care of this. ..or generally: how do you create two crosslinked foreign keyed tables? hopefully an easy problem for the real professionals! -> here we go BEGIN; -- begin table transaction -- Only Postgresql CREATE TABLE institute_t ( nameVARCHAR(48) PRIMARY KEY, street VARCHAR(48) NOT NULL, zip VARCHAR(16), townVARCHAR(32) NOT NULL, country CHAR(2) NOT NULL, /* country codes ISO-3166*/ phone VARCHAR(32) NOT NULL, fax VARCHAR(32), admin VARCHAR(16) REFERENCES admin_t ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE admin_t ( login VARCHAR(16) PRIMARY KEY, passwordVARCHAR(16) NOT NULL, email VARCHAR(32) NOT NULL, real_name VARCHAR(32) NOT NULL, street VARCHAR(48) NOT NULL, zip VARCHAR(16), townVARCHAR(32) NOT NULL, country CHAR(2) NOT NULL, /* country codes -- refer to ISO-3166*/ phone VARCHAR(32) NOT NULL, fax VARCHAR(32), access INTEGER NOT NULL, institute VARCHAR(48) REFERENCES institute_t ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED ); COMMIT; of course I get the ERROR, that admin_t doesn't exist. So? help the stupid! pls! best greets, Chris
Re: [SQL] two tables - foreign keys referring to each other...
ThX Kirby, ThX Martijn, as you can see I'm new to the SQL- and database world. My policy for this project is to FORCE an admin to be in an institute. If one admin behaves bad for the database project the institute is responsible for her/him. This institute would be represented by a special admin (therefore the link back). Anyway, I see that crosslinking is really a little bit tricky... I will do this check in the application level (Java) and see, if this is easier and even necessary. For now I might use only admin(fk)->institute. This is necessary to trace back responsibility. as beginner I appreciate good hints, cheers, Chris > > IMHO, you should consider not having the admin table have a link to the > institute table. If you want to find the institute a particular admin > is connected with, find that look in the institutes table. The astute > reader will note the advice is symmetric, you can not have a link from > the institute to the admin. If you don't want to have dangling admin's > you might be able to get a trigger/stored procedure to do it for you > (Note:I done triggers in Oracle, never in PostGres so take that with a > grain of salt. I would be shocked if you couldn't do it with a Trigger > under PostGres. I believe it us commonly done with long objects as a > matter of fact). > > Do what you like, and I hope this helps. > > Kirby >
[SQL] DELETE FROM fails with error
Hi folks,
I have the problem that I can't delete datasets out of my tables. It's like that:
I have a table:
tblshop
ID_Shop oid with sequence
---
Sh_Name
ID_Country
...
there is an index on ID_Country
I have a second table:
tblcountry
ID_Country oid with sequence
--
C_Name
...
I have a reference between these two tables pointing from tblshop.ID_Country to
tblcountry.ID_Country
When I try to delete a row from tblshop I get the error that postgres can't find
the attribute id_shop. My SQL-command looks like follows:
DELETE FROM tblshop WHERE tblshop."ID_Shop" = 12
the same happens when I try to do:
DELETE FROM tblshop WHERE tblshop."ID_Country" = 3
I also tried this query without quotes, with simple quotes (') without the leading
tablename and so on, but I always get the error-message:
ERROR: Attribute 'id_shop' not found
Please, can anyone help me out? It is really anoying when you can't delete datasets
especially because my application is already online (I use postgres with PHP) and
there are 20 tables with alltogether 120 MB of data in it
chris
--
+-+
| chris |
| Database Developer |
| uscreen GmbH|
| |
| --- |
| eMail [EMAIL PROTECTED] |
| Fon (02 02) 24 49 88 - 23 |
+-+
---(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] realising a counter in an pgpsql-trigger
Hi folks,
I want to do the following: I have a column in every table, named c_update, it's
purpose is to hold how many times the dataset was updated. This should be done
automatically by the trigger - which already set sthe update-user, -time and date.
How can I do this ???
chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] [Fwd: [Gborg-bugs] BUG: reference error when using inherited tables (ID: 269) (new)]
I received this bug on a project I administer that Isn't related to my project. I forwarded it here to see if any of you could help this person. [EMAIL PROTECTED] wrote: > > Title: reference error when using inherited tables > Bug Type: Software bug > Severity: Serious > Software Version: Other > Environment: k6III-500/394 > SuSE 7.1 > Postgres 7.1 > > Created By: gorefest > Description: Hi > > I have a problem with inherited refences. > For example : > CREATE TABLE A(LNR integer Primary key blabla); > CREATE TABLE B () INHERITS(A); > CREATE TABLE C(LNR integer primary key blabla, RNR Intger not null, unique(RNR), >FOREIGN KEY(RNR) REFERENCES A.LNr ON DELETE CASCADE); > > will throw an error, if i try to insert an object into B with a counterpart in C. A >with a counterpart in C works. Due to the fact, that the inheritance is an acyclic >graph, the machine should look in B to. But i get a reference error instead. Are >references on inherited tables not implemented yet ? > > greetings gorefest > Status: Submitted > > http://www.greatbridge.org/project/gborg/bugs/bugupdate.php?269 > > ___ > Gborg-bugs mailing list > [EMAIL PROTECTED] > http://www.greatbridge.org/mailman/listinfo/gborg-bugs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] query PostgreSQL from c++
Hi folks, I'm asked to write a "small" c++ application to query a PostgreSQL DB. In principel I can't see why this should be so difficult but I don't really know a lot about the API. Therefor I thought I just ask if anyone has done this before - of course I think - and might be able and willing to give me a few tips. Maybe there's even a document somewhere which I could consult? So if someone has tipps and/or examples or maybe a suggestion for a book, please mail me, chris ---(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] Select question
Hi all, although not new to databases, I'm new to the wonderful world of PostGreSQl and SQL in general. Question: I do this query phone=# select * from phonelog where cdate > 2001-05-18 order by cdate limit 2 ; And I get theis result cdate| ctime | countrycode | success | carrier | duration | phonenumber | areacode | pseq +---+-+-+-+--+-- ---+--+-- 2001-04-01 | 0 | 370 | 1 | 1 |8 | "3703348" | "33" | 4005 2001-04-01 | 0 | 98 | 1 | 1 | 15 | "9871162" | "71" | 3889 Although I specified that I want only dates > 5/18/2001, I get dates 4/1/2001. Clearly, I ask the system the wrong question. How do I ask this question the correct way? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Stored Procedures?
I read something about stored procedures in the Great Bridge User's Manual (Page 74 under "PG_LANGUAGE"). It is only mentioned briefly and there are no explanations of how it works. Can anybody let me know, how I can write a stored procedure and how to run it? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Unique record Identifier?
Hi all, I'm busy writing an application using PostGreSQL and PHP, so my db reads are 'stateless' and I don't know at record 'write' time which record I have read to begin with. The records I have, have an index, most tables do have a unique index but the index values could get changed during an update. In order to be able to re-read a record, I would like to use some unique identifier of that record, like a unique db-wide rec-id. Does this exist in PostGres and if so, how do I access it? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Unique record Identifier?
on 05/30/2001 16:33, Josh Berkus at [EMAIL PROTECTED] wrote: > Chris, > Thanks Josh - that was my next step. At the moment, the database is not in production, I'm running a Progress database there, but I'm trying to learn the dos and don'ts in PG. I don't really need to backfill the records, I can just drop the table and re-create it (On my Mac PowerBook it loads 1.2 million records in about 5 minutes without any index on the table). I'm sure, I will have many more questions in the future ... Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: [PHP] PHP-PostgreSQL - install
find libpq.so.2.1 in the tree from the compile (find . -name libpq.so.2.1 -print) then copy it to /usr/libexec manually. you might also want to check the permissions on the library, it should be 755 (rwxr-xr-x). Best regards, Chris - Original Message - From: "Dorin Grunberg" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, June 05, 2001 1:34 PM Subject: [PHP] PHP-PostgreSQL - install > Hi, > > I have a fresh install on OpenBSD 2.8. I did install PostgreSQL7.1.2 and > PHP 4.0.5. > > When I try to start Apache I get this error: > > /usr/libexec/ld.so: httpd: libpq.so.2.1: No such file or directory > /usr/sbin/apachectl start: httpd could not be started > > TIA > > Dorin > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] rpt
Hilkiah, you could use any report writing tool which connects to databases via ODBC. Download the Windows ODBC driver and install it. Set the '-i' flag on the postmaster, set up the ODBC interface to PostGreSQL in Windows and you're ready to fly :). Best regards, Chris - Original Message - From: "Hilkiah Lavinier" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, June 08, 2001 7:32 AM Subject: [SQL] rpt > Hi, this is probably not the correct group for this post, but it's kinda > related. Does anyone know of a report writer/generator that works for > postgres? It would be nice if it is natively built in or some 3rd party > tool that I can use. > > Would appreciate any info. > > regards, > Hilkiah > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Unknown values in int8 fields?
Hi Chris,
When I load records with copy from ... And one of the fields (last
one) is left empty, I want the default of nextval('sequence_table') to kick
in - but it doesn't.
Now, the field with a unique index on it stays blank. No big deal if I could
go and say 'update rate set recno = nextval('sequence_rate') where recno = 0
- but it's not that easy as the recno is not 0 but - hmm what? What can I
check for? I tried '?' and ? And 0 and ... And ... And ... But nothing works
... Now what?
Best regards
Chris
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] First steps in plpgsql - language not recognized?
Hi all, I have tried (and failed) my first steps in Pl/PgSQL. This must be real simple (like a 'create language' or something) but I don't (yet) know how to do this and maybe someone has a quick answer for me here. I get this (I'm sure it's a clear case of RT(F)M, but there are so many Ms to R, I don't know where to begin): [postgres@chill-025 postgres]$ psql -f x.sql phones psql:x.sql:1: ERROR: RemoveFunction: function 'testfunc(int4)' does not exist psql:x.sql:9: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal, and created procedural languages. The first error is ok - the error in line 9 is what I'm worried about. The code is what's in the tutorial, no biggy ;). Best regards and thanks for the help, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] PL/PGSQL and external (flat ASCII) files - Urgent ... :)
Hi all, I need to know how I can access a flat file from within a PL/PGSQL script. I have an Oracle PL/SQL book here and they refer to FOPEN(...), FCLOSE(...) but these functions don't seem to exist in PL/PGSQL.. What can I do instead? I checked the Programmer's Guide to Procedural Languages (PostGreSQL 7.1.2 and 7.2) but there is no info on it. Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] PL/PGSQL and external (flat ASCII) files - Urgent ...:)
Hi Tom, If life was easy, everything would just come in the format I'd like it. But since it isn't, I need to create records like this one: cdate| ctime | countrycode | areacodex | success | carrier | duration| phonenumber | areacode | pseq | loadno | frline | entity | loaddate | loadtime | prefix | toline | dur306 | dur180180 | recno +--+-+---+-+-+-- +-+--+--+-+++--- -+--++++---+- 2001-07-15 | 23:55:52 | 98 | 0 | f | 53092 | 48| 11970 | |0 | 8280646 | 2017 | 001| 2001-07-16 | 02:05:48 | 092| 2116 |0.8 | 3 | 5493891 >From an input file where the records looks like this one: 020-13 016-05 07-15-2001 23:59:07 00:00:59 09678634321208 78634321208 005300 ^M (yes, that is a crtl-M at the end of the record, believe it or not :). If there is any other way of getting this data into the database, I would love to know about it. I have written a pretty complex routine to scan the phone number in the input record to figure out what country is called and then, within the country, which area was called. Country codes can be 1, 2 or 3 digits long. As you can see, this is not a simple 'import' where I simply use 'copy'. What it looks like I will do is to import this into a temp record, which I then cut to pieces, extracting the info I want. Once I have what I need to create the actual record, I can delete the temp record. Best regards, Chris on 07/17/2001 16:06, Tom Lane at [EMAIL PROTECTED] wrote: > "Chris Ruprecht" <[EMAIL PROTECTED]> writes: >> I need to know how I can access a flat file from within a PL/PGSQL script. > > You can't --- and you should ask yourself hard questions about why your > system design needs that, or whether the data in question shouldn't be > inside the database to begin with. Among other problems, data in a flat > file will not be under transactional control. That could cause such > interesting problems as a transaction that logically precedes another > one being able to see a later version of the flat file than the "later" > transaction sees. > > If you are absolutely intent on doing this, you could use a function > coded in C or in pltclu (unsecured pltcl). But there's no feature in > plpgsql to do it, and requests for one are not likely to meet with much > favor. > > regards, tom lane _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] PL/PGSQL and external (flat ASCII) files - Urgent ...:)
Hi Josh, on 07/17/2001 18:31, Josh Berkus at [EMAIL PROTECTED] wrote: > Chris, > > Hmmph. People are in a bad mood this week; obviously few other people > on the list have had to write applications for the banking industry, > which trades everything in flat files. Give Chris a break! > Thanks ;). I'm new to PostGreSQL and to PHP and to SQL - I have done Progress RDBMS/4GL for the last 12 years and it absolutely spoils you. But in the last 2 weeks, I have hammered out an application (you can have a look at http://chill-025.bbnow.net, use user demo and password demo). >>> From an input file where the records looks like this one: >> >> 020-13 016-05 07-15-2001 23:59:07 00:00:59 09678634321208 >> 78634321208 >> 005300 ^M > > The answer to your question is somewhat annoying, though: You can't use > PL/pgSQL for this task. Basically, two other PostgreSQL function > languages - PL/tcl and PL/perl - have excellent text-parsing ability. > As such, there is no movement affort to replicate that functionality in > PL/pgSQL. Well, I found a way. I created this table: Attribute | Type | Modifier ---++-- s | character varying(400) | type | character varying(10) | Index: i_dl2_type To which I copy (psql copy statement) two files. One contains s = entity code and type = 'entity', the others has a long string of data and NULL in the type. I found that pl/pgsql has the functions I need (btrim, substring, mod, date stuff) which are virtually identical to the statements in the Progress-4GL code. This allows me to get my data into the database pretty efficiently. Sorry, Tom, I can not use a text muncher, since I have to populate, for example the Pseq field with data, which comes out of another table to link these records together (a phone call is linked to an area code, which in turn is linked to a country, which is linked to a rate at which the call is charged, etc.) I can not go the perl or TCL route since I do this all in my spare time. A friend of mine needs this application for his business and we have plans to sell it on, once we have it completed. So, the only hacker who plays with this right now, is me, and me has no clue about Perl or TCL and although me would like to learn these languages one day, this applications need to be completed no later than this coming weekend. The pl/pgsql part is just a small portion of the whole thing. I still need to figure out a way to export data which I calculate to let gnuplot create pretty pictures for me. And today is the first day, I try pl/pgsql ... Today, the data import program must get finished ... Do I need to say more? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] TODO List
Bruce, while you're at TO-DO list additions: I'd like to have a construct like: loop if then next [loop]; end if; [more statements] end loop; I want to be able to skip to the next iteration of the loop, if a certain condition is met but I do not want to exit the loop all together. There doesn't seem to be functionality for that right now. Best regards, Chris - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> [...] > > Both LIMIT and OFFSET seem to have that restriction. I will add this to > the TODO list. > _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] TODO List
Josh, I would love to do something like that. Unfortunately, on a scale from 1 to 10, my C knowledge is about minus 5. Maybe, if my current project makes any profit and I don't have to work for a boss any longer, I might find some time to learn how to program in C and then, I might add the one or other thing. I'd love to see pl/pgsql develop into what I saw the other day in the Oracle PL/SQL book.. Best regards, Chris - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Bruce Momjian" <[EMAIL PROTECTED]>; "Chris Ruprecht" <[EMAIL PROTECTED]> Cc: "p-sql" <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 3:42 PM Subject: Re: [SQL] TODO List > Bruce, Chris, > > A lot of us would like a fuller PL/SQL implementation in PL/pgSQL. > However, Jan is busy with other things and I don't see anyone stepping > up to the plate to take on the project. > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Full-text Indexing and Primary Keys
Josh,
Full Text Indexing
It will allow you to store text into a database and find portions of this
text again, based on a few words of the text in the record.
Say, for example, you want to store the bible in a PostGres database. You
will store these fields:
1. Book
2. Chapter
3. Verse
4. Verse text
If you know your bible, you probably can find any passage by just going to
book/chapter/verse directly. But if you're not, and you just want to find
all verses, where "Nathan", "Solomon" and "mother" is mentioned, you need
something which lets you do that - and fast.
What you want is something which does:
select verse_text from bible where verse_text contains 'Nathan & Solomon &
mother';
or even:
select verse_text from bible where verse_text contains 'Nath* & Solo* &
moth*';
This would be similar to "find file on hard drive by content" - which, if
not indexed, takes forever.
Hope this makes the issue a little more clear.
Best regards,
Chris
- Original Message -
From: "Josh Berkus" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, July 23, 2001 10:32 AM
Subject: [SQL] Full-text Indexing and Primary Keys
> Folks,
>
> 1. Can anyone explain to me what "full-text indexing" is, and why we do
> or don't need it for Postgres? The marketing types keep asking me about
> it ("buzzword o' the day") and I don't have an answer for them.
>
> 2. I propose that future versions of PostgreSQL require a primary key at
> table creation. Frankly, I'm a little mystified as to why this was not
> done already, but it's not too late to correct ...
>
> -Josh
>
>
>
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Big table - using wrong index - why?
Hi all,
I have a table with about 6 million records in it.
I have 9 different indexes on the table (different people need to access it
differently)
If you look at the details below, you can see that it's selecting an index
which doesn't have the fields I'm searching with - and it takes for ever.
There is an index which does have the right fields but it's not being used.
I have done a re-index but it didn't help. How can I force it to use index
i_pl_pseq instead of i_pl_loadtimestamp?
Here are the details:
Table "phonelog"
Attribute | Type | Modifier
-+---+--
-
cdate | date | not null
ctime | time |
countrycode | integer |
areacodex | integer |
success | boolean |
carrier | integer |
duration| integer |
phonenumber | character varying(20) |
areacode| character varying(30) |
pseq| bigint|
loadno | bigint|
frline | integer |
entity | character varying(3) | not null
loaddate| date |
loadtime| time |
prefix | character varying(3) |
toline | integer |
dur306 | double precision |
dur180180 | double precision |
recno | bigint| default nextval('SEQ_phonelog'::text)
Indices: i_pl_carrier,
i_pl_date_country_carrier,
i_pl_date_line,
i_pl_entity_date,
i_pl_loadtimestamp,
i_pl_phoneno,
i_pl_prefix,
i_pl_pseq,
i_pl_recno
phones=# \d i_pl_pseq
Index "i_pl_pseq"
Attribute | Type
---+--
entity| character varying(3)
pseq | bigint
btree
phones=# explain select * from phonelog where entity = '001' and pseq >=
9120 and pseq <= 9123;
NOTICE: QUERY PLAN:
Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39
rows=607 width=137)
EXPLAIN
phones=# \d i_pl_loadtimestamp
Index "i_pl_loadtimestamp"
Attribute | Type
---+--
entity| character varying(3)
loaddate | date
loadtime | time
btree
Best regards,
Chris
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
---(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] Big table - using wrong index - why?
Hi Joe, I found the problem - it was a typical "rrrhhh" - error. Since pseq is declared int8, I need to say select * from phonelog where entity = '001' and pseq >= 9120::int8 and pseq <= 9123::int8; (casting the two numbers). Then, it works like a charm ... Best regards, Chris - Original Message - From: "Joe Conway" <[EMAIL PROTECTED]> To: "Chris Ruprecht" <[EMAIL PROTECTED]>; "pgsql" <[EMAIL PROTECTED]> Sent: Monday, July 30, 2001 11:43 AM Subject: Re: [SQL] Big table - using wrong index - why? > > phones=# \d i_pl_pseq > > Index "i_pl_pseq" > > Attribute | Type > > ---+-- > > entity| character varying(3) > > pseq | bigint > > btree > > > > phones=# explain select * from phonelog where entity = '001' and pseq >= > > 9120 and pseq <= 9123; > > NOTICE: QUERY PLAN: > > > > Index Scan using i_pl_loadtimestamp on phonelog (cost=0.00..209247.39 > > rows=607 width=137) > > > > EXPLAIN > > > > phones=# \d i_pl_loadtimestamp > > Index "i_pl_loadtimestamp" > > Attribute | Type > > ---+-- > > entity| character varying(3) > > loaddate | date > > loadtime | time > > btree > > Just a guess, but what happens if you build i_pl_pseq(pseq, entity), i.e. > reverse the key fields? Also, has the table been vacuum analyzed? > > -- Joe > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] More Database Design Stuff
Well, consider this: You have a customer list, each customer with his own unique customer number. Each of your customers has at least one address but some of them have 2 or more. Therefore, you have to create two tables, say one is called 'cust' the other 'cust_addr'. The 'cust_addr' table now needs to have a unique primary key, for which the cust_no alone doesn't qualify, so you need to have a second column, say you decide on 'addr_type' and allow for 'I' - Invoice or 'D' - Delivery (very simplified example). You can now create a unique primary key on this table on two columns, 'cust_no' and 'addr_type'. I don't really care if people say it's bad to do this kind of thing, I like to tell them that I will do what works for me, and there is usually not much to argue about that point ;). Best regards, Chris - Original Message - From: "Jimmie Fulton" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 02, 2001 3:23 PM Subject: [SQL] More Database Design Stuff > This may seem like a newbie type of question: for what reason would you > need a multi-column primary key. None of the books that I have read explain > why or why not. It seems that the more normalized your database is, the > less need for multi-column primary keys. Are multi-column primary keys > considered bad form? I have never needed to use them, but I happen to use > auto-incrementing integer primary keys on all tables, as discussed earlier > this week. Any thoughts on this subject is appreciated. > > > Jimmie Fulton > Systems Administrator > Environmental Health & Safety Office > Emory University School Of Medicine > > > ---(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 _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] Functions returning more than one value
Hi all, How can I get more than one value back from a function? I have a situation here, where a function needs to return a value - but also needs to indicate from which type of record the value comes. The most elegant would be something like the 2 functions listed below. They don't work, since v_val3 and v_val4 are treated as constants in test2() and can not be changed. Is there a way this can make it into a future release? Best regards, Chris dropfunctiontest1(); create functiontest1() returns integer as ' declare v_res boolean; v_val1 integer; v_val2 integer; begin v_val1 := 1; v_val2 := 2; v_res := test2( v_val1, v_val2 ); return v_val1 + v_val2; end; ' language 'plpgsql'; dropfunctiontest2(integer, integer); create functiontest2(integer, integer) returns boolean as ' declare v_val3 alias for $1; v_val4 alias for $2; begin v_val3 := 3; v_val4 := 4; return true; end; ' language 'plpgsql'; _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Functions returning more than one value
Josh, the two functions are just a tiny example of what I want to do which is: call a function with 2 or more arguments (v_val1 and v_val2). The called function (test2() in the example) will MODIFY the two arguments and then return a true value. After test2() has run, the value of the two arguments 'has changed' to what test2() has assigned to them. I know that this might not be good programming practice since many applications rely on functions NOT changing the variables which get passed to them as arguments. maybe if we could specially declare them as 'changeable' parameters ... The functionality of 'returns record' is nice to have but it would not help me in this case. I would have to create a record each time I want to pass more than one value back to the calling procedure. This means 'disk access' which is another word for 'this is going to slow down my program'. But this reminds me: where are temp tables kept? On disk or in memory? - that might be a way out of the dilemma. Best regards, Chris - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: "Chris Ruprecht" <[EMAIL PROTECTED]> Sent: Wednesday, August 08, 2001 10:06 AM Subject: Re: [SQL] Functions returning more than one value > Chris, > > > How can I get more than one value back from a function? > > I assume that you're looking for a workaround here. You've been on the > list long enough to know that we're all waiting for record-returning > ability in 7.2, 7.3 or more likely 8.0. > > > The most elegant would be something like the 2 functions listed > > below. They > > don't work, since v_val3 and v_val4 are treated as constants in > > test2() and > > can not be changed. Is there a way this can make it into a future > > release? > > I'm *really* confused. These functions seem to have nothing to do with > your first question. Mind documenting, line-by-line, what you're trying > to do with the two functions you provided? I can't puzzle it out from > your code. > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(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] Temp tables being written to disk. Avoidable? [Another TO-DO]
(Maybe this thread is dead now, but here goes anyway) Suggestion: have an environment variable or a PostgreSQL parameter to indicate where to write the temp-table to. This way, you could easily direct it to a RAM disk (if small enough) or to a drive other than your main database. Default could be $TMP. Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] More on the TO DO wishlist
I did some pl/pgsql this morning and forgot a ';' at the end of a line. The result was, that the compiler complained about a wrong statement in line 304 - which is at the end of the program. The other error I made was that I used a new record without defining the record first. This, the program only detected, when it first tried to use the new record (select into xxx * from yyy...). Can the parser be changed to be a little more intelligent about it's error reporting, and can it be changed to check if all variables, records, etc. have been defined before the program runs? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] challenging query
Consider the following table: A B C D select? --- 1 FOO A1 100 n 1 BAR Z2 100 n 2 FOO A1 101 y 2 BAR Z2 101 y 3 FOO A1 102 y 4 BAR Z2 99 y 5 FOO A1 99 n 6 BAR Z2 98 n 7 FOO AB 103 y 7 BAR ZY 103 y This table has the idea of "groups", that is, a group is defined as all of the words from B that have the same number A. The values in column C also matter- we want to select both groups A=7 and A=1 since they contain different values C. Note that the groups defined by A=1 and A=3 are distinct- they do not contain the same number of words from B, so we want to select them both. Also note that D is datetime, and all the rows with the same number A will have the same D (this is actually ensured by a single row in another table.) I want to select all of the numbers A which define distinct groups and have the highest datetime D. Is this possible in a SQL query? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Selecting latest value II
what about using 'distinct' in you select statement? - Original Message - From: "Haller Christoph" <[EMAIL PROTECTED]> To: "Patrik Kudo" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, September 20, 2001 11:02 AM Subject: Re: [SQL] Selecting latest value II > What do you mean by > "the latest val for each userid" > I cannot understand how a value of type integer > can have a attribute like "latest". > Sorry, but I need at least a bit more information. > Regards, Christoph > > > > On Thu, 20 Sep 2001, Haller Christoph wrote: > > > > > Try > > > create NEWtable (userid text, val integer, ts timestamp); > > > insert into NEWtable > > > select userid, val, max(ts) from table group by userid, val; > > > > That won't work. That will give me multiple userid-val combinations. Sure, > > the userid-val combinations will be unique, but I want unique userids > > with only the latest val for each userid. > > > > /Patrik Kudo > > > > > > > > > > Hi, > > > > > > > > I have a table which basically looks like this: > > > > > > > > create table (userid text, val integer, ts timestamp); > > > > > > > > This table holds multiple values for users, timestamped for history > > > > reasons. > > > > > > > > Now I need to fetch the latest val for each userid to insert into a new > > > > table (with about the same schema, except for uniqueness on userid). > > > > I belive this should be a trivial task, but I'm experience total lack of > > > > insight here... > > > > > > > > Comments? > > > > > > > > /Patrik Kudo > > > > > > > > > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Search by longitude/latitude
Hi all, I need to implement "Find all hospitals in a 5 mile radius". Say I have all the coordinates on them stored in a table with the fields longitude and latitude. Has anybody some sample code for that? Best regards, Chris _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] sql question:
hi all: I'm trying to figure out SQL to do the following: I have an application that tracks SQL that is being sent to the database, and one of it's features is the ability to identify whether a query is an insert, update, delete, select, select with all rows returned, the query is the first in a user sessionand many other criteria. Because of the nature of SQL, i.e. many of the above could be true, the deisgners made each flag a 'bit'. So an example is: 4 is a select 8 is insert 16 is update 32 is first query in session 64 is delete 128 is a cancelled query 256 is database cancelled query Now the SQL that I have to find is 'which of these records is a delete?' The values could be 64, 96, 416, 445, 320 and many others. All in all there are probably 20 possible values and the permutations are to lengthy to put in a 'like', so I need some kind of algorithm. Does anyone have any ideas? email: [EMAIL PROTECTED] thanks, Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Last record
Select * from where <...> desc limit 1; Desc = from the bottom up, limit 1 = just one record. Best regards, Chris On Wednesday 24 July 2002 10:36 am, Leao Torre do Vale wrote: > Dear Sir, > > If you already have the answer of the question below please, send to me. > > Best Regards > > Leao > > Maputo - Mozambique > > How can select one field of last > record of table? > > (ex: SELECT LAST ) > > Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] 1 milion data insertion
> > Ok, this was a test. I'd like to know what would be happen. > But, from you, great PostGres DBA's, what is the best way to > insert a large number of data? > Is there a way to turn off the log? > Is there a way to commit each 100 records? Yes, "COPY" actually does an append. So just do what you do now 10,000 times for 100 records. It's a bit safer. I've done 1M recod COPYsmany times on a low-end PC, no trouble Put the log file someplace with more room. You should be able to run for a month without worrying about log files over filling Logging is controled likely from the startup script. Maybe in /etc/rc.d details depend on your OS. = Chris Albertson Home: 310-376-1029 [EMAIL PROTECTED] Cell: 310-990-7550 Office: 310-336-5189 [EMAIL PROTECTED] __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com ---(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] FIFO Queue Problems
I'm having a race condition with a FIFO queue program that I've created... CREATE TABLE fifo ( id serial, data varchar(100), status int4 DEFAULT 0 ); I have a program that claims a row for itself my $processid = $$; my $sql_update =
[SQL] Using VIEW to simplify code...
Right now I dynamicly generate the SQL for an incredibly ugly 4 table join based on user information client-side. I got the bright idea to create a view and then run a MUUUCH simpler client-side query on that view. The problem is that PostgreSQL apparantly runs the view FIRST and then applies the criteria AFTER assembling the whole view. I was hoping that it would rewrite the "select" in the view to include the criteria BEFORE running the view. That way it could take advantage of the indexes the way my giant-and-hard-to-maintain-client-generated-sql does. Any thoughts or suggestions? If you MUST have the giant-and-hard-to-maintain-client-generated-sql statement and its related explain, I'll produce it. I cringe at the thought of having to redact that monster, tho. CG __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Using VIEW to simplify code...
> If you want help, you must provide details. The PG version number is > relevant also. Understood. PostgreSQL 7.2.3. Here's the generated client side sql: select case when (hasflag(ut.flags,1) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data like '%RNF'then ' ' else 'Free' end else case when trl.trans_data like '%RNF' then ' ' else case when ct.misc_charge = '0'::money then 'Free' else 'View for ' || to_char(ct.misc_charge::float8,'FM$9990D90') || '' end end end as " ", trl.trans_date::timestamp(0) as "Date", tl.longtype as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged", trl.user_reference_id as "Reference ID" from trans_log_1 trl, addtypelong tl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) and (tl.shorttype=trl.trans_type) ) union all select case when (hasflag(ut.flags,16) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data like '%RNF' then ' ' else 'Free' end else case when trl.trans_data like '%RNF' then ' ' else case when ct.misc_charge = '0'::money then 'Free' else 'View for ' || to_char(ct.misc_charge::float8,'FM$9990D90') || '' end end end as " ", trl.trans_date::timestamp(0) as "Date", tl.longtype as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged", trl.user_reference_id as "Reference ID" from trans_log_2 trl, addtypelong tl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) and (tl.shorttype=trl.trans_type) ) union all select case when trans_type = 'NS' then ' ' else case when (hasflag(ut.flags,16) or (current_timestamp - '1 day'::interval < trl.trans_date)) then case when trl.trans_data like '%RNF' then ' ' else 'Free' end else case when trl.trans_data like '%RNF' then ' ' else case when ct.misc_charge = '0'::money then 'Free' else ' ' end end end end as " ", trl.trans_date::timestamp(0) as "Date", case when trl.trans_type = 'NS' then 'Name' else 'Archive: ' || tl.longtype end as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge::float8,'FM$9990D90') as "Charged", trl.user_reference_id as "Reference ID" from trans_log_3 trl, addquerytype tl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) and (tl.querytype=trl.trans_type) ) union all select case when (fdf is null or fdf='') then ' ' else 'Free' end as " ", trl.trans_date::timestamp(0) as "Date", 'FORM: ' || trl.trans_type as "Type", trl.trans_data as "Query Data", to_char(trl.trans_charge, 'FM$9990D90') as "Charged", user_reference_id as "Reference ID" from trans_log_4 trl, user_table ut, company_table ct where ((trl.username='myuser') and (trl.username=ut.username) and (ut.company_name=ct.company_name) and (trl.trans_date >= current_timestamp-'60 days'::interval) ) order by 2 desc, 4 LIMIT 20 OFFSET 0; Explain: Limit (cost=4339.83..4339.83 rows=20 width=158) -> Sort (cost=4339.83..4339.83 rows=285 width=158) -> Append (cost=2477.60..4328.19 rows=285 width=158) -> Subquery Scan *SELECT* 1 (cost=2477.60..2578.56 rows=187 width=157) -> Hash Join (cost=2477.60..2578.56 rows=187 width=157) -> Seq Scan on company_table ct (cost=0.00..80.41 rows=1041 width=32) -> Hash (cost=2477.13..2477.13 rows=187 width=125) -> Hash Join (cost=287.56..2477.13 rows=187 width=125
Re: [SQL] Using VIEW to simplify code...
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 19 Nov 2002, Chris Gamache wrote: > >> Understood. PostgreSQL 7.2.3. > > > 7.3 will be better for this. There were questions about the safety > > of pushing clauses down in queries with union and intersect and > > earlier versions wouldn't push down because we weren't sure it was > > safe. Except will still be problematic, but union and intersect should be > > much happier. > > Yeah, the UNIONs in the view are definitely the big problem. Can you > try on 7.3rc1 and see how it goes? That's something to look forward to! I'm going to have to hold off upgrading until y'all feel like 7.3rc1 should become 7.3.0. I wish we had a development environment to use, but we don't. I'll let you know how things fare after that. CG __ Do you Yahoo!? Yahoo! Web Hosting - Let the expert host your site http://webhosting.yahoo.com ---(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] Problem with a lookup table! Please help.
Hi, In my capacity as a vet student, I'm trying to create a database of antibiotics. The way that I have set it up so far is to have one main table listing the antibiotics versus their respective efficacies against the four major groups of bacteria. Due to the way that my PHP frontend works, I have assigned a number to the efficacy - 1 being excellent and 5 being poor efficacy against the particular bacterium. However, I now want to have a new table which converts numbers into words. The problem is this, if I join the main table with the "translation" lookup table, the column names for each of the four categories in the main default to the column name in the lookup table and hence are all the same. What SQL expression should I use to translate the cryptic numbers into plain english whilst preserving the column headings in the main table? Regards, Chris J
Re: [SQL] Problem with a lookup table! Please help.
Hi,
Thanks for your reply. Table definitions are:
CREATE TABLE tblantibiotics ('Antibiotic'
varchar(50),'Activity_against_grampos' int,'Activity_against_gramneg'
int,'Activity_against_aerobes' int,'Activity_against_anaerobes' int);
CREATE TABLE efficacy ('Efficacy_code' int,'Plain_english' varchar (10));
In table efficacy, 'Efficacy_code' contains a 1 - 5 scale of efficacy. Each
string in 'Plain_english' is a plain English descriptor of the value in
'Efficacy_code'. For example, 1 => Excellent, 2=> Good, etc etc. What I
need to do is to substitute the integers set in the "Activity..."
columns in tblantibiotics with the strings in 'Plain_english' according to
the integer set in 'Efficacy_code'. Any ideas?
Chris J
- Original Message -
From: "Josh Berkus" <[EMAIL PROTECTED]>
To: "Chris Jewell" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Monday, December 09, 2002 9:08 PM
Subject: Re: [SQL] Problem with a lookup table! Please help.
Chris,
> In my capacity as a vet student, I'm trying to create a database of
antibiotics. The way that I have set it up so far is to have one main table
listing the antibiotics versus their respective efficacies against the four
major groups of bacteria. Due to the way that my PHP frontend works, I have
assigned a number to the efficacy - 1 being excellent and 5 being poor
efficacy against the particular bacterium. However, I now want to have a
new
table which converts numbers into words. The problem is this, if I join the
main table with the "translation" lookup table, the column names for each of
the four categories in the main default to the column name in the lookup
table and hence are all the same. What SQL expression should I use to
translate the cryptic numbers into plain english whilst preserving the
column
headings in the main table?
Please post your table definitions as SQL statements.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] A Costly function + LIMIT
PostgreSQL 7.2.3 I have a function that is quite costly to run on 1000's of records... Let's call it "widget". I have a query (SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM eastern_usa ORDER BY state, city, zip, name LIMIT 5000) UNION ALL (SELECT name, address, city, state, zip, widget(name, address, city, state, zip) FROM western_usa ORDER BY state, city, zip, name LIMIT 5000) ORDER BY 4, 3, 5, 1 LIMIT 100 OFFSET 0; It runs "widget" on 1 records. The damage would be negligible if it could run on the 100... Any ideas? CG __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(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] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied.
Try running the initdb program first. Best Wishes, Chris Travers - Original Message - From: "Zengfa Gao" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, January 08, 2003 9:43 AM Subject: [SQL] PgSQL 7.3: /opt/pgsql/bin/postgres: Execute permission denied. > Hi, > > I download PgSQL source, compiled it, then try to > start pgsql, I got: > > > # su postgres -c '/opt/pgsql/bin/initdb > --pgdata=/var/opt/pgsql/data' > The program > '/opt/pgsql/bin/postgres' > needed by initdb does not belong to PostgreSQL version > 7.3, or > there may be a configuration problem. > > This was the error message issued by that program: > /opt/pgsql/bin/initdb[135]: /opt/pgsql/bin/postgres: > Execute permission denied. > > I check the permission of postgres: > # ls -l /opt/pgsql/bin/postgres > -rwxr-xr-x 1 root bin2994176 Jan 8 > 09:53 /opt/pgsql/bin/postgres > > But same code works fine on my another HPUX 11.11 > system. File permission is same. > > Does anyone have some ideas? > > Thanks! > > Zengfa > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Race condition w/ FIFO Queue reappears!
Tom's suggested solution worked GREAT in 7.2.x ... I tried taking the plunge into 7.3.1 tonight. In 7.3.1, when my FIFO queue program goes to grab a row, TWO processes grab the same row, almost without fail. I even changed my locking statement to the dreaded LOCK TABLE fifo IN ACCESS EXCLUSIVE MODE; it still exhibits the same behavior. I've tried variations on the theme, but I can't seem to figure it out. I'm stumped! The postgresql configuration is as identical (IMO) as I could possibly make it considering the changes from 7.2 to 7.3. I can't imagine a config option would control something so basic. I can't find any reference to it in the 7.3 docs, and my tired eyes did not pick any fixes remotely pertaining to this type of locking problem in the HISTORY file. I'm (sadly) switching back to 7.2 until we can figure this out. CG >Chris Gamache <[EMAIL PROTECTED]> writes: >> I have a program that claims a row for itself >> my $processid = $$; >> my $sql_update = <> UPDATE fifo >> set status=$processid >> WHERE id = (SELECT min(id) FROM fifo WHERE status=0); >> EOS >> The problem occurrs when two of the processes grab the exact same row at the >> exact same instant. > >Probably the best fix is to do it this way: > > BEGIN; > LOCK TABLE fifo IN EXCLUSIVE MODE; > UPDATE ... as above ... > COMMIT; > >The exclusive lock will ensure that only one process claims a row >at a time (while not preventing concurrent SELECTs from the table). >This way you don't need to worry about retrying. > > regards, tom lane __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Splitting text into rows with SQL
Using Postgresql 7.2.3 ...
In order to search using indexes I need to split a composite field into its
components and return it as rows... If this would only work:
create table table_with_composite_fields (
data1 serial,
data2 varchar(100),
composite_field text
);
insert into table_with_composite_fields (data2, composite_field) values
('something1','---,---,---');
create table other_table (
data3 serial,
data4 varchar(100),
uuid uniqueidentifier
);
create index 'other_table_uuid_idx' on other_table(uuid);
insert into other_table (data4, uuid) values
('something2','---');
insert into other_table (data4, uuid) values
('something3','---');
insert into other_table (data4, uuid) values
('something4','---');
select * from other_table ot where ot.uuid in (select split(composite_field)
from table_with_composite_field where data1=1) order by data3;
data3 | data4| uuid
---++-
1 | something2 | ---
2 | something3 | ---
3 | something4 | ---
any ideas for creating my fictional "split" function? I don't mind if the
solution is head-slapping-ly simple. I'm too close to the problem and can't
seem to figure it out!
CG
__
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Elegant SQL solution:
There are so many (bad) ways to skin this cat... I'm looking for a more elegant
solution.
If I
SELECT date_part('month',rowdate) as month, count(*) as rows FROM mytable GROUP
BY month;
It might only return
month | rows
---+--
1 | 234
3 | 998
4 | 403
5 | 252
10| 643
12| 933
I would like:
month | rows
---+--
1 | 234
2 | 0
3 | 998
4 | 403
5 | 252
6 | 0
7 | 0
8 | 0
9 | 0
10| 643
11| 0
12| 933
I could create a one-column table with values 1 - 12 in it, and select from
that table with a where clause matching "month". I could also create a view
"SELECT 1 UNION SELECT 2 UNION ..." and select against the view. There MUST be
a more elegant way to do this.
Any thoughts?
__
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Inquiry From Form [pgsql]
I know this is basic, but couldn\'t find and in a hurry to know the answer. When interfacing with PostgreSQL through PSQL, it appears that DML statements are auto-commited, that is, a change I make in one session is seen from another without the original session issueing a COMMIT. Is this a result of PSQL interface and if so, can it be turned off. Is PostgreSQL transactional in the sense that I can issue several DMLs and then ROLLBACK. If so, how. Thanks and sorry for the newbie question. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to determine the currently logged on username
Hi all; I will be writing a stored proceedure that will allow a currently logged in user to change his/her password. The function needs to be only able to change the password of the currently logged in user, so it will only take a varchar() argument and needs to look up the username of the currently logged in user. How do I do this? Any ideas? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] SECURITY DEFINER changes CURRENT_USER?
Hi all; I found an unexpected behavior while trying to write a function to allow users to change their own passwords. The function is as follows: CREATE OR REPLACE FUNCTION change_password(VARCHAR) RETURNS BOOL AS ' DECLARE username VARCHAR; CMD VARCHAR; password ALIAS FOR $1; BEGIN SELECT INTO username CURRENT_USER; CMD := ''ALTER USER '' || username || '' WITH PASSWORD ''; CMD := CMD || '''''''' || password || ''''''''; EXECUTE CMD; RETURN TRUE; end; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER I would expect this to change the password of the user currently logged in but instead it changes MY password. Evidently when a function is called which is set to SECURITY DEFINER, it changes the context of the current user. The CURRENT_USER then returns the name of the definer rather than the invoker of the function. So this being said-- are there any workarounds that don't allow anyone to change anyone else's password? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] length of array
Hello Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned. Thanks Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] length of array
Hello Thanks for that solution, Joe - nice use of nested functions ! Related to this problem, I want to constrain a selection using elements of this variable length array. I want to constrain where all elements of the array are 0. I would like to do it like this in Oracle select field from table N where [conditions] and NVL(N.level[1],0) = 0 and NVL(N.level[2],0) = 0 and NVL(N.level[3],0) = 0 and NVL(N.level[4],0) = 0 So if a row only has two elements in the array, but the first two both had values "0", then the row would return. At the moment, I have this : and N.level[1] = 0 and N.level[2] = 0 and N.level[3] = 0 and N.level[4] = 0 but my row with 2 elements in the array won't be returned with this condition. Chris -Original Message- From: Joe Conway [mailto:[EMAIL PROTECTED] Sent: 28 August 2003 01:40 To: Chris Faulkner Cc: [EMAIL PROTECTED] Subject: Re: [SQL] length of array Chris Faulkner wrote: > Is there a function in postgres to return the length of an array field ? I > have seen array_dims(array) but this returns a character value. Ideally, I'd > like something numeric returned. > Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least): SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl; SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl; In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for: regression=# select array_lower(array_fld, 1) from tbl; array_lower - 1 (1 row) regression=# select array_upper(array_fld, 1) from tbl; array_upper - 2 (1 row) See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTA X-ARRAY-CONSTRUCTORS HTH, Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] BEFORE UPDATE Triggers
PostgreSQL Version: 7.2.3 Procedural Language: PL/pgSQL I have a table which contains a field for the user who last modified the record. Whenever a row in this table is updated, I want to have an UPDATE trigger do the following things: 1) Ensure the UPDATE query supplied a value for the action_user column 2) Log the record to an audit table so I can retrieve a change log Part 2 was trivial, however it seemed natural that if I had the following conditional in the trigger function: IF NEW.action_user ISNULL THEN ... I could raise an exception if that field was not supplied. (which would be the case if the function were triggered on an INSERT) Unfortunately it seems this is not the case. The NEW record contains values representing both the values explicitly provided with the UPDATE as well as the existing values which were not stipulated in the query. Is there any clever way around this limitation? It isn't the end of the world if I cannot verify this constraint in postgres, however it would have made it easier to ensure no one is making mistakes. Oh, and I am aware of the current_user variable. In my case this is useless as I don't care about the user at the database layer but rather at the application layer. Thanks in advance, cva ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] undefine currval()
I'm using sequences and currval() to retrieve the last inserted row in a table. If currval() is undefined, as it is when a connection is made, then I know no rows were inserted in that table and can take a different action. This is problematic when using a connection pooling library, as the value of currval() for any given sequence could possibly be set from a previous "connection". One (theoretical) workaround would be to issue some sort of command to the back-end database to wipe all values of currval() when a "new" connection is made. I've done some digging in the system tables and source code, and can't find an obvious solution. Perhaps one you you gurus can suggest a SQL statement to do such a thing. Alternately, if there is a better way to retrieve the last inserted row for any given table, I'd be very grateful for the tip. It would need to be independent of the connection history, and undefined if there has not been a row inserted to the table during a definable interval of time (drop anchor when the "connection" begins, raise anchor when the "connection" ends), and be independant of the other connections inserting rows to the same table. Any idaeas? CG __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(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] create new field
Hello I would like to change the type of a column. At the moment, it is varchar(4) but I would like it to be int. All values in the field at the moment are actually integer. I tried a way I had seen in the archives - it foes along the lines of adding a column, using update, drop the old column and rename the new one. alter table tab add column new_col int4; update tab set new_col = "OLD_COL"; ERROR: column "new_col" is of type integer but expression is of type characte r You will need to rewrite or cast the expression OK - so I tried casting. template1=# update tab set new_col = "OLD_COL"::int4; ERROR: Cannot cast type character to integer I understand this - some tables might have characters in the varchar but how to get around it in my case ? I know that my character field has only integers in it ? Thanks Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] monitor sessions
Hello Can anyone tell me - is there a system table or view that I can query to show all current sessions ? Thanks Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] sql performance and cache
Hello all
I have two very similar queries which I need to execute. They both have
exactly the same from / where conditions. When I execute the first, it takes
about 16 seconds. The second is executed almost immediately after, it takes
13 seconds. In short, I'd like to know why the query result isn't being
cached and any ideas on how to improve the execution.
The first query attempts to find the maximum size of an array in the result
set- the field is called "level". IT contains anything between 1 and 10
integers. I just need to know what the largest size is. I do this to find
out the maximum size of the "level" array.
"max(replace(split_part(array_dims(level),':',2),']','')::int)"
I know this is big and ugly but is there any better way of doing it ?
The second query just returns the result set - it has exactly the same
FROM/Where clause.
OK - so I could execute the query once, and get the maximum size of the
array and the result set in one. I know what I am doing is less than optimal
but I had expected the query results to be cached. So the second execution
would be very quick. So why aren't they ? I have increased my cache size -
shared_buffers is 2000 and I have doubled the default max_fsm... settings
(although I am not sure what they do). sort_mem is 8192.
The from / where is
FROM oscar_node N, oscar_point P
where N."GEOM_ID_OF_POINT" = P."POINT_ID"
and N."TILE_REF" = P."TILE_REF"
and N."TILE_REF" in ('TQ27NE','TQ28SE','TQ37NW','TQ38SW')
and P."TILE_REF" in ('TQ27NE','TQ28SE','TQ37NW','TQ38SW')
and P."FEAT_CODE" = 3500
and P.wkb_geometry && GeometryFromText('BOX3D(529540.0 179658.88,530540.0
180307.12)'::box3d,-1)
oscar_node and oscar_point both have about 3m rows. PK on oscar_node is
composite of "TILE_REF" and "NODE_ID". PK on oscar_point is "TILE_REF" and
"POINT_ID". The tables are indexed on feat_code and I have an index on
wkb_geometry. (This is a GIST index). I have increased the statistics size
and done the analyze command.
Here is my explain plan
Nested Loop (cost=0.00..147.11 rows=1 width=148)
Join Filter: ("inner"."GEOM_ID_OF_POINT" = "outer"."POINT_ID")
-> Index Scan using gidx_oscar_point on oscar_point p (cost=0.00..61.34
rows=1 width=57)
Index Cond: (wkb_geometry && 'SRID=-1;BOX3D(529540 179658.88
0,530540 180307.12 0)'::geometry)
Filter: ((("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bpchar) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" =
'TQ38SW'::bpchar)) AND ("FEAT_CODE" = 3500))
-> Index Scan using idx_on_tile_ref on oscar_node n (cost=0.00..85.74
rows=2 width=91)
Index Cond: (n."TILE_REF" = "outer"."TILE_REF")
Filter: (("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bpchar) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" =
'TQ38SW'::bpchar))
I am seeing this message in my logs.
"bt_fixroot: not valid old root page"
Maybe this is relevant to my performance problems.
I know this has been a long message but I would really appreciate any
performance tips.
Thanks
Chris
---(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
Re: [SQL] sql performance and cache
Hello
Thanks for the reply.
> The short answer is that PG doesn't cache query results. The only
> way it could
> do so safely is to lock all tables you access to make sure that no other
> process changes them. That would effectively turn PG into a
> single-user DB in
> short notice.
I am not sure I agree with you. I have done similar things with Oracle and
found that the second query will execute much more quickly than the first.
It could be made to work in at least two scenarios
- as a user/application perspective - you accept that the result might not
be up-to-date and take what comes back. This would be acceptable in my case
because I know that the tables will not change.
OR
- the database could cache the result set. If some of the data is changed by
another query or session, then the database flushes the result set out of
the cache.
> I assume these two queries are linked? If you rely on the max size being
> unchanged and have more than one process using the database, you
> should make
> sure you lock the rows in question.
I can rely on the max size remaining the same. As I mentioned above, the
tables are entirely read only. The data will not be updated or deleted by
anyone - I don't need to worry about that. The data will be updated en masse
once every 3 months.
> There is a discussion of the postgresql.conf file and how to tune it at:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
Thanks for that.
> Given the explain attached, 16 secs seems slow. Could you post an EXPLAIN
> ANALYSE of either/both queries to the performance list. I'd drop
> the sql list
> when we're just talking about performance.
To be honest, my main concern was about the cache. If the second one could
use a cache amd execute in 2 seconds, that would be better that reducing the
execution of each individual query by 30% or so.
Thanks for the offer of help on this one. explain analyze gives me the same
as the last message - did you want verbose ?
Nested Loop (cost=0.00..147.11 rows=1 width=148) (actual
time=84.00..12323.00 rows=67 loops=1)
Join Filter: ("inner"."GEOM_ID_OF_POINT" = "outer"."POINT_ID")
-> Index Scan using gidx_oscar_point on oscar_point p (cost=0.00..61.34
rows=1 width=57) (actual time=0.00..9.00 rows=67 loops=1)
Index Cond: (wkb_geometry && 'SRID=-1;BOX3D(529540 179658.88
0,530540 1
80307.12 0)'::geometry)
Filter: ((("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bp
char) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" = 'TQ38SW'::bpchar))
AND
("FEAT_CODE" = 3500))
-> Index Scan using idx_on_tile_ref on oscar_node n (cost=0.00..85.74
rows=2 width=91) (actual time=0.06..150.07 rows=4797 loops=67)
Index Cond: (n."TILE_REF" = "outer"."TILE_REF")
Filter: (("TILE_REF" = 'TQ27NE'::bpchar) OR ("TILE_REF" =
'TQ28SE'::bpchar) OR ("TILE_REF" = 'TQ37NW'::bpchar) OR ("TILE_REF" =
'TQ38SW'::bpchar))
Total runtime: 12325.00 msec
(9 rows)
Thanks
Chris
---(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] Query planner: current_* vs. explicit date
PsotgreSQL 7.2.4: Query planner is behaving strangely. It operates differently for explicit dates and derived dates... any ideas on why? ( and why I might not have noticed this before... ) CREATE TABLE trans_table ( id serial, user_name varchar(50), trans_type varchar(50), trans_data varchar(50), trans_date timestamptz, trans_uuid uniqueidentifier, CONSTRAINT trans_table_pkey PRIMARY KEY (id) ) WITH OIDS; ... Insert lots of data ... CREATE INDEX trans_table_date_idx ON trans_table USING btree (trans_date); CREATE INDEX trans_table_user_date_idx ON trans_table USING btree (user_name,trans_date); CREATE INDEX trans_table_uuid_idx ON trans_table USING btree (trans_uuid); VACUUM ANALYZE trans_table; EXPLAIN SELECT id FROM trans_table WHERE trans_date >= current_date::timestamp; Seq Scan on trans_table (cost=0.00..177369.52 rows=315267 width=4) EXPLAIN SELECT id FROM trans_table WHERE trans_date >= '10/22/2003 00:00:00 AM'::timestamp; Index Scan using trans_table_date_idx on trans_table (cost=0.00..1474.69 rows=417 width=4) CG __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query planner: current_* vs. explicit date
Thanks Tom (and others!) Right-on-the-money, as always... By giving it a definitive range I was able to coax query planner to use the index: SELECT id FROM trans_table WHERE trans_date >= (SELECT current_date::timestamptz) AND trans_date < (SELECT current_timestamp); gave me from midnight to the present... Aside from a slight amount of ugliness, the solution is quite adequate. The subselects shouldn't cause too much overhead, yes? BTW, This didn't work: SELECT id FROM trans_table WHERE trans_date >= current_date::timestamptz AND trans_date < current_timestamp; Which was a "nonconstant" version of the above. I think it still suffers from the timestamp >= unknown_value problem. CG --- Tom Lane <[EMAIL PROTECTED]> wrote: > being careful that both comparison values are nonconstant (don't use > 'infinity'::timestamp, for instance, even though that might seem like > a reasonable thing to do). The planner still has no idea how many rows > will be fetched exactly, but it does realize that this is a range > condition, and its default assumption about the number of matching rows > is small enough to encourage indexscan use. > > Of course this workaround assumes that you can pick an upper bound that > you are sure is past the end of the available values, but that's usually > not hard in the sort of context where you would have thought that the > one-sided inequality test is a sane thing to do anyway. __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(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] [HACKERS] Schema boggle...
Hi Marc, I was actually leaning towards schema's as a method to partition the data out when I was beginning to plan AMS 2, your suggestions of schema's confirmed this as a good approach for what we were looking for so I started to code the initial layout for the Schema in April/May, but at the time I did not have the full picture in my head on dealing with some sections of the database To address Josh's comments though. Monolithic tables have their uses, I don't and wont contest that, however we've been using a monolithic structure in our current AMS distribution and in a lot of areas it has been quite a pain to try and bring some uniformity into the structure. First off, I have an ever changing platform and one of the issues I had was that development under the monolithic structure because problematic and very difficult to merge new elements in, granted the SQL schema was not all that great and a number of areas could have used improvement, but over all it was more trouble then it was worth. By breaking out our client content into a schema structure, I've simplified the management of my data immensely, I can easily delete old/unused data with out having to worry about updating scripts that have been written to clean out structures. I can give clients greater control over their own data with out worrying about some "security" issue that I might have missed in my programming, so there's and added level of safety in an already tight structure. I've "objectified" out the content into an easily changeable/"update friendly" system of tables, views and functions. I've reduced a fair amount of overhead by limiting the order of tables, For instance, with our original monolithic dataset, we have approx 90+ tables handling all of the content that we use on a regular basis. With this new structure I've reduced that down to a total of 30 tables, 8 of which are repeated across the schemas. The method we are using the schemas in also allows me to work in some assumptions that were tricky to code under the monolithic structure, with the schema system, simplicity of the table set allowed allowed me to simulate those assumptions with out having to re-write code each time I have an update, thus giving me a highly dynamic dataset. That and I'm more confident working on a 3D level, versus 2D, the schema levels introduce the third dimension into my structure and simplify visualizing how I want things to work. Within that third dimensional structure, it made sense to be able to code out an SQL statement that would have searched the schema set for matching patterns to that given in the SQL query, similar to the way one can search for a pattern on data in a column. But Tom has given me an idea that will allow me to work out how to search multiple schemas within a dynamic plpgsql function that figures out all my patterned schemas and executes the resulting query as he suggested, I just need to learn plpgsql programming so that I can work out all of the details (Tom, I might bug you about that once in a while). At 09:45 PM 11/5/03 -0400, Marc G. Fournier wrote: Actually, the use of schema's was my idea, to speed up some dreadfully slow queries dealing with traffic stats from a table that was growing painfully monolithic ... the idea is/was that it would be easier to backup/remove all data pertaining to a specific client if they decided to close their account ... On Wed, 5 Nov 2003, Josh Berkus wrote: > Chris, > > > I work with Marc Fournier, for those who don't know, and have been > > working extensively with the schemas feature for Hub.Org's new Account > > Management package. Each client's data is stored in a seperate schema > > set asside just for them (though they will never have direct access to > > it, it helps us "balance" out the data. Each schema has the same set of > > tables, with which different data is stored, depending on the client. > > While this is an interesting use of Schema, it is not, IMHO, a good way to > partition client data.Far better to have monolithic tables with a > "client_id" column and then construct *views* which are available in each > schema only to that client. Otherwise, as you've discovered, top-level > management becomes a royal pain. > > I'd be happy to discuss this further on PGSQL-SQL, which is really the > appropriate mailing list. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(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
[SQL] Do update permissions require select permissions
I have run across a problem while creating a database with row level permission checking via views and triggers. The just of which is update does not work for a user unless they also have select permissions. Select permissions are handled with a view, while insert, update and delete permissions are handled via triggers. The simplest table which demonstrates the problem is: create table data_t (id int4, stuff text); grant insert,update,delete on data_t to public; As the table creator issue the query: insert into data_t values (1, 'Some stuff'); Now as some other user one can do: update data set stuff = 'other stuff'; And it works okay. But the following fails: update data set stuff = 'yet other stuff' where id = 1; Why is this? The reason that I need to not let the user have select on the table is that they are only allowed to select certian rows and there is a view that takes care of this. For insert update delete there are trigger functions. I know that rules on the view would handle the problem but I am using inheretence and the query plans grow to over 270 rows when rules on views on base tables are put together. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to completely move a table to another schema?
Hi all; I have a function which moves a table from one schema to another by updating the relnamespace field of pg_class: CREATE OR REPLACE FUNCTION move_relation(VARCHAR, VARCHAR, VARCHAR) RETURNS BOOL AS ' -- $1 is the table name -- $2 is the source schema -- $3 is the destination schema -- UPDATE pg_catalog.pg_class SET relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $3) WHERE relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $2) AND relname = $1; UPDATE pg_catalog.pg_type SET typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $3) WHERE typnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = $2) AND typname = $1; SELECT TRUE; ' LANGUAGE SQL; Am I missing anything? I have already had a few problems that led me to discover that I needed to put in the second update query. Just figured I would check. Best Wishes, Chris Travers ---(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] INHERITS and Foreign keys
Hi Pedro; I understand that at the moment it is more of a design limitation than a bug. I think we should vote the desired behavior as a feature request, however. Best Wishes, Chris Travers - Original Message - From: "Pedro" <[EMAIL PROTECTED]> > >> Foreign keys, unique and primary key constraints do not meaningfully > >> inherit currently. At some point in the future, that's likely to change, > >> but for now you're pretty much stuck with workarounds (for example, using > >> a separate table to store the ids and triggers/rules on each of the > >> tables > >> in the hierarchy in order to keep the id table in date.) > > hi > > same problem here on 7.4 > can we vote for this bug somewhere ?! > > thanks for your time > Pedro > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Historic Query using a view/function ?
...Postgresql 7.2...
I'm building the history of a table using rules. I've been trying to figure out
a way to select on a table as it would have appeared in a point in time. I
can't seem to wrap my brain around the problem, tho.
Given some tables
CREATE TABLE list (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
CONSTRAINT list_pkey PRIMARY KEY (num)
) WITH OIDS;
CREATE TABLE list_log (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
mod_type varchar(3),
log_date timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone
) WITH OIDS;
And some rules...
CREATE RULE list_del AS ON DELETE TO list DO INSERT INTO list_log (num, name,
type, modified, mod_type) VALUES (old.num, old.name, old.type, old.modified,
'D'::"varchar");
CREATE RULE list_upd AS ON UPDATE TO list WHERE ((old.name <> new.name) OR
(old.type <> new.type)) DO INSERT INTO list_log (num, name, type, modified,
mod_type) VALUES (old.num, old.name, old.type, old.modified, 'U'::"varchar");
It'd be great to be able to do something like...
SELECT * FROM hist_list('10/10/2003'::date) WHERE name like '%Jones';
... I don't think Functions can return tables in 7.2 ... Can anyone think of a
way around this?
CG
__
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Distributed keys
It seems to me that if the inheritance/fireign key behavior was changed so that foreign key constraints could exist within the entire inheritance tree, this problem would be solved. According to previous posts, the behavior will probably change at some point but does not appear to be a priority at the moment. If it were possible to allow FK constraints to work against the inheritance tree rather than a single table therein you could have managers, teachers, and subs as tables inherited tables from employees and the problem would be solved. Currently a workaround I can see is: Hide the actual tables in a shadow schema, and inherit as above. Have each table be represented as a view in the public schema joining the table to another table storing the employee unique identifiers. Place unique constraints on the unique identifiers table. Create rules for inserting, updating, and deleting the records. Have the Employee view search the entire inheritance tree. However, this is assuming that the data you are storing for the employees differs substantially depending on position. If this is not the case, you would do better by having a single employee table and include a field indicating whether the employee is a manager, teacher, or sub. Best Wishes, Chris Travers - Original Message - From: "Michael Glaesemann" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, December 24, 2003 4:42 AM Subject: [SQL] Distributed keys Hello all! An area of the relational database model that is not included in the SQL standard are distributed keys and distributed foreign keys. A quick example (Case 1): employees (id, name); schools (name, location); teachers (employee_id, school_name); subs (employee_id); managers (employee_id, school_name); with constraints unique employees(id) teachers(employee_id) references employees(id) teachers(school_name) references schools(name) subs(employee_id) references employees(id) managers(employee_id) references employees(id) The idea is that employees must be in one (and only one) of either teachers, subs, or managers. Currently, this might be represented in something like (Case 2) employees (id, name, employee_type, school_name); schools (name, location); employee_types (type); with constraints employees(employee_type) references employee_types(type) employees(school_name) references schools(name) where employee_types includes "teacher", "sub", and "manager" Or it might be represented with a number of rules or triggers that perform all of the necessary checking. employees(school_name) can't have a not null constraint because if the employee is a sub, they aren't associated with a school. Using the terms "distributed key" and "foreign distributed key", in the first case employee_id is a "distributed key" in that it must occur in only one of the tables teachers, subs, or managers. Distributed keys are similar in concept to primary keys—they must be unique. This guarantees an employee_id in teachers is not found in subs or managers, an employee_id in subs is not found in managers or teachers, and an employee_id in managers is not found in subs or teachers. employees(id) is a foreign distributed key in teachers, subs, and managers (as employee_id). Foreign distributed keys are similar in concept to foreign keys in that employees(id) must be referenced by a single tuple in one of teachers, subs, or managers. Another use would be in this situation (something I'm working on right now): I want to link comments by employees by employee_id, but comments from non-employees by name (as they don't have an id). comments(id, comment); comments_nonemployees(comment_id, name); comments_employees(comment_id, employee_id); with constraints comments_nonemployees(comment_id) references comments(id) comments_employees(comment_id) references comments(id) and comments(id) must be listed in either comments_nonemployees(comment_id) or comments_employees(comment_id) I haven't looked very far into how to implement distributed keys and foreign distributed keys in PostgreSQL beyond briefly looking at the pg_constraint system table, thinking a distributed key would be something making employee_id unique in teachers(employee_id) UNION subs(employee_id) UNION managers(employee_id). A distributed key is distributed over a number of tables, rather than a single one, so there'd have to be a list of relid-attnum pairs, rather than a single relid-attnum pair, such as conrelid and conkey in pg_constraint. Here's a brief sketch of the idea: pg_distributed distname name the name of the distributed key constraint distrelid oid the relid of one of the tables involved in the distributed keys distkey int2[] a list of the attnum of the columns of the table with oid distrelid involved in the distributed key distforkey bool true if foreign distributed key distfrelid oid if a foreig
Re: [SQL] Anti log in PostgreSQL
Definition of log (base n) is that log n(x) = y where n^y = x for all values of x and y. n is the base. So a base 10 log would be reversed by doing 10^x=y. If we know x, we use the exponential operation; if we know y we use log(y) = x. For ln (natural logs, base e, e is approx. 2.818), use e^x=y. Hope this explains things. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] sort by on two columns
Title: Message SELECT * FROM customers ORDER BY last_name, first_name Works for me. - Original Message - From: Andy Lewis To: [EMAIL PROTECTED] Sent: Saturday, January 03, 2004 8:15 AM Subject: [SQL] sort by on two columns Hi All, Is it possible to sort by two columns? Using the query below? SELECT table1.name, table2.name, FROM table1, table2 WHERE table1.id = table2.id ORDER BY I want to be able to sort the names select from two different tables and two different colums(same data type). Is this possible? Thanks, Andy
[SQL] Historic Query using a view/function ?
...Postgresql 7.2...
I'm building the history of a table using rules. I've been trying to figure out
a way to select on a table as it would have appeared at a point-in-time. I
can't seem to wrap my brain around the problem, tho.
Given some tables
CREATE TABLE list (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
CONSTRAINT list_pkey PRIMARY KEY (num)
) WITH OIDS;
CREATE TABLE list_log (
num int4 NOT NULL,
name varchar(50),
type varchar(50),
modified timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone,
mod_type varchar(3),
log_date timestamptz DEFAULT ('now'::text)::timestamp(6) with time zone
) WITH OIDS;
And some rules...
CREATE RULE list_del AS ON DELETE TO list DO INSERT INTO list_log (num, name,
type, modified, mod_type) VALUES (old.num, old.name, old.type, old.modified,
'D'::"varchar");
CREATE RULE list_upd AS ON UPDATE TO list WHERE ((old.name <> new.name) OR
(old.type <> new.type)) DO INSERT INTO list_log (num, name, type, modified,
mod_type) VALUES (old.num, old.name, old.type, old.modified, 'U'::"varchar");
It'd be great to be able to do something like...
SELECT * FROM hist_list('10/10/2003'::date) WHERE name like '%Jones';
... I don't think Functions can return tables in 7.2 ... Can anyone think of a
way around this?
CG
__
Do you Yahoo!?
Find out what made the Top Yahoo! Searches of 2003
http://search.yahoo.com/top2003
---(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] Calendar Scripts - Quite a complex one
Hi all; If I understand Kumar's post correctly, he is having some question relating to the issue of even recurrance. I would highly suggest reading the ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject. HERMES (my app with appointment/calendar functionality) doesn't yet support appointment recurrance, and I have not formalized my approach to this. However, here is the general approach I have been looking at: 1: Have a separate table of recurrance rules (1:1 with appointments) or have a recurrance datatype. 2: Build some functions to calculate dates and times when the appointment would recurr. You can also have a "Recur Until" field so you can limit your searches this way. 3: Use a view to find recurring appointments on any given day. This avoids a very nasty problem in the prepopulation approach-- that of a cancelled recurring meeting. How do you cancel ALL appropriate instances of the meeting while leaving those that occured in the past available for records? Kumar-- if you are working with PHP, I would be happy to work with you in this endevor so that the same functionality can exist in my open source (GPL'd) application. I think that the source for this would likely be one of those things that might be best LGPL'd if added to my app. Best Wishes, Chris Travers - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 1:06 PM Subject: Re: [SQL] Calendar Scripts - Quite a complex one > Hi, > > The complexity comes while scheduling the appointments. Let us say, I have > scheduled so many meetings in my calendar of various schedules like daily, 3 > days once, weekly, bi weekly. monthly, bi monthly, etc. > > While I open the calendar for end of this year (say Dec 2004), I need to > show those meetings in my calendar, but I have data until Jan 2004. > > What is the best way to show it. Populating the records from Jan 2004 to Dec > 2004 in the pgsql function and display it in the calendar, or just write a > query to generate temporary records only for that Dec 2004 and not storing > them at the database. > > Please shed some idea. > > Regards > Kumar > > - Original Message - > From: "Josh Berkus" <[EMAIL PROTECTED]> > To: "Peter Eisentraut" <[EMAIL PROTECTED]>; "Kumar" <[EMAIL PROTECTED]>; > "psql" <[EMAIL PROTECTED]> > Sent: Wednesday, January 07, 2004 3:43 AM > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > Peter, > > > You can probably lift out the complete calendar functionality from an > > existing groupware solution, say, www.egroupware.org. I'm not sure > > whether it's practical to do the calendar things in the database, since > > you will also need a significant amount of intelligence in the client > > to display reasonable calendar graphics, for instance. > > But all of the appointments, holidays, etc can and should be stored in the > database, and by using function programming one can automate generating all > of the raw data for the calendar graphics. We do this with our legal > calendaring app. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Calendar Scripts - Quite a complex one
Hi Kumar and others; I have never worked with functions to return references to cursors. Is there a reason why it has to be done this way rather than returning a setof appointments? In that case: create function app_today returns setof appointment (date) as ' declare new_appoint appointment; appoint_recur recurrance begin for appointment in [SELECT query] loop -- do calculations if [condition] then return next; end if; end loop; end; ' language plpgsql; Note that the function is off my head and not even guaranteed to be exactly what you need. Best Wishes, Chris Travers - Original Message - From: "Kumar" <[EMAIL PROTECTED]> To: "Chris Travers" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> Sent: Wednesday, January 07, 2004 7:39 PM Subject: Re: [SQL] Calendar Scripts - Quite a complex one > Hi, > yes yes. U understood in a very correct way, as i have 2 tables - > appointments and recurrences. And we are not gonna use PHP. > > For future dates, I am not gonna populate, instead I am gonna check for the > recurrences tables for ever appointments and based on the conditions, I am > gonna say how many time that appointment recure in that month and the > timestamp. > > To process that I have get all the appointment data and its recurrence > pattern data into the cursor. Is there a way to get the records one by one > from the cursor and calculate it patterns. > > CREATE OR REPLACE FUNCTION crm.fn_calendar_daily_activities(timestamp) > RETURNS refcursor AS > 'DECLARE > cal_daily_date ALIAS FOR $1; > ref REFCURSOR; > > BEGIN > OPEN ref FOR > SELECT > > RETURN ref; > > END;' > LANGUAGE 'plpgsql' VOLATILE; > > How to open the cursor here so that I could check its recurrences pattern. > > Please shed some light. > > Regards > kumar > > - Original Message - > From: "Chris Travers" <[EMAIL PROTECTED]> > To: "Kumar" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "Peter Eisentraut" > <[EMAIL PROTECTED]>; "psql" <[EMAIL PROTECTED]> > Sent: Wednesday, January 07, 2004 1:19 PM > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > > Hi all; > > > > If I understand Kumar's post correctly, he is having some question > relating > > to the issue of even recurrance. I would highly suggest reading the > > ICalendar RFC (RFC 2445) as it has some interesting ideas on the subject. > > HERMES (my app with appointment/calendar functionality) doesn't yet > support > > appointment recurrance, and I have not formalized my approach to this. > > However, here is the general approach I have been looking at: > > > > 1: Have a separate table of recurrance rules (1:1 with appointments) or > have > > a recurrance datatype. > > > > 2: Build some functions to calculate dates and times when the appointment > > would recurr. You can also have a "Recur Until" field so you can limit > your > > searches this way. > > > > 3: Use a view to find recurring appointments on any given day. > > > > This avoids a very nasty problem in the prepopulation approach-- that of a > > cancelled recurring meeting. How do you cancel ALL appropriate instances > of > > the meeting while leaving those that occured in the past available for > > records? > > > > Kumar-- if you are working with PHP, I would be happy to work with you in > > this endevor so that the same functionality can exist in my open source > > (GPL'd) application. I think that the source for this would likely be one > > of those things that might be best LGPL'd if added to my app. > > > > Best Wishes, > > Chris Travers > > > > - Original Message - > > From: "Kumar" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]>; "Peter Eisentraut" <[EMAIL PROTECTED]>; "psql" > > <[EMAIL PROTECTED]> > > Sent: Wednesday, January 07, 2004 1:06 PM > > Subject: Re: [SQL] Calendar Scripts - Quite a complex one > > > > > > > Hi, > > > > > > The complexity comes while scheduling the appointments. Let us say, I > have > > > scheduled so many meetings in my calendar of various schedules like > daily, > > 3 > > > days once, weekly, bi weekly. monthly, bi monthly, etc. > > > > > > While I open the calendar for end of this year (say De
[SQL] Problem with plpgsql function
Hi All,
I've been bangin away on a 7.4.x based database server trying to get a
plpgsql function to work the way I'm expecting it to. I've used various
resourced on the net to come up with the function, and as far as I can
tell it's "in proper form", but I've got the feeling that I'm missing
something.
I've created a new data type called:
CREATE TYPE account_info AS (username text, password text);
With that I want to return multiple rows, based on the results of the
function, using the SETOF and rowtype declarations, such that the
function looks like:
CREATE OR REPLACE FUNCTION get_account_info(text) RETURNS SETOF
account_info AS '
DECLARE
acc account_info%rowtype;
domain_name ALIAS FOR $1;
company_id RECORD;
BEGIN
acc.username := NULL;
acc.password := NULL;
SELECT INTO company_id cs.id, to_char(cs.id, ''FM0999'') AS cid FROM
virtual_host vh
LEFT JOIN virtual_machine vm ON (vm.id = vh.vm_id)
LEFT JOIN company_summary cs ON (cs.id = vm.company_id)
WHERE vh.domain_name = domain_name;
FOR acc IN EXECUTE ''SELECT '''' || company_id.cid || '''' || c.id,
a.password FROM company_summary cs
LEFT JOIN contact c ON (c.company_id =
cs.id)
LEFT JOIN company_'' || company_id.cid ||
''.account a ON (a.contact_id = c.id)
WHERE cs.id = '' || company_id.id
LOOP
RETURN NEXT acc;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
According to the system, the function is created with out issue, and
there appear to not be any syntax errors being returned, however when I
execute the function in the query like this:
select get_account_info('test.com');
I get this error:
ERROR: set-valued function called in context that cannot accept a set
The backend logger results look like:
Jan 15 13:42:56 jupiter 5439[3164]: [128-1] LOG: query: select
get_account_info('test.com');
Jan 15 13:42:56 jupiter 5439[3164]: [129-1] LOG: query: SELECT NULL
Jan 15 13:42:56 jupiter 5439[3164]: [129-2] CONTEXT: PL/pgSQL function
"get_account_info" line 7 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [130-1] LOG: query: SELECT NULL
Jan 15 13:42:56 jupiter 5439[3164]: [130-2] CONTEXT: PL/pgSQL function
"get_account_info" line 8 at assignment
Jan 15 13:42:56 jupiter 5439[3164]: [131-1] LOG: query: SELECT cs.id,
to_char(cs.id, 'FM0999') AS cid FROM virtual_host vh LEFT JOIN
virtual_machine vm ON (vm.id =
Jan 15 13:42:56 jupiter 5439[3164]: [131-2] vh.vm_id) LEFT JOIN
company_summary cs ON (cs.id = vm.company_id) WHERE vh.domain_name = $1
Jan 15 13:42:56 jupiter 5439[3164]: [131-3] CONTEXT: PL/pgSQL function
"get_account_info" line 10 at select into variables
Jan 15 13:42:56 jupiter 5439[3164]: [132-1] LOG: query: SELECT 'SELECT
'' || company_id.cid || '' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[132-2] LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-3] LEFT JOIN company_' || $1 ||
'.account a ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[132-4] WHERE cs.id = ' || $2
Jan 15 13:42:56 jupiter 5439[3164]: [132-5] CONTEXT: PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [133-1] LOG: query: SELECT ' ||
company_id.cid || ' || c.id, a.password FROM company_summary cs
Jan 15 13:42:56 jupiter 5439[3164]:
[133-2] LEFT JOIN contact c ON
(c.company_id = cs.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-3] LEFT JOIN company_0011.account a
ON (a.contact_id = c.id)
Jan 15 13:42:56 jupiter 5439[3164]:
[133-4] WHERE cs.id = 11
Jan 15 13:42:56 jupiter 5439[3164]: [133-5] CONTEXT: PL/pgSQL function
"get_account_info" line 15 at for over execute statement
Jan 15 13:42:56 jupiter 5439[3164]: [134-1] ERROR: set-valued function
called in context that cannot accept a set
Jan 15 13:42:56 jupiter 5439[3164]: [134-2] CONTEXT: PL/pgSQL function
"get_account_info" line 20 at return next
Can anyone see anything that I missed? Or has any suggestions?
--
Chris Bowlby <[EMAIL PROTECTED]>
Hub.Org Networking Services
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Problem with plpgsql function
Ack, I knew it was something small, I was under the impression that I
had been using that, it just took someone to point it out to make me
look again :>... thanks..
On Thu, 2004-01-15 at 14:47, Joe Conway wrote:
> Chris Bowlby wrote:
> > select get_account_info('test.com');
> >
> > I get this error:
> >
> > ERROR: set-valued function called in context that cannot accept a set
>
> This is the "classic" SRF error -- you need to use an SRF like a
> relation in the FROM clause, so do this instead:
>
>select * FROM get_account_info('test.com');
>
> HTH,
>
> Joe
--
Chris Bowlby <[EMAIL PROTECTED]>
Hub.Org Networking Services
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Is it possible in PostgreSQL?
Moving thread over to SQL list as it belongs
there.
Bronx: This certainly is possible, but IMO,
not in one query. Actually doing it will be relatively complex. For
purposes of maintenance, I am thinking that doing this would be better handled
by wrapping at least one view.
CREATE VIEW sales_pre_proc AS
SELECT name, quantity, to_char("date", '') AS
year, to_char("date", 'MM') FROM sales;
This is needed for the group by statement
below to function properly:
CREATE VIEW sales_month_summary AS
SELECT name, sum(quantity) AS quantity, year, month
from sales_pre_proc
GROUP BY name, year, month;
This will give you a view that will have the
sum information. Now we just have to create the statement which will
create the pivot effect. I understand that there is something under
contrib/tablefunc for this, but I do not have it on my system (cygwin), at
the moment. Perhaps someone else can help.
Failing that, you can write your own function to
return each row. I was working on a quick proof of concept but it was not
working properly.
Best Wishes,
Chris Travers
- Original Message -
From:
Bronx
To: [EMAIL PROTECTED]
Sent: Tuesday, January 13, 2004 6:58
AM
Subject: [ADMIN] Is it possible in
PostgreSQL?
Hi,
I've got problem with one specific query.
I've got the table
with many of rekords like
these:
name
| quantity | date
---
aaa
2
2003-04-01
bbb
4
2003-04-12
ccc
5
2003-05-12
aaa
3
2003-01-14
aaa
1
2003-12-09
bbb
9
2003-08-08
and so on ...
Does
anybody know how make query which return grouped
records by month of year and name
(also sum of quantity).
It is possible to make a query whitch return
something like that:
name | 01 | 02 | 03 | 04 | ... | 12
(months)
aaa
x x x
x ... x
bbb
x x x
x ... x
ccc
x x x
x ... x
where x means sum of quantity in month.
It is possible to make it in one query?
I know that in Access is construction : PIVOT.
Thanks
Adam
Re: [SQL] comparing nulls
Hi Ken, Under 7.3.x this option was removed, you need to test via: SELECT * from table where field IS NULL; On Tue, 2004-01-20 at 09:43, Kenneth Gonsalves wrote: > in postgres7.1 i had a table where an integer field could be null. There was > no default value. a select statement like so: > 'select * from table where field = null' > would give all the rows where that field had no value. > on porting to 7.3.2, this doesnt work. How to do this? -- Chris Bowlby <[EMAIL PROTECTED]> Hub.Org Networking Services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] comparing nulls
To achieve a higher level of SQL compliancy.. On Tue, 2004-01-20 at 10:24, Kenneth Gonsalves wrote: > On Tuesday 20 January 2004 19:26, Chris Bowlby wrote: > > Hi Ken, > > > > Under 7.3.x this option was removed, you need to test via: > > > > SELECT * from table where field IS NULL; > thanx - works in both 7.1 and 7.3 - why do these guys keep fooling around > with these thangs? -- Chris Bowlby <[EMAIL PROTECTED]> Hub.Org Networking Services ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Database diagram
There is a free Perl script which is called something like pgautodoc which creates DIA diagrams from databases. Take a look for it on Freshmeat. - Original Message - From: "Ganesan Kanavathy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, January 20, 2004 1:38 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] How to retrieve N lines of a text field.
Hi all;
This is a complex issue, and i am tryign to figure out how to use regular
expressions to resolve this issue. I need to retrieve the first N lines of
a text field. N would be assigned using a parameterized query, if possible.
I had thought about using something like:
select substring(test from '#"' || repeat('%\n', $1) || '#"%' for '#') from
multiline_test;
However, this always selects every line but the final one (because %\n seems
to be interpreted to be the largest possible string, while I want it to be
the smallest possible string).
Is there a workaround? Any other help? Or do I need to write a UDF?
Best Wishes,
Chris Travers
---(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]
You can also use PGexecParams() (see the libpq documentation). It can be a little more cumbersome to use, though. Best Wishes, Chris Travers - Original Message - From: MUKTA To: [EMAIL PROTECTED] Sent: Thursday, January 29, 2004 8:08 PM Subject: [SQL] Hi I have an urgent problem I want to insert values into a table using the C syscalls provided by the libpq library, but i find that i can not insert into the table when i use variables instead of values...like so: int a,b,c,d; using the C function res=PQexecute(Conn,"INSERT into table values(a,b,c,d)); executing above statement with plain integers does fine and inserts them into table.. Is there some special way to insert variables rather than plain values? do i have to build functions (in sql) or somehting?help! Thanx
[SQL] Could not convert UTF-8 to ISO8859-1
I've noticed a difference in behavior between 7.2 and 7.3 with regards
to character recoding and I'm a little perplexed about how to work
around.
I have a database in LATIN-1 that is accessed read-write by a Java app.
Naturally, the Java code keeps all of its strings in UTF8 so when I
prepare a sql statement, someone is recoding these characters to
LATIN-1 for me.
In 7.2, if the Unicode string contained a character that wasn't valid
in the database encoding (LATIN-1) either pgsql or the jdbc driver (I'm
not really sure which) would silently convert these characters to
question marks.
In 7.3, the same string will throw a "Could not convert UTF-8 to
ISO8859-1" error.
I can work around this by doing the following hack in Java:
String s = "some unicode string";
byte[] tmp = s.getBytes("latin1");
s = new String(tmp, 0, tmp.length, "latin1");
But I'm sure there is a better way to do this.
Any suggestions?
cva
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Storing a range of numbers
CoL wrote: or you can have one column, with array type. than the first element is the min value the second is the max:) Question is: is it good for your applications or not, how woud you use it, etc. I thought about using an array but it seems overly complicated for what I'm doing. Two rows will work fine ... a numeric range type seemed obvious at the time so I figured I just missed it in the documentation or something. Thanks for the reply. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] tsearch2 trigger alternative
Tsearch2 comes with its own tsearch2 trigger function. You pass column names to
it, and it puts a vanilla tsvector into the column names in TG_ARGV[0] (zero
based, yes?). Not only can you pass column names to it, but you can pass simple
functions to it as well. This is magical to me. :)
I'm trying to figure out how to do the same thing, except instead of returning
a vanilla tsvector, I want to return a specially weighted tsvector. I've
created a function that can do this:
create or replace function name_vector (text) returns tsvector as '
select setweight(to_tsvector(substr($1,1,strpos($1,'',''))),''C'') ||
to_tsvector(substr($1,strpos($1,'','')+1,length($1)));
' language 'sql';
so...
Plain:
select to_tsvector('Einstein, Albert');
to_tsvector
-
'albert':2 'einstein':1
Weighted:
select name_vector('Einstein, Albert');
name_vector
--
'albert':2 'einstein':1C
Now, to somehow package that into a magical trigger function...
All the examples for creating trigger functions that I've found use static
column names, NEW and OLD ... I would like to create a generic trigger
function, as the tsearch2 trigger function does, to return the specially
weighted tsvector.
Its like a lighter to a caveman. Can anyone lend a hand?
CG
__
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools
---(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] IMPORT TOOL
[EMAIL PROTECTED] (Louie Kwan) writes:
> Do anyone of you aware of any PG import tool same as SQL*LOADER in ORACLE
>
> We are doing some DataWarehouse work and need to import a large set of data
> from csv files.
>
> We are trying a import tool from ems-hitech.com, but I have troubles when
> loading time and date columns.
>
> Any help is much appreciated.
>
> Thanks
> Louie
>
> -- | Creating NNM_HOST Table
> -- |
>
> CREATE TABLE NNM_NODES (
> lastLoadTime_e DATE,
> domain_name VARCHAR(10),
>
> ovtopo_idINTEGER NOT NULL,
> node_typeVARCHAR(10),
> node_nameVARCHAR(30),
> ov_statusVARCHAR(10),
> ipaddressVARCHAR(15)
> );
> data.csv
>
> 31/01/2004,D1H01,845,IP,dfrQCQCRD1,Normal,142.130.35.1
> 31/01/2004,D1H01,849,IP,dfrQCIMCP1,Normal,142.130.130.33
> 31/01/2004,D1H01,853,IP,dfrQCSIBL1,Normal,142.130.130.19
At this point, the 'equivalent' to SQL*LOADER is some combination of
Perl, Python, Tcl, or some other favorite scripting language.
I seem to recall that someone (he'll remain nameless :-)) was working
on a tool rather like this; I'm not sure where deployment of that
stands.
--
output = ("cbbrowne" "@" "ntlug.org")
http://www3.sympatico.ca/cbbrowne/advocacy.html
"SCSI is *NOT* magic. There are *fundamental technical reasons* why it
is necessary to sacrifice a young goat to your SCSI chain now and
then."
---(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] SCHEMA's the easy way?
I'm planning on dipping my toes into the world of schemata. I have tables, created in the Public schema, that I'd like to move to the new schema: SELECT * INTO new.tablename FROM public.tablename; CREATE SEQUENCE ...; CREATE INDEX ...; ALTER TABLE ...; BLAH ...; BLAH ...; BLAH ...; DROP public.tablename; REPEAT ...; REPEAT ...; REPEAT ...; VOMIT; Is there an easier, faster, less user-error-prone way around this? __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Schemata & User-Defined-Type casting issues
PostgreSQL 7.4.2 ... Background: I'm attempting to migrate tables which were created in the pre-schema days to a sensible schema setup. I'm using the "uniqueidentifier" column in some of these tables. When I created the new schema, I created an instance of "uniqueidentifier" and its supporting functions and casts within the new schema. When I try to "INSERT INTO myschema.mytable ... SELECT ... FROM public.mytable;" It's having difficulty seeing that the data types are compatible across the schema. An explicit cast (without first casting to a neuter data-type) won't work for the same reason. I'm torn: Should I create a "cast" to allow for casting of this data-type across schemas, or should I have created the table referencing the user-defined type in the public schema? I expect that this problem will rise up now and again. I'd like to solve it in the this early phase with a proper deisgn-based fix. If it makes a difference, I would like to not include this schema in the search path, to explicitly refer to it as myschema.mytable anywhere I need to reference it. CG __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(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] Schema + User-Defined Data Type Indexing problems...
PostgreSQL 7.4.2 -- All vacuumed and analyzed. I inserted the uniqueidentifier datatype into a new schema that I'm working on by changing the search_path to "my_schema" in the contrib SQL. It effectively created the datatype within the schema, all of its functions, operators, and operator classes. To move the data from the public schema into the new "my_schema" I had to create an assignment cast public.uniqueidentifier to my_schema.uniqueidentifier. I was profiling queries and I couldn't figure out why PostgreSQL wasn't using indexes. I'm having a heck of a time, and it seems like in my thrashing about to find a solution to this problem I have ruined the uniqueidentifier datatype in the schema... CREATE INDEX mt_uuid_idx ON my_schema.my_table USING btree (my_uuid); ERROR: data type my_schema.uniqueidentifier has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type. I can look at the operator classes and see that there is an operator class for btree for my_schema.uniqueidentifier. I must be doing something wrong with my schema set-up to have this much trouble with it. If this is the norm for complexity when using schema, I'm not sure it is worth the effort to impliment. Other PostgreSQL users are comfortable with the schema implimentation... I _must_ be doing something wrong. The bottom line for my problem is that searches that should be using indexes in the schema aren't. Please help me find out what's going on. CG __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(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
Re: [SQL] Schema + User-Defined Data Type Indexing problems...
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Chris Gamache <[EMAIL PROTECTED]> writes: > > I'm having a heck of a time, and it seems like in my thrashing about > > to find a solution to this problem I have ruined the uniqueidentifier > > datatype in the schema... > > > CREATE INDEX mt_uuid_idx > > ON my_schema.my_table USING btree (my_uuid); > > > ERROR: data type my_schema.uniqueidentifier has no default operator class > for > > access method "btree" > > HINT: You must specify an operator class for the index or define a default > > operator class for the data type. > > > I can look at the operator classes and see that there is an operator class > for > > btree for my_schema.uniqueidentifier. > > IIRC, the opclass has to be in a schema that is in your schema search > path to be found by CREATE INDEX by default. If it isn't, you could > specify it explicitly: > > CREATE INDEX mt_uuid_idx > ON my_schema.my_table USING btree (my_uuid USING my_schema.uuidopclass); > > It's possible that we could think of a more convenient behavior for > default opclasses, but I don't want to do something that would foreclose > having similarly-named datatypes in different schemas. You have any > suggestions? That /is/ important to be able to have similarly named datatypes in different schemas. I'll give the explicit opclass a go. Indeed, if I place the schema in my search path the index creation and index scans seem to work perfectly. I had wanted to have to specify the schema whenever I referenced objects in it instead of putting it in my search path. I had no concept of exactly how truly separated schemas are. The only idea that I can think of (and, again, I may be underestimating the level of separation that needs to exist between schema) is that object creation could implicitly looks to the current schema for a usable index/opclass/whatever first before checking the search path. A SELECT could look first to the schema of the table before checking the search path for a usable index. Is it even possible to create an index that lives in a different schema from the table it is indexing? CG __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(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] Schema + User-Defined Data Type Indexing problems...
--- Tom Lane <[EMAIL PROTECTED]> wrote: > In practice I'm not sure that this is really a situation that we need to > fret about, because using a datatype that isn't in your search path has > got notational problems that are orders of magnitude worse than this > one. The functions and operators that do something useful with the > datatype would also have to be schema-qualified every time you use them. > This is perhaps tolerable for functions but it's quite unpleasant for > operators :-( You can't write > select * from foo where my_uuid = 'xxx'; > instead > select * from foo where my_uuid operator(my_schema.=) 'xxx'; > Yech. I think you'll end up putting uuid's schema in your search path > before long anyway. Right you are. I guess the moral of the story is that when using custom datatypes, search_path is a required setting. I guess that is why the "public" schema should be just that, completely accessable by any user with rights to the DB. So, is the best-practice for the my_schema tables to reference the user-defined datatype in the "public" schema? CREATE TABLE my_schema.foo (uuid public.uniqueidentifier); __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Converting integer to binary
Once upon a time in PostgreSQL there was a function : bitfromint4 ... Any idea where it has disappeared to? You can do # select B'10101101'::int4; int4 -- 173 (1 row) but you want to go # select 173::varbit; which is what bitfromint4 used to do. CG --- Bruno Wolff III <[EMAIL PROTECTED]> wrote: > On Thu, Jun 10, 2004 at 14:52:41 +0100, > Stephen Quinney <[EMAIL PROTECTED]> wrote: > > > > I have searched around but I cannot see any standard way in PostgreSQL > > to convert from an integer into a binary representation. > > > > Now I have an algorithm to do it so I could write an SQL function, I > > guess. If there's a standard way to do it though that would be quite nice. > > There doesn't seem to currently be a function that does this. to_char > would be the logical place since that is what is used to convert various > numeric types to strings with a decimal representation. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] LIKE on index not working
Hi all,
For some reason I just can't get this to use the index for the following
query. I'm using PostgreSQL 7.3.4.
Here's the details (let me know if you need anymore information to provide
any assistance):
Indexes: person_pkey primary key btree (personid),
ix_person_active btree (bactive),
ix_person_fullname btree (tsurname, tfirstname),
ix_person_member btree (bmember),
ix_person_supporter btree (bsupporter),
ix_person_surname btree (lower(tsurname))
smartteamscouts=# explain analyze select * from person where bmember = 1 AND
lower(tsurname) like lower('weaver');
QUERY PLAN
---
Seq Scan on person (cost=0.00..12946.58 rows=310 width=416) (actual
time=873.94..1899.09 rows=6 loops=1)
Filter: ((bmember = 1) AND (lower((tsurname)::text) ~~ 'weaver'::text))
Total runtime: 1899.64 msec
(3 rows)
smartteamscouts=# explain analyze select * from person where bmember = 1 AND
lower(tsurname) = lower('weaver');
QUERY PLAN
---
Index Scan using ix_person_surname on person (cost=0.00..1265.78 rows=310
width=416) (actual time=0.91..2.03 rows=6 loops=1)
Index Cond: (lower((tsurname)::text) = 'weaver'::text)
Filter: (bmember = 1)
Total runtime: 2.36 msec
(4 rows)
As you can see, using the '=' operator it works just fine, but as soon as
the 'like' operator comes into it, no good.
Is this a bug in 7.3.4? Or is it something else I need to adjust?
Thanks for your help!
Chris
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] LIKE on index not working
[EMAIL PROTECTED] ("Chris Cox") writes:
> Hi all,
>
> For some reason I just can't get this to use the index for the following
> query. I'm using PostgreSQL 7.3.4.
>
> Here's the details (let me know if you need anymore information to provide
> any assistance):
>
> Indexes: person_pkey primary key btree (personid),
> ix_person_active btree (bactive),
> ix_person_fullname btree (tsurname, tfirstname),
> ix_person_member btree (bmember),
> ix_person_supporter btree (bsupporter),
> ix_person_surname btree (lower(tsurname))
>
> smartteamscouts=# explain analyze select * from person where bmember = 1 AND
> lower(tsurname) like lower('weaver');
> QUERY PLAN
>
> ---
> Seq Scan on person (cost=0.00..12946.58 rows=310 width=416) (actual
> time=873.94..1899.09 rows=6 loops=1)
>Filter: ((bmember = 1) AND (lower((tsurname)::text) ~~ 'weaver'::text))
> Total runtime: 1899.64 msec
> (3 rows)
>
> smartteamscouts=# explain analyze select * from person where bmember = 1 AND
> lower(tsurname) = lower('weaver');
> QUERY PLAN
>
> ---
> Index Scan using ix_person_surname on person (cost=0.00..1265.78 rows=310
> width=416) (actual time=0.91..2.03 rows=6 loops=1)
>Index Cond: (lower((tsurname)::text) = 'weaver'::text)
>Filter: (bmember = 1)
> Total runtime: 2.36 msec
> (4 rows)
>
> As you can see, using the '=' operator it works just fine, but as soon as
> the 'like' operator comes into it, no good.
>
> Is this a bug in 7.3.4? Or is it something else I need to adjust?
A problem with this is that it needs to evaluate lower(tsurname) for
each row, which makes the index pretty much useless.
If you had a functional index on lower(tsurname), that might turn out
better...
create index ix_lower_surname on person(lower(tsurname));
--
"cbbrowne","@","acm.org"
http://www3.sympatico.ca/cbbrowne/oses.html
Make sure your code does nothing gracefully.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIKE on index not working
Hi Peter, Thanks for the tip. The locale we're using is en_US.UTF-8. From my limited knowledge of locales, that's a non-C one isn't it? Am I right in saying that to fix it I need to initdb again with a C locale? How do I go about doing that on an environment with some 132 databases? What a pain! Chris - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Chris Cox" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, July 22, 2004 10:41 PM Subject: Re: [SQL] LIKE on index not working > Am Donnerstag, 22. Juli 2004 09:38 schrieb Chris Cox: > > For some reason I just can't get this to use the index for the following > > query. I'm using PostgreSQL 7.3.4. > > In 7.3, LIKE cannot use an index unless you set the locale to C. In 7.4, LIKE > can use an index, but it has to be a different kind of index, as explained > here: > > http://www.postgresql.org/docs/7.4/static/indexes-opclass.html > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > ---(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
Re: [SQL] SQL Challenge: Arbitrary Cross-tab
Josh Berkus wrote: Folks, I have a wierd business case. Annoyingly it has to be written in *portable* SQL92, which means no arrays or custom aggregates. I think it may be impossible to do in SQL which is why I thought I'd give the people on this list a crack at it. Solver gets a free drink/lunch on me if we ever meet at a convention. Might be possible. Would certainly be ugly. The Problem: for each "case" there are from zero to eight "timekeepers" authorized to work on the "case", out of a pool of 150 "timekeepers". This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447 | 047 132113 | 021 132113 | 115 132113 | 106 etc. But, a client's e-billing application wants to see these timekeepers displayed in the following horizontal format: case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 213447 | 047 | | | | | | | | 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | etc. Order does not matter for timekeepers 1-8. This is a daunting problem because traditional crosstab solutions do not work; timekeepers 1-8 are coming out of a pool of 150. Can it be done? Or are we going to build this with a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL "advanced feature") If it can be done, it might be extremely ugly. I am thinking a massive set of left self joins (since there could be between 0 and 8). Something like: select case_id FROM authorized_timekeeper t0 LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper GROUP BY case_id) t1 ON case_id LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper WHERE timekeeper_id <> t1.timekeeper GROUP BY case_id) t2 ON case_id LEFT JOIN (SELECT case_id, min(timekeeper_id) FROM authorized_timekeeper WHERE timekeeper_id NOT IN (t1.timekeeper, t2.timekeeper) GROUP BY case_id) t3 etc If this is not an option, instead I would create a series of views. Something like: CREATE VIEW t1 AS select case_id, min(timekeeper_id) AS tk_id from authorized_timekeepers group by case_id; CREATE VIEW t2 AS select case_id, min(timekeeper_id) AS tk_id from authorized_timekeepers WHERE tk_id NOT IN (SELECT tk_id FROM t1) group by case_id; CREATE VIEW t3 AS select case_id, min(timekeeper_id) AS tk_id FROM authorized_timekeepers WHERE tk_id NOT IN (SELECT tk_id FROM t1) AND tk_id NOT IN (SELECT tk_id FROM t2) GROUP BY case_id; Etc. Then you do a left join among the views. Hope that this helps. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
