Re: [SQL] select returns no line
This locale-thing sounds good...:-) As I remember, we've changed the locale settings after the creation of the table. The former opinions (space after the text, value length, etc.) didn't help us too much. Anyway, thanks to all. So, I try to re-create the index. Attila Quoting Tom Lane <[EMAIL PROTECTED]>: > Attila Kevei <[EMAIL PROTECTED]> writes: > > Our database has a SELECT problem using varchar columns in WHERE > clause > > (but not in all rows!!!). > > We can fix the whole table (or just the row) as shown below but later > it > > seems wrong again (and after the fix the row isn't UPDATEd). > > Very bizarre. Is the bogus SELECT using an index? (Check with EXPLAIN > if you're not sure.) I am suspicious that it is using a corrupted > index. Try dropping and rebuilding the index and see if the problem > persists. > > If you have built Postgres with locale support, an easy way to get > corrupted indexes on text/char/varchar columns is to start the > postmaster with different locale environment variables at different > times. Different locales mean different logical sort orders, and > a btree index that is out of order is corrupt by definition. The > most common way of shooting yourself in the foot is to sometimes start > the postmaster from a boot script, and sometimes start it by hand from > a user login that has different locale environment than the boot script. > 7.1 will have some defenses against this, but in older releases you > have to be careful. > > regards, tom lane > -- x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x
[SQL] How to change the ownership of the table?
Hai Please, any one guide me how to change the ownership of the table? Regards, -- Ramesh HR Trainee Engineer EASi Technologies 213, 3rd Main, 4th Cross Chamrajpet, Bangalore - 560 018 India Ph.: 660 1086 / 660 2365 / 667 2984 Extn.: 155 Facsimile: 667 5274 www.easi.soft.net
[SQL] Use of INNER JOIN and others ??!?
Is it possible to use the statements INNER JOIN, OUTER JOIN, LEFT JOIN and RIGHT JOIN in the new version of PostgreSQL 7.1 ? Luis Sousa
Re: [SQL] DATE
john whale wrote: >PLEASE ADVISE HOW I SHOULD ALTER THE COMMAND: > ><$NOW;DD;> > >TO GIVE ME A DATE THAT IS X DAYS FORWARD > >ie: I WISH TO REPRESENT A DATE IN FORM WHICH IS A 7 DAYS FORWARD >OF THE DATE NOW. I don't recognise the format you are using, but in standard SQL: template1=# select CURRENT_DATE as now, template1-#CAST (CURRENT_DATE + INTERVAL '7 days' AS DATE) as week; now |week + 2001-01-24 | 2001-01-31 (1 row) Use to_char() for date formatting. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If anyone has material possessions and sees his brother in need but has no pity on him, how can the love of God be in him?" I John 3:17
[SQL] Some queries
Dear sir, Q 1) While retrieving BLOBs from the database... There is an Exception displaying : with message... Fast path protocol error : z Eventhough i am importing like : import postgresql.fastpath.*; import postgresql.largeobject.*; and connecting : Fastpath fp; LargeObjectManager lobj; lobj = ((postgresql.Connection)conn).getLargeObjectAPI();// ...code that opens a connection ... fp = ((postgresql.Connection)conn).getFastpathAPI(); //Fastpath object allowing access to functions on the postgresql backend. by osing lobj i want to get Image directy into HTML by lobj.open(OID). Q 2) Is it posiible to get LargeObjects from database and display in HTML. Ex: I want to display image file dynamically by retrieving database , without using streams . i.e without creating file and storing it to disk. Could you answer these queries ...and reply back ASAP.. If there are any such methods pls. send me the code too.. Rgds Som
[SQL] i have a big trouble
Hello list, i expirienced that postgres seems doesn't want to eat it's own dump... why? even freshly created! whats wrong with me? www_mod_lv=> \copy news from '/home/sites/www.mod.lv/web/news.sql' \. ERROR: copy: line 2, pg_atoi: error in " At the international humanitarian mission of KFOR the Latvian unit participates with 10 soldiers from the National Armed Forces. The KFOR Latvian contingent - Military Police and medical units - will operate within the contingent of UK. " : can't parse " At the international humanitarian mission of KFOR the Latvian unit participates with 10 soldiers from the National Armed Forces. The KFOR Latvian contingent - Military Police and medical units - will operate within the contingent of UK. " PQendcopy: resetting connection i tried to do \copy from stdin, than throw in a one row of data, ended with \., it doesnt work also. This is my table: CREATE TABLE news( id SERIAL PRIMARY KEY, flag CHAR(1) NOT NULL, rubrika CHAR(1) NOT NULL, datums_ TIMESTAMP NOT NULL, datums VARCHAR(30) NOT NULL, virsraksts VARCHAR(200) NOT NULL, teksts TEXT, url VARCHAR(30), url_txt VARCHAR(50) ); an example row of data (tab separated) attached. anyone may have an idea? -- Sandis 609 9237502 45 a 2 2000-03-24 17:09:52+02 24.03.2000 ON THE RESULTS OF THE VISIT IN BRUSSELS Today on the 24th of March, the Minister of Defence, Mr. Ìirts Valdis Kristovskis, and Minister of Foreign Affairs, Mr. Indulis Bçrziòð, participated at the meeting of the North Atlantic Council and Latvia, led by the NATO Secretary General, Lord George Robertson. The Latvian representatives presented the Progress report on what has been accomplished and achieved in implementation of the Membership Action Plan (MAP) of Latvia for 2000. \ The Minister of Defence, Mr.Ìirts Valdis Kristovskis, in his speech informed ambassadors of the NATO member-countries, on what has been accomplished in the defence system of Latvia within MAP. Mr. Ì.V.Kristovskis noted, that Latvia's activity and full determination to participate in the process, is aimed at preparing Latvia to be ready to accept the invitation to join the North Atlantic Treaty Organisation at the next round of enlargement. At the same time Minister of Defence emphasised, that a lot still has to be done in improvement of the systems of procurement and military education. \ During the meeting it was noted that Latvia is one of the best NATO candidate - states as concerns the execution of the Membership Action Plan. It was also concluded at the session, that Membership Action Plan of Latvia for 2000 is well prepared, realistic, and financially well founded. The ambassadors of NATO member countries, acknowledged that Latvia has a systemic and purposeful approach in solving state defence related issues. \ Ambassadors of the NATO members states also positively evaluated Latvia's readiness to contribute to European security, more precisely - to participate by providing officers of the Latvian National Armed Forces for peace-keeping and peace - support missions in Bosnia - Hercegovina and Kosovo, as well as noting the close co-operation among the Baltic States themselves. \ Participants at the session also discussed the need for Latvia to enlarge her defence budget to 2% of the GDP, which is the average level of NATO countries. To this the Latvian representatives responded that the Government of Latvia has expressed the political will increasing state defence expenditures. Today at the session in Brussels, both the Minister of Defence, Ì.V.Kristovskis, and Minister of Foreign Affairs, once more stressed Latvia's strong determination to continue the initiated process of preparation for joining NATO.
Re: [SQL] Use of INNER JOIN and others ??!?
> Is it possible to use the statements INNER JOIN, OUTER JOIN, LEFT JOIN > and RIGHT JOIN in the new version of PostgreSQL 7.1 ? Yes in 7.1beta. See FAQ 'outer' entry for a short description. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] Convert an interval in seconds
Hi all !
I've search all the doc to find a function which would allow me to convert an
interval into a number of seconds. What I get is @ 1 hour, what I would like is
-3600. I'd think to something like select interval('sec', myTime) - Ingres
syntax - but it doesn't work.
Does somebody has an idea ?
__
B. Carrupt
[SQL] blobs and small binary objects
I'm new to Postgresql and am trying to figure out how I'd store Serializable objects, byte arrays and potentially large binary objects. JDBC has APIs for getObject(), getBytes() and getBlob(), but how do I define those attributes inside a table? I don't see the typical BYTE or BINARY or BLOB types. I know that 7.0.3 still suffers from the 8K row limit, so I'd hope that the JDBC library would automatically handle this for me, if I define some small objects as byte arrays, and larger objects are blobs. I saw somewhere a type BYTEA (byte array?), but I can't find it in the documentation to know if this is a real type, one of the user defined types that's a common extension, etc. Anybody handle these issues yet? Thanks, David
Re: [SQL] Joining several tables
As long as the contactid is not null in any of the tables its easy. You didn't give any column name information so I'll wing it. Not knowing the relationships between the tables means this might get you a fair amount of repeat data. If there is only ONE row in each table for a given contactid it'll work ok. SELECT street,phone,contname FROM Address a, PhoneNumber p, Contact c WHERE a.contactid=p.contactid and a.contactid=c.contactid; I have no affiliation with cold remedy manufacturers (heh, Contact c...) "Stephan Richter" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Hello, I have the following situation: > > I have an Address, PhoneNumber and Contact table all having a contactid in > common, but sometimes the phone number does not exist. > > I want to join these three tables. How do I do that? I could not find the > syntax for multiple tables... > > Regards, > Stephan > -- > Stephan Richter > CBU - Physics and Chemistry Student > Web2k - Web Design/Development & Technical Project Management >
[SQL] Re: blobs and small binary objects
I discovered type OID in the JDBC sample database for BLOBs, but I don't see it in the documentation. >From a quick test, it appears that the blob itself is not stored in the row itself, but is instead an object pointer of some kind. Is an OID actually a fixed length field that points to my blob? Or is it more like a VARCHAR, in which case it would be better to store it at the end of the row for storage performance reasons? David
[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] help with query
Hi, I have a table messages which has (for simplicity) two columns - msg_id, last_id, where last_id is sort of symbolical link - it points to the msg_id of the last version of message. There are no limitation to the number of versions. The problem I have is to get history of message with given MSG_ID. simple example - message with MSG_ID=1 has changed by editors (1 - 2 - 3 ... 11) and last version has msg_id=11. Data are shown only for this specific case, there are much more data for different MSG_ID. msg_id, last_id --- 11, ... 3,4 2,3 1,2 I need to get msg_id of all messages which parent is MSG_ID. (hmm, probably I have to get this example as (parent, child) example) Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
[SQL] SQL Query Results
I want to show a certain person the commands I type and the query results, and I want to save them in a file. I tried \e filename and \E filename and its not working. Can someone please help me??
[SQL] select returns no line
Hi, Our database has a SELECT problem using varchar columns in WHERE clause (but not in all rows!!!). We can fix the whole table (or just the row) as shown below but later it seems wrong again (and after the fix the row isn't UPDATEd). Idea? Thanks Attila Environment: [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2] goodwill=>\d users Table= users +--+--+---+ | Field | Type| Length| +--+--+---+ | user_id | int4 not null default nextval ( | 4 | | user_login | varchar() not null |15 | | user_passwd | varchar() not null |15 | | user_exp | timestamp| 4 | +--+--+---+ Indices: users_pkey users_user_login_key The problem: goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd|user_exp ---+--+---+ (0 rows) goodwill=> select * from users where user_id=4; user_id|user_login|user_passwd |user_exp ---+--+-+ 4|test |0PDv7a2EESjZo| (1 row) goodwill=> update users set user_login=user_login where user_id=4; UPDATE 1 goodwill=>select * from users where user_login='test'; user_id|user_login|user_passwd |user_exp ---+--+-+ 4|test |0PDv7a2EESjZo| (1 row) -- x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x
Re: [SQL] i have a big trouble
Sandis Jerics <[EMAIL PROTECTED]> writes: > www_mod_lv=> \copy news from '/home/sites/www.mod.lv/web/news.sql' > \. > ERROR: copy: line 2, pg_atoi: error in " > At the international humanitarian mission of KFOR the Latvian unit participates with >10 soldiers from the National Armed Forces. The KFOR Latvian contingent - Military >Police and medical units - will operate within the contingent of UK. " Notice that the complaint is from pg_atoi, so the problem is that the given text is being taken as an integer field --- that is, as the start of a new row of data. I suspect what is happening is that the embedded newlines in your text column are getting munged up so that they look like ordinary newlines (end of a data row) to COPY. A newline that is part of the data, and not a row separator, has to be represented as a backslash followed by a newline (\n) character. Anything that messes this up will confuse COPY. One theory that comes to mind is that you edited the dump file with an editor that converts Unix-style newlines (\n) to DOS-style (\r\n). Then your embedded newlines would become backslash \r \n ... in other words, a quoted \r followed by a regular end-of-row marker. regards, tom lane
Re: [SQL] How to change the ownership of the table?
Ramesh H R <[EMAIL PROTECTED]> writes: > Please, any one guide me how to change the ownership of the table? There's an ALTER TABLE command for this in current sources, but I think it's new in 7.1. In older releases, you have to reach in by hand and change the 'relowner' field of the table's pg_class row to match the 'usesysid' field of the pg_shadow entry for the desired new owner. Working out the exact UPDATE command to use is left as an exercise for the student ;-) regards, tom lane
[SQL] RE: SQL Query Results
In psql you can try \g or \o. -Original Message- From:[SMTP:[EMAIL PROTECTED]] Sent: Tuesday, January 23, 2001 10:03 PM To: [EMAIL PROTECTED] Subject:SQL Query Results I want to show a certain person the commands I type and the query results, and I want to save them in a file. I tried \e filename and \E filename and its not working. Can someone please help me??
[SQL] Making a foreign key chain - good idea or bad idea?
I just did something which seems to work alright and which makes sense to me now but which I have a funny feeling about. It may be good standard practice (and I just don't know about it) or dangerously foolish or just plain silly: I created a foreign key reference on a column that is also the primary key for this table as in create table institute ( id int4 references index ( id ) PRIMARY KEY, . . . and then used that column as a reference for a foreign key constraint in a couple of other tables: create table boss ( institute_id int4 references institute ( id ), . . . create table staff ( institute_idint4 references institute ( id ), . . . I am not really sure what happens when I delete or modify the id column in the table at the top of this "chain". Except for this uncertainty I don't think this scheme would pose a problem, but I may be wrong. Can anyone enlighten me? Ta, Frank
Re: [SQL] pl/pgsql Limits
Ian, > That works, but when do you delete the records? I delete the records: a) When the user runs the report a second time, with different parameters. b) After the user exits, as part of a DB-wide clean-up procedure (Function) that dumps everything with the user's session key. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] How to change the ownership of the table?
Hi, there, I believe that you can use iat least: pg_dump -t table -f out dbname then vi out change the owner (first line -- connnect ...) then drop the old table and reload new table by psql dbname < out If your table have no index or any constraint, you can use SELECT * into newtable then if you have the previlage. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 24 Jan 2001, Ramesh H R wrote: > Hai > > Please, any one guide me how to change the ownership of the table? > > Regards, > > -- > Ramesh HR > Trainee Engineer > EASi Technologies > 213, 3rd Main, 4th Cross > Chamrajpet, Bangalore - 560 018 > India > Ph.: 660 1086 / 660 2365 / 667 2984 Extn.: 155 > Facsimile: 667 5274 > www.easi.soft.net > > >
Re: [SQL] plpgsql language
On Wed, Jan 24, 2001 at 01:39:48PM +0800, chard wrote: > hello, > i got this error when i tried to create a function > "unrecognized language specified in CREATE FUNCTION: 'plpgsql'" why is > that? man createlang -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
[SQL] Please don't kill me!
I have two statements that accomplish the same task and I'm trying to decide which to use. One uses a sub-select, and the other just does a few more joins. I expect that giving the SELECT statement's themseleves won't get me much help, so here is the output of the EXPLAIN query that I ran on both of them. I read the FAQ on EXPLAIN a bit but I'm still confused. So could somebody help me understand why it appears as though the first query will run much faster (?) than the second? --snip!-- Nested Loop (cost=81.80..114.17 rows=33 width=68) InitPlan -> Seq Scan on l_portal_statuses (cost=0.00..22.50 rows=10 width=4) -> Merge Join (cost=81.80..86.63 rows=3 width=52) -> Merge Join (cost=59.13..63.43 rows=33 width=44) -> Sort (cost=22.67..22.67 rows=10 width=28) -> Seq Scan on contacts m (cost=0.00..22.50 rows=10 width=28) -> Sort (cost=36.47..36.47 rows=333 width=16) -> Seq Scan on buildings b (cost=0.00..22.50 rows=333 width=16) -> Sort (cost=22.67..22.67 rows=10 width=8) -> Seq Scan on contracts c (cost=0.00..22.50 rows=10 width=8) -> Index Scan using executives_pkey on executives e (cost=0.00..8.14 rows=10 width=16) Merge Join (cost=174.38..247.30 rows=333 width=76) -> Index Scan using executives_pkey on executives e (cost=0.00..60.00 rows=1000 width=16) -> Sort (cost=174.38..174.38 rows=33 width=60) -> Merge Join (cost=167.58..173.53 rows=33 width=60) -> Merge Join (cost=59.13..63.43 rows=33 width=44) -> Sort (cost=22.67..22.67 rows=10 width=28) -> Seq Scan on contacts m (cost=0.00..22.50 rows=10 width=28) -> Sort (cost=36.47..36.47 rows=333 width=16) -> Seq Scan on buildings b (cost=0.00..22.50 rows=333 width=16) -> Sort (cost=108.44..108.44 rows=100 width=16) -> Merge Join (cost=92.50..105.12 rows=100 width=16) -> Sort (cost=69.83..69.83 rows=1000 width=12) -> Seq Scan on contracts c (cost=0.00..20.00 rows=1000 width=12) -> Sort (cost=22.67..22.67 rows=10 width=4) -> Seq Scan on l_portal_statuses l (cost=0.00..22.50 rows=10 width=4) --snip!-- Hopefully that's not too ugly. TIA -- Dave
[SQL] Problem with Dates
I am using 7.0.3, I have a column tstamp defined to be 'date'; With a current value of '31-12-2000', if I update tstamp=tstamp+'1 year'::timespan I get '1-1-2002' Is this what is supposed to occur. If this isn't the right way to do this, how should it be done ? Thanks -- Glen and Rosanne Eustace, GodZone Internet Services, a division of AGRE Enterprises Ltd., P.O. Box 8020, Palmerston North, New Zealand Ph: +64 6 357 8168, Mobile: +64 21 424 015
Re: [SQL] Making a foreign key chain - good idea or bad idea?
On Wed, 24 Jan 2001, Frank Joerdens wrote: > I just did something which seems to work alright and which makes sense > to me now but which I have a funny feeling about. It may be good > standard practice (and I just don't know about it) or dangerously > foolish or just plain silly: I created a foreign key reference on a > column that is also the primary key for this table as in > > create table institute ( > idint4 references index ( id ) PRIMARY KEY, > . . . > > and then used that column as a reference for a foreign key constraint in > a couple of other tables: > > create table boss ( > institute_id int4 references institute ( id ), > . . . > > create table staff ( > institute_id int4 references institute ( id ), > . . . > > I am not really sure what happens when I delete or modify the id column > in the table at the top of this "chain". Except for this uncertainty I > don't think this scheme would pose a problem, but I may be wrong. Can > anyone enlighten me? If you mean index(id), as long as there exists an institute(id) that references it, you won't be allowed to delete or update it to a distinct value. And, you won't be able to delete or update institute(id) as long as there exists at least one boss(id) or staff(id) that references it. You know that there shouldn't be orphaned boss(id) or staff(id) rows because those can't exist without a institute(id) row of the correct value and that requires the index(id) value.
Re: [SQL] How to change the ownership of the table?
> > > Working out the exact UPDATE command to use is left as an exercise > for the student ;-) > > regards, tom lane It is funny.. LOL Najm
Re: [SQL] Problem with Dates
On Thu, Jan 25, 2001 at 08:49:27AM +1300, Glen and Rosanne Eustace wrote: > I am using 7.0.3, > I have a column tstamp defined to be 'date'; > > With a current value of '31-12-2000', > if I update tstamp=tstamp+'1 year'::timespan > I get '1-1-2002' This almost sounds like it takes the year 2000, figures out it needs 366 days, and uses that for "1 year". However, I don't see that error myself: template1=> select '2000-12-31'::timestamp+'1 year'; ?column? 2001-12-31 00:00:00-08 (1 row) template1=> select '2000-12-31'::timestamp+'1 year'::timespan; ?column? 2001-12-31 00:00:00-08 (1 row) mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] unreferenced primary keys: garbage collection
On Wed, 24 Jan 2001 00:26:58 -0500 (EST), Michael Fork wrote: >One other method is to setup up the foreign keys as ON DELETE RESTRICT, >then outside of your transaction block issue a DELETE FROM address WHERE >add_id = 1; If there are still records in the other tables referencing >this record, it will error out and nothing will happen, however if no >related records are left, the delete will succeed (you have to do it >outside of transaction, otherwise I belive it will rollback on the >error if other rows are found to be referencing the primary key) Yes, that's the approach I originally posted. The rollback issue is the thing I'm complaining about. The code in question gets called from within a parent function, which uses a single transaction block for all of its operations. This means that executing a query outside a transaction block (or within a separate one) is not an option. I want to be able to tell postgres not to rollback the whole transaction just because my delete attempt fails. I can think of 3 ways to do this: 1. Allow the delete to fail without throwing a fatal error. (Perhaps a warning would suffice.) 2. Allow the client to tell postgres not to roll back if a specified query produces an error. 3. Implement nested transactions. Forest
[SQL] Veering OT opinions please XQL versus XML-QL
just wondering which of these two formats seems best pros and cons of each i know that supposedly xql is simpler in style than XML-QL but XML-QL has some nice sql like syntax the perl modules seem to work nicer too
[SQL] Rule not invoked in 7.1
I have a number of views that seemed to work fine in 7.0.3. When I try to do an update to these views under 7.1, I get the following error: ERROR: Cannot update a view without an appropriate rule. For example, there's a view that looks like this: create view pay_req_v_prl as select empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid as _oid from pay_req create rule pay_req_v_prl_update as on update to pay_req_v_prl where old.status = 'appr' do instead update pay_req set status = new.status, gross = new.gross, cost = new.cost, ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot = new.hot where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq; The sql looks like this: update pay_req_v_prl set gross = 90.09 where empl_id = 1010 and wdate = '2001-01-08' and seq = 1; The record it should update does seem to have status = 'appr' and it updates fine on my 7.0.3 box. Any ideas? begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
Re: [SQL] Rules and transactions
Brett Schwarz wrote: > If I have a rule, is the rule inside a tranaction along with the table that > it references. For example, if I have a rule that deletes an entry from > table B, whenever an entry in table A gets deleted, then is the delete for > table A and table B wrapped inside the same transaction? > > Same question I guess goes for triggers. Yes and yes. Both solutions are strictly covered by transactions. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[SQL] Re: Problem with Dates
pressie# select '31/12/2000'::date + '1 year'::timespan; ?column? - 01/01/2002 00:00:00.00 NZDT (1 row) pressie=# Well I do :-( I vaguely remember someone else having the same problem and it was something to do with daylight saving. I don't recall the solution though, if there was one. Glen.
[SQL] Re: Problem with Dates
On Thu, Jan 25, 2001 at 03:06:38PM +1300, Glen and Rosanne Eustace wrote: > pressie# select '31/12/2000'::date + '1 year'::timespan; > ?column? > - > 01/01/2002 00:00:00.00 NZDT > (1 row) What are the outputs of select '31/12/2000'::date; select '31/12/2000'::date + '365 days'::timespan; and 364, 363, etc. Not sure if gets you anywhere. But data points. There is an email archive on the postgresql.org website you could search if you think it's been answered before. mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [SQL] Re: Problem with Dates
On Thu, 25 Jan 2001, Glen and Rosanne Eustace wrote: > pressie# select '31/12/2000'::date + '1 year'::timespan; > ?column? > - > 01/01/2002 00:00:00.00 NZDT > (1 row) > > pressie=# > > Well I do :-( > > I vaguely remember someone else having the same problem and it was > something to do with daylight saving. I don't recall the solution > though, if there was one. It might also have something to do with your timezone. I did the exact same query on my server and got the correct result: cp=> select '31/12/2000'::date + '1 year'::timespan; ?column? 2001-12-31 00:00:00-05 (1 row) cp=> What happens if instead you add the days? -- Brett http://www.chapelperilous.net/~bmccoy/ --- We are what we are.
[SQL] Insertion and Retrieval of BLOB
Hi Everybody, There are two methods we can insert and retrieve image from db. One is by JDBC way , another is by Postgres extensions. I have problem with both while implementing. If im trying to retrieve the image (which is stored in OID type) by Postgres extensions, Ex: using LargeObjectManager lobj; LargeObject lobj.open(OID); Exception :Fast path protocol error: Z If im trying insert image by JDBC way i.e by getBinaryStream()a and setBinaryStreams() methods of PreparedStatement Im getting Exception saying : Exception : InputStream parameter is not supported. any idea ? -- Som --
Re: [SQL] Rule not invoked in 7.1
Kyle <[EMAIL PROTECTED]> writes: > ERROR: Cannot update a view without an appropriate rule. 7.1 insists that you provide an *unconditional* DO INSTEAD rule for a view. What do you think was happening on your old database when the "where old.status = 'appr'" clause wasn't satisfied? Nothing good I'm afraid. If you really do need conditional rules, you can satisfy the check by writing one unconditional DO INSTEAD NOTHING rule and then one or more conditional non-INSTEAD rules. But you should think carefully about what you expect to happen when you use a conditional rule. regards, tom lane
Re: [SQL] SQL Query Results
On Wed, 24 Jan 2001 18:02, wrote: > I want to show a certain person the commands I type and the query results, > and I want to save them in a file. I tried \e filename and \E filename and > its not working. Can someone please help me?? I don't know if this is available on your particular machine but you could try using the 'script' command just before you start psql. When you have done that which you wish the "certain person" to see, exit psql in the normal way with a '\q', and then exit the script command with an 'exit' man script for the full details. have fun. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
