your template1 database, you can have it created automatically in all
new databases you create, so it's one less thing to worry about.
HTH.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
h ILIKE to_ascii(...).
Also, not sure it's a good idea to use ILIKE simply to get
lower-case-matching. If the user string ends with '%', for instance, it
will match everything-starting-with, which is probably not what the user
meant. Better the check against lower().
There might b
R ... )
is bound to be _much_ faster!
And even better is
SELECT *
FROM ... contacts c1
WHERE NOT EXISTS (SELECT * FROM groups WHERE groupname='c' or
groupnum='d' or groupnume='e' ... AND groups.contactnum=c1.contactnum)
EXISTS is almost always faster in PG.
rting on this junk column, we can force the
totals at the bottom).
p.s. don't forget the "union __all__", otherwise you'll get rid of
duplicate entries in the your table.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Indep
#x27;,10,'2002-03-02');
select *,
( select sum(amt)
from checks c2
where c2.id<=c1.id as c2)
from checks c1;
will give you the full accounting. To get just March, put a
where-date-between clause in both the outer and inner queries.
This will run slowly
where c1.id_father=c2.id_father
and c2.child_age > c1.child_age);
swap the '>' to '<' for youngest.
- J.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
functions). In some cases, this
might be a better solution.
- J.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
a function
> that creates modification hisotry).
Josh --
Good example! I'll bet a lot of PG users may have never realized that you
can use the same sequence across several tables.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Manage
stencies: two "a"s
SELECT * FROM raw;
The issue is that there are no IDs over 10 that have another ID that is
exactly their value, so the first update to "dbl" does nothing.
The second time, w/o the ID>10 restriction, it finds 1(a), and double
that, 2(b), and adds 10; getting
SEQUENCES and SERIAL data
type in the documentation.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, pleas
s/pupq/pg_in_aggregates
It was written w/examples in DTML, Zope's scripting language, rather than
in Perl/DBI, but you should be able to easily follow it.
Essentially, what you want to end up with is something like this:
SELECT make_into_li ( make_into_text ( url, name ) );
where make_into_te
entation
> can be found?
COALESCE is the SQL standard name for this. You'll find details in the
documentation, in the Conditional Expressions section (4.10). Copy at:
http://candle.pha.pa.us/main/writings/pgsql/sgml/functions-conditional.html
--
Joel BURTON | [EMAIL PROTECTED] | joel
On Sat, 13 Oct 2001, Timothy J Hitchens wrote:
> It's been a while since I used postgresql but today I have converted one
> of my web apps but with one small problem. I goto do a group as
> designed and executed in mysql and I get told that this and this must be
> part of the aggreate etc I am p
\d table_name" to see fields in
a table and the SQL behind that.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 2: you can get off all lists at once with
ists
("md")? Using a random number when a real-world code could do only forces
your user to do that lookup themselves.
[apologies to the international readers: Maryland is a state in the USA,
and "MD" is the postal code abbreviation for it]
I think that you could make some basic rule
owner FROM pg_class WHERE relkind = 'r' and
> relowner != 26;
>
> Is user postgres always 26? Maybe you have to find that out first.
system tables all ~ '^pg', which is probably a better check than
user=postgresql.
hth,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Sun, 29 Apr 2001, LeoDeBeo wrote:
> can anybody explain me the syntax of Create Table documentation??
This doc is much improved in the more recent PG create table help.
Check out the online 7.1 Reference Manual, and there's a much nicer CREATE
TABLE grammar.
--
Joel Burton
SELECT id FROM tbl ORDER BY order_val(id);
And you could even index on order_val(id), so that it runs a bit faster.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
RULE snog AS ON UPDATE TO foo DO INSTEAD ( ...; ... );
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
:
* do it in your front end (Python/Perl/PHP/Pwhatever).
If you get better ideas, and they aren't cc'd to the list, please do so.
HTH,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcas
ate a front-end using linked tables. Any good Access book will
walk through the basics of linked tables.
PostgreSQL-specific stuff is in a FAQ at www.scw.org/pgaccess
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---
ined to do this sort of thing, it might even make sense
to argue that DROP TABLE hides the table (sets an attrib so that it
doesn't show, query planner doesn't see it, etc.); it should actually be
removed from disk when the database on VACUUM.
--
Joel Burton <[EM
o trigger mechanicism for the SELECT
> statement?
You could use a RULE instead -- you might be able to get what you want
this way.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
the sequence
after the importing so that the sequence starts w/the first new number
with SELECT SETVAL('b_id_seq', xxx), where xxx is the number for it to
begin new id numbers.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washin
e output, could you
pipe your query results through `cat -b`, which will add line numbers?
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 3: if posting/reading thr
On Wed, 11 Apr 2001, Najm Hashmi wrote:
> Joel Burton wrote:
>
> > On Wed, 11 Apr 2001, Najm Hashmi wrote:
> >
> > > From pgsql, I try to insert data in table using the \i command. Nothing
> > > takes place and after this command pgsql is hung... i
ng
> problem with.
> I have attached my file with message. Could someone help me out here what is
> the reason for this behaviour.
Works just fine for me (Pg7.1 RC3, Linux).
Can you do manual inserts into the table?
Can you insert just a few records using \i?
Can you vacuum the tab
t, fname text);
copy to *this* table, then copy from this table to the names table,
ignoring duplicates in the import:
SELECT distinct fname, lname into names from import;
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
name = 'relation name'. attnum > 0 is perhaps the only
odd part -- it has to do w/hiding certain system columns of tables that
ordinary users don't realize are there are don't care about.
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Supp
ating on its development? Or do you know
> someone who is?
Post it along w/any quick notes about the architecture (about 20 minutes
of your time covering the concepts would probably save me two hours). I'd
be happy to look over it to see if I can help.
--
Joel Burton <[EMAIL PROTECT
fic.
http://www.zope.org/Members/pupq/pg_in_aggregates
Hoping someone finds it useful.
Cheers,
--
Joel Burton <[EMAIL PROTECTED]>
Director of Information Systems, Support Center of Washington
---(end of broadcast)---
TIP 5: Have you checked o
On Thu, 11 Jan 2001, Markus Wagner wrote:
> first of all, I looked at the mailing list list at th pg web site and I
> did not find a list named "pgsql-interfaces".
Hmmm. I see it at
http://www.postgresql.org/users-lounge/index.html
Perhaps you looked elsewhere, or you saw an old mirror? (In
e from pg_hba.conf for him)
What error message does he get in Access?
Can he connect to the database from another Linux/Unix machine via
psql? (or, from his Windows machine using a PostgreSQL-compatible command
interface, like isql)?
There's a FAQ on PostgreSQL + Access at www.scw.or
7; is a ten-character, not eleven
character long string. Try substr(datefoo,1,10) and it works for me
(under 7.1devel).
However, this all seems sloppy. Why not extract the date, and
compare it as a date?
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
datestamp,
batch_number, instructions FROM widgets ORDER BY batch_number,
datestamp desc;
(sort by batch then by date (last first) and show the first (aka
'distinct') row, considering only the batch_number for distinctness)
HTH.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
could get the record that way.
If you don't mean the last insert period, but rather the last insert
just to this table, you could add a TIMESTAMP column DEFAULT
CURRENT_TIMESTAMP and just select the record w/the latest
timestamp.
HTH,
--
Joel Burton, Director of Information Systems -*- [EM
Perhaps
SELECT * FROM tbl WHERE chromat::TEXT ~~ ('%' || sample || '%'
)::TEXT;
?
Also, upgrading isn't difficult in most cases; you can pg_dumpall and
upgrade and restore your files. 7.0 has many nice features over the
6.x series.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
On 29 Nov 2000, at 19:42, Tom Lane wrote:
> "Joel Burton" <[EMAIL PROTECTED]> writes:
> > create rule dev_ins as on update to dev_col_comments where
> > old.description isnull do instead insert into pg_description (
> > objoid, description) values (old.att
functions that might have been changed.) Or, perhaps you
have constraints on your table that call a function that you've re-
created.
If you have a backup of your database (ie pg_dumpall), you can
grep this file for 24011. This should be the original oid of the
function that is lost.
Go
e docs are long but fuzzy on rules
(they seem to suggest, for instance, that "create rule foo on
update to table.column" will work, when this is not implemented yet,
so perhaps the docs are ahead of the implementation?)
Any help would be great!
I do read the pgsql lists, but always a
E 'plpgsql';
SELECT F(null,'Hello');
returns null.
Is this intentional? Is there a way around this?
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
seem to correspond to anything.)
Is there a way to do this? Any help would be very appreciated.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
42 matches
Mail list logo
