Re: [SQL] VARCHAR vs TEXT
> > "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: > > BM> CHAR() is best when storing strings that are usually the > BM> same length. VARCHAR() is best when storing variable-length strings, > BM> but you want to limit how long a string can be. TEXT is for strings > BM> of unlimited length, maximum 1 gigabyte. BYTEA is for storing > BM> binary data, particularly values that include NULL bytes. > > Could you add the length limitation for TEXT to the reference manual? > I searched high and low for that limit, but never found it. Also, > what's the max VARCHAR() or CHAR() I can create? Is that also 1Gb? TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth mentioning here? CHAR()/VARCHAR() also 1GB limit. -- 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] VARCHAR vs TEXT
> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: >> Could you add the length limitation for TEXT to the reference manual? >> I searched high and low for that limit, but never found it. Also, >> what's the max VARCHAR() or CHAR() I can create? Is that also 1Gb? BM> TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth BM> mentioning here? CHAR()/VARCHAR() also 1GB limit. My personal belief is that most FAQ entries could go away if the reference documentation had the necessary information... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] VARCHAR vs TEXT
There is a limits FAQ item, not a separate limits FAQ. Sorry for the confusion. > Perhaps 'limits' should be part of FAQ, not separate entity? > > Also a reference (or link) to 'limits' from other sections such as > mentioned below may be more appropriate than duplicating the information. > > Frank > > At 08:56 AM 10/16/01 -0400, you wrote: > >> > "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: > >> > >> BM> CHAR() is best when storing strings that are usually the > >> BM> same length. VARCHAR() is best when storing variable-length > strings, > >> BM> but you want to limit how long a string can be. TEXT is for > strings > >> BM> of unlimited length, maximum 1 gigabyte. BYTEA is for storing > >> BM> binary data, particularly values that include NULL bytes. > >> > >> Could you add the length limitation for TEXT to the reference manual? > >> I searched high and low for that limit, but never found it. Also, > >> what's the max VARCHAR() or CHAR() I can create? Is that also 1Gb? > > > >TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth > >mentioning here? CHAR()/VARCHAR() also 1GB limit. > > > >-- > > 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 > > > >---(end of broadcast)--- > >TIP 4: Don't 'kill -9' the postmaster > > > -- 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Deleting obsolete values
This may look familiar to you - it was on the list last month.
Consider the following table
create table partitur
(userid text, val integer, ts timestamp DEFAULT NOW() );
Do some inserts
insert into partitur values('Bart', 1440);
insert into partitur values('Lisa', 1024);
insert into partitur values('Bart', 7616);
insert into partitur values('Lisa', 3760);
insert into partitur values('Bart', 3760);
insert into partitur values('Lisa', 7616);
To retrieve the latest values (meaning the last ones inserted)
Tom Lane wrote
>This is what SELECT DISTINCT ON was invented for. I don't know any
>comparably easy way to do it in standard SQL, but with DISTINCT ON
>it's not hard:
>SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
>ORDER BY userid, ts DESC;
My question now is
Is there a way to delete all rows the select statement did not
bring up?
After that *unknown* delete statement
select userid, val, ts from partitur ;
should show exactly the same as the SELECT DISTINCT ON (userid) ...
did before.
Regards, Christoph
---(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] VARCHAR vs TEXT
Bruce Momjian writes: > TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth > mentioning here? CHAR()/VARCHAR() also 1GB limit. It is already mentioned there. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Restricting access to Large objects
On Tue, 16 Oct 2001 03:46, Tom Lane wrote: > "Aasmund Midttun Godal" <[EMAIL PROTECTED]> writes: > > How can I restrict access to large objects. > > You can't. This is one of the many deficiencies of large objects. But now that the limit on row length / size has gone away, and that the new BYTEA type has appeared, it would seem that the need for large objects is redundant. Someone with more knowledge than I might like to comment. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] MEDIAN as custom aggregate?
Tom, > Um ... does that work? I thought LIMIT was fairly restrictive about > what it would take as a parameter --- like, constants or $n > parameters > only. > > I do not know of any median-finding algorithm that doesn't require a > depressingly large amount of storage... Me neither. You're right; the query didn't work. Here's a link to the median-finding function I posted to the CookBook: http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=1654 Anyone who can improve it is welcome! -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 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] When will vacuum go away?
"Michael Richards" <[EMAIL PROTECTED]> writes: > I've been watching for this for some time. First it was 7.0, then > 7.1. Does anyone have any idea on when the row re-use code will be > ready? VACUUM isn't disappearing any time soon, but 7.2's version of vacuum runs in parallel with normal transactions, so it's not so painful to run it frequently. See discussion in development docs, http://candle.pha.pa.us/main/writings/pgsql/sgml/maintenance.html > Given trouble with Great Bridge is there any info out there on when > 7.2 might hit the streets? The last several postponements of 7.2 beta have *not* been the fault of the ex-GreatBridge folks around here. You can find a snapshot that should be pretty durn close to 7.2beta1 at ftp://ftp2.us.postgresql.org/pub/dev/postgresql-snapshot.tar.gz (note that at last word, other mirrors were not up to date --- if the doc/TODO file doesn't contain a date in October, it's stale). I think the only thing we're still waiting on is some datetime fixes from Tom Lockhart... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] EXECUTE ... INTO?
"Josh Berkus" wrote: >Folks, > >Can anybody tell me the syntax for sending the result of an EXECUTE to a >variable within a PL/pgSQL function again? Jan Wieck posted it to the >list this summer, but the "searchable list archives" are bogging down. FOR variable IN EXECUTE ''SELECT ...'' LOOP END LOOP; -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "But be ye doers of the word, and not hearers only, deceiving your own selves." James 1:22 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Triggers do not fire
Hallo! I have written a very small test procedure to show a possible error on PostgreSQL V7.1.1. A trigger does not fire the function on update but on inserts. Does someone have made the same experiences? Here is the short example which is doing nothing important, just showing the situation: DROP TABLE test; CREATE TABLE test ( "sensor_id" int4 NOT NULL, "epoch" datetime NOT NULL, "value" float4 NOT NULL, PRIMARY KEY (sensor_id,epoch)); DROP FUNCTION f_test(); CREATE FUNCTION f_test() RETURNS OPAQUE AS ' BEGIN RAISE NOTICE ''Fired %'',TG_OP; RETURN NULL; END; ' LANGUAGE 'plpgsql'; DROP TRIGGER t_test; CREATE TRIGGER t_test BEFORE INSERT OR UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE f_test(); INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND sensor_id = 1; The result is as follows: INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); NOTICE: Fired INSERT INSERT 0 0 UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND sensor_id = 1; UPDATE 0 The insert notice can be shown! The update notice is not there! Why? -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] index problem
Hi, I have that: 1) db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121; NOTICE: QUERY PLAN: Group (cost=0.00..29970.34 rows=921 width=4) -> Index Scan using progdgyxy_idx2 on prog_dgy_xy (cost=0.00..29947.32 rows=9210 width=4) than: 2) db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121; NOTICE: QUERY PLAN: Group (cost=66927.88..67695.39 rows=30700 width=4) -> Sort (cost=66927.88..66927.88 rows=307004 width=4) -> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004 width=4) I making some banchmarks on: oracle vs postgres vs mysql. And this is breaking me now;) Mysql and oracle width same table and index use that index on pxygy_pid; I had vacuum before. Time with mysql: bash-2.04$ time echo " select count(*) from PROG_DGY_XY where pxygy_pid>12121;" | mysql -uuser -ppasswd db count(*) 484984 real0m13.761s user0m0.008s sys 0m0.019s Time with postgres: bash-2.04$ time echo "select count(*) from PROG_DGY_XY where pxygy_pid>12121 " | psql -Uuser db count 484984 (1 row) real0m22.480s user0m0.011s sys 0m0.021s And this is just a little part of another selects joining tables, but because this index is not used, selecting from 2 tables (which has indexes, and keys on joining collumns) takes extrem time for postgres: 2m14.978s while for mysql it takes: 0m0.578s !!! this select is: select distinct PROG_ID,PROG_FTYPE,PROG_FCASTHOUR,PROG_DATE from PROG_DATA, PROG_DGY_XY where prog_id=pxygy_pid order by prog_date,prog_ftype,prog_fcasthour indexes: PROG_DATA: create index prod_data_idx1 on prog_data (prog_date,prog_ftype,prog_fcasthour); prog_id is primary key PROG_DGY_XY: create unique index progdgyxy_idx1 on PROG_DGY_XY (PXYGY_PID,PXYGY_X,PXYGY_Y); create index progdgyxy_idx2 on PROG_DGY_XY (PXYGY_PID); Thx CoL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] to_char()??
On Mon, 15 Oct 2001 09:56:26 +0800, guard <[EMAIL PROTECTED]> wrote:
> thanks
>
> I have run "select substr('hi there', 3, 5)::varchar(5) as xx;"
> but get error message
> Error: ERROR: parser: parse error at or near ":"
>
Works for me on 7.1.2 and pre-7.2
What version are you using?
select version();
That still leaves it unclear in my mind what exactly you want
from your query.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] to_char()??
Try: ::text
CoL
guard wrote:
> thanks
>
> I have run "select substr('hi there', 3, 5)::varchar(5) as xx;"
> but get error message
> Error: ERROR: parser: parse error at or near ":"
>
> --
>
> "Lee Harr" <[EMAIL PROTECTED]> ¼¶¼g©ó¶l¥ó·s»D
> :9qd0j0$1gc3$[EMAIL PROTECTED]
>
>>>how to
>>>select substr('hi there',3,5) as xx -->> xx change char type
>>>
>>>
>>How about:
>>
>>select substr('hi there', 3, 5)::varchar(5) as xx;
>>
>>or is this not what you mean?
>>
>>
>
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Why would this slow the query down so much?
On Monday 15 October 2001 16:12 pm, Tom Lane wrote: > Stuart Grimshaw <[EMAIL PROTECTED]> writes: > > SELECT a.category, b.headline, b.added, c.friendlyname > > FROM caturljoin as a > > INNER JOIN stories as b ON (a.url = b.source) > > INNER JOIN urllist as c ON (a.url = d.urn) > > WHERE a.category = 93 ORDER BY b.added DESC LIMIT 1; > > (I assume "d.urn" is a typo for "c.urn"...) > > The query plan you show looks pretty reasonable if the planner's row > count estimates are in the right ballpark. How many caturljoin rows > have category = 93? How many stories rows will match each caturljoin > row? How many urllist rows ditto? There are 194 rows in caturljoin where url = 93, 29806 rows in stories will match those 194 rows and only 1 row in urllist will match. -- | Stuart Grimshaw <[EMAIL PROTECTED]> | Chief Operations Officer | Football Networks Ltd |- | t:07976 625221 | f:0870 7060260 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] VARCHAR vs TEXT
Perhaps 'limits' should be part of FAQ, not separate entity? Also a reference (or link) to 'limits' from other sections such as mentioned below may be more appropriate than duplicating the information. Frank At 08:56 AM 10/16/01 -0400, you wrote: >> > "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: >> >> BM> CHAR() is best when storing strings that are usually the >> BM> same length. VARCHAR() is best when storing variable-length strings, >> BM> but you want to limit how long a string can be. TEXT is for strings >> BM> of unlimited length, maximum 1 gigabyte. BYTEA is for storing >> BM> binary data, particularly values that include NULL bytes. >> >> Could you add the length limitation for TEXT to the reference manual? >> I searched high and low for that limit, but never found it. Also, >> what's the max VARCHAR() or CHAR() I can create? Is that also 1Gb? > >TEXT limit is 1GB, as shown on the 'limits' FAQ item. Is it worth >mentioning here? CHAR()/VARCHAR() also 1GB limit. > >-- > 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 > >---(end of broadcast)--- >TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Text/Image, JSP tomcat. How can I operate the text and image type field in Postgresql? only in java/jsp
I want to store a long article in the Postgresql in Linux, how can I put the content into it and redraw it back to show? urgent. Thanks. I use JSP. I note that all database systems are very dull in BLOB. Why? Frank Zhu. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] VARCHAR vs TEXT
I want to store a long article in the Postgresql in Linux, how can I put the content into it and withdraw it back to show? urgent. Thanks. I use JSP. I note that all database systems are very dull in BLOB, but we need it to make a good system. At least, the documentation/faq should have much on it. thanks Frank Zhu. "Bruce Momjian" <[EMAIL PROTECTED]> дÈëÏûÏ¢ÐÂÎÅ :[EMAIL PROTECTED] > > On Wed, 10 Oct 2001, Aasmund Midttun Godal wrote: > > > > > I am sure this question has been answered in some form or another > > > before, but I can't really find anything on exactly this issue. > > > > > > Are there any differences between varchar and text other than > > > > > > 1. varchar has limited size > > > 2. varchar is SQL 92 text is not? > > > > > > Especially regarding performance. > > > > > > Or am I correct to assume that if you need a place to store some text, > > > and you are not sure how much (like an email address or a name) you > > > are best off using text? > > > > Pretty much yes. text and varchar are pretty equivalent other than > > the fact that varchar specifies a maximum size. > > I have added the following paragraph to the FAQ: > > CHAR() is best when storing strings that are usually the > same length. VARCHAR() is best when storing variable-length strings, > but you want to limit how long a string can be. TEXT is for strings > of unlimited length, maximum 1 gigabyte. BYTEA is for storing > binary data, particularly values that include NULL bytes. > > -- > 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 > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org ---(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] Triggers do not fire
> The result is as follows: > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); > NOTICE: Fired INSERT > INSERT 0 0 > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND > sensor_id = 1; > UPDATE 0 > > The insert notice can be shown! > The update notice is not there! > > Why? My guess... Because there are no rows the update matches? By returning NULL, you are aborting the insert (see INSERT 0 0) and thus there are no rows for the update to do so no triggers are run. ---(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] index problem
On Mon, 15 Oct 2001, Szabo Zoltan wrote: > Hi, > > I have that: > > 1) > db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid=12121; > NOTICE: QUERY PLAN: > > Group (cost=0.00..29970.34 rows=921 width=4) >-> Index Scan using progdgyxy_idx2 on prog_dgy_xy > (cost=0.00..29947.32 rows=9210 width=4) > > than: > 2) > db=> explain select pxygy_pid from prog_dgy_xy where pxygy_pid>12121; > NOTICE: QUERY PLAN: > > Group (cost=66927.88..67695.39 rows=30700 width=4) >-> Sort (cost=66927.88..66927.88 rows=307004 width=4) > -> Seq Scan on prog_dgy_xy (cost=0.00..32447.66 rows=307004 > width=4) > > I making some banchmarks on: oracle vs postgres vs mysql. And this is > breaking me now;) Mysql and oracle width same table and index use that > index on pxygy_pid; > I had vacuum before. I assume you mean you did a vacuum analyze (a plain vacuum isn't sufficient). If you did just do a regular vacuum, do a vacuum analyze to get the updated statistics. How many rows actually match pxygy_pid>12121? Is 307000 rows a reasonable estimate? How many rows are in the table? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] index problem
On Tue, 16 Oct 2001, CoL wrote: > --- > The 2 table query, where prog_data has ~8800 rowsn and index on prog_id: > bash-2.04$ time echo "explain select distinct > prog_id,prog_ftype,prog_fcasthour,prog_date from prog_dgy_xy,prog_data > where pxygy_pid=prog_id " | psql -Uuser db > NOTICE: QUERY PLAN: > > Unique (cost=7432549.69..7680455.07 rows=2479054 width=32) >-> Sort (cost=7432549.69..7432549.69 rows=24790538 width=32) > -> Merge Join (cost=148864.65..161189.33 rows=24790538 width=32) >-> Index Scan using prog_data_pkey on prog_data > (cost=0.00..701.12 rows=8872 width=28) >-> Sort (cost=148864.65..148864.65 rows=921013 width=4) > -> Seq Scan on prog_dgy_xy (cost=0.00..30145.13 > rows=921013 width=4) I'm guessing that the approximately 25 million row estimate on the join has to be wrong as well given that prog_data.prog_id should be unique. Hmm, does the explain change if you vacuum analyze the other table (prog_data)? If not, what does explain show if you do a set enable_seqscan='off'; before it? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Triggers do not fire
Reiner Dassing <[EMAIL PROTECTED]> writes: > I have written a very small test procedure to show a possible error > on PostgreSQL V7.1.1. The error is yours: you set up the trigger function to return NULL, which means it's telling the system not to allow the INSERT or UPDATE. > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); > NOTICE: Fired INSERT > INSERT 0 0 Note the summary line saying that zero rows were inserted. > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND > sensor_id = 1; > UPDATE 0 Here, zero rows were updated, so of course there was nothing to fire the trigger on. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Triggers do not fire
Your update trigger is fired FOR EACH ROW and no rows are updated i.e. no trigger fired! On Tue, 16 Oct 2001 14:48:59 +0200, Reiner Dassing <[EMAIL PROTECTED]> wrote: > Hallo! > > I have written a very small test procedure to show a possible error > on PostgreSQL V7.1.1. > A trigger does not fire the function on update but on inserts. > Does someone have made the same experiences? > > > Here is the short example which is doing nothing important, just showing > the > situation: > > DROP TABLE test; > CREATE TABLE test ( > "sensor_id" int4 NOT NULL, > "epoch" datetime NOT NULL, > "value" float4 NOT NULL, > PRIMARY KEY (sensor_id,epoch)); > > DROP FUNCTION f_test(); > CREATE FUNCTION f_test() RETURNS OPAQUE AS ' >BEGIN > RAISE NOTICE ''Fired %'',TG_OP; >RETURN NULL; >END; > ' LANGUAGE 'plpgsql'; > > > DROP TRIGGER t_test; > CREATE TRIGGER t_test BEFORE INSERT OR UPDATE ON test FOR EACH ROW > EXECUTE PROCEDURE f_test(); > > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND > sensor_id = 1; > > The result is as follows: > INSERT INTO test VALUES(1,'2000-10-11 12:00:00',-20.2); > NOTICE: Fired INSERT > INSERT 0 0 > UPDATE test SET value = 1000.0 WHERE epoch = '2000-10-11 12:10:00' AND > sensor_id = 1; > UPDATE 0 > > The insert notice can be shown! > The update notice is not there! > > Why? > > > -- > Mit freundlichen Gruessen / With best regards >Reiner Dassing > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Variables.
I would really like a feature :) I do not know whether it is part of the SQL standard. Variables... e.g. CREATE VARIABLE foobar INTEGER DEFAULT 1 NOT NULL; SELECT * FROM thebar WHERE id = foobar; CREATE TEMPORARY VARIABLE... CREATE CONSTANT Basically all the functionality from the table columns (CHECK and REFERENCES...) and it must take any type, so that you can use user-created types as well. SET foobar 3; it could adopt properties from both runtime info and sequences... My abilities in C, as in many other things is severely limited so I am asking someone else to do the dirtywork :). And I do appreciate that this may take some time. It would be nice to have some sort of GRANT revoke SCHEME on it too... I do acknowledge that this can be achieved with temporary and non temporary tables, but it can get ugly with these. While I am at it I would highly suggest that there is some sort of SESSION_ID environment variable. Some of the uses: * Store things like personal preferences * Parameters from a webserver * Client application name * etc. Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Restricting access to Large objects
Christopher Sawtell <[EMAIL PROTECTED]> writes: > On Tue, 16 Oct 2001 03:46, Tom Lane wrote: >> You can't. This is one of the many deficiencies of large objects. > But now that the limit on row length / size has gone away, and that the new > BYTEA type has appeared, it would seem that the need for large objects is > redundant. Someone with more knowledge than I might like to comment. Indeed, large objects are looking rather dinosaurian to me. TOASTed fields seem a far more natural and flexible way of dealing with large values. We still have some work to do on TOASTed fields --- for example, there's no API to read or write segments of a TOASTed field, as there is for large objects. And it'd be nice to be able to store or retrieve BYTEA values without worrying about quoting/escaping problems. But it makes a lot more sense to expend effort on fixing those issues than it does to expend effort on improving support for large objects. IMHO anyway. regards, tom lane ---(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] Performance problems - Indexes and VACUUM
Tom, Folks: I am having a rather interesting time getting performance out of my database. I'd really appreciate some feedback from the list on this. As you may recall, I've gotten around Postgres' lack of rowset-returning stored procedures by constructing "pointer tables" which simply hold lists of primary keys related to the user's current search. This is an excellent approach for a browser-based application, and I have since used this idea on other databases, even one that supports stored procedures. However, this means that I clear all of these pointer tables on a periodic basis (how frequently depends on usage). Just clearing the records didn't work, because of the Postgres "padded index" problem where eventually the indexes on these tables becomes full of deleted rows. Which gives me problem 1: 1. INDEXES: I discovered, the hard way, a peculiar problem. If you drop and re-create a table within the same transaction (in a function, for example) the indexes do not get dropped completely. Doing this to several tables, I had the disturbing experience of seeing incorrect rows in response to some queries. Specifically dropping each of the indexes, dropping the tables, re-creating the tables, and re-creating the indexes seems to work. However, this seems to me to indicate a potential problem with DDL commands within transactions. The second problem is giving me severe grief right now: 2. I have a very complex view designed for browsing client information. This view involves 2 other views, and two custom aggregates which are based on sub-queries (could only do it in Postgres!). The query plan is as long as this e-mail, but thanks to optimization and good indexing it runs in about 2 seconds right after a VACUUM. Unfortunately, 6 hours after a VACUUM, the query bogs down. The query plan does not seem to have changed much, but somehow what took 50% of the processor for 2 seconds at 8:30AM flattens the processor for a full 45 seconds at 3:30 pm. Once VACUUM can be run in the background, I suppose that this can be dealt with, but until then does anyone have any suggestions? -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] nvl() function
Hi Steven, you may use COALESCE. This function should have the same behaviour as Oracle's nvl. For documentation look at http://www.postgresql.org/idocs/index.php?functions-conditional.html Andre "Steven Dahlin" <[EMAIL PROTECTED]> schrieb im Newsbeitrag 9qj13u$2v5l$[EMAIL PROTECTED]">news:9qj13u$2v5l$[EMAIL PROTECTED]... > I am trying to find the equivalent in Postgresql to the Oracle sql function > nvl(). With nvl() you give two parameters. The first may be a field/column > or variable. If the value is not null then it is returned by the function. > For example the with query below if the :ClientParameter is passed then only > those rows which have a clientdesc matching the parameter are returned. If > the :ClientParameter is null then those rows which have clientdesc = > clientdesc are returned (all rows): > > selectclientid, > clientdesc > from clients > where ( clientdesc = nvl( :ClientParameter, clientdesc ) ) > > I have looked thru all the documentation I could find but nowhere were any > built-in SQL functions delineated. Does anyone know where the documentation > can be found? > > Thanks, > Steve > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
