Re: [SQL] Is it This Join Condition Do-Able?

Mischa Sandberg [EMAIL PROTECTED] writes: Anyone care to comment on the third row of output? I think you mistyped the last INSERT: insert into c values(2, 'C2'); insert into b values(3, 'C3'); I suppose you meant insert into c ... regards, tom lane

Re: [SQL] Problem with a Pettern Matching Check

that (bpcharregexeq). I have a feeling that we added that operator definition at some point for backwards compatibility, but it seems a bit odd now. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Problem with a Pettern Matching Check

Michael Fuhr [EMAIL PROTECTED] writes: On Mon, Aug 15, 2005 at 08:21:23PM -0400, Tom Lane wrote: Given that we consider trailing spaces in char(n) to be semantically insignificant, would it make sense to strip them before doing the regex pattern match? How standards-compliant would

Re: [SQL] insert into / select from / serial problem

with, say, INSERT INTO newtable (fielda, fieldb) SELECT field1, field2 FROM anothertable regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan

Re: [SQL] about subselect

and B38.tglavd = Temp_hasil2.Tanggal) And A01.tglavd = (select max(B01.tglavd) from ap012 B01 where A01.nojob = B01.nojob and B01.tglavd = A38.tglavd) )A Where Temp_hasil2.NIK = A.NIK; regards, tom lane

Re: **SPAM** [SQL] Faster count(*)?

for it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Calling SQL functions that return sets

) there are things you can't do any other way. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Calling SQL functions that return sets

Chris Mungall [EMAIL PROTECTED] writes: On Mon, 1 Aug 2005, Tom Lane wrote: Chris Mungall [EMAIL PROTECTED] writes: What are the reasons for deprecating the use of the function in the SELECT clause? The semantics of having more than one set-returning function in the target list are, um

Re: [SQL] REINDEX DATABASE

. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Different encodings in different DBs in same cluster

consistent results. When you use a non-C locale, it's best to stick to the encoding that the locale expects. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Can SELECT statements throw an error

exception, cardinality violation, insufficient resources, and operator intervention categories are possible. See the error codes appendix for some ideas. And of course, if the SELECT invokes a user-defined function, no holds are barred ... regards, tom lane

Re: [SQL] Dumping table definitions

to do this if I need to, but first wanted to check if Pg already had tools to export the table structure (without the data). Does it? pg_dump with the -s switch is a much better way ... regards, tom lane ---(end of broadcast

Re: [SQL] problem (bug?) with in (subquery)

to reverse-engineer a test case from your description ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] problem (bug?) with in (subquery)

be unique already, we * don't need to do anything. */ Of course, that needs to read ... unique already, *and we are using all of its output columns in our DISTINCT list*, we don't need to do anything. regards, tom lane ---(end of broadcast

Re: [SQL] problem (bug?) with in (subquery)

Luca Pireddu [EMAIL PROTECTED] writes: On July 15, 2005 08:58, Tom Lane wrote: Ah-hah: this one is the fault of create_unique_path, which quoth In any case, it looks like Tom has already found the problem :-) Thanks guys! On closer analysis, the test in create_unique_path is almost

Re: [SQL] Copy user privileges

done just as well. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through

Re: [SQL] Make COUNT(*) Faster?

Harald Fuchs [EMAIL PROTECTED] writes: FOR row IN EXECUTE 'EXPLAIN SELECT * FROM ' || tbl LOOP fails with the following message: ERROR: cannot open non-SELECT query as cursor [ checks CVS history... ] Use 8.0.2 or later. regards, tom lane

Re: [SQL] Make COUNT(*) Faster?

this functionality. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] Index creation question for expression (col1 || '-' || col2)

Ying Lu [EMAIL PROTECTED] writes: A question about creating index for the following expression. CREATE INDEX idx_t1 ON test (col1 || '-' || col2); You need more parentheses: CREATE INDEX idx_t1 ON test ((col1 || '-' || col2)); regards, tom lane

Re: [SQL] Make COUNT(*) Faster?

Steve Wampler [EMAIL PROTECTED] writes: Tom Lane wrote: If you want something cheap, you could use the same technique the planner uses nowadays: take RelationGetNumberOfBlocks() (which is guaranteed accurate) and multiply by reltuples/relpages. Yes - this would be an excellent approximation

