Re: [SQL] Create Assertion -- Question from a newbie

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Well, it's on the SQL standard, so we should look forward to > implementing (some form of) it some day. Do *any* databases implement SQL standard Assertions? -- greg ---(end of broadcast)--- TIP 5: do

Re: [SQL] query to select a linked list

well with the gist indexes. It does require changing your data model denormalizing it slightly which makes it hard to "reparent" children, but if that isn't an operation you have to support I think it makes most other operations you might want to do much easier to support.

Re: [SQL] query to select a linked list

uilt > for 8.2 and installed, but I haven't tried using them. > > I would think that connectby is at least worth looking into. Uhm, no, I guess I'm not sure. I didn't realize it was in the tablefunc module either. -- Gregory Stark EnterpriseDB http://www.ente

Re: [SQL] aggregate query

rent definition. Something like this for example: SELECT inspection_id, meter_id, case when bay=1 then bay_1_use when bay=2 then bay_2_use when bay=3 then bay_3_use when bay=4 then bay_4_use when bay=5 then bay_5_use else null

Re: [SQL] Recursively traversing a partially ordered set

ocation support isn't exactly a strong point in Postgres either. Plenty of people get bitten by their database being initdb'd in a locale they didn't expect. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [SQL] JOIN

iple times because there are multiple join matches. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] trigger firing order

wait until the trigger on B is executed if the trigger on B doesn't actually get triggered until someone updates B and it's the trigger on A doing the update? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--

Re: [SQL] percentages of a column

umeric / (select count(*) from basket) * 100, 0)::text||'%' as "%" from basket group by fruit_name order by "%"; fruit_name | count | % +---+- Bananas| 5 | 10% Apples | 15 | 30% Oranges|30 | 60% (3 rows) --

Re: [SQL] Converting from MS Access field aliases

in Postgres too. But the defined aliases are only in the resulting output columns, not in scope for the where clause. In fact the expressions used aren't even evaluated for rows which don't match the where clause which is pretty important if those expressions are subqueries or volatile funct

Re: [SQL] Query Plan

even in the one which you label "without date_trunc". Instead try to write a where clause that the database can understand the end points of. Something like (col >= '20070703' and col < '20070704'). That's something the database can apply a normal in

Re: [SQL] The nested view from hell - Restricting a subquerry

happen because it simply does not have that information. The more I look at this view the more I think it's just seriously broken. Why is it grouping by order_id at all if, I suspect, there will only be one record per order_id in eg_orders?? -- Gregory Stark Enter

Re: [SQL] The nested view from hell - Restricting a subquerry

;s a hypothetical data set for which this algorithm fails: order_idinvoice_id mileage 1 1 100 1 2 100 Your algorithm would produce order_id max(invoice_id) sum(mileage

Re: [SQL] The nested view from hell - Restricting a subquerry

"Gregory Stark" <[EMAIL PROTECTED]> writes: > Nis Jørgensen <[EMAIL PROTECTED]> writes: > >> 1. Look up all order_ids for which (order_id,my_invoice_id) appear in >> eg_orders >> >> 2. Find all rows (in both branches of the UNION) with these id

Re: [SQL] is there a 'table' data type in pg?

ent kinds of records depending on the type of object. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] PG won't use index on ORDER BY

g the varchar_pattern_ops? The default operator set is the one you want for handling ordering. The pattern_ops operator set is for handling things like x LIKE 'foo%' -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadca

Re: [SQL] PG won't use index on ORDER BY

indexes... If you initd in the C locale you only need one index. In other locales the collation order and the pattern order are different. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] SELECT syntax synopsis: column_definition?

s record as 'select 1' language sql; CREATE FUNCTION postgres=# select * from testf() as (i integer); i --- 1 (1 row) I haven't quite figured out how this is useful though. It probably makes more sense if you use plpgsql but I still don't quite see what the use case is. -- Gr

Re: [SQL] Incomprehensible dogged sort in Merge Join

