[SQL] what is maximum size of "text" datatype in postgres?
Hi, i wanted to know, what is maximum size of "text" datatype in postgres. It is given as "unlimited characters" in the postgres documentation. But it doesn't store characters more than 5000. The connection get lost if i tried to store more than 5000 characters. I had read somewhere on forum that its max limit is 8 bytes, and we can increase it to 32 bytes. Do any one know how to increse it? or do anyone know any other way to solve this problem. Thanks, Smita ___ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] what is maximum size of "text" datatype in postgres?
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Smit") would write:
> i wanted to know, what is maximum size of "text"
> datatype in postgres.
> It is given as "unlimited characters" in the postgres
> documentation.
> But it doesn't store characters more than 5000. The
> connection get lost if i tried to store more than 5000
> characters.
>
> I had read somewhere on forum that its max limit is 8
> bytes, and we can increase it to 32 bytes.
8 bytes is certainly not the right answer.
Creating a little table, and throwing some wide strings at it...
tutorial=# create table widetext (main text);
CREATE
tutorial=# insert into widetext values ('0123456789... repeated a
bunch of times...');
INSERT 2625519 1
... increasing size each time, for several instances ...
tutorial=# select length(main) from widetext;
length
1100
5500
11000
(3 rows)
That's way more than 8 bytes; that's way more than 5000 bytes.
> Do any one know how to increse it? or do anyone know any other way
> to solve this problem.
What tool are you using to insert the data?
Is it possible that it has some buffer size limitation that is biting
you?
--
"cbbrowne","@","ntlug.org"
http://linuxfinances.info/info/
"My experience as a member of the APB (Accounting Principles Board)
taught me many lessons.A major one was that most of us have a
natural tendency and an incredible talent for processing new facts in
such a way that our prior conclusions remain intact."
-- Charles Horngren
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Interpolation and extrapolation in SQL
On my first try, interpolation and extrapolation turns out to be pretty easy to do. In psql: -- the "lookup" table CREATE TABLE p ( x DOUBLE PRECISION NOT NULL UNIQUE, y DOUBLE PRECISION NOT NULL ); INSERT INTO p VALUES (1,1); INSERT INTO p VALUES (2,5); INSERT INTO p VALUES (5,14); INSERT INTO p VALUES (10,21); -- the table that contains our x values, the y values of which will be -- looked up in the lookup table. CREATE TABLE q ( x DOUBLE PRECISION NOT NULL ); INSERT INTO q VALUES (0); INSERT INTO q VALUES (1); INSERT INTO q VALUES (2); INSERT INTO q VALUES (3.5); INSERT INTO q VALUES (5.5); INSERT INTO q VALUES (10); INSERT INTO q VALUES (11); -- query A. only handles interpolation \set x1 '(SELECT p.x FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1)' \set x2 '(SELECT p.x FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1)' \set y1 '(SELECT p.y FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1)' \set y2 '(SELECT p.y FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1)' SELECT q.x, CASE WHEN :x1 = :x2 THEN :y1 ELSE(:y1 + (q.x-:x1)/(:x2-:x1)*(:y2-:y1)) END AS y FROM q; -- query B. also handles extrapolation, and the note column tells us -- whether a certain y value is directly taken from p, or interpolated, -- or extrapolated. \set x0 '(SELECT p.x FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1 OFFSET 1)' \set x3 '(SELECT p.x FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1 OFFSET 1)' \set y0 '(SELECT p.y FROM p WHERE p.x <= q.x ORDER BY p.x DESC LIMIT 1 OFFSET 1)' \set y3 '(SELECT p.y FROM p WHERE p.x >= q.x ORDER BY p.x ASC LIMIT 1 OFFSET 1)' SELECT q.x, CASE WHEN :x1 = :x2 THEN :y1 WHEN :x1 IS NULL THEN (:y2 + (q.x-:x2)/(:x3-:x2)*(:y3-:y2)) WHEN :x2 IS NULL THEN (:y0 + (q.x-:x0)/(:x1-:x0)*(:y1-:y0)) ELSE (:y1 + (q.x-:x1)/(:x2-:x1)*(:y2-:y1)) END AS y, CASE WHEN :x1 = :x2 THEN 'direct' WHEN :x1 IS NULL THEN 'extrapolated to the left' WHEN :x2 IS NULL THEN 'extrapolated to the right' ELSE 'interpolated' END AS note FROM q; -- C. to handle the case where x in p is not unique, replace the x0..x3 and y0..y3 template into: \set x1 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1)' \set x2 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x >= q.x ORDER BY p2.x ASC LIMIT 1)' \set y1 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON p.x=p2.x WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1)' \set y2 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON p.x=p2.x WHERE p2.x >= q.x ORDER BY p2.x ASC LIMIT 1)' \set x0 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1 OFFSET 1)' \set x3 '(SELECT p2.x FROM (SELECT DISTINCT x FROM p) p2 WHERE p2.x >= q.x ORDER BY p2.x ASC LIMIT 1 OFFSET 1)' \set y0 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON p.x=p2.x WHERE p2.x <= q.x ORDER BY p2.x DESC LIMIT 1 OFFSET 1)' \set y3 '(SELECT p.y FROM (SELECT DISTINCT x FROM p) p2 LEFT JOIN p ON p.x=p2.x WHERE p2.x >= q.x ORDER BY p2.x ASC LIMIT 1 OFFSET 1)' Questions: 1) Is the above correct? (It gives me correct result, but I'm not 100% sure) 2) Does the optimizer cache the result of identical subqueries (e.g. :x1 or :x2, which is mentioned several times in the query)? If yes, how do I know this? 3) Is there another (simpler, more elegant, more efficient) way to do interpolation/extrapolation in SQL? -- dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Interpolation and extrapolation in SQL
David Garamond <[EMAIL PROTECTED]> writes: > 2) Does the optimizer cache the result of identical subqueries (e.g. :x1 > or :x2, which is mentioned several times in the query)? No. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Difficulties to use a Set Returning Function on a join
Hi, I have a Set Returning Function that works great when called alone, but I'm having problems to join it with another table and use this table's primary key as the parameter for the SRF. I'm sending it with the names in Brazilian Portuguese, but I think it might be compreensible english speakers. This works fine: SELECT * FROM obtem_situacao_pedido(1); cd_pedido | cd_pedido_situacao | id_tipo ---++- 1 |106 | 1 (1 row) But these doesn't: SELECT p.*, hsp.* FROM pedido p LEFT OUTER JOIN obtem_situacao_pedido(p.cd_pedido) hsp ON hsp.cd_pedido = p.cd_pedido; ERROR: Relation "p" does not exist SELECT p.*, hsp.* FROM obtem_situacao_pedido(p.cd_pedido) hsp RIGHT OUTER JOIN pedido p ON hsp.cd_pedido = p.cd_pedido; ERROR: Relation "p" does not exist SELECT osp.* FROM pedido p JOIN obtem_situacao_pedido(pedido.cd_pedido) osp USING(cd_pedido); NOTICE: Adding missing FROM-clause entry for table "pedido" ERROR: FROM function expression may not refer to other relations of same query level Thanks in advance, -- +---+ | Alvaro Nunes MeloAtua Sistemas de Informacao | | [EMAIL PROTECTED]www.atua.com.br | |UIN - 42722678(54) 327-1044| +---+ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] create unique index schema.index_name on table (column)?
I'm working with a closed source application that reads an ASCII file and as it does it (via ODBC) creates tables and indexes, inserting as it goes. Like so: CREATE TABLE bgsuser.CAXCTRLD ( CTRLS INTEGER, INTVL INTEGER); CREATE UNIQUE INDEX bgsuser.PRIMARY_CAXCTRLD ON bgsuser.CAXCTRLD (CTRLS,INTVL); INSERT ... INSERT ... The application is designed to work through ODBC and psqlODBC does a great job. My problem is the username (schema qualifier) on the create index statement. I've decided it's easiest to copy/paste/modify the create statements from the ODBC trace logs.. oh, well. Michael Glaesemann wrote: On Sep 12, 2004, at 5:42 AM, [EMAIL PROTECTED] wrote: Is the syntax "schema_name.index_name" for create unique index wrong, unsupported or what? I know is doesn't work as postgres kicks me back a syntax error each time (version 7.3.2 & 7.4.5). -sigh- Could you give a full example (including the error) of what you're doing? It's kind of hard to give a suggestion based on your brief description. Michael ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CREATE RULE ignored, what did I do wrong
I was trying to implement a pseudo-delete, where the (millions of) records in several child tables were actually deleted, but a flag was set in the summary table instead of deleting it, as an archiving mechanism. (If the flag was already set, the WHERE clause in the RULE should be false, and the delete happen as usual?!) The FK relation below has the summary table as the child, and isn't critical. It's the tables for which this is the parent that are the issue. Do you have an idea how to implement this best? Tom Lane <[EMAIL PROTECTED]> wrote .. > [EMAIL PROTECTED] writes: > > Foreign-key constraints: > > "$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) > ON UPDATE CASCADE ON DELETE CASCADE > > Rules: > > del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode > > WHERE (NOT old.is_deleted) DO INSTEAD ... > > The DELETE commands generated by the foreign key ON DELETE CASCADE will > get rewritten by your ON DELETE rule. You probably do not want to do > this; or at least not make it an INSTEAD rule. > > There has been some debate in the past about whether rules should be > able to break foreign-key constraints, but I tend to class it as a > "you should know what you're doing" feature. Preventing this kind > of error would inevitably result in a serious reduction of the power > of the rule feature. > > regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