Re: [SQL] Make COUNT(*) Faster?

, if an approximate answer is good enough, there are a whole other set of possible solutions. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] left joins

to plain JOIN, but even if we did not do that you'd get the same result. I've heard it claimed that Oracle produces different results; if true, it must have something to do with their rather standards-challenged interpretation of NULL ... regards, tom lane

Re: [SQL] Prepare plan in plpgsql

Jocelyn Turcotte [EMAIL PROTECTED] writes: i'm wondering if there is a way to prepare and execute a plan in a plpgsql function. You do not need that because plpgsql automatically caches plans for SQL statements appearing in a plpgsql function. regards, tom lane

Re: [SQL] 'show full processlist' in postgres?

Erik Wasser [EMAIL PROTECTED] writes: But 'current_query' is still always empty... B-( The pg_stats views lag reality by a certain amount, so checking for your own query is generally not gonna work. Try starting a long-running query in another session. regards, tom lane

Re: [SQL] ENUM like data type

own type using the builtin textin and textout functions, and then add just the functions you wish to provide. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Foreign key pg_dump issue and serial column type

work in every context that you might think. It'd probably make sense for alter column type to accept it, but for now what you gotta do is create a sequence and set the column default manually. regards, tom lane ---(end of broadcast

Re: [SQL] optimizer, view, union

.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [SQL] empty view, replace view, column type change?

, CURRENT_TIMESTAMP, and LOCALTIME. Thoughts? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] after delete trigger behavior

that. 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] after delete trigger behavior

). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] WHY transaction waits for another transaction?

deferred, so that they're not checked until the transaction is about to commit. This is not bulletproof, but because it considerably reduces the time window for a conflict, it may do as a workaround until 8.1 is ready. regards, tom lane ---(end

Re: [SQL] Putting an INDEX on a boolean field?

down in the join tree, using logic similar to what we use to decide where ordinary WHERE quals can bubble down to. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan

Re: [SQL] partial index on non default tablespace syntax

bothered to make a tablespace to test with, but the point is the syntax is fine.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Putting an INDEX on a boolean field?

Marc G. Fournier [EMAIL PROTECTED] writes: Does that make sense? Would it ever get used? It could get used if one of the two values is far less frequent than the other. Personally I'd think about a partial index instead ... regards, tom lane

Re: [SQL] foreign key on pg_shadow

in the near future either, though I think there is something about it on the TODO list. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] predicate locking

. Range locks are a far cry from general predicate locks. regards, tom lane PS: kindly don't email me privately while posting the same message to the lists. You think I have time to answer things twice? ---(end of broadcast

Re: [SQL] predicate locking

either. The documentation is not wrong; or at least, what you've quoted does not show that it is. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] access to new/old in dynamic sql for audit table

. 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

Re: [SQL] SELECT very slow

well but I think it has similar semantic constraints. The solution is to use a cursor and FETCH a reasonably small number of rows at a time. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Indices and user defined operators

Dmitri Bichko [EMAIL PROTECTED] writes: So, is there any way to make these operators use an index defined as above? If you've set things up so that the operators are defined by inline-able SQL functions, I'd sort of expect it to fall out for free ... regards, tom lane

Re: [SQL] Indices and user defined operators

-- Index Scan using fooi on text_tbl (cost=0.00..4.68 rows=1 width=32) Index Cond: (upper(f1) = 'FOO'::text) (2 rows) This is with CVS tip, but I'm pretty sure it works as far back as 7.4. regards, tom lane ---(end

Re: [SQL] to listadmin

are sufficiently poor list citizenship to merit removal from the list. Marc is the man to complain to in such cases --- Marc, do you see a name like [EMAIL PROTECTED] on pgsql-sql? regards, tom lane ---(end of broadcast)--- TIP 4: Don't

Re: [SQL] how to store more than 3 MB of character data in Postgres Varchar field

, or equivalently varchar with no length specification. The 1-MB limit on what you can write as a length spec is somewhat arbitrary, but certainly an enforced length spec larger than that would be a completely arbitrary number as well ... regards, tom lane

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