.568 rows=547796 > loops=1) > Sort Key: name > -> Seq Scan on > user_history (cost=0.00..22103.96 &g

Re: [SQL] Incomprehensible dogged sort in Merge Join

"Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes: > Gregory Stark wrote: >> >> What version are you running with? > > 8.0.1, sorry for missing. There have been 12 bug-fix releases since then on the 8.0 branch including updating timezones to reflect the new

Re: [SQL] Speeding up schema changes

d reason to use CHAR instead of varchar or text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you don't store anything more in it. text or varchar will take only as many bytes as the data you're storing (plus 4 bytes). -- Gregory Stark EnterpriseDB

Re: [SQL] Index usage in bitwise operation context

't see that it's going to be used for a join condition. Lastly, you could look for a GIST index method for varbit which would be superior to both of the above tactics. I'm still not sure it would be able to handle a join clause though, but maybe? --

Re: [SQL] Extracting hostname from URI column

pace into sections like .co.uk or .com.au. Canadian domains can be bought directly under .ca like amazon.ca. I think you'll have to store a specific list of tlds and how deep you want to look. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(

Re: [SQL] Speeding up schema changes

"Decibel!" <[EMAIL PROTECTED]> writes: > On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: >> Also, incidentally do you have a good reason to use CHAR instead of varchar >> or >> text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you >&g

Re: [SQL] ERROR: failed to re-find parent key in "pk_ep07"

"pk_ep07" > > Update to a newer PG version, possibly? This symptom has been seen > before... It was fixed in these bug-fix releases: 7.4.15, 8.0.10, and 8.1.6 which were released on August 1st of this year. There have actually been 3 to 4 more bug-fix releases sinc

Re: [SQL] Returning the total number of rows as a separate column when using limit

ith that. But I don't think that's a great solution either. (yet? I think cursors are getting more useful in Postgres, perhaps it will be eventually.) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Returning the total number of rows as a separate column when using limit

between 1 AND 20" is > rather expensive compared to PG's implementation of LIMIT. Oralce keeps > snapshot-info in the index, so counting only involves the index AFAIK. Well that's only going to be true if the index satisfies the whole query which is not going to be true for the

Re: [SQL] Returning the total number of rows as a separate column when using limit

"Gregory Stark" <[EMAIL PROTECTED]> writes: > "Andreas Joseph Krogh" <[EMAIL PROTECTED]> writes: > >> That's what I'm doing now. I run the query with "limit+1" as limit and if it >> results in more than limit, I know

Re: [SQL] functions are returns columns

e: CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof record(int,int) AS $$ SELECT fooid, foosubid FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; The return type if present has to match the OUT (and BOTH) parameters. -- Gregory Stark EnterpriseDB http://www.enterpri

Re: [SQL] functions are returns columns

"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: >> You're almost there: > >> CREATE FUNCTION getfoo (IN int, OUT int, OUT int) returns setof >> record(int,int) AS $$ >> SELECT fooid, foosubid FRO

Re: [SQL] postgres bogged down beyond tolerance

ually to tell the boot scripts what values to store (using sysctl) so you don't have to run sysctl every time you reboot. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadca

Re: [SQL] pg_clog (?) problem with VACUMM

do That's almost the right idea for a last ditch attempt to extract what data you can from a corrupted table. You have to fill the file with nul bytes though. Something like dd if=/dev/zero of= bs=1k count=nnn where nnn is, uh, I'm not sure how large, it won't take much to co

Re: [SQL] marking tuples

like intersection to see other plans which add flag columns. In those cases though they are used by the direct parent of the node which added it, so the planner can just mark a field in the parent indicating which column it should look at for the flag. -- Gregory Stark EnterpriseDB

Re: [SQL] UTF8 encoding and non-text data types

pect this kind of conversion to live in the same place as things like roman numerals or other more flexible formatting. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Sql ORDER BY and ASC/DESC question

CASE ? WHEN 1 THEN name ASC WHEN 2 THEN name DESC WHEN 3 THEN height ASC WHEN 4 THEN height DESC ELSE id ASC END But to the planner this will be basically an opaque expression. It won't be able to use any indexes on these columns. Also, incidentally you might want to use text strings ins

Re: [SQL] Sql ORDER BY and ASC/DESC question

"Tom Lane" <[EMAIL PROTECTED]> writes: > Gregory Stark <[EMAIL PROTECTED]> writes: > >> ORDER BY >> CASE ? >> WHEN 1 THEN name ASC > > Uh, no, putting the ASC/DESC decoration inside a CASE like that is not > gonna work doh! I had a feeli

Re: [SQL] Check before INSERT INTO

a ?? You'll have to explain what you want n_gen to contain. Then you'll have to decide whether you want to do this in ASP where you can certainly change the SQL all you like, or on the server where you can have triggers which change the values being stored or executing additional queries.

Re: [SQL] Check before INSERT INTO

;) For what it's worth your script is a security hole. Look into using query parameters which in ASP will probably be represented by "?". The method above will allow hackers to get direct access to your database and do nasty things. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] REFERENCES and INHERITS restrictions?

people is using inheritance for partitioning support. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send

Re: [SQL] Subqueries

It's that queries on one side of a join can't refer to tables on the other side of the join. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] regexp_replace and UTF8

ks in advance. You could use a plperl function to use one of the many html parsing perl modules? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)

Re: [SQL] Funtion to clean up strings?

> the +49 isn't applicable, but not both. This is the standard format for phone numbers. Parenthesized digits -- as you suspected -- represent digits which must only be dialled when using the number locally and must be omitted from outside. -- Gregory Stark EnterpriseDB http:/