ract a pretty useful description of all the options
majordomo supports (there are a lot of 'em these days).
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
dn't exist before
> the execution.
More to the point, the later SELECTs are probably being parsed with
regard to the previous versions of tmp_cr and tmp_db that existed at
the start of the line.
This is fixed in 7.1, but in previous releases you'd best split up
that sequence of operat
"Ligia Pimentel" <[EMAIL PROTECTED]> writes:
> Does anyone know if it is possible to make a FULL OUTER JOIN in Postgres 7?
In 7.1.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
at all if you build your system like that.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
, but I'd
guess that the net effect would be relatively small.
Really what you want to be asking yourself is which columns do you
need an index on for your query logic. What datatype they are is a
minor consideration.
regards, tom lane
---(end o
ractable thing to deal with than mappings of
individual temp table names.
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
18887
(1 row)
shows that the above view's reference to int8_tbl isn't broken.
Of course you'll need to be superuser to do the UPDATE on pg_rewrite,
and you will probably find that you need to quit and restart the backend
before it will use the changed view definition.
Good luck!
regards, tom lane
PS: Yes, I know we gotta fix this...
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
ide PL functions, for example.
2. An ALTER VIEW command that lets you change a view's defining query,
while keeping the same OID, as long as the names and types of the output
columns don't change. This would reduce the need to drop and recreate
views.
regard
portion of the original query string that corresponds
to the body of the CREATE RULE/VIEW command. But that could be fixed
with some straightforward hacking...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
"Peter J. Schoenster" <[EMAIL PROTECTED]> writes:
> On 22 Mar 2001, at 10:05, Tom Lane wrote:
>> There is *no* performance advantage of CHAR(n) over VARCHAR(n).
> I wonder if this question of char/varchar is postgresql specific or
> rdbms in general.
It's de
s more than a couple K
then the text will be compressed and difficult to spot or extract.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
ings(1) on the pg_proc table, which will be ... hmm ...
$PGDATA/base/YOURDBOID/1255. Look in pg_database if you're not sure of
the OID of the database you are using.
If you have not vacuumed then the latest version of the row will be the
one closest to the front of
version), initdb, reload.
Long version: Great Bridge's docs explain upgrading in excruciating
detail ;-). You can download PDFs for free from
http://www.greatbridge.com/docs/
regards, tom lane
---(end of broadcast)---
TI
a "clean" way to implement this feature in postgresql?
> I really don't want my applications to have to know what indexes are in
> place for a given table.
I'm confused. What does "replace into" have to do with having to know
what indexes are in place?
me as essentially bogus in any case...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
ELECT and SELECT
FOR UPDATE have different visibility rules, so you probably don't want
to intermix them.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
add FOR UPDATE to that final SELECT?
You're right, the initial SELECT FOR UPDATE is a waste of cycles
considering that you're not using the value it returns. But you'll
still need the last select to be FOR UPDATE so that it plays by the
same rules as the UPDATE does.
correct way of quoting a mixed-case field name.
I think the field is not named quite like you think it is. Try doing
pg_dump -s -t tblshop databasename
to see what the field names really are.
regards, tom lane
---(end of broadcast)
Dirk Lutzebaeck <[EMAIL PROTECTED]> writes:
> on 7.0.3 want to COUNT
> SELECT DISTINCT a,b FROM t;
In 7.1 you could do
select count(*) from (select distinct a,b from t) as t1;
In 7.0 and before I think you have no choice but to use a temp table.
t change value inside a transaction. See
http://www.postgresql.org/devel-corner/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensiv
e character you never use in column A, say '|', you
could do count(distinct(a || '|' || b)) with some safety, but this
strikes me as still a pretty fragile approach.
regards, tom lane
---(end of broadcast)--
open transaction started.
But offhand I think it does not matter for anything else.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command
ros:
You need an extra level of quoting because the function body is itself
a string literal. You might find the quoting discussion in
http://www.postgresql.org/devel-corner/docs/postgres/plpgsql-porting.html
helpful.
regards, tom lane
---(end of b
be easy to implement,
but that doesn't make it a good idea.
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])
es. The string-literal parser eats one
level of backslashes, but you need the pattern that arrives at LIKE
to look like "a\\b%".
BTW, "PostgreSQL version 7.1" does not exist yet.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
o full release cycles --- therefore,
it can wait another cycle for a fix that has been considered, reviewed,
and tested. Let's not risk making things worse by releasing a new
behavior we might find out is also wrong.
regards, tom lane
---(end o
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
d test a fix in the early
part of the 7.2 development cycle, and then back-patch it into a 7.1.x
release perhaps 2 or 3 months from now.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Use
ws & functions. Is
> there a setting that can change this back to the "regular" behavior of
> sum(integer) = integer?
Sorry, no. You can coerce the result back to int4 if you care to risk
overflow: "select sum(int4field)::int4" or some such.
Error: ERROR: VACUUM (repair_frag): FlushRelationBuffers returned -2
I believe this is fixed in 7.1RC1. Leastwise, one possible cause of
this message is fixed in RC1 ;-)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and
zas. You appear to be envisioning one firing per tuple
deleted. I'd recommend doing the UPDATE inside a trigger instead.
That approach will operate in the way you envision.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
hen the plan is created.
In 7.1 you can work around this problem by using plpgsql's FOR ... EXECUTE
notation, but I don't think there's any good answer in 7.0.
for rec in execute ''select id,url from urlinfo where url like
''||quote_litera
ce. You'll need to provide some
details about the problem queries and the plans you get for them in 6.5
and 7.0.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
s up even more. See the
admin documentation for more info.
I don't recommend using IDENT for connections from untrusted machines,
but on localhost it's as trustworthy as your local sysadmin...
regards, tom lane
---(end of broadcast)-
NOTICE: Buffer Leak: [059] (freeNext=54, freePrev=58, relname=tblstsc1,
> blockNum=0, flags=0x14, refcount=-4 -1)
(a) What Postgres version is this?
(b) Could we see the schemas for the tables? (pg_dump -s output is the
best way)
regards, tom lane
--
rouped query, and so
references to the ungrouped h.* columns in the SELECT targetlist are
not well defined. Lord knows what result you were getting from 7.0 ...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
rotocols and isn't under our control).
> What is the best way of dealing with this problem as I ended up stopping and
> restarting the postmaster?
It would've been sufficient to find and SIGTERM the individual backend
from the lost session.
regards, to
stamp;
?column?
-
0012-05-28 00:00:00
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
optimisations by pre-parsing the SQL ?
Unless your TCP connection is running across tin cans and string,
the transfer time for the query text is negligible ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get o
oreign key references? If not automatically, will VACUUM ANALYZE do
> this for me?
No, and no :-(.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
OTICE :-(.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
7;t gotten round to
convincing myself about whether that transformation is always valid,
or what conditions it needs to be valid. A TODO item for some
future release...
regards, tom lane
---(end of broadcast)---
TIP 6: Have yo
ks is right anyway). The real problem is the planner
thinks that LIKE '%http://www.postgresql.org/%%' is really selective; it
has no idea that most of your table mentions pgsql.org URLs :-(. We
need better statistics to fix this properly. (On my list for 7.2.)
| group 2 | 00:00:03
3 | group 3 | 00:00:03
4 | group 4 | 00:00:03
5 | group 5 | 00:00:03
6 | group 6 | 00:00:03
7 | group 7 | 00:00:03
8 | group 8 | 00:00:03
9 | group 9 | 00:00:03
10 | group 10 | 00:00:03
(10 rows)
regards, tom lane
of a less appropriate plan. Hard to
tell without seeing what EXPLAIN has to say, though.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
rtMem parameter (backend -S switch) set to?
That's about the only use I can think of for RAM beyond what's needed to
cache the whole database ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscr
(new in 7.1):
select zylinder.*, a_typ as typ, a_t_definition_d as text
from (zylinder left join auftrag on (a_nr = z_a_nr))
left join auftrags_typ on (a_t_code = a_typ);
regards, tom lane
---(end of broadcast)---
TIP
n run from PSQL. Huh?
row_count is a keyword in plpgsql ... not sure how long it's been a
keyword ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
st of
evaluation for functions, but it's not being used for anything ...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
"comp" <[EMAIL PROTECTED]> writes:
> but after connection when I type testdb=3D>\df it gives this error:
> " ERROR: Function 'oid8types(oidvector)' does not exist.
Use the version of psql that came with 7.0.3, not some older version.
ion are you using?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
't marked mergejoinable, but plain old int certainly doesn't
have that problem. I think there's something you haven't told us. Is
either of these tables actually a view?
regards, tom lane
---(end of broadcast)---
Tim Perdue <[EMAIL PROTECTED]> writes:
>> Is either of these tables actually a view?
> Hehe - no. I sent the \d of both tables at the bottom of that email.
\d isn't very helpful for these sorts of reports. How about pg_dump -s ?
is with the grants/revokes per se;
probably the slowdown is elsewhere. Have you looked for changes in
the EXPLAIN results for the queries being used?
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
lly I'd vote for losing them altogether...
> I wish people could also lose the preaching in their signatures.
Amen, brother ;-)
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FA
null
input, rather a null result will be assumed automatically --- with much
less overhead than an explicit test for null would need.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once wit
thinks it's
useless, because you're discarding the result. (I think that plpgsql is
being overly anal-retentive about it, since such a query might indeed be
useful if you then examine FOUND or ROW_COUNT, but that's the issue at
the moment.) Try making it a SELECT INTO instead.
the second need. For the first, perhaps
use the FOR ... loop construct in plpgsql.
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
ruser status. You are laboring under a severe
misapprehension if you think that epi has ANY restrictions on what he
can do ...
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
"Josh Berkus" <[EMAIL PROTECTED]> writes:
> Can someone explain this to me?
Are you taking into account that SQL booleans are actually three-valued?
They can be TRUE, FALSE, or NULL (NULL taken as meaning "don't know").
regards, tom lan
'll appear in 7.1.1, or you
can grab CVS or a nightly snapshot if you are in a hurry.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
er to develop a GIST opclass instead of rtree.
In the long run I suspect GIST will be better supported than rtree,
since it's more general.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through U
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
erted into has no foreign keys?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
the maillist archives, but www.postgresql.org is too
friggin' slow at the moment ...
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Jeff Hoffmann <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> How the heck did the GIST index code get developed/tested without some
>> opclasses?
> doing some digging at berkeley, i found the original pggist patch file
> that created the gist access method &a
according to the SQL92
spec, but we don't support that (yet).
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])
Time to update to 7.1...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
ws have a lot of
problems in that version, and one of the problems is that you can't
do another level of aggregating on their results.
Basically a view and a subselect are the same thing, so you can't get
around the restrictions of one by using the other...
7.1 is what Sara needs.
It looks to me like some of the uses of IsSharedSystemRelationName
could/should be replaced by examining pg_class.relisshared, but in other
places it's really necessary to determine sharedness with nothing but a
relname to go on.
This will all need to be rethought when we implement schemas, anyway
Cedar Cox <[EMAIL PROTECTED]> writes:
> When I try to run pg_dump I get a segmentation fault. This only seems to
> happen if the PGDATABASE environment variable is set and I don't supply
> the database name on the command line.
Fixed.
returns the return value for the function and no error.
But how would KPSQL know what value the function might have returned?
Something fishy here ... did you check the postmaster log to see whether
an error is really being reported or not?
regards, tom lane
d version of my example
While it's not a general solution, there's always transitivity:
select f.id
from foo f, ola o
where f.id = (
select max( b.id )
from bar b
where b.bling = "i kiss you!"
)
and o.id != f.id
regards, tom
"J.Fernando Moyano" <[EMAIL PROTECTED]> writes:
> The postgres version is 7.0.2 ...
I'd suggest an update to 7.1 ...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
"Emils Klotins" <[EMAIL PROTECTED]> writes:
> Running 7.0.2 on Alpha/RedHat 6.2 256MB RAM
Update to 7.1. 7.0.* has a lot of portability problems on Alphas,
and one of them is that regexps with between 33 and 64 states don't
work (int vs long problem...)
e
> order by date;
What gives you the idea that this doesn't work?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
::=
UPDATE
SET
[ WHERE ]
Postgres allows the clause, but treats it as supplying *additional*
table references besides the target table reference. Thus the error.
In other words: you can JOIN, but not against the target
inappropriately in committing a new-feature item
before we'd made the branch for 7.2 development. But I don't have the
time to argue about it...
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Type reltime is old and deprecated. Don't use it.
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere&
it'd not be clear
how far the alternatives are supposed to extend.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL P
tors that're clearly marked as MS-SQL-isms. But I don't
think we'd do the world any favor by adopting a clearly nonstandard
notation as part of our standard operator set.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
"guard" <[EMAIL PROTECTED]> writes:
> update table_a set trn_no = table_b.trn_no from table_a,table_b
> where table_a.cust_no=table_b.cust_no
> IN pgsql 7.0x IS OK
> if run 7.1 IS error ??
More details please?
regards, tom lane
--
want to own that database.
(You will then have to find the objects he *should* own, and fix
their owner ID values...)
There should be a unique index on pg_shadow.usesysid, but due to an
old oversight there isn't. Fixing this is on the TODO list.
regards, tom lane
Keith Gray <[EMAIL PROTECTED]> writes:
> Is it possible to get/configure PostgreSQL to handle
> as within a dleimited string?
We already do.
regression=# select 'O''SHEA';
?column?
--
O'SHEA
(1 row)
regards, tom lan
Keith Gray <[EMAIL PROTECTED]> writes:
> This may be a problem in "ipgsql" then??
I guess. What is that, anyway?
> ...or is it different in update from select?
Nope, a literal is a literal.
regards, tom lane
--
FROM race r2
WHERE r2.personid = r.personid
ORDER BY r2.date DESC
LIMIT 1) AS carid
so that the result tracks the outer query, and in this form it'd be
redone once per output row.
regards, tom lane
--
a match.
In your above example, I'm not sure whether it's right to put i.active
in the ON part or in WHERE. It depends on what you want to happen for
status rows that match only inactive images, and whether you consider
them different from status rows that match
r order. But the Unique
filter needs to see its inputs in order by the fields being made
unique.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
ate - '2000-06-12'::date;
?column?
--
365
(1 row)
> performance=# SELECT timestamp('2001-06-12'::date - '2000-06-12'::date);
>timestamp
>
> 1970-01-01 01:06:05+01
> (1 row)
timestamp(integer) converts a Unix timestamp val
n its output, which it shouldn't oughta
have done. You can't see the null from outside the system, but it
manages to mess up text comparisons anyway.
BTW, you should consider using inet or cidr datatype for that column
rather than varchar...
regards, tom lane
--
[EMAIL PROTECTED] writes:
> How can you use a distinct on () including the whole union.
In 7.1 you can write
select distinct ... from (select ... union select ...) ss;
regards, tom lane
---(end of broadcast)---
TIP 3:
ing the sort order. The outer path's
ordering is preserved only in relatively small test cases...
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
e one.
See any discussion of SQL NULLs --- Bruce's book talks about this IIRC,
or we've been over the turf more than once in the mailing list archives.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checke
"Marc Sherman" <[EMAIL PROTECTED]> writes:
> I'd like to select the newest (max(timestamp)) row for each id,
> before a given cutoff date; is this possible?
select * from log order by timestamp desc limit 1;
regards, tom lane
-
id, timestamp desc;
See the SELECT reference page for more about this.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
temp table name would refer
to a different table in each session.
(You realize, of course, that CURRENT_USER already exists per SQL spec.
I assume you just meant that you'd like to have things *like*
CURRENT_USER, but defined by yourself...)
her trying with
earlier releases.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
now allows update/delete over inheritance
hierarchies, which never worked before --- that doesn't look like a
dying feature to me.
I would not really recommend using multiple inheritance, but single
inheritance seems like a safe-enough bet.
regards, tom lane
-
viously :-(). It
would be fairly difficult to change, anyway.
> How can I get the result I want?
If you want to copy the data actually inserted, a trigger is a much
better bet than a rule.
regards, tom lane
---(end of broadcast)
ry as where cdate > 1978 (result of
integer subexpression) and then doing some weird integer-to-date
conversion. In general, any constant of a non-numeric datatype needs
to be quoted in SQL queries.
regards, tom lane
---(end of broadcast)---
301 - 400 of 2610 matches
Mail list logo