of dimensions. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] 'true'::TEXT::BOOLEAN

variable. plpgsql's notions of type safety are pretty lax ;-) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] CASE WHEN foo IS NULL THEN DEFAULT ELSE foo END

expression. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] using a selected row as a function parameter

not like there's some huge inefficiency in doing it as a join. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] 'true'::TEXT::BOOLEAN

if everyone's convinced it's a good idea or not. You'd also have to argue about whether varchar should be included in the special dispensation ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet

Re: [SQL] getting details about integrity constraint violation

think there's already been some discussion about it; but no one has stepped up with a concrete proposal, much less volunteered to do the work ... regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through

Re: [SQL] (Mis)using the PostgreSQL planner to get estimated row counts

will be. Depending on what you want the results for, it might be best to ignore any top-level LIMIT node. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Impossible with pl/pgsql?

) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] Sql select like question.

guidelines: http://www.postgresql.org/docs/8.0/static/bug-reporting.html regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [SQL] Sql select like question.

doesn't fail for me ... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] could not devise a query plan

for the report! Seems I overlooked a case when fixing the original report last year. Patch for 8.0 attached (it's the same in 7.4 too). regards, tom lane Index: joinpath.c === RCS file: /cvsroot/pgsql/src/backend

Re: [SQL] ARRAY() returning NULL instead of ARRAY[] resp. {}

arrays containing nulls we punt and return a null array value. That's wrong too ... but it's a different issue. The point Markus is complaining about seems like it should be easily fixable. regards, tom lane ---(end of broadcast

Re: [SQL] DBD::Pg on Enterprise 3

Core has been removed from RH Enterprise 3 baseline. AFAICT it's shipped on the CDs. It may well not be part of the minimal installation ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive

Re: [SQL] Meaning of ERROR: tuple concurrently updated

concurrently ANALYZE the same table and conflict when they both try to update the same pg_statistic rows. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] encoding

-linux-gnu Proves nothing, since configure scripts ignore unrecognized --enable and --with options. (The Autoconf boys steadfastly maintain that that's a feature not a bug, but I disagree.) The more relevant check is configure --help | grep locale regards, tom lane

Re: [SQL] how many tuples on a cursor?

... regards, tom lane ---(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] pgtop, display PostgreSQL processes in `top' style

to a reasonable extent. I'm unconvinced that we should expend the overhead to be able to do the second. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org

Re: [SQL] Array of Arrays of int

languages consider them the same ;-)) 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

Re: [SQL] Build issues: -static builds resulting initdb problems

now, but you won't want any dynamically loaded libraries later. This is a Bad Idea; complain to the linker hackers about it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] Build issues: -static builds resulting initdb problems

that then ... but in any case I don't think it's a Postgres bug. 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] SYNTAX ERROR ON FOR... LOOP

, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] several questions about R-tree index

suggestion to consider GIST. GIST has its own issues, but at least there are people looking at it/using it/working on it. R-tree doesn't seem to have any user community that really cares. regards, tom lane ---(end of broadcast

Re: [SQL] Matching the MYSQL Describe table-name command

the current user has access to, which seems a bit silly to me. The SQL99 spec defines some of the views as showing only what you own, and others as showing whatever you have access to. I think we track the spec, but feel free to point out discrepancies. regards, tom lane

Re: [SQL] C function - other process

. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] C function - other process

, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Odd error message ( hash indexes do not support whole-index scans)

Marinos Yannikos [EMAIL PROTECTED] writes: Shouldn't PostgreSQL behave in a different way in this case? (e.g. just not use the index): Good catch. But why are you using a hash index for this? regards, tom lane ---(end of broadcast

Re: [SQL] Function to either return one or all records

; ... but that won't do anything to solve the performance problem. Doesn't help for the second select to use an index, if the first one grovels over the whole table anyway ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't

Re: [SQL] DateAdd function ?

are faster and simpler to use. 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

Re: [SQL] [GENERAL] 'Select INTO in Execute (dynamic query )

FOR ... IN EXECUTE. See the plpgsql docs. 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] Getting the output of a function used in a where clause

not true for you, you're going to have some issues ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] dynamic 'INSERT' query?

... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

the change fully transparent we'd have to define now() as when you issued BEGIN, but there's a pretty good argument that now() should correspond to the time of the transaction snapshot. regards, tom lane ---(end of broadcast)--- TIP 5

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

be event #3 since that corresponds to the database snapshot you see. 100% backwards compatibility would require setting now() at event #1, but will anyone weep if we change that? regards, tom lane ---(end of broadcast)--- TIP

Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)

xmin might not be quite the right metric for this purpose. It might be worth thinking about whether we could do better with a little more info in PGPROC. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget

Re: [SQL] max question

each row will form its own unique group :-( What is it you are trying to accomplish here? In particular, what led to that choice of GROUP BY? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive

Re: [SQL] Query runs very slowly in Postgres, but very fast in other DBMS

, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] subselect query time and loops problem

. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] Question on triggers and plpgsql

the AFTER trigger fires. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Question on triggers and plpgsql

Andrew Sullivan [EMAIL PROTECTED] writes: On Fri, Apr 08, 2005 at 10:36:26AM -0400, Tom Lane wrote: AFAICS the only way that you could get into a can't-roll-back situation is if the trigger tries to propagate the update outside the database. For instance, the proverbial trigger to send mail

Re: [SQL] getting count for a specific querry

with the problem of counting the total dataset 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] Question on triggers and plpgsql

. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] getting count for a specific querry

DESC LIMIT 1; There are periodic discussions in the hackers list about teaching the planner to do that automatically, and it will probably happen someday; but it's a complicated task and not exceedingly high on the priority list. regards, tom lane

Re: [SQL] [GENERAL] Problems with Set Returning Functions (SRFs)

from test) ss; regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings

Re: [SQL] 9.17.5. Row-wise Comparison

a few months back. The spec's semantics correspond exactly to the sort ordering of a multiple-column btree index, and so there are good reasons why we'd want to provide that behavior even if it weren't mandated by the spec. regards, tom lane ---(end

Re: [SQL] Order of items in FROM causes error?

MySQL tend to misunderstand this, because MySQL gets it wrong...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] cost of CREATE VIEW ... AS SELECT DISTINCT

dominates them both. 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] How to make update statement to work

what's the chapter and verse that says so? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Postgre: 8.0.1 Create Table insde a function gives strange error at execution time

it ought to substitute its variable for a reference or not. It will always do so, even in cases where there arguably might be a way for it to tell that it shouldn't (and there are cases where it simply couldn't tell, anyway). regards, tom lane ---(end

Re: [SQL] Securing VIEWS that use contrib/pgcrypto -- how?

Moran.Michael [EMAIL PROTECTED] writes: How do you secure a VIEW so that only certain users may use it? GRANT/REVOKE? regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister

Re: [SQL] Process priority.

. 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

Re: [SQL] How to force subquery scan?

or OFFSET. SELECT * FROM (SELECT ... OFFSET 0) ss; In principle the planner could figure out that this offset is a no-op, throw it away, and then flatten the query. But it doesn't at the moment, and I doubt we'll teach it to do so in the future. regards, tom lane

Re: [SQL] Parameterized views proposition

. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [SQL] sql: LIKE problem

\\, and then the LIKE operator sees that as a quoted backslash. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] datestyle setting

question is, how do i set the pgdatestyle in the database as 'European,sql'? If it's a reasonably recent version of PG, either ALTER DATABASE SET or ALTER USER SET might serve. regards, tom lane ---(end of broadcast)--- TIP 5

Re: [ADMIN] [SQL] Postgres schema comparison.

-v ^-- | md5sum The problem I have with this, is that I have to run the command per table, Why? If the problem is varying order of table declarations, try 8.0's pg_dump. regards, tom lane ---(end of broadcast)--- TIP 4

Re: [SQL] definative way to place secs from epoc into timestamp

10:11:35 EST 2005 The system's internal idea of the time didn't change (modulo the few seconds it took to type the commands), but the way it is displayed changed. regards, tom lane ---(end of broadcast)--- TIP 5: Have you

Re: [SQL] date - date returns integer?

date arithmetic. I think the definitions of these operators are fine. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do

<    5   6   7   8   9   10   11   12   13   14   >