now of one way to make the
index work. Use
groupes.nom>='beatl' AND groupes.nom<='beatlz'
And that should work - although 'z' is a poor character to use - pick the
highest valid character in your character set. You might also want to look
in the archives for the thread on pgsql-general with a subject of 'Simple
Question: Case sensitivity'
- Richard Huxton
- Original Message -
From: "Tom Lane" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, January 03, 2001 1:02 AM
Subject: Re: [SQL] Weird problem with script...
> [EMAIL PROTECTED] writes:
> > I'm building a script to create the tables in my database.
ondering if any of the smarter people on the list
have an SQL way of doing it (something with sub-queries?)
PS - I realise I might get 'xxxyyy' or 'yyyxxx' without forcing an order but
I don't actually care in this case.
TIA
- Richard Huxton
ggregates
without resorting to C. Shame it's not a standard SQL feature.
Thanks Tom - don't know how you find the time to give so much help in the
lists.
- Richard Huxton
ustment on 30th Sep or 1st Oct - that
caused issues in some previous versions of postgres IIRC (have a rummage in
the archives)
- Richard Huxton
ase
Don't know the script you're talking about. Check the settings in the
connect command. Try "perldoc Pg" for information on how the Pg module
works.
- Richard Huxton
| varchar() not null |
15 |
> | user_exp | timestamp|
4 |
>
+--+--+-
--+
> Indices: users_pkey
>
>users_user_login_key
Have you tried dropping the index? Could be mangled or a locale problem...
- Richard Huxton
oll in ('286.35', '286.30', '286.25')
instead of all the UNIONs? This is the same as
... AND (Z_durch_sol1='286.35' OR Z_durch_sol1='286.30' ...)
HTH
- Richard Huxton
;s and
> file transfer ist the most simple thing to do. Does anyone have a script
> to automate this?
>
Look into pg_dumpall
- Richard Huxton
e query.
You could use rules/triggers to set a "dirty" flag for each record that
needs copying - but it sounds like you're already doing that.
If you wanted things to be more "real-time" you could look at LISTEN/NOTIFY
- Richard Huxton
; value, which must be set to nextval()) will be used; to obtain the id,
> if indeed you need it, you can than select currval(), which is
> guaranteed to work on a per-backend basis.
Yep - it's either get nextval and insert or insert and check currval.
- Richard Huxton
pplication and do the join "by hand" there.
- Richard Huxton
al unique, footxt text);
create table bar (barid serial,
barfoo int4 references foo (fooid),
bartxt text);
Then after a few inserts...
delete from foo where fooid=1;
ERROR: referential integrity violation - key in foo still
referenced from bar
- Richard Huxton
omewhere that explains this just point
> me to it.
Look at the page "datetime-types.htm" in the docs (might only be in the docs
for 7.1).
- Richard Huxton
to know what you are trying to acheive. The best way to
measure speed is to build a system and populate it with dummy data.
Then, analyse your results.
- Richard Huxton
e:
select foo_function('x');
At present it is only useful for use by other functions (I believe). I think
Tom said this is on the developer todo list though, so sometime after 7.1 it
should be possible.
- Richard Huxton
ables by quoting
them, as you've discovered.
This usually crops up converting from a different DB to Postgresql.
- Richard Huxton
access refers to as a "totals
query". Try something like:
SELECT LecturerName,min(ProjectCode) FROM tblSuggestions GROUP BY
LecturerName
Basically, anything that is not min() or max()ed should be mentioned in the
GROUP BY.
You might need to quote "LecturerName" (like that) etc since they are
mixed-case. On the other hand the ODBC might deal with all that for you.
- Richard Huxton
ge on this in the programmer's guide.
- Richard Huxton
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
time and run a
separate reaper process to kill anything not used for 15 minutes (or whatever).
You should be able to automate this to a degree with triggers etc.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
one for
each session with the table hard-coded (you could also do this from the
application I'd guess)
3. Try pl/Tcl - I _think_ that lets you construct a dynamic query, but I
don't know TCL so can't say (it's supposed to be easy enough but I've
ne
Unfortunately, setting DEBUG_PRINT_xxx
doesn't seem to show any detail, do I can't show a trace.
Of course, with a trigger you can have an IF..THEN..ELSE to make sure you
control the order of execution.
- Richard Huxton
---(end of broadcast)-
rip the final trailing comma.
Note that this is probably not a good idea - the ordering of the
contacts will not be well-defined. When I asked about this Tom Lane was
quite surprised that it worked, so no guarantees about long-term suitability.
- Richard Huxton
--
rib/ directory for details on full-text indexing (fti).
- Richard Huxton
---(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
ext;
y text;
nd text;
begin
d := rtrim(to_char(t::timestamp, \'Day\'));
m := rtrim(to_char(t::timestamp, \'DD Month\'));
y := rtrim(to_char(t::timestamp, \'\' ));
nd := d || \' \' || m || \' \' || y;
return nd;
end;' lang
chive
> with a simple command like move ?
>
begin;
insert into archive_foo (select * from foo where foo_id=1);
delete from foo where foo_id=1;
commit;
Is probably the closest you could get.
Alternatively, you could wrap the above up in a function and just go:
select do_archiv
Christopher Sawtell <[EMAIL PROTECTED]> said:
> On Wed, 14 Mar 2001 01:38, Richard Huxton wrote:
> > From: "Christopher Sawtell" <[EMAIL PROTECTED]>
> >
> > > Please could a kind soul help me with this.
>
> [ ... ]
>
> > Note
ee: cannot find block containing chunk
It's complaining that memory it tries to free hasn't been allocated.
Something has got mangled here. I'd take a backup of the table drop the
index and recreate it, see if that helps.
Also - what version of Postgres is this? One of the developers
fact_counts_agg SET open_count=open_count-1 \
>WHERE group_artifact_id=new.group_artifact_id;
Failing that, email me the definition of artifacts_count_agg and artifact
and I'll play with it here.
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ming the entries are put
in one at a time and in order. That way you just need to look at the
last entry to determine if the new one is in the same batch.
Any use?
- Richard Huxton
> --
> George Young, Rm. L-204[EMAIL PROTECTED]
> MIT Lincoln Laboratory
> 244 Wood S
^^^
> ug2.role = map.role);
^^^
I take it these are actually "user_group_map"?
- Richard Huxton
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an a
do a \d or pg_dump it to see
how it works.
> I would like to migrate to a job number created when the insert is
> done.
Once you see how it works, pg_dump the database, edit the file and
re-import the data. Nice clean solution and easy to cope with if
something
e will be gaps in your numbering.
Also check out the SERIAL data-type which can provide automatic numbering
for the fields.
I'm sure there are examples in Bruce's book (there's a link on
www.postgresql.org)
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
rchives for someone else who did this recently (in the last month or so,
Tom Lane was involved in the discussion too). I forget the fella's name, but
he should have almost exactly what you want.
You can then do something like:
select typ, commify(diam) from zylinder group by typ;
- Richard Huxt
u'll need to combine the sequence with another value - see my
postgresql notes at techdocs.postgresql.org and perhaps look into the
plpgsql cookbook (www.brasileiro.net from memory)
- Richard Huxton
---(end of broadcast)---
TIP 2: you ca
t can then update your database.
This can be as simple as placing a script into /etc/cron.monthly/ on some
systems (e.g. Linux Redhat) but in any case is not too complicated.
PS - it is usually easier to do this early on the first day of each month
(every month has a day 1, not all have a day 31).
ROUP BY typ;
typ | joinall
-+--------
1 | 800,840,870,1120
2 | 760,800,900,1200,1234,1352
(2 rows)
Note the explicit cast of diam into text.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
the postgres notes from techdocs.postgresql.org - I've got an
example there of exactly this (it's in the "automating" chapter - sorry,
forget the precise URL)
Also have a look at the Cookbook (linked to from same place) which might
well have more examples.
- Richard Huxto
I believe this is on the
todo list for a later 7.x release.
Just from the top of my head, you might try a view with a select rule,
although I'm not completely clear what your objectives are.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
...ret.fieldn);'. At least, I haven't succeeded any other
way.
> > Messy, but the best method available right now.
> >
> > > -Original Message-
> > > From: Richard Huxton [SMTP:[EMAIL PROTECTED]]
> > > Sent: Tuesday, March 27, 2001 2:27 AM
&g
e the SELECT-statement out you get '''\\\'' and that is not
> unterminated.
Had something similar myself the other day. The reason is that you are
already one deep in Postgres' string-parser, so you need something like:
SELECT '''' AS RESULT
was fixed before 7.0.2
You might want to try the to_char function and see if that displays the
same problem.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
ything relevent I tend to return either 1 or
'succeeded' or similar.
- Richard Huxton
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
this sounds like a strange way to do things, but i think this is the
> best way to go.
Definitely look at some of the general-purpose templating modules.
They'll all handle tables.
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
From: "Stef Telford" <[EMAIL PROTECTED]>
> Richard Huxton wrote:
>
> Each select works on a view, rather than hardcode the view into the
> perl CGI, i would rather have the table header/column titles returned
> as the first item as text/html (i know abou
3.php.If
there is another way out pls tell.
> I tried using functions to get the work done.but couldn't get my way out.
This is more of a cgi question really - I'd suggest a quick visit to
www.faqs.org and have a look at the CGI faq.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
ook in the pg_types table:
select oid,* from pg_types where typname like 'lin%';
Don't go deleting it without knowing what it is though. If your table is
called "links" I'd guess it's fine to delete it, but I'd take a backup first
anyway.
- Richard Huxton
just
on one of them.
I tend to apply indexes to fields that take part in a join then add them one
at a time to other fields as it becomes clear which takes part in important
queries. Don't forget that it takes a certain amount of effort to maintain
an index.
You've already found the EXPLAI
feature ? how can I work around this ?
It's a feature in versions before 7.1 (or possibly 7.0.x) you can still get
that behaviour in 7.1 by asking for strict null handling on a specific
function.
The only remedy AFAIK is to upgrade.
- Richard Huxton
---(end of br
From: "Picard, Cyril" <[EMAIL PROTECTED]>
> Thank you ! I planned to use the 7.1 to get the outer join capability.
>
> Is the 7.1 stable ?
Pretty much - it's reached release candidate stage and I've been using it
for development
ss and it is VERY SLOW.
If you need to import large quantities of data, look at the copy
command, that tends to be faster.
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
is basically just a select rule that rewrites
queries based on it.
Indexes on underlying tables should be used though. Difficult to suggest
what indices you might need without knowing the view/tables/queries
involved.
- Richard Huxton
---(end of broadcast)--
From: "Keith Gray" <[EMAIL PROTECTED]>
> Richard Huxton wrote:
> >
> > Indexes on underlying tables should be used though. Difficult to suggest
> > what indices you might need without knowing the view/tables/queries
> > involved.
>
> As an e
gt; clause.
In 7.1 at least you can do it if you alias the sub-query:
select max(d) from (select count(b) as d from c group by a) as calias;
Sorry, I don't know if 7.0.2 handles this (but you probably want to upgrade
anyway - I seem to remember some problems with 7.0.2)
HTH
posible manipulating groups? Or do I have to
> play with triggers.
Sounds like you want different groups for different users, and a view for each
group. You can then define rules for each view defining the access that is
available.
Is that what you're after?
-
y I have lost the link but I remeber that I have
You can get to it from http://techdocs.postgresql.org/ - one of Roberto
Mello's contributions IIRC
- Richard Huxton
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please sen
ello's plpgsql cookbook and the int8 page in my
postgresql notes - I summarised anything useful I saw about it. The
first example I give is probably fine for just generating unique id's.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you ch
From: "Wei Weng" <[EMAIL PROTECTED]>
> If it is not, is it possible to acquire a lock on a row ? how about a
> lock on a table?
All functions take place within a transaction, but since PG doesn't support
nested transactions yet you can't roll back the effects
uch difference either way unless the query was very fast to execute.
- Richard Huxton
---(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
From: "John Hasler" <[EMAIL PROTECTED]>
> Richard Huxton writes:
> > All functions take place within a transaction, but since PG doesn't
> > support nested transactions yet you can't roll back the effects of a
> > nested function.
>
> Do you
so:
CREATE VIEW get_emotions_view AS
SELECT emotion_id, emotion1, ...
ORDER BY date_epoch + full_rating*3600*12
LIMIT 300;
and then issue a query like:
SELECT * FROM get_emotions view
WHERE emotion_id
BETWEEN last_em_id()-3000 AND last_em_id();
If you set the "is_cachable" flag on the las
You can create a functional index as easily as a normal one:
CREATE FUNCTION calculated_score(integer, integer) RETURNS integer AS '
BEGIN
RETURN $1 + ($2 + 3600 + 12)
END;
' LANGUAGE 'plpgsql';
CREATE INDEX emot_calc_idx
ON emotions ( calculated_score(dat
when I attemped to
> restore the data (via \i filename), it failed selectively; some tables
> were restored but many were not. No errors were logged.
Try having a look at the order the tables get inserted esp. with regard to
any foreign keys etc. - I'm not sure pgdump is
dump can dump 7.0 databases (Philip)
Might be worth a quick upgrade & see what happens.
Failing that, it isn't something to do with permissions and pgdump
connecting as various users?
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
rom heights;
cm
-
150
160
(2 rows)
richardh=> select cm,count(id) from people, heights where height>=cm group
by cm;
cm | count
-+---
150 | 4
160 | 2
(2 rows)
HTH
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
otherwise #1.
I'd be very interested in any clever way of doing this without a temporary
table (or equivalent - if functions could return sets of values you could
use that, but it's basically the same thing).
- Richard Huxton
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
estdate limit 40 offset 10;
This calculates the results and then throws away the first nine and anything
after the 50th.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
date selector.
Out of curiosity Josh, why aren't you validating in PHP? - only takes a
couple of lines there.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
& DBI)
- typos corrected etc.
If you do download it, please take a minute to send some feedback, coz I
can't tell which pages are useful without it.
- Richard Huxton
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, pleas
sense so I stuck the example in my PostgreSQL notes.
- Richard Huxton
---(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
get this message:
>
> ERROR: UNIQUE constraint matching given keys for referenced table
> "sequences" not found
>
> The problem is that the referenced field and table exist.
> Any hint?
Do you have a unique index on sequences.seq_code?
- Richard Huxton
aren't enforcing uniqueness on (tnumber,mnumber) now might be a good
time to do so.
If the teamnum isn't always a fixed length search for the '-' with strpos()
richardh=> select strpos('abcdefg','e');
strpos
5
HTH
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
> raise exception ''Member '' || unitno || '' not found'';
RAISE EXCEPTION ''Member % not found'', unitno;
Don't know why the parser for RAISE doesn't like string concat. Possibly
because it maps to the elog() error
From: "Tom Lane" <[EMAIL PROTECTED]>
> "Richard Huxton" <[EMAIL PROTECTED]> writes:
> > Don't know why the parser for RAISE doesn't like string concat.
>
> Laziness ;-). Someone should fix plpgsql so that RAISE does take
> expre
From: "Richard Huxton" <[EMAIL PROTECTED]>
> False Laziness perhaps (spot my Perl background)
>
> OK - stick me down for having a look at it. Had a quick peek and I think
> it's within my abilities. Give me a couple of weeks, because I haven't
> looke
the current version, might be worth posting a bug report.
You can work around it with something like:
... substr(to_char(t.tnumber,'000'),2,3) ...
HTH
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
;id" or "seq" field of type SERIAL and get on with your project. The reason
people on the list are shouting is because we got burnt at some time with
exactly this thing and we're trying to stop that happening to you.
Oh - check the glossary at techdocs.postgresql.org and look at
From: "Roberto João Lopes Garcia" <[EMAIL PROTECTED]>
> Is there any way, possible an SQL or pgsql command, to get the server
version?
select version();
- Richard Huxton
---(end of broadcast)---
TIP 3: if posting/reading thr
María Elena Hernández wrote:
>
To get a list of columns, run "psql -E" and do a standard "\df foo"
where foo is your table name. This will show you exactly how PG does it.
I've found this very useful with all the backslash commands.
HTH
- Richard Huxton
--
only way I've ever known to do it. Note the one-hour offset
because I'm currently in BST rather than GMT timezone (ignore the few
seconds discrepancy - that's me querying then cutting and pasting).
Be interested to find out if there's a neater way. Can't believe there's
9.9') => ' 0012.0'
to_char(12,'FM9990999.9') => '0012'
I think the issue is you look at to_char() and make assumptions if you're
not familiar with it. I *seem* to remember someone saying Oracle worked this
way.
- Richard Huxton
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
ent will get a batch of numbers
to use (for efficiency reasons). Be aware that I'm not 100% certain on that
last sentence.
- Richard Huxton
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
<> NULL) AND (epilepsy_class IN ('g',
> It shouldn't be possible to insert a value into focus when epilepsy_class
> has one of the values 'g' or 'n'. But it is. Can anyone help?
Should that not be "IS NOT NULL"?
- Richard Huxton
-
both and check the documentation for where both diverge from
standards.
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
a date? It does strike me as a little
unexpected that a quoted string doesn't default to text.
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
nline
manual and the mailing archives (try searching on "aggregate" and "catenate"
or "concat").
HTH
- Richard Huxton
---(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
Of course, that's just shuffling the complexity around since you'll need a
view with the relevant rewrites and possibly some way of detecting scheduling
conflicts?
- Richard Huxton
---(end of broadcast)---
TIP 3: if posting/reading throug
rilliant. The usual advice is to
try to rewrite the IN as an EXISTS instead, but I'm not clear on how you'd do
that in this case.
Actually, looking at it, it might run a separate subquery for each row.
Ideally, there'd be some way of having a "PERGROUP LIMIT" impos
e (1027593096::bigint) with the name of your column.
HTH
- Richard Huxton
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
ir own "currval". Also
read up on nextval and sequences.
Sequences / serial type are more flexible than MySQL's AUTO_INCREMENT. You can
have multiple serials in a table, and share a sequence between several tables
if you want.
- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
pports postgresql serializes the queries by
> simply locking when a query manipulates a PGconn object and unlocking
> when it is done. (And similiarly, it creates a PGconn object on the
> stack for each concurrent queries.)
I assume you've ruled the application end of things ou
initial
value of each to 1,000,000 and 99,000,000 (or whatever) and then use
whichever sequence is appropriate.
In the example above you'd want something like:
id int not null default nextval('item_low_seq')
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
;,set_time;
delete from history where complete_time <= set_time;
return var_history_age_limit;
END;'
LANGUAGE 'plpgsql';
-- Richard Huxton
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Wednesday 14 Aug 2002 3:20 pm, Wei Weng wrote:
> On Wed, 2002-08-14 at 05:18, Richard Huxton wrote:
> > On Tuesday 13 Aug 2002 9:39 pm, Wei Weng wrote:
[30 connections is much slower than 1 connection 30 times]
> > What was the limiting factor during the test? Was the CP
ipt: pg_ins_test.pl ===
#!/usr/bin/perl -w
my $id = shift;
open CMD,"| psql -q";
my $key = "${id}AAA";
for (my $i=0; $i<100; $i++) {
print CMD "INSERT INTO foo (id,name) VALUES ('$key','Test name');\n";
$key+
ow AFAIK pg_dump's
been like that pretty much forever.
Note - if you only pg_dump the table, you won't get the sequence, you need to
dump the whole DB and grep away the bits you don't want.
- Richard Huxton
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
tname || ' ' || lastname)::varchar as expert ...
If it works, could you let the list know in case anyone else needs this in
future. If not, there is an ODBC list too (see postgresql.org website for
details)
- Richard Huxton
---(end of broadcast)--
ive. If you had a genuinely complex query, the time to analyse options
would be a benefit, but here I'm guessing it's not. Perhaps try it with
increasing amounts of data and more restrictions and see if performance stays
constant.
- Richard Huxton
---(end of
n the table to check for duplicates. For thousands of
records that can only be slower.
To put your mind at ease, I'd put together some test data and try it - nothing
like seeing some real evidence.
HTH
- Richard Huxton
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Thursday 12 Sep 2002 7:12 pm, david williams wrote:
> To anyone who can help me,
>
> I am new at Postgresql and am having some problems.
> I went a stage further attempt to put this query into a function as such
> CREATE FUNCTION getallusers() RETURN integer AS'
> DECLARE
> Liahona CURSOR
1 - 100 of 859 matches
Mail list logo
