Re: [SQL] Column limits in table/ views
On Fri, 2003-06-06 at 18:02, A.M. wrote: > I have 560 columns of NUMERIC(10,14). To not run up against max column > restraints, I split the information into two tables. Does the column > limit on tables imply the same limit for views or selects or could I > potentially select a row across both tables and make a view that hides > the split? The limit is documented as having to do with the size of a block. The largest a single row can become is 8k with primitive types (text, and other variable length types will 'overflow' into another area -- see TOAST). Selects should be able to return more columns than that. Both views and tables create a ROW TYPE to represent it, which is where the limitation is. Thus, I would not expect you to be able to create a view. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] "Join" on delimeter aggregate query
Eivind Kvedalen <[EMAIL PROTECTED]> writes: > SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a; > The ORDER BY is included to sort the rows before they are aggregated. I'm > not sure that this guarantees that they actually will be sorted, but maybe > some of the postgresql hackers can confirm/deny this? This technique will work reliably as of 7.4, but it's not reliable in existing releases. The GROUP BY will do its own sort on A, and unless qsort() is stable on your machine (which it's not, in most implementations) the secondary ordering by B will be destroyed. The fix in 7.4 simply makes the planner smart enough to notice that the sub-select's output is already adequately sorted for grouping by A. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Column limits in table/ views
"A.M." <[EMAIL PROTECTED]> wrote: > I have 560 columns of NUMERIC(10,14). To not run up against max column > restraints, I split the information into two tables. Does the column > limit on tables imply the same limit for views or selects or could I > potentially select a row across both tables and make a view that hides > the split? Hi, just for curiosity, can I known why do you need a table or a view with more then 560 Columns ? Usually have a big table like yours is sign of a not good design. Regards Gaetano Mendola ---(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] "Join" on delimeter aggregate query
Eivind Kvedalen <[EMAIL PROTECTED]> writes: > Ok. What I actually had in mind was whether the optimizer would remove the > ORDER BY clause completely or not, No. If you put an ORDER BY in a subselect, I think the system should honor it. 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])
Re: [SQL] Using a RETURN NEXT
From: "Mr Weinbach, Larry" <[EMAIL PROTECTED]> > But at execution time I am getting thi error : > > WARNING: Error occurred while executing PL/pgSQL > function word_case > WARNING: line 5 at return next > ERROR: Set-valued function called in context that > cannot accept a set > > I also tried using my own type defined but I got the > same error. > > Any hint or idea will be appreciated ... I guess that you are calling that function in this way: #select word_case(); that function is a "table function" so you should use it like a table: #select * from word_case(); Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "Join" on delimeter aggregate query
On Sun, 8 Jun 2003, Tom Lane wrote: > Eivind Kvedalen <[EMAIL PROTECTED]> writes: > > SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a; > > > The ORDER BY is included to sort the rows before they are aggregated. I'm > > not sure that this guarantees that they actually will be sorted, but maybe > > some of the postgresql hackers can confirm/deny this? > > This technique will work reliably as of 7.4, but it's not reliable > in existing releases. The GROUP BY will do its own sort on A, and > unless qsort() is stable on your machine (which it's not, in most > implementations) the secondary ordering by B will be destroyed. > > The fix in 7.4 simply makes the planner smart enough to notice that > the sub-select's output is already adequately sorted for grouping > by A. Ok. What I actually had in mind was whether the optimizer would remove the ORDER BY clause completely or not, as it isn't used in the top-level SELECT query, and SQL doesn't in general guarantee ordered rows back unless there's an ORDER BY in the top-level SELECT (I haven't read the SQL standard, so I might very well be wrong here). The GROUP BY sorts on A to do the grouping correctly, right? (That is, removing the duplicates from A) (Now, thinking more about this, removing the ORDER BY in the optimizer as I suggested above would effectively remove the ORDER BY in created views, right?) Eivind -- | Mail: [EMAIL PROTECTED] | Lazy on IRC | HP: www.stud.ifi.uio.no/~eivindkv | "Jeg skal vrenge deg med håret | Tlf: 22187123/93249534 | inn." | | -- Yang Tse Lyse ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Coalesce/Join/Entries may not exist.
I've got three tables, I'll shorten the columns down just so you get the idea: lists --- id|order_id list_results id|lid|total orders id|max All of the columns are int's. What I'm trying to do is something like: select (o.max-coalesce(sum(lr.total),0)) from orders o,list_results lr where lr.l_id in (select l.id from lists l, orders o where l.order_id=X and o.id=l.order_id) group by o.max This would, in theory, return a number which should be o.total-sum(lr.total) The problem is, there may not be any data in list_results OR lists regarding the order ID. If data from list_results.total exists, and is referencing lists.id, which in turn is referencing orders.id through lists.order_id, return o.max-lr.total. If data from list_results or lists DOESN'T exist, I would just want to go ahead and return orders.max. I was hoping the coalesce would be able to do this, but it doesn't. The subquery is in there because frankly I'm not sure how to do multiple left joins, which I think would have to exist. The easy way out for me here I think would be to make list_results.order_id and leave lists out of it, but then I'd have redundant data in two tables. Any suggestions on this one? ---(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] Column limits in table/ views
Rod Taylor <[EMAIL PROTECTED]> writes: > On Fri, 2003-06-06 at 18:02, A.M. wrote: >> I have 560 columns of NUMERIC(10,14). To not run up against max column=20 >> restraints, I split the information into two tables. Does the column=20 >> limit on tables imply the same limit for views or selects or could I=20 >> potentially select a row across both tables and make a view that hides=20 >> the split? > The limit is documented as having to do with the size of a block. The > largest a single row can become is 8k with primitive types (text, and > other variable length types will 'overflow' into another area -- see > TOAST). > Selects should be able to return more columns than that. Both views and > tables create a ROW TYPE to represent it, which is where the limitation > is. Thus, I would not expect you to be able to create a view. But a view row is never stored on disk, so the block-size limit doesn't come into play. You will still be constrained by the max column count (1600), because that comes from the tuple header layout --- the size of the header plus null bitmap has to fit into a uint8 field. But a view with 560 columns would work. I think some performance issues might come up if you actually try to select all the columns at once, because there are places whose behavior is O(N^2) in the number of columns. Dunno whether this effect will be noticeable with ~560 columns though. 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])
Re: [SQL] Creating Views with Column Names based on Distinct
At 10:59 AM 6/6/03, Damien Dougan wrote: I was wondering if it is possible to create a table view based on a table which is effectively an "attribute list". For example, suppose I have two tables: CREATE TABLE user ( userid integer, username character varying, userpassword character varying, startdate date ); CREATE TABLE userdetail ( userid integer, attributename character varying, attributevalue character varying ); Now I want to make a public view of the user, which would have all of the defined fields in user, and all of the defined attributes across userdetail. I'll think you'll find what you're looking for if you search the archives of this mailing list for 'crosstab'. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] how to determine array size
I need to enumerate the constraints on any given column in a table, so I'm examining pg_constraint to get the relevant information. The conkey array contains a list of constrained columns, and although I am able to check conkey[1] for constraints on a single column, I would like to properly handle multi-column constraints. How do I determine the size of the conkey array? I haven't found any field that looks like it contains the number of values in conkey. Do I have to check each element of the array sequentially, until I get a NULL value from one of them? (Section 5.12 of the User's Guide seems to forbid this: "A limitation of the present array implementation is that individual elements of an array cannot be SQL null values.") Moreover, that method doesn't give me a nice way of selecting all constraints on a specific column, as I would have to write clauses like this: ... WHERE conkey[1] = blah OR conkey[2] = blah OR conkey[3] = blah OR conkey[4] = blah ... Can somone offer a better way? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "Join" on delimeter aggregate query
Thanks very much, this helps immensely. I've worked with functions before,
but never aggregates. I guess there's some more bedtime reading for me to
look into now.
Re: sorting, this is not important to me, but I will keep the issues brought
up by Tom Lane in mind when I use this.
On Saturday 07 June 2003 02:06 pm, Eivind Kvedalen wrote:
> Hi
>
> You can create an aggregate function to solve this. A friend of mine asked
> the same question a while ago, and I created a possible example solution
> for him, which I paste here:
>
> CREATE FUNCTION concat(varchar,varchar) RETURNS varchar
> AS 'SELECT CASE
>$1 WHEN \'\' THEN $2
>ELSE $1 || \',\'|| $2
> END AS RESULT;'
> LANGUAGE SQL;
>
> /* DROP AGGREGATE concat(varchar); */
>
> CREATE AGGREGATE concat (
> BASETYPE = varchar,
> SFUNC = concat,
> STYPE = varchar,
> INITCOND = ''
> );
>
> /* Example code */
>
> DROP TABLE test;
> CREATE TABLE test (
> a varchar,
> b varchar
> );
>
> INSERT INTO test VALUES ('A', '1');
> INSERT INTO test VALUES ('A', '3');
> INSERT INTO test VALUES ('A', '2');
> INSERT INTO test VALUES ('B', 'a');
> INSERT INTO test VALUES ('C', 'b');
> INSERT INTO test VALUES ('C', 'c');
>
> SELECT a, concat(b) FROM (SELECT a,b FROM test ORDER BY a,b) T GROUP BY a;
>
> /*
>
> a | concat
> ---+-
> A | 1,2,3
> B | a
> C | b,c
>
> */
>
> The ORDER BY is included to sort the rows before they are aggregated. I'm
> not sure that this guarantees that they actually will be sorted, but maybe
> some of the postgresql hackers can confirm/deny this? I guess this isn't
> important to you, though.
>
> On Fri, 6 Jun 2003, Michael A Nachbaur wrote:
> > Hello everyone,
> >
> > I've set up PostgreSQL as the authentication / configuration database for
> > my mail server (Postfix + Courier-IMAP), and though it works beautifully,
> > I need some help on my aliases query.
> >
> > You see, define aliases in a database table as rows in a column in the
> > form of "Source" and "Target". The problem is that one source address
> > can be delivered to multiple targets (e.g. internal mailing list, or a
> > temporary forward to another address), but postfix only processes the
> > first record returned from an SQL query.
> >
> > Postfix can deliver to multiple targets, if you separate the targets with
> > comas, like so:
> >
> > Source Target
> > [EMAIL PROTECTED] [EMAIL PROTECTED],[EMAIL PROTECTED],
> >
> > What I would like to do, is something like the following (I know I'd need
> > to group the query, but you get the idea):
> >
> > Select DISTINCT(Source), JOIN(Target, ',') FROM Aliases
> >
> > Is there any way this can be done with Postfix?
>
> Eivind
--
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] Retype
Hi, I have query " SELECT id_user FROM user WHERE (freg_u & 2 ) > 0 ". freg_u is type smallint. Query return this error. PostgreSQL query failed: ERROR: Unable to identify an operator '&' for types 'smallint' and 'integer' You will have to retype this query using an explicit cast in How I retype freg_u to interger ? -- Rado Petrik <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] find open transactions/locks in 7.2?
[select version() --> PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 3.0.4] I'm getting hangups every day or so, I presume due to some open transaction that insert/update/delete'ed on a table that is used by my main app without a commit. Is there some way (in 7.2!) to find who's locking what or who has a transaction open? -- George -- I cannot think why the whole bed of the ocean is not one solid mass of oysters, so prolific they seem. Ah, I am wandering! Strange how the brain controls the brain! -- Sherlock Holmes in "The Dying Detective" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
