[SQL] Text insert max. size.
Hi, I'm doing some planning for storing some xml data and am wondering what is the maximum amount I could insert with one SQL statement. For example if I read an xml file off the hard drive and wanted to store that in a text column what would be the largest file I could insert. How about a 10 KB file ? I guess also each programing language may have limitations too. I this case I'm interested in using PHP. Thank you kindly. Regards Rudi. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] yet pg_toast reindex
which index? could you help me again? Eric Anderson Martins Miranda Net Admin @ Via Net SAO - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, June 17, 2003 8:50 PM Subject: Re: [SQL] yet pg_toast reindex > "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > > Sorry about this sucks thread but now i´v got following error > > ERROR: relation "pg_toast_26474986" is of type "t" > > You may have to use REINDEX INDEX (on the index not the toast table of > course) to get it to work in older versions. REINDEX didn't use to > think it could work on toast tables. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(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] Object-Relational table design question
On June 17, 2003 12:23 pm, Josh Berkus wrote: > Personally, I'm not a fan of inherited tables; I think they muddy up > the relationality of SQL without providing any additional We actually are doing what the original poster is in the process of doing; we have an ISP billing system based on postgresql. I have to agree with the above. We actually did use inheritence for a few things (though not for account definitions), and I've always found it somewhat of a pain. Especially when pg_dump was broken and corrupted the database on restore... I believe this is fixed now, but I can't see the benefit of the complication, whereas it does make the relationships more murky as described above. > JOIN webhosting ON service.id = webhosting.service This would work though it's not very scaleable. Our current system makes all elements of a service into what we call an 'attribute'. The attributes are defined in a table, and attached to each account type, and turned on or off, and twiddled with various definitions such as term/period billing, etc. This makes it relatively easy to add new services... just add another entry in the account attributes table, whereas with hard coded joins above, if you add more services you're going to have to edit all of your code where joins take place. So the billing job, for example, if you want a list of services that a customer's account has: SELECT * FROM account_attribute WHERE account_type=customer.account_type AND bill_mode>0; (We go even further and do resource based accounting in yet another relation which references the attributes... it's a bit complicated, but I think its proving quite flexible so far, and cleaner than using inheritance). -- Tim Middleton | Cain Gang Ltd | But the trouble was that my hysterical fit [EMAIL PROTECTED] | www.Vex.Net | could not go on for ever. --Dost (NFTU) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Text insert max. size.
On Wed, 18 Jun 2003, Rudi Starcevic wrote: > Hi, > > I'm doing some planning for storing some xml data and am wondering > what is the maximum amount I could insert with one SQL statement. There's no limit to max SQL statement size in postgresql, but practically, you might want a REALLY big machine if you're gonna toss around 100 megs in a single query. > For example if I read an xml file off the hard drive and wanted to store > that in a text column what would be the largest file I could insert. > How about a 10 KB file ? How about 1 gig. Nice limit, eh? :-) > I guess also each programing language may have limitations too. > I this case I'm interested in using PHP. Generally you're gonna hit a limit with the max memory size set in the php.ini file, which defaults to 8 meg max process size before abort. You can crank this up so high your machine starts to swap out. I'd guess PHP is likely limited to some number internally, but I've messed around with a hundred or so megs before. If you wanna build a HUGE SQL query, you're likely better off to use fread and build the query in a file then run it with `psql dbname
Re: [SQL] Object-Relational table design question
Tm, > This would work though it's not very scaleable. Our current system makes > all elements of a service into what we call an 'attribute'. The > attributes are defined in a table, and attached to each account type, > and turned on or off, and twiddled with various definitions such as > term/period billing, etc. This makes it relatively easy to add new > services... just add another entry in the account attributes table, > whereas with hard coded joins above, if you add more services you're > going to have to edit all of your code where joins take place. Yeah, that's a very good approach. I use it for any client where they need to be able to add new "attributes" and services after the system is built. It also works for other things ... for example, a "skills" list for an HR database. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] Object-Relational table design question
On Wednesday 18 June 2003 06:20 am, Tm wrote: > On June 17, 2003 12:23 pm, Josh Berkus wrote: > > Personally, I'm not a fan of inherited tables; I think they muddy up > > the relationality of SQL without providing any additional > > We actually are doing what the original poster is in the process of > doing; we have an ISP billing system based on postgresql. I have to > agree with the above. We actually did use inheritence for a few things > (though not for account definitions), and I've always found it somewhat > of a pain. Especially when pg_dump was broken and corrupted the > database on restore... I believe this is fixed now, but I can't see the > benefit of the complication, whereas it does make the relationships > more murky as described above. Yeah, the inheritance support did seem to be a little funky, especially with the indexing problems mentioned in the documentation. > > JOIN webhosting ON service.id = webhosting.service > > This would work though it's not very scaleable. Our current system makes > all elements of a service into what we call an 'attribute'. The > attributes are defined in a table, and attached to each account type, > and turned on or off, and twiddled with various definitions such as > term/period billing, etc. This makes it relatively easy to add new > services... just add another entry in the account attributes table, > whereas with hard coded joins above, if you add more services you're > going to have to edit all of your code where joins take place. How scalable would this be? If every time you want to get details on a customer you had to do a join across several tables, multiple records of which would be combined into the same result, what kind of hit would you sustain if you had a large number of customers, attributes and/or users accessing the system? > So the billing job, for example, if you want a list of services that a > customer's account has: > > SELECT * FROM account_attribute > WHERE account_type=customer.account_type > AND bill_mode>0; > > (We go even further and do resource based accounting in yet another > relation which references the attributes... it's a bit complicated, but > I think its proving quite flexible so far, and cleaner than using > inheritance). I did something to this effect years ago on an Oracle database, but since I was just a newbie to SQL, I assumed there had to be a better way of doing this. :-) Beginners luck? -- Michael A Nachbaur <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] comparing querys
how can i compare two querys' eficiency??? TIA, -- Lucas Lain [EMAIL PROTECTED] #! /Scripting/Manager (??) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] comparing querys
Take a look at EXPLAIN and EXPLAIN ANALYSE: http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-explain.html On Wed, 2003-06-18 at 16:23, Lucas Lain wrote: > how can i compare two querys' eficiency??? > > TIA, -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] Trouble porting a Oracle PL/SQL procedure to PL/pgSQL--THANKS!
On Tue, 17 Jun 2003 00:42:54 -0400 Tom Lane <[EMAIL PROTECTED]> writes: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Mon, 16 Jun 2003, Tony G. Harris wrote: > >> The problem is, when I call the function, I get: > >> sql-practice=# select update_pay(); > >> ERROR: pay_cursor: no such class > >> WARNING: plpgsql: ERROR during compile of update_pay near line > 2 > >> ERROR: pay_cursor: no such class > > > I think it's complaining because you can't use > pay_cursor%ROWTYPE. > > Yeah, you're right. I didn't believe that theory at first because > the line number reference didn't point at the line with %ROWTYPE > ... > but upon digging into it I find that plpgsql's code for determining > the line number to report is flat wrong for this case. (I've > committed > a quick fix into CVS tip, but I wonder whether the whole mechanism > shouldn't be rethought. Calling plpgsql_scanner_lineno() all over > the > place doesn't seem real clean.) > > > Maybe declaring IndRec as being of type record may work (don't > know > > for certain). > > That's what I'd try. We don't consider that declaring a cursor > creates > a named rowtype ... I'm surprised that Oracle seems to think it > does. > > regards, tom lane > > Stephan, Tom, Thank you both for your insights. You were right, Stephan, declaring IndRec as a RECORD type worked ! That and a couple other no doubt "quick and dirty" fixes and the function ran exactly how it was supposed to. Again, thank you both, and everyone on the pgsql-SQL list for all the help. Tony Tony G. Harris 928 Sibley Street Hammond, IN 46320-1649 Phone: (219) 931-3546 FAX: (646) 405-8569 email: [EMAIL PROTECTED] I returned, and saw under the sun, that the race is not to the swift, nor the battle to the strong, neither yet bread to the wise, nor yet riches to men of understanding, nor yet favour to men of skill; but time and chance happeneth to them all. -- Ecclesiastes 9:11 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Create rule/trigger
Title: Create rule/trigger Hi All, Can we create trigger/rule that specifies : Do action ‘Y’ When inserts/updates to any table in the database ‘X’ occurs? If not, what is the alternate way to do this? Thx. In advance. Anagha
[SQL] set decimal point in SQL
I have a field set up as money and I try to calculate some number and put in this field, but the number will be 6 demical point and I want to cut it to only 2-4 decimal point, how can I do it? Thanks <>
Re: [SQL] [HACKERS] Our FLOAT(p) precision does not conform to spec
On 16 Jun 2003 at 18:15, Tom Lane wrote: > This is a straightforward change and would not break pg_dump files, > since fortunately pg_dump always references the underlying types and > never refers to anything as FLOAT(p). But I wonder whether it is > likely to break many existing applications. There is a hazard of some > existing app asking for (what it thinks is) float8 and getting float4 > instead. Especially apps. which rely on number(3) not to accept anything greate than +/- 999. I hate the syntax of putting decimal digits as range checkers in SQL field. But oracle does that and consequently lot of oracle apps rely on it. I won't be surprised if float(p) notion brings same assurance to such app developers. I think this would become and FAQ after changes are implemented. Who reads release notes anyway?..:-) > Is it worth trying to provide some sort of backwards-compatibility mode? > We could imagine adding a GUC variable to select binary or decimal > precision, but I really don't want to. It would increase the amount of > work needed by more than an order of magnitude, and this problem doesn't > seem worth it. I'd rather just list this under Incompatibilities in the > 7.4 release notes. Is it possible to have float-2(p) and float-10(p)? Would it be adding code sections instead of replacing it? That would be cleaner than GUC variable. Everything defaults to float-10(p) for backward compatibility for a period of a release and in 7.5, it gets switched to float-2(p). In the meantime, the behaviour remains available who cares to read the docs and notes. Just a thought.. Bye Shridhar -- Genderplex, n.: The predicament of a person in a restaurant who is unable to determine his or her designated restroom (e.g., turtles and tortoises). -- Rich Hall, "Sniglets" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] set decimal point in SQL
On Mon, Jun 16, 2003 at 05:02:34PM -0500, Bu, Ning wrote: > I have a field set up as money and I try to calculate some number > and put in this field, but the number will be 6 demical point and I > want to cut it to only 2-4 decimal point, how can I do it? Thanks round(); eg select round(foo, 2) frombar ...will give you values in column foo rounded to 2 decimal places. -- Rodger Donaldson[EMAIL PROTECTED] I don't mind straight people, as long as they act gay in public ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] set decimal point in SQL
--- Rodger Donaldson <[EMAIL PROTECTED]> wrote: > On Mon, Jun 16, 2003 at 05:02:34PM -0500, Bu, Ning > wrote: > > > I have a field set up as money and I try to > calculate some number > > and put in this field, but the number will be 6 > demical point and I > > want to cut it to only 2-4 decimal point, how can > I do it? Thanks > > round(); eg > > selectround(foo, 2) > from bar > > ...will give you values in column foo rounded to 2 > decimal places. > If you use "numeric(x,2)", where x is the largest number of digits you are likely to need, the rounding will be done for you. Type "money" is deprecated anyway, and is likely to be dropped in a future release. You can format the numeric value on output if you want to display currency symbols or whatever. __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
