Re: [SQL] Problem with Day of Week
Ok, so there is actually two standards then. Is this documented
anywhere? Is this is something that is going to change? I don't want
to write and app and have things "break" during and upgrade :)
Thanks for the response.
On Mon, 5 Feb 2001, Karel Zak wrote:
>
> On Mon, 29 Jan 2001, Keith Perry wrote:
>
> > Greetings,
> >
> > I notice some talk about date problems and interestingly enough planning
> > out an application in which I will need to be able to manipulate dates.
> > I notice however that there seems to be a discrepancy with the day or
> > week in 7.0.3
> >
> > ---
> >
> > pmhcc=# select date_part('dow','now'::timestamp);
> > date_part
> > ---
> > 1
> > (1 row)
> >
> > pmhcc=# select to_char('now'::timestamp,'D');
> > to_char
> > -
> > 2
> > (1 row)
> >
>
> See:
>
> test=# select date_part('dow','2001-02-11'::timestamp);
> date_part
> ---
> 0
>
> test=# select to_char('2001-02-11'::timestamp, 'D');
> to_char
> -
> 1
>
>
> date_part is based on zero - use range 0-6
> to_char is based on one - use range 1-7
>
> Karel
>
Re: [SQL] Problem with Day of Week
Always- I think I'll use the to_char since I think you all are saying that that is ISO or at least POSIX. On Mon, 5 Feb 2001, Karel Zak wrote: > > On Mon, 5 Feb 2001 [EMAIL PROTECTED] wrote: > > > Ok, so there is actually two standards then. Is this documented > > anywhere? Is this is something that is going to change? I don't want > > to write and app and have things "break" during and upgrade :) > > I mean you can be caseful. Not changes planned here. > > date_part() is not documented to much in detail, but formatting > functions are described good. > > Karel >
[SQL] Copying data with triggers
Hi all, In the database I'm designing, I want to have audit tables that keep a log of all inserts, updates and deletes that occur on any table. e.g. If i had a table Info, create table Info ( info_id SERIAL, some_data text ) I would also have a corresponding audit table create table AudInfo (aud_key_id SERIAL, info_id int4, some_data text, aud_operation_type, aud_log_time timestamp default now() ) now I tried creating a trigger on Info, so that whenever an insert occurs, the records are copied to the audit table. create function tp_info () returns opaque as ' begin -- insert into audit table insert into AudInfo (info_id, some_data, aud_operation_type) values (new.info_id, new.some_data, ''i''); return new; end; ' language 'plpgsql'; create trigger tp_info before insert on Info for each row execute procedure tp_info(); This doesn't work however. A parse error occurs in the first line. I suspect that I cannot insert a record into another table in a trigger. I'm not sure why though. Anybody else done similar operations within a trigger procedure? Or know of a work around? Cheers, Keith.
[SQL] tip: weird parse error for pl/pgsql
Hi everyone, After fiddling for about a day to work out why my pl/pgsql stored procedures weren't working I finally discovered why. It seems that pl/pgsql has a problem parsing Window style new line characters. I was writing my stored procedures using a Windows app, and then running them on my Linux Postgres database. It keeped on giving me... an error found one line 1 near " " which wasn't very helpful. Anyway just make sure you write your stored procs in your Unix environment or save them as Unix format. I hope this tip saves somebody some time. :) Cheers, Keith.
Re: [SQL] Re: Argument variables for select
Hi Andreas, I've worked with MS SQL stored procedures before and they are quite powerful. Its a shame postgres doesn't have the same level of features as offered by MS SQL, but apart from this area it is still a very good database. Perhaps in the coming versions we will see more stored procedure features? I'm not sure what your application is exactly but there are ways to work around the features that are missing. Good luck with convincing your boss :) Keith. At 02:28 PM 29/08/2000 +0200, Andreas Tille wrote: >On Mon, 28 Aug 2000, Yury Don wrote: > > > > Create Function VarSelect ( varchar, varchar ) > > >returns int > > >As ' > > > Declare num int ; > > > > > > Begin > > >Select Into num Count(*) From $1 Where $2 ; > > >return num; > > > End ; > > >' language 'plpgsql' ; > > > > > > Could someone please explain who to type the exact syntax so that > > > I can ship the table to select from as $1 and the condition to select > > > what as $2? Or do I have to concatenate a string with the whole > > > select statement and how to call this string? In MS SQL server this > > > could be done with > > >Exec ( query ) > > > > > AFAIK it's impossible with plpgsql, but it's possible in pltcl. > >It is really hard to believe that I'm in so very deep trouble with >PostgreSQL. It seems to me that stored procedures are far from beeing >as usefull as I learned them to know in MS SQL server. Once I try >to follow one hint I'm standing in frot of the next even harder problem. > > >web=# create function testfunc( ) >web-# returns int >web-# As ' >web'# spi_exec "SELECT count(*) AS $num FROM testtable" >web'# >web'# return $num ; >web'# End; ' >web-# language 'pltcl' ; >ERROR: Unrecognized language specified in a CREATE FUNCTION: >'pltcl'. Recognized languages are sql, C, internal and the created >procedural languages. > > >I have installed the pgtcl package of my Debian distribution, so I guess >it should be available. Once more the question: Is it really necessary to >use a further interpreter instead of sticking with SQL commands to use >the original problem. > >I have to admit that my boss wonders why I'm switching from a working >solution (MS SQL) to so much trouble :-(. I really hope to convince him >to OpenSource but it's much harder than I expected. > >Kind regards > > Andreas.
[SQL] installing pgaccess
Anybody know how to compile pgaccess from postgres source files? It must be a configure option, but I can't find it. Keith.
Re: [SQL] how to store a query, that results in a table
This is not really possible with postgresql at the moment. Better off trying to work around, perhaps using a view. That way you have a way to change the select statement without actually modifying your client code. Keith. At 06:09 PM 22/09/2000 -0400, Nelson wrote: >thank you jie Liang for your response, but my problems are: >1. How to store a query in the database. >2. How to give a parameter from outside of database, for example: >select * from table1 where row1 = my_parameter_outside. >Give me an example please. > >
[SQL]
Hi ppl, Just wondering if anyone knows if its possible to set the transaction isolation level from inside a stored procedure in plpgsql. Or do I need to run the 'set transaction isolation level' command before I called the stored procedure? I'm not even sure that would work, I assume plpgsql uses the current transaction isolation level? Any help would be much appreciated :) Cheers, Keith.
[SQL] Setting the transaction isolation level for a stored procedure
Sorry, I forgot to put a subject title in my last email. So here it is again. Hi ppl, Just wondering if anyone knows if its possible to set the transaction isolation level from inside a stored procedure in plpgsql. Or do I need to run the 'set transaction isolation level' command before I called the stored procedure? I'm not even sure that would work, I assume plpgsql uses the current transaction isolation level? Any help would be much appreciated :) Cheers, Keith.
[SQL] Transaction isolation level for plpgsql
Hi all, Does anybody know the transaction isolation level default when a plpgsql stored procedure is called? Is it possible to set it? Or is this controlled by SPI? Cheers, Keith.
Re: [SQL] trigger examples
This is an example script I use to keep a log of all database operations on my tables. Hope it helps. At 05:05 PM 3/10/2000 +1000, Carolyn Lu Wong wrote: >[EMAIL PROTECTED] wrote: > > > > Carolyn Lu Wong wrote: > > > > > > I need to write a trigger to create a new record in log table if it's a > > > new record or if column A and B changes. Can someone give me an example > > > how to do this. > > > > > > Are there any sites that gives trigger examples?? > > > > > > Thanks. > > > > the fulltextindex in the contrib dir gives an example > >The example uses C. Is it possible to create the trigger with plpsql >function? table_customer.sql
[SQL] Re: what is the best way to set-up keywords in tables and Queries ?
Do you want to have a field name called "SELECT"? If so, you can use brackets in SQL 7 or higher. [SELECT] You can also use quoted identifiers (and the double quote) "SELECT" Keith "lesstif" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > what is the best way to set-up keywords in tables and Queries? please post > examples! > I am worried about a field for each keyword > > less > >
Re: [SQL] Persistent Connects (pg_pconnect)
Hi Colleen,
When you use specify a host parameter... Php will attempt to connect to
your postgres server using TCP/IP...
if you are not running the postgres backend with the -i option, then these
connections won't happen. When you don't
specify a host name then Php will use a local Unix socket.
Not sure about the persistant database connection stuff. What version of
Php are you using?
The only thing I could say, is to try explicitly closing the persistent
database connections in your Php code.
Maybe there is a bug in Php.
Hope this helps,
Keith.
At 12:34 PM 21/11/2000 +, Colleen Williams wrote:
>Hello all,
>
>I am having some problems with persistent connections.
>
>The PHP scripts in my content management system (CMS) are connecting using
>pg_pconnect(" dbname='MYDB' user='user' password='password''
>port='5432' "). I have not been able to connect to the PostgreSQL
>database when I include the host parameter in the pg_pconnect string and I
>don't understand why. Each time one of us uses the CMS, a new persistent
>connection process owned by postgres is started up. With 3 people
>accessing the CMS sporadically, 32 persistent connections are in existence
>and then Linux tells me there are too many connections! Sometimes I
>believe existing connections are reused but not often!
>
>Has anyone else had this sort of experience with persistent connections?
>It is driving me mad so I would appreciate any help or pointers you might have.
>
>Thanks,
>Colleen.
>
>Colleen Williams
>[EMAIL PROTECTED]
>
>0207 484 8825
>
>Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
>http://www.digital-arts.co.uk
Re: [SQL] Persistent Connects (pg_pconnect)
Just looked at the Php documentation... according to that.. it goes...
"An 'identical' connection is a connection that was opened to the same
host, with the same username and the same password (where applicable)."
Perhaps that means you need to specify the host for this to work.
Let me know on your findings. I'll be interested to know.
Cheers,
Keith :)
At 12:34 PM 21/11/2000 +, Colleen Williams wrote:
>Hello all,
>
>I am having some problems with persistent connections.
>
>The PHP scripts in my content management system (CMS) are connecting using
>pg_pconnect(" dbname='MYDB' user='user' password='password''
>port='5432' "). I have not been able to connect to the PostgreSQL
>database when I include the host parameter in the pg_pconnect string and I
>don't understand why. Each time one of us uses the CMS, a new persistent
>connection process owned by postgres is started up. With 3 people
>accessing the CMS sporadically, 32 persistent connections are in existence
>and then Linux tells me there are too many connections! Sometimes I
>believe existing connections are reused but not often!
>
>Has anyone else had this sort of experience with persistent connections?
>It is driving me mad so I would appreciate any help or pointers you might have.
>
>Thanks,
>Colleen.
>
>Colleen Williams
>[EMAIL PROTECTED]
>
>0207 484 8825
>
>Digital Arts | British Columbia House | 1 Regent Street | London | SW1Y 4NR
>http://www.digital-arts.co.uk
Re: [SQL] Blob Upload
Do you mean VARCHAR[30] or VARCHAR(30)?? I think you're creating an array of chars... but what you want is just a VARCHAR type with 30 characters... It may explain the array error you are getting. Keith At 11:07 AM 21/11/2000 -0200, Aristeu Gil Alves Junior wrote: >I´m trying to upload a gif image from a web interface (apache/php) without >success. >I´m using the input type ="file". >Have someone already made this work? > >And even when I try to upload from the SQL interface of Tkl PgAccess... : >The table: >CREATE TABLEimages (name VARCHAR[30] , img OID); > >SQL Command >INSERT INTO images (name, img) VALUES (´img01.gif´ , >lo_import(´/images/test.gif´) ); > >it brings up the error: >ERROR: array_in: Need to specify dimension > >Any help will be of great value. >Thanks to all. > >Best Regards > >Aristeu Gil Alves Junior<[EMAIL PROTECTED]> >IT Analyst >Porto Alegre/RS - Brasil > >"Communications without intelligence is noise; >Intelligence without communications is irrelevant." >Gen. Alfred. M. Gray, USMC
Re: [SQL] Create table doesn't work in plpgsql
I believe a couple of months back... a EXECUTE command was added to plpgsql to allow users to dynamic sql statements. So if you get the current development version you should be able to call EXECUTE CREATE TABLE or whatever sql you like. (I think this is still not in 7.03, not sure though) I'm not sure about the exact syntax, but if you look in past threads for "execute" I'm sure you'll find it. Have fun. Merry Xmas. Keith. At 02:00 PM 22/12/2000 +0100, Volker Paul wrote: > > Can this be done using tcl or perl? > >I'll try them and report what I find out. > >V.Paul
[SQL] Boolean and Bit
Hello All, This is my first post (so be gentle with me)... Is there a searchable archive? I would like suggestions and examples of adding SQL-92 data type BIT compatibility to a PostgreSQL schema. >From the doc's I gather you can "CREATE TYPE bit" with storage int or int4... but I don't know about the input/output for zero and one. Should SQL (ODBC) be able to ask "WHERE bitfield;" or should it ask "WHERE bitfield = 1;" ? Any response gratefully recognized... Keith
[SQL] Re: Boolean and Bit
Josh Berkus wrote: > > > The solution to this is not to use BLOBs, but rather to use file system > handles for the location of the binary data on the server. This way, > all you need is DOS-to-UNIX and UNIX-to-DOS translation for the > filesystem handles, something easily accomplished through > string-manipulation functions or stored procedures. > > -Josh Berkus Do you have an example for implementing this?
[SQL] Inserting and incrementing with MAX aggregate
I was wondering if this statement was still supported in 7.0.x versions of pgSQL. I upgraded from a 6.x version where say: insert into events (eid,name) values (max(eid)+1,'server down'); works but not in the 7.x variants. I know that I could use the serial type for that column but in the interest not having to rewrite the code (or dump, drop and recreate the tables/data), I wanted to know if there was a more stand way to incrementing a field automatically that would be fairly portable. Any help would be appreciated. Keith Perry VCSN Inc. [EMAIL PROTECTED] http://vcsn.com
[SQL] Re: Inserting and incrementing with MAX aggregate
Ahhh, thank you that worked. I don't know why but for some reason I didn't think I could do a subquery in an insert *laff*- 'learn something new everyday :) Keith- Ian Harding wrote: > Could you not: > > insert into events (eid,name) values ((SELECT max(eid) FROM > EVENTS)+1,'server down'); > > It looks like it would work. It's just a subquery... > > Ian > > Keith Perry wrote: > > > I was wondering if this statement was still supported in 7.0.x versions > > of pgSQL. I upgraded from a 6.x version where say: > > > > insert into events (eid,name) values (max(eid)+1,'server down'); > > > > works but not in the 7.x variants. > > > > I know that I could use the serial type for that column but in the > > interest not having to rewrite the code (or dump, drop and recreate the > > tables/data), I wanted to know if there was a more stand way to > > incrementing a field automatically that would be fairly portable. Any > > help would be appreciated. > > > > Keith Perry > > VCSN Inc. > > [EMAIL PROTECTED] > > http://vcsn.com
[SQL] DROP Column
Is DROP Column implemented in 7.x? Keith
[SQL] Problem with Day of Week
Greetings,
I notice some talk about date problems and interestingly enough planning
out an application in which I will need to be able to manipulate dates.
I notice however that there seems to be a discrepancy with the day or
week in 7.0.3
---
pmhcc=# select date_part('dow','now'::timestamp);
date_part
---
1
(1 row)
pmhcc=# select to_char('now'::timestamp,'D');
to_char
-
2
(1 row)
pmhcc=# select version();
version
-
PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)
pmhcc=# select now();
now
2001-01-29 12:57:46-05
(1 row)
---
Now as far as I know, Sunday is supposed to 1, so I would think that
date_part is doing something funky unless I am mis-understanding
something. I was hoping that someone might be able to shead some light
on this. Thanks.
Keith C. Perry
VCSN, Inc.
http://vcsn.com
[SQL] Query Limitations
PostgreSQL 6.4 seems to have limitations in Query Length when I "CREATE VIEW" ... is this limit defined further, when I create a query on a query... it seems to compound the queries and reach the limit sooner!! Is this limit programmable? Is it default higher in 7.0? What is the most stable 7.X release? Keith ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Index on View ?
Is it possible (feasible) to create an index on a view. We have a large table and a defined sub-set (view) from this table, would it be possible to keep an index of the sub-set. Keith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Index on View ?
Richard Huxton wrote: > > Indexes on underlying tables should be used though. Difficult to suggest > what indices you might need without knowing the view/tables/queries > involved. As an example I may have an "Invoice" table with several thousand invoices. I could design a query/view "Aged" to get all unpaid invoices greater than 15 days old. I would often look for Invoices per Client and should have an index on Invoice(ClientID). e.g. CREATE INDEX Invoice_ClientID ON Invoice(ClientID); Is there any advantage in having an index on ClientID for the Aged query? e.g. CREATE INDEX Aged_ClientID ON Aged(ClientID); Would this index be continually maintained by the RDBMS or only on lookup? Keith ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
Not quite sure how your code is organised...
but you could access the variable $conn by including "connect.php" into the
"query.php" script.
Otherwise, you will need to use persistent connections... which can be
achieved via pg_pconnect...
a persistent connection will instead of creating a new database connection
each time.. it will try to use
an existing connection that is no longer being used (persistent connections
do tend to have a lot of quirks tho)
Keith
At 11:33 AM 18/04/2001 +0200, Picard, Cyril wrote:
>Hi all, sorry for the maybe offtopic questions, but I did not get any answer
>from the PHP ML.
>
>I would like to query a postgresql db with the php language.
>Today I wrote a script (connectandquery.php) performing the following :
>- connect to the DB : $conn = pg_Connect("dbname = foo");
>- execute the query : $res = pg_Exec($conn,"SELECT * from BAR");
>
>
>But I would like to write this in two scripts :
>- connect.php : $conn = pg_Connect("dbname = foo");
>- query.php : $res = pg_Exec($conn,"SELECT * from BAR");
>
>but I don't know how to get the $conn variable (defined in connect.php) in
>the script query.php.
>
>Any idea is welcome !
>
>---(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 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Escape Quotes
When using apostrophies the PostgreSQL string seems to like an escape character as follows: update client set code = 'O\'SHEA' where clientid = 2; The ANSI-92 standard seems to suggest that this could/should be handled by ::= ' ::= update client set code = 'O''SHEA' where clientid = 2; Is it possible to get/configure PostgreSQL to handle as within a dleimited string? Keith Gray ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Escape Quotes
Tom Lane wrote: > > Keith Gray <[EMAIL PROTECTED]> writes: > > Is it possible to get/configure PostgreSQL to handle > > as within a dleimited string? > > We already do. > > regression=# select 'O''SHEA'; > ?column? > -- > O'SHEA > (1 row) > > regards, tom lane Sorry, This may be a problem in "ipgsql" then?? ...or is it different in update from select? Keith ---(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: Escape Quotes
> > > Is it possible to get/configure PostgreSQL to handle > > > as within a delimited string? > > > > We already do. > > > > This may be a problem in "ipgsql" then?? > ...or is it different in update from select? The problem is in ipqsql... it doesn't handle update, but does handle select. Both work fine for psql (linux). Keith. ---(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] ipsql (was - Escape Quotes)
Roberto Mello wrote: > > What the heck is ipsql?? > > -Roberto ipgsql A Win32 client for interactive Postgres session Keith README Interactive PostgreSQL presents comfortable windows environment to execute sql queries, edit tables data, view tables list and structure, execute sql scripts, etc. This application design on Delphi using PostgreSQL components, which included in Winzeos Library: http://www.zeos.dn.ua/download/winzeos-latest.zip PARAMETERS Connect parameters for Interactive PostgreSQL stored in the ipgsql.ini file in the same directory that ipgsql.exe You can change its manually or using File/Options dialog Parameters short description: [Preferences] PgSQLPort= ; PostgreSQL port number (default 5432) IsAlive=1 ; Open alive queries (default 1) IsCached=0; Use cached updates (default 0) QueryAll=0; Query all records when open (default 0) AutoCommit=0 ; Auto commit updates (default 0) AutoRecovery=0; Auto rollback transaction when ; errors occured (default 1) Login= ; PostgreSQL login(no default) Host= ; Host name (no default) DataBase=; Database name (no default) EXTRA FEATURES Program supports PostgreSQL Large Objects. In postgreSQL database they represented as Oid fields which store LO handle. In IPgSql these fields translate to Blob field. You may store in Blob fields text, images or any binary data. Open blob field editor by double click in grid or choose View/Blob Editor menu item. LICENCING Zeos Library is distributed with the GPL licence and costs you nothing. DOWNLOADS The latest version can be found on: http://www.zeos.dn.ua/download/ipgsql-latest.zip KNOWN BUGS 1. When your sql query crash and AutoRecovery set to false end transaction manually. If AutoRecovery set to true after crash transaction is auto rollbacked. 2. IPgSql works only with PostgreSQL 6.5+ CREDITS - Steve Wei for idea and support designing PostgreSQL components Yours, Sergey Seroukhov, Chief Engineering of Capella Development Group. <[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] Re: where's ALTER TABLE table DROP [ COLUMN ] column???
Bruno Boettcher wrote: > > Hello! > > as far as i can tell, i can add columns to a table, but can't remove > them later on. > > is this true? My thought would be that somebody must have written an automated version of select [all columns except named] from [named table] into droptemp; drop [named table]; select * from droptemp into [named table]; Would this be available or easily written as a "rule" ?? Could anyone familiar with the code comment? -- Keith Gray ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] ODBC 7.1
Greetings All, Is this the correct forum to discuss ODBC driver issues? I have a Linux/PostgreSQL server which has been upgraded to run 7.1 - the main reason was to be able to use the TOAST extensions. We have some documentation attached to fields which is up to 64kb. After upgrading the server I installed the current (7.1) ODBC driver for Win32. It now seems that concurrent table writes are limited? My write times are fairly slow. (significantly slower than 6.4/7.0) Some tables which are updated simultaneously - like sales and general ledger are not staying in synch. Any suggestions would be welcomed (including upgrades) -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Index Scan Backward vs. Sort/Sequential Scan when using ORDER BY
Greetings, I have stumbled upon a confusing aspect of PostgreSQL queries involving ORDER BY. In trying to figure out just why my ORDER BY queries were so slow, I came across something interesting. First, let me give you 2 very similar queries: 1) SELECT p.uid FROM client_profiles p INNER JOIN client_profiles_2 c USING(uid) WHERE (p.profiles_gender='M') AND (p.profiles_orientation[2] = 'F' OR p.profiles_orientation[1]='M') ORDER BY c.profiles_2_last_update DESC LIMIT 5; 2) SELECT p.uid FROM client_profiles p INNER JOIN client_profiles_2 c USING(uid) WHERE (p.profiles_gender='F') AND (p.profiles_orientation[2] = 'F' OR p.profiles_orientation[1]='M') ORDER BY c.profiles_2_last_update DESC LIMIT 5; The only difference is in #1, p.profiles_gender='M' while in #2 p.profiles_gender='F'. SELECT count(uid) FROM client_profiles WHERE profiles_gender='M'; -- 408526 SELECT count(uid) FROM client_profiles WHERE profiles_gender='F'; -- 54713 Here are the EXPLAINs: 1) EXPLAIN #1: Limit (cost=0.00..1763.83 rows=5 width=24) -> Nested Loop (cost=0.00..2203068.58 rows=6245 width=24) -> Index Scan Backward using index_client_profiles_2_last_up on client_profiles_2 c (cost=0.00..239553.52 rows=394263 width=16) -> Index Scan using client_profiles_pkey on client_profiles p (cost=0.00..4.97 rows=1 width=8) 2) EXPLAIN #2: Limit (cost=36046.44..36046.44 rows=5 width=24) -> Sort (cost=36046.44..36046.44 rows=160 width=24) -> Nested Loop (cost=0.00..36040.58 rows=160 width=24) -> Index Scan using index_client_profiles_gender on client_profiles p (cost=0.00..35064.98 rows=198 width=8) -> Index Scan using client_profiles_2_pkey on client_profiles_2 c (cost=0.00..4.91 rows=1 width=16) Now the only reason I can see to explain this is because there are many more p.profiles_gender='M' than p.profiles_gender='F', Postgres knows its faster to do a Index Scan Backward than a Sort/Sequential Scan (and trust me, it is!). However it thinks the opposite when I am searching for p.profiles_gender='F' and form my results, that just isn't true. Does anyone have any insight as to how I can 'force' postgres to use Index Backward Scan for #2??? Or, perhaps another method of making my ORDER BY faster ?? Your help would be greatly appreciated, Thanks -- Keith Bussey [EMAIL PROTECTED] Programmer - WISOL.com (514) 398-9994 ext. 225 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] PgAdmin
The install for PgAdmin wont run without MDAC 2.5?? MDAC 2.5 is installed as part of an upgrade(?) to Internet Explorer 5.5 I have IE5.5 but cannot install PgAdmin. What is the exact dependency? Can I force an Install? -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[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] Alias Join Table
If I have the following 'Hierachy' table... Child | Parent -- 1 | 0 2 | 1 3 | 1 4 | 3 5 | 4 6 | 3 7 | 4 How do I return a list 5,4,3,1 ? SELECT a.Child FROM Hierachy AS a, Hierachy AS b WHERE a.Parent = b.Child AND a.Child = 5; ... is obviously a bit simplistic in my approach. -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Beginner's List
Josh Berkus wrote: > > To help remedy this, ... > Can anyone suggest something? > Could we set-up a forum with a product like Request Tracker where a group of experienced users could take questions from a web-based queue? -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] ORDER BY case insensitive?
Jason Earl wrote: > > You can, however, create an index like: > > create index MyTable_lower_idx on MyTable > (lower(name)); > > It won't help with your particular query, but it > certainly would help for queries like: > > SELECT * FROM MyTable WHERE lower(name) = 'jason'; > How would PostgreSQL know to use the index MyTable_lower_idx when I do a ... SELECT * FROM MyTable WHERE lower(name) LIKE 'jas%'; -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] 2 Selects 1 is faster, why?
Masaru Sugawara wrote: > > You are right. And this type of optimising are not yet implemented. > Tom said it in the prior discussions. > ...but is it true that if you place the filter clause first, the join will not have to complete the whole table? eg. SELECT item.description, stock.available FROM item, stock WHERE item.itemid = '1234' AND item.itemid=stock.itemid; ...would be more efficient than, SELECT item.description, stock.available FROM item, stock WHERE item.itemid=stock.itemid AND item.itemid = '1234'; -- Keith Gray Technical Services Manager Heart Consulting Services P/L mailto:[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])
Re: [SQL] Problem with my query whithout double-quotes
Stephan Szabo wrote: > On Wed, 24 Jul 2002, ROUWEZ Stephane wrote: > > >>Hi, >>My pgsql runs on WinNT Server 4. When I try to >>SELECT nom, prenom FROM individu WHERE numero=2 >>I have : ERROR: Relation "individu" does not exist >>It only works if I write : >>SELECT "Individu"."Nom","Individu"."Prenom" FROM "Individu" WHERE >>"Individu"."NumIndiv"=2 >>Can someone help me ? >> > > It looks like you created the table with double quotes around the > names at which point you should always use double quotes to refer > to it (yes, if the name was "foo" you *can* refer to it as foo, but > you really shouldn't). Your table names are case sensitive. PostgreSQL will make them all lowercase by default, unless you quote them. -- Keith Gray Technical Services Manager Heart Consulting Services P/L mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Timestamp Error - 7.2
We have moved from 7.1 to 7.2 and get the following error when
extracting dates.
Bad timestamp external representation ' '
eg. INSERT INTO mytable VALUES('1', '2001-09-24')
Seems to accept dd/mm/
(What about ISO default?)
--
Keith Gray
Technical Services Manager
Heart Consulting Services
---(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] IN, EXISTS or ANY?
Josh Berkus wrote: >>But EXISTS is an entirely different animal which is often faster >>... isn't that in the FAQ? There is no reference to EXISTS in the SELECT documentation?? Is this explained somewhere else? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Slow performance on MAX(primary_key)
Help, I have just been comparing some large table performance under 7.1 using the select max(primary key)from table; We are using this for various functions including sequence. It is taking 9 seconds to return this from around 1 million records. Shouldn't this be an instantaneous lookup? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Slow performance on MAX(primary_key)
Ludwig Lim wrote: >>I have just been comparing some large table >>performance under 7.1 using the >> >> select max(primary key)from table; >> > > Try using the following as alternative : > > SELECT primary_key > FROM table > ORDER BY primary_key desc > LIMIT 1; > > This should work if primary_key is indexes. > > As of now, Max() doesn't utilizes the indices hence > it always do a sequential scan. Thanks Ludwig, That does help performance, but I was using a "standard" SQL command wrapped in a VB6 ADO ODBC program. Is this likely to be sorted in 7.2 ? Is anyone looking at this? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Slow performance on MAX(primary_key)
Richard Huxton wrote: >>> As of now, Max() doesn't utilizes the indices hence >>>it always do a sequential scan. >>Is this likely to be sorted in 7.2 ? >>Is anyone looking at this? > As I understand, the problem is that the optimisation only applies for simple > cases... Getting MIN() adn MAX() seems fairly trivial to me. When is on an index or more importantly Primary Key it must be a common SQL. Would it be possible in the code to look at the field in MIN() or MAX() and if it is indexed use a similar method to the suggested SQL work around? Can I help this to happen? -- Keith Gray Technical Services Manager Heart Consulting Services ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL to list databases?
On Thu, Jan 23, 2003 at 12:56:50PM -0600, Ben Siders wrote: > > Is there a query that will return all the databases available, similar > to what psql -l does? Any time you wish to know what psql is doing with ''meta'' commands (like the \ commands), use the -E switch, which will display the queries it's sending to the backend. man psql for more details. --keith -- [EMAIL PROTECTED] public key: http://wombat.san-francisco.ca.us/kkeller/kkeller.asc alt.os.linux.slackware FAQ: http://wombat.san-francisco.ca.us/cgi-bin/fom ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SELECT from a list
Hello
I am wondering if it is possible to use a SINGLE LIKE statement for a
selection from a list.
For example: If I want to return all results that a phrase starts with a
number, can I make a call similar to the following:
SELECT * FROM table WHERE phrase LIKE
{'0%','1%','2%','3%','4%','5%','6%','7%','8%','9%'};
If not is there an easier way than having to call this:
SELECT * FROM table WHERE phrase LIKE '0%' OR phrase LIKE '1%' OR phrase
LIKE '2%' OR phrase LIKE '3%' OR phrase LIKE '4%' OR phrase LIKE '5%' OR
phrase LIKE '6%' OR phrase LIKE '7%' OR phrase LIKE '8%' OR phrase LIKE
'9%';
Thank you.
Keith
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] update/insert data
Hi All, I have two tables in different schemas. The first table in the data_transfer schema is loaded with a COPY command. I need to transfer the data to the second schema inserting new records and updating existing records. What is the best way to achieve this functionality? Kind Regards, Keith __ 99main Internet Services http://www.99main.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] sum query
Hi All, I am trying to join three tables and sum the quantities. The first table contains all of the possible items. The second table contains orders and the third table contains the items on each order. For all items found in tbl_item I need the total quantity on open orders. If an item is not on any open order the quantity should be zero. Can someone please explain the best way to do this? tbl_item id| ... --+... AB12 | ... CD34 | ... EF34 | ... GH12 | ... JK56 | ... tbl_order order | closed |... --++... 1 | false |... 2 | true |... 3 | true |... 4 | false |... 5 | false |... tbl_item order | id| quantity --+---+- 1 | AB12 | 10 1 | CD34 | 5 2 | CD34 | 3 3 | EF34 | 2 3 | GH12 | 20 4 | GH12 | 4 5 | AB12 | 5 id| quantity --+- AB12 | 15 CD34 | 5 EF34 | 0 GH12 | 4 JK56 | 0 Kind Regards, Keith __ 99main Internet Services http://www.99main.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] Returning a bool on DELETE in a proc.
> Hi, > > I'm currently writing a function which encapsulates a delete and should > return a bool as indicator for success. > > I tried: > > DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2; > SELECT TRUE; > > but this makes me not happy. > How can I distingruish wehter DELETE affected 0 or more rows and > return that while DELETE is not capable of returning any value? > > And is the whole function executed if the DELETE fails or will it > stop bevore the select? > > I had a read throu SQL-functions while nothing matched me. > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster Robert, I do not know what version you are working with but here is the 7.4 documentation on result status. http://www.postgresql.org/docs/7.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS Kind Regards, Keith PS I am a novice myself so don't put too much faith in my post. ;-) __ 99main Internet Services http://www.99main.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Conversion ideas (Views/procedures)
> I am new to Postgres and am converting our MSSQL database to Postgres. > > I just did my first function. > My question is does it make sense to create functions for some of > the common functionality available to the MSSQL world (left, isnull, > etc) so I do not need visit every view, and stored procedure to > remove them? If it does is there a body of work anywhere? If not > what is the best approach just remove all non existent (re-engineer) > functions from my views and procedures. I understand some of the > items are available, but a bit different either the name of the > function or its use. I did see a how to that alerted me to the > datefiff being date_diff and to watch out for the yy needing to be > 'year' etc. he mentioned to use coalesce() instead of isnull also. > Any other common ones I should be aware of? > > Joel Fradkin > I like the idea myself of a 'library' that when placed first in the 'search path' would allow an application designed for a MSSQL database to operate. Obviously a similar library for Oracle, MySQL and others would also be benefical. They could have the affect of increasing PostgreSQL's use by reducing the immediate porting requirements. I do not know if this is possible but it would be cool stuff. Kind Regards, Keith __ 99main Internet Services http://www.99main.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] hardware mod based on feedback from the list
On Thu, 27 Jan 2005 17:11:55 +, Richard Huxton wrote > > Joel Fradkin wrote: > > > > I was told the Xeon processors will do fine up to 64gig. I > > realize the 64bit chips may be faster, but it is also new > > and I feel safer with existing technologies and hardware > > vendors. > > > > My understanding is that CPU (4 Xeon 3gig processors) will > > not be a issue, but hopefully adding the additional drive > > systems will help the most for IO is what I am told is the > > big issue and hopefully utilizing the recommendation will > > help minimize the bottleneck. > > I believe there may well be an issue with multiple Xeon's - check > the mailing list archives for details. > > -- >Richard Huxton >Archonet Ltd Joel, I am currently running PostgreSQL v8.0.0 using RedHat Enterprise Linux v3 on a Dell with '2 PROCESSOR, 80532K, 2.8GHZ, 512K, 533, DECISION ONE'. So far so good. :-) Kind Regards, Keith ---(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] More efficient OR
Hi All, In several of my SQL statements I have to use a WHERE clause that contains mutiple ORs. i.e. WHERE column1 = 'A' OR column1 = 'B' OR column1 = 'C' Is there a more efficient SQL statement that accomplishes the same limiting functionality? Kind Regards, Keith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] More efficient OR
> > Hi All,
> >
> > In several of my SQL statements I have to use a WHERE clause
> > that contains mutiple ORs. i.e.
> >
> > WHERE column1 = 'A' OR
> > column1 = 'B' OR
> > column1 = 'C'
> >
> > Is there a more efficient SQL statement that accomplishes the
> > same limiting functionality?
> >
> > Kind Regards,
> > Keith
> >
>
> Scott wrote:
> The in() construct is (nowadays) basically the same as
> ORing multiple columns;
>
> where column1 in ('A','B','C')
>
>
> Sean Davis wrote
> Could 'in' or 'between' do what you want? I know that using 'in'
> is equivalent to what you have below. Could 'between' be more
> efficient--you could do explain analyze on various options to see
> what the actual plan would be.
>
> Sean
Thanks Scott and Sean for the post.
It sounds like IN will save some typing and code space but not decrease the
execution time.
BETWEEN won't work for my real life query because the limiting values are
quite disparate.
Kind Regards,
Keith
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
UPDATE TRIGGER on view WAS: Re: [SQL] Relation in tables
On Wed, 16 Feb 2005 19:56:25 +0100, PFC wrote > [snip] Use UPDATE triggers on the > views, which in fact write to the products table [snip] You can DO that!?! Are you saying that a client can DELETE or INSERT or UPDATE a view and through a trigger you can make this happen? Way cool. Can you provide a simple example? And all this time I thought that you couldn't write to a view. Kind Regards, Keith ---(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] aggregate / group by question
T E Schmitz wrote:
Hello,
I must apologize for not coming up with a more descriptive subject line.
I am struggling with the following query and I am not even sure
whether what I want to achieve is possible at all:
The problem in real-world terms: The DB stores TRANSAKTIONS - which
are either sales or refunds: each TRANSAKTION has n ITEMS related to
it, which contain their RETAIL_PRICE and DISCOUNT. At the end of day,
a total is run up, which should show the sum of refunds, sales and
discounts.
Tables:
TRANSAKTION
---
KIND ('R' or 'S' for refund or sale)
TRANSAKTION_PK
PAYMENT_METHOD (cheque, cash, CC)
ITEM
TRANSAKTION_FK
ITEM_PK
RETAIL_PRICE
DISCOUNT
Desired result set:
PAYMENT_METHOD | category | SUBTOTAL
Cash | sales| 103,55
Cash | discounts| -0,53
Cash | refunds | -20,99
CC | sales| 203,55
CC | discounts| -5,53
CC | refunds | -25,99
where
sales amount is the sum of RETAIL_PRICE
discount amount is the sum of DISCOUNT
refunds is the sum of (RETAIL_PRICE-DISCOUNT)
I've had a stab at it but my sales amount is short of the
RETAIL_PRICEs of all discounted ITEMs:
select PAYMENT_METHOD,
case
when KIND='R' then 'R'
when KIND='S' and DISCOUNT is not null then 'D'
when KIND='S' and DISCOUNT is null then 'S'
end as CATEGORY,
sum(case
when KIND=2 then -(RETAIL_PRICE-coalesce(DISCOUNT,0))
when KIND=1 and DISCOUNT is not null then -DISCOUNT
when KIND=1 and DISCOUNT is null then RETAIL_PRICE
end) as SUBTOTAL,
from ITEM
inner join TRANSAKTION on TRANSAKTION_PK =TRANSAKTION_FK
where ...
group by PAYMENT_METHOD,CATEGORY
order by PAYMENT_METHOD,CATEGORY
Your comment implies that the amount of retail sales is the sum of all
amounts regardless of whether or not discount IS NULL. So perhaps
losing the 'IS NULL' from you retail_price case may fix your statement.
If not...
This may be a bit heavy handed AND I am still a novice AND I am not on
my system so I can't test it but how about
SELECT merged_data.payment_method,
merged_data.category,
merged_data.subtotal
FROM (
-- Get the refunds. (kind = 'R')
SELECT transaktion.payment_method,
'refunds' AS category,
-1 * sum( item.retail_price - COALESCE(item.discount) )
AS subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'R'
GROUP BY transaktion.payment_method
UNION ALL
-- Get the sales. (kind = 'S')
SELECT transaktion.payment_method,
'sales' AS category,
sum( item.retail_price - COALESCE(item.discount, 0) ) AS
subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'S'
GROUP BY transaktion.payment_method
UNION ALL
-- Get the discounts. (kind = 'S' AND discount IS NOT NULL)
SELECT transaktion.payment_method,
'discounts' AS category,
-1 * sum( COALESCE(item.discount, 0) ) AS subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'S'
AND transaktion.discount IS NOT NULL
GROUP BY transaktion.payment_method
) AS merged_data
ORDER BY merged_data.payment_method,
merged_data.category;
--
HTH
Kind Regards,
Keith
---(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] truncating table permissions
Lynwood Stewart wrote: I was expecting "truncate table " to truncate a table if I had delete permissions. This does not appear to be the case. Would someone confirm this for me, or let me know what I am doing wrong. This is the case. You are not doing anything wrong. There was a discussion on this on the NOVICE list beginning on 2/22. The subject was "Question on TRUNCATE privileges" At the end of the day the answer is to have the table owner create a truncate function with SECURITY DEFINER privilege. The following is from Tom Lane. See CREATE FUNCTION. Something like (untested) create function truncate_my_table() returns void as $$ truncate my_table $$ language sql security definer; You'd probably then revoke the default public EXECUTE rights on this function, and grant EXECUTE only to selected users. -- Kind Regards, Keith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL query help?
John McGough wrote: SELECT Count(*) FROM Work WHERE (UserID='user1' AND MAX(Finished)=0) Work:- +---+---++-+--+ | ID | JobID | UserID | Finished | Comment | +---+---++-+--+ | 1 | 1| user1 | 0 | ...| | 2 | 1| user1 | 1 | ...| | 3 | 2| user2 | 0 | ...| | 4 | 3| user1 | 0 | ...| | 5 | 2| user2 | 0 | ...| | 6 | 2| user1 | 1 | ...| | 7 | 3| user1 | 0 | ...| +---+---++-+--+ All I want it to do is return the number of unfinished jobs for a specific user. In this example it would return 1 because job number 3 is not finished and user1 was the last person working on it. but I keep getting MySQL error # - Invalid use of group function John, I may be missing something but how about SELECT count(id) AS unfinished FROM work WHERE userid = 'user1' AND finished = 0 GROUP BY jobid; -- Kind Regards, Keith ---(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] Numeric and CSV under 8.0.1 ?
On Fri, 8 Apr 2005 12:51:47 -0400, Stef wrote > Hello Everyone, > Currently, here at work, I am doing the whole > 'advocacy' part of postgreSQL. It's not really hard to > do, as the other database's are MySQL and Sybase ;) > > There is obviously a whole spat of data munging > going on in the background, and I noticed that psql in > 8.0.1 now accepts CSV ! Great. > > Except, it doesn't really appear to be 100% there. > Numeric's wrapped in '...' don't want to appear to go in. > Is this a 'known problem' ? > > Table "public.visitor_main" > Column | Type | Modifiers > > --+--+-- > iuserid | numeric(12,0)| not null > firstname| character(25)| not null > lastname | character(25)| not null > > Sample Data line > '3236','Alonzo','Peter' > > ERROR: invalid input syntax for type numeric: "'3236'" > CONTEXT: COPY visitor_main, line 1, column iuserid: "'3236'" > > Thoughts ? > Regards > Steph Steph, '3236' is a string not a numeric. As I see it (novice that I am) you have three choices. 1) Write an external program (gawk, sed, etc.) to remove the quotes around that field. 2) Import the data into an intermediate table and then using an after trigger move and manipulate the data using CAST. 3) Import the data into your table using a BEFORE trigger and manipulate the data using CAST. HTH Kind Regards, Keith ---(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] default value for select?
On Mon, 09 May 2005 12:57:41 -0400, Mark Fenbers wrote > I want to update a column in myTable. The value this column is set > todepends on a nested select statement which sometimes returns 0 > rowsinstead of 1. This is a problem since the column I'm trying to > updateis set to refuse nulls. Here's a sample: > > update myTable set myColumn = (Select altColumn from altTable > wherealtColumn != 'XXX' limit 1) where myColumn = 'XXX'; > > MyColumn cannot accept nulls, but sometimes "Select altColumn > ..."returns 0 rows, and thus, the query fails. > > Is there a way to set a default value to be inserted into myColumn > ifand when "select altColumn ..." returns zero rows? > > Mark Mark, I do not know if it will work but I would try the COALESCE function. http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html Kind Regards, Keith ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as
On Tue, 17 May 2005 12:01:03 -0500, Scott Marlowe wrote
> On Thu, 2005-05-12 at 14:07, [EMAIL PROTECTED] wrote:
> > Hi:
> >
> > Oracle has a pseudo-column "ROWNUM" to return the sequence
> > number in which a row was returned when selected from a table.
> > The first row ROWNUM is 1, the second is 2, and so on.
> >
> > Does Postgresql have a similar pseudo-column "ROWNUM" as
> > Oracle? If so, we can write the following query:
> >
> > select *
> > from (select RowNum, pg_catalog.pg_proc.*
> > from pg_catalog.pg_proc) inline_view
> > where RowNum between 100 and 200;
>
> You can get a functional equivalent with a temporary sequence:
>
> create temp sequence rownum;
> select *, nextval('rownum') as rownum from sometable;
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
Scott,
I realize that this thread went off in another direction however your
suggestion proved very helpful for a problem that I was trying to solve. I
wanted the row number of a set returned by a function. Here is a chopped
version of the function that I wrote.
CREATE OR REPLACE FUNCTION func_bom(integer, integer)
RETURNS SETOF func_bom AS
$BODY$
DECLARE
v_number ALIAS FOR $1;
v_line ALIAS FOR $2;
v_type varchar(8);
r_row interface.func_so_line_bom%rowtype;
BEGIN
SELECT tbl_item.item_type INTO v_type
FROM tbl_line_item
JOIN tbl_item
ON tbl_line_item.item_id = tbl_item.id
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line;
IF v_type = 'ASY' THEN
CREATE TEMP SEQUENCE row_number
INCREMENT BY 1
START WITH 1;
FOR r_row IN SELECT tbl_line_item.number,
tbl_line_item.line,
nextval('row_number') AS subline,
tbl_assembly.quantity AS bom_quantity,
tbl_assembly.component_id AS bom_item_id,
tbl_item.item_type AS bom_item_type,
tbl_item.description AS bom_item_description
FROM tbl_line_item
LEFT JOIN tbl_assembly
ON ( tbl_line_item.item_id::text =
tbl_assembly.id::text
)
JOIN tbl_item
ON ( tbl_assembly.component_id::text =
tbl_item.id::text
)
WHERE tbl_line_item.number = v_number
AND tbl_line_item.line = v_line
ORDER BY tbl_line_item.number,
tbl_line_item.line,
tbl_assembly.component_id
LOOP
RETURN NEXT r_row;
END LOOP;
DROP SEQUENCE row_number;
ELSIFv_item_type = 'THIS'
OR v_item_type = 'THAT'
OR v_item_type = 'OTHER' THEN
FOR r_row IN SELECT
[snip]
LOOP
RETURN NEXT r_row;
END LOOP;
END IF;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
Although I have no need to limit the output I tried it just for giggles and it
worked fine.
SELECT * FROM func_bom(12345, 1) WHERE subline between 4 AND 6;
Thanks!
Kind Regards,
Keith
---(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] Rule
David wrote:
What I want to do is setup some kind of rule so that whenever a s_data
field is updated, that the time_stamp gets update to the current time/date.
Normally you want to do that with a before trigger rather than a rule.
Ok, I have no knowledge of Tiggers except what I just read in the docs
section. Look like I need to make a procudure then call it with a trigger.
Is there a better location for Tigger/Procudure Examples. The trigger
seems fairly, however I got lost in the procudure part.
David
David,
Here is a trigger function that I wrote for storing audit information.
Whether or not a query provides the user and/or timestamp this procedure
sets them. Naturally you will need to modify them for your data model.
CREATE OR REPLACE FUNCTION interface.tf_audit_data()
RETURNS "trigger" AS
$BODY$
BEGIN
--Set the user name.
SELECT * FROM session_user INTO NEW.audit_user;
--Set the timestamp.
NEW.audit_timestamp := ('now'::text)::timestamp(6) with time zone;
--Send the modified record down the pipe.
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION interface.tf_audit_data() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO postgres;
GRANT EXECUTE ON FUNCTION interface.tf_audit_data() TO public;
CREATE TRIGGER tgr_audit_data
BEFORE INSERT OR UPDATE
ON sales_order.tbl_line_item
FOR EACH ROW
EXECUTE PROCEDURE interface.tf_audit_data();
--
Kind Regards,
Keith
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[Fwd: RE: Re: [SQL] Rule]
Personally I feel that if this individual can't be bothered to white list the postgresql.org domain they should be banned from the list. Kind Regards, Keith Original Message Subject:RE: Re: [SQL] Rule Date: Wed, 8 Jun 2005 19:02:39 -0300 (BRT) From: AntiSpam UOL <[EMAIL PROTECTED]> To: keithw <[EMAIL PROTECTED]> ANTISPAM UOL » TIRA-TEIMA <http://antispam.uol.com.br> Olá, Você enviou uma mensagem para [EMAIL PROTECTED] Para que sua mensagem seja encaminhada, por favor, *clique aqui* <http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVAMqAqRQr%2BDFbu2DcFQ10C0ySypbrCRoslvleIrQFAMO7c36mP3ZUehXUEg0dUgva%2BhU%0AWwUDyU5D032tO3eNaWIJ%2BSThmoun81A9a3mbOEklX9tn%2FC6RHBh4> Esta confirmação é necessária porque [EMAIL PROTECTED] usa o Antispam UOL, um programa que elimina mensagens enviadas por robôs, como pornografia, propaganda e correntes. *As próximas mensagens enviadas para [EMAIL PROTECTED] não precisarão ser confirmadas*.* *Caso você receba outro pedido de confirmação, por favor, peça para [EMAIL PROTECTED] incluí-lo em sua lista de autorizados. *Atenção!* Se você não conseguir clicar no atalho acima, acesse este endereço: http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVAMqAqRQr%2BDFbu2DcFQ10C0ySypbrCRoslvleIrQFAMO7c36mP3ZUehXUEg0dUgva%2BhU%0AWwUDyU5D032tO3eNaWIJ%2BSThmoun81A9a3mbOEklX9tn%2FC6RHBh4 Hi, You´ve just sent a message to [EMAIL PROTECTED] In order to confirm the sent message, please *click here* <http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVAMqAqRQr%2BDFbu2DcFQ10C0ySypbrCRoslvleIrQFAMO7c36mP3ZUehXUEg0dUgva%2BhU%0AWwUDyU5D032tO3eNaWIJ%2BSThmoun81A9a3mbOEklX9tn%2FC6RHBh4> This confirmation is necessary because [EMAIL PROTECTED] uses Antispam UOL, a service that avoids unwanted messages like advertising, pornography, viruses, and spams. *Other messages sent to [EMAIL PROTECTED] won't need to be confirmed*.* *If you receive another confirmation request, please ask [EMAIL PROTECTED] to include you in his/her authorized e-mail list. *Warning!* If the link doesn´t work, please copy the address below and paste it on your browser: http://tira-teima.as.uol.com.br/challengeSender.html?data=dfj2RcMRVAMqAqRQr%2BDFbu2DcFQ10C0ySypbrCRoslvleIrQFAMO7c36mP3ZUehXUEg0dUgva%2BhU%0AWwUDyU5D032tO3eNaWIJ%2BSThmoun81A9a3mbOEklX9tn%2FC6RHBh4 Use o *AntiSpam UOL* <http://antispam.uol.com.br> e proteja sua caixa postal Personally I feel that if this individual can't be bothered to white list the postgresql.org domain they should be banned from the list. -- Kind Regards, Keith ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: **SPAM** [SQL] Faster count(*)?
Tom Lane wrote: [EMAIL PROTECTED] writes: I believe running count(*) means fulltable scan, and there's no way to do it without it. But what about some "intermediate" table, with the necessary counts? There's a fairly complete discussion in the PG list archives of a reasonably-efficient scheme for maintaining such counts via triggers. It wasn't efficient enough that we were willing to impose the overhead on every application ... but if you really NEED a fast count(*) you could implement it. I'd like to see someone actually do it and put up working code on pgfoundry; AFAIK it's only a paper design so far. I was kicking this around and came up with the following. I have hit a couple of snags. In the function I attempt to count the number of rows in a table being checked for the first time. I wanted to use 'FROM TG_RELNAME' but as you can see I had to hard code my test table and comment out the trigger parameter. FROM tbl_demo--TG_RELNAME Can someone tell me why that won't work? Also the function doesn't seem to be getting ROW_COUNT properly. The end result is that for this test the table is properly inserted into the monitoring table after it's forth insert but it is never updated after that. Can someone help me see the forest through the trees? -- Clean up the environment. DROP TABLE tbl_row_count; DROP TABLE tbl_demo; DROP FUNCTION tf_update_row_count(); -- Build the table for holding the row counts. CREATE TABLE tbl_row_count ( relid oid NOT NULL, row_count int8 NOT NULL DEFAULT 0 ) WITHOUT OIDS; ALTER TABLE tbl_row_count OWNER TO postgres; COMMENT ON COLUMN tbl_row_count.relid IS 'Contains relation id number.'; COMMENT ON COLUMN tbl_row_count.row_count IS 'Contains the number of rows in a relation.'; -- Build a table to test the trigger on. CREATE TABLE tbl_demo ( first_name varchar(30) NOT NULL ) WITHOUT OIDS; ALTER TABLE tbl_demo OWNER TO postgres; COMMENT ON TABLE tbl_demo IS 'Table used for demonstrating a trigger.'; -- Create the trigger function to maintain the row counts. CREATE OR REPLACE FUNCTION public.tf_update_row_count() RETURNS "trigger" AS $BODY$ DECLARE v_row_count int8; BEGIN --Store the row count before it disappears. GET DIAGNOSTICS v_row_count = ROW_COUNT; --Check if this is a new table. PERFORM relid FROM public.tbl_row_count WHERE relid = TG_RELID; IF FOUND THEN -- Data for this table is already in the row count table. IF TG_OP = 'INSERT' THEN UPDATE public.tbl_row_count SET row_count = row_count + v_row_count WHERE relid = TG_RELID; ELSIF TG_OP = 'DELETE' THEN UPDATE public.tbl_row_count SET row_count = row_count - v_row_count WHERE relid = TG_RELID; END IF; ELSE -- This is a new table so it needs to be counted. SELECT count(*) FROM tbl_demo--TG_RELNAME INTO v_row_count; INSERT INTO public.tbl_row_count ( relid, row_count ) VALUES ( TG_RELID, v_row_count ); END IF; RETURN NULL; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION public.tf_update_row_count() OWNER TO postgres; GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO postgres; GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO public; -- Insert some initial data into the demo table. INSERT INTO public.tbl_demo ( first_name ) VALUES ( 'Keith' ); INSERT INTO public.tbl_demo ( first_name ) VALUES ( 'Ed' ); INSERT INTO public.tbl_demo ( first_name ) VALUES ( 'Kryss' ); -- Create the trigger on the demo table. CREATE TRIGGER tgr_update_row_count AFTER INSERT OR DELETE ON public.tbl_demo FOR EACH STATEMENT EXECUTE PROCEDURE public.tf_update_row_count(); -- Examine the starting state of the tables. SELECT * FROM public.tbl_demo; SELECT * FROM public.tbl_row_count; SELECT relid, relname, row_count FROM public.tbl_row_count LEFT JOIN pg_class ON ( tbl_row_count.relid = pg_class.oid ); -- Insert a row. INSERT INTO public.tbl_demo ( first_name ) VALUES ( 'Jarus' ); -- Examine the new state of the tables. SELECT * FROM public.tbl_demo; SELECT relid, relname, row_count FROM public.tbl_row_count LEFT JOIN pg_class ON ( tbl_row_count.relid = pg_class.oid ); -- Insert two more rows. INSERT INTO public.tbl_demo ( first_name ) VALUES ( 'Dani' ); INSERT INTO public.tbl_demo ( first_name ) VALUES ( 'Mary' ); -- Examine the final state of the tables. SELECT * FROM public.tbl_demo; SELECT relid
[SQL] dow question
Hi All, I am working on a query which in part is CASE WHEN extract(dow from tbl_detail.ship_by_date) = 0 THEN 'Sunday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 1 THEN 'Monday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 2 THEN 'Tuesday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 3 THEN 'Wednesday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 4 THEN 'Thursday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 5 THEN 'Friday ' || tbl_detail.ship_by_date::text WHEN extract(dow from tbl_detail.ship_by_date) = 6 THEN 'Saturday ' || tbl_detail.ship_by_date::text END AS sort_by_string Is there a better way to do this? The CASE seems inefficient and wordy but perhaps not. I was hoping for day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS sort_by_string but AFAIK day_name(date) or something like it is merely my fantasy. ;-) -- Kind Regards, Keith ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] dow question
Michael Glaesemann wrote: On Dec 8, 2005, at 11:03 , Keith Worthington wrote: Is there a better way to do this? The CASE seems inefficient and wordy but perhaps not. I was hoping for day_name(tbl_detail.ship_by_date) || tbl_detail.ship_by_date::text AS sort_by_string but AFAIK day_name(date) or something like it is merely my fantasy. ;-) Will to_char() do what you want? Something like test=# select to_char(current_date, 'Day -MM-DD'); to_char -- Thursday 2005-12-08 (1 row) http://www.postgresql.org/docs/current/interactive/functions- formatting.html Michael Glaesemann grzm myrealbox com Michael, That will do nicely. I didn't realize until now after your tip and reading the to_char documentation how much I could do with to_char. to_char(tbl_detail.ship_by_date, 'FMDay, /MM/DD') AS ship_by_string, is exactly what the doctor ordered. Thanks for the tip. -- Kind Regards, Keith ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Defaulting a column to 'now'
On Wed, 14 Dec 2005 13:10:50 -0500, Ken Winter wrote
> How can a column's default be set to 'now', meaning 'now' as of when each
> row is inserted?
>
> For example, here's a snip of DDL:
>
> create table personal_data (.
>
> effective_date_and_time TIMESTAMP WITH TIME ZONE not null default
> 'now',.
>
> The problem is, when PostgreSQL processes this DDL, it interprets the 'now'
> as the timestamp when the table is created, so that the tables definition
> reads as if the DDL were:
>
> effective_date_and_time TIMESTAMP WITH TIME ZONE not null default '
> 2005-12-14 11:00:16.749616-06 ',
>
> so all of the newly inserted rows get assigned effective_date_and_time
> = '
> 2005-12-14 11:00:16.749616-06 ', which in addition to being wrong
> leads to uniqueness constraint violations.
>
> ~ TIA
>
> ~ Ken
Ken,
effective_date_and_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
('now'::text)::timestamp(6) with time zone
Kind Regards,
Keith
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Change definition of a view
On Thu, 9 Feb 2006 18:11:24 +0100, Andreas Roth wrote > Hello, > > one question: Is it possible to add or remove a column from a view > without drop the view and recreate it? > > If one or more rules depend on a view, it's very hard to extend a > view. I use the following procedure to extend a view: - drop depending > rules - drop view - recreate view with additional column - recreate > all rules > > Any help is welcomed! > > Thanks, > Andreas Roth Andreas, I believe that is exactly what you have to do. Kind Regards, Keith ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Returning a set from an function
G'day, Looking for an example showing how to return a set from either a sql function or a plpsqq function. Thanks -- Keith Hutchison http://balance-infosystems.com http://realopen.org http://www.kasamba.com/Keith-Hutchison ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] "CASE" is not a variable
Hi All, The following is a section of code inside an SQL function. When I attempt to run it I get the error message '"CASE" is not a variable'. If I split this into two queries (one for each variable) it works fine. Obviously I have a work around but I would like to understand what I am doing wrong. TIA SELECT tbl_item_bom.so_subline INTO v_so_subline, CASE WHEN tbl_mesh.mesh_type = 'square' THEN ( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0* tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4* tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'cm' THEN 2.54 * tbl_mesh.mesh_size WHEN tbl_mesh.unit_of_measure = 'm' THEN 0.0254 * tbl_mesh.mesh_size ELSE 0 END ) WHEN tbl_mesh.mesh_type = 'diamond' THEN ( CASE WHEN tbl_mesh.unit_of_measure = 'in' THEN tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'ft' THEN 12.0* tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'mm' THEN 25.4* tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'cm' THEN 2.54 * tbl_mesh.mesh_size / 2.0 WHEN tbl_mesh.unit_of_measure = 'm' THEN 0.0254 * tbl_mesh.mesh_size / 2.0 ELSE 0 END ) ELSE 0 END INTO v_mesh_size FROM sales_order.tbl_item_bom LEFT JOIN peachtree.tbl_mesh ON tbl_item_bom.item_id = tbl_mesh.item_id WHERE tbl_item_bom.so_number = rcrd_line.so_number AND tbl_item_bom.so_line = rcrd_line.so_line AND tbl_item_bom.component_type = 'net'; Kind Regards, Keith ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] "CASE" is not a variable
> > "Keith Worthington" <[EMAIL PROTECTED]> writes: > > The following is a section of code inside an SQL function. > > On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote > SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause > (there can be only one). > > regards, tom lane plpgsql This is part of a function inside a v8.0.2 database. I didn't realize that the INTO clause was only limited to one instance. I was trying to accomplish SELECT col_a INTO var1, col_b INTO var2, col_c INTO var3, ... FROM foo WHERE fobar; Kind Regards, Keith ---(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] "CASE" is not a variable
On Wed, 28 Jun 2006 10:48:31 -0700, Bricklen Anderson wrote > Keith Worthington wrote: > >>> "Keith Worthington" <[EMAIL PROTECTED]> writes: > >>> The following is a section of code inside an SQL function. > >> On Wed, 28 Jun 2006 12:16:29 -0400, Tom Lane wrote > >> SQL, or plpgsql? It looks to me like misuse of the plpgsql INTO clause > >> (there can be only one). > >> > >>regards, tom lane > > > > plpgsql > > > > This is part of a function inside a v8.0.2 database. > > > > I didn't realize that the INTO clause was only limited to one instance. I > > was > > trying to accomplish > > > > SELECT col_a INTO var1, > >col_b INTO var2, > >col_c INTO var3, > >... > > FROM foo > > WHERE fobar; > > > > Kind Regards, > > Keith > > try it like > select col_a,col_b,col_c INTO va1,var2,var3 > > not sure if 8.0.2 allows you to do that, however. Apparently it does. Just to wrap up this thread and hopefully help the next person. When SELECTing multiple values into variables only use one instance of the INTO clause. i.e. SELECT col_a, col_b, col_c INTO var1, var2, var3 FROM foo WHERE fobar; Kind Regards, Keith ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] week ending
Hi All, I just finished writing a query that groups data based on the week number. SELECT EXTRACT(week FROM col_a) AS week_number, sum(col_b) AS col_b_total FROM foo WHERE foobar GROUP BY EXTRACT(week FROM col_a) ORDER BY EXTRACT(week FROM col_a); I would like to generate the starting date or ending date based on this number. IOW instead of telling the user "week number" which they won't understand I would like to provide either Friday's date for "week ending" or Monday's date for "week beginning". SELECT AS week_ending, sum(col_b) AS col_b_total FROM foo WHERE foobar GROUP BY EXTRACT(week FROM col_a) ORDER BY EXTRACT(week FROM col_a); -- Kind Regards, Keith ---(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
[SQL] Bitemporal - problem with correalated subquery?
no AND ((A.vt_begin < B.vt_end AND B.vt_end < A.vt_end) OR (A.tt_start < B.tt_stop AND B.tt_stop < A.tt_stop)) AND NOT EXISTS (SELECT customer_no FROM Customers AS B2 WHERE B2.customer_no = B.customer_no AND ((B2.vt_begin <= B.vt_end AND B.vt_end < B2.vt_end) OR (B2.tt_start <= B.tt_stop AND B.tt_stop < B2.tt_stop; And if run on the data below, should pull out customer_no's '2' and '3'. But does not seem to select any of the rows in which there are gaps in Customers during the validity of Prop_Owner?? The data I used is as follows: Customers: customer_no |customer_name | vt_begin | vt_end | tt_start | tt_stop -++----+----++ 1 | keith | 2006-01-01 | -12-31 | 2006-01-01 | 2006-12-31 1 | keith | 2006-01-01 | 2006-12-31 | 2006-12-31 | -12-31 1 | keith | 2006-12-31 | -12-31 | 2006-12-31 | 2007-12-31 1 | keith | 2006-12-31 | 2007-12-31 | 2007-12-31 | -12-31 2 | simon| 2004-01-01 | -12-31 | 2004-01-01 | 2004-12-01 2 | simon| 2004-01-01 | 2004-12-31 | 2004-12-01 | -12-31 <= 2 | simon| 2004-12-31 | -12-31 | 2004-12-15 | -12-31 <= 3 | john | 2000-01-01 | -12-31 | 2000-01-01 | 2001-01-01 3 | john | 2000-01-01 | 2001-01-01 | 2001-01-01 | -12-31 <= 3 | john | 2002-01-01 | -12-31 | 2002-01-01 | -12-31 <= The arrows indicate where there are gaps. Properties: prop_no | prop_name -+-- 1 | house 2 | flat 3 | penthouse Prop_Owner: prop_no | customer_no | vt_begin | vt_end | tt_start | tt_stop -+-++++ 1 | 1 | 2006-02-01 | -12-31 | 2006-02-01 | 2006-12-01 2 | 2 | 2004-02-01 | -12-31 | 2004-01-01 | 2004-12-25 3 | 3 | 2000-02-01 | -12-31 | 2000-01-01 | -12-31 The 2nd and 3rd lines should have not been able to be inserted with the trigger as they "bridge" records with gaps in the Customers table. I hope this makes sense and that someone can explain why the last part of P_O_integrity is not selecting the records covered with gaps correctly. It seems to be returning B2 from the NOT EXISTS correlated sub query rather than B?!? Thanks for any help you can give. Keith ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Temporal databases
Hi Philippe, Have a look at the post I made to the pgsql-sql list I made on 6th November 2007 (day before you posted this). It contains alot of stuff for temporal tables within Postgres. However temporal tables (when done properly) are a very complex subject at the best of times. It depends how much you want to integrate them. A good place to start is Joe Celko's book "SQL For Smarties" and Richard Snodgrass' book "Developing Time-Oriented Database Applications in SQL" - this book is no longer in print, but is downloadable (for FREE!) from: http://www.cs.arizona.edu/~rts/tdbbook.pdf Richard Snodgrass is one of the leading experts in the field. I warn you - the book is heavy going - but so worth it!! Keith On Wednesday 07 November 2007 13:22, Philippe Lang wrote: > Hi, > > Does anyone have experience, tips, links, regarding how to build > temporal databases with Postgresql? > > Thanks > > Philippe > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] returning a recordset with pl/pgsql
Hi everyone, I'm using postgresql with php4. I wanted to write a stored procedure to return a recordset (using pl/pgsql). From what I've read from the documentation it seems like you can only returned postgres types like strings, booleans, etc. Is it possible to return recordsets using pl/pgsql? If so, how do I declare the return type in the declaration? Thanks in advance. Cheers, Keith.
[SQL] 8K Limit, whats the best strategy?
Hi everyone, Just wondering what strategies people have used to get around the 8K row limit in Postgres. If anyone has been troubled by this limitation before and has a nice solution around it, I would love to hear it. My application is a web-based system in which needs to store large amounts of text. The text stored needs to searchable as well. Cheers, Keith.
[SQL] select query that would join two databases
dear all, How to create a query that would join two databases? In MSSQL, i use select * from dbA.dbo.tableA a dbB.dbo.tableB b where a.id=b.id Is there anyway to write the above query in postgressql ? Please email me back. Thanks, Keith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
