I understand that it is possible for tables from different databases that have
the exact same data
but different Encoding can sort rows in differet orders.
Could this allow affect the order that triggers and rules are fired?
Regards,
Richard Broersma Jr.
---(end of
ndexes
on FKs.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
se the where syntax in your query since it does not and
cannot reference a NEW
or OLD tuple.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
ips using UNIQUE natural keys.
Would a design like this be practical?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
T-CAVEATS
Overcoming this limitation is on the todo list, but as far as I know it isn't
slated to be fixed
in the upcomming 8.3. Perhaps 8.4 will include this feature.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don&#
ment.
Regards,
Richard Broersma Jr.
---(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 to the mailing list cleanly
user defined column type).
http://www.postgresql.org/docs/8.2/interactive/ddl-constraints.html
http://www.postgresql.org/docs/8.2/interactive/sql-createdomain.html
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
the safe utilization
of update triggers?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
--- Osvaldo Rosario Kussama <[EMAIL PROTECTED]> wrote:
> Try:
> SELECT your_field ~ '.*[[:digit:]]{2}$';
This could be simplified a little. :o)
WHERE your_field ~ '\\d{2}$';
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will i
tement, you could insert into two or more tables.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
programmatic way to control how triggers are
> fired if there
> is more than one trigger for a particular type (before, after) the insert,
> update, delete
> commands.
The triggers fire in alphbetical order.
Regards,
Richard Broersma Jr.
---(end of broadcas
--- Bob Pawley <[EMAIL PROTECTED]> wrote:
> I have developed a PostgreSQL database c/w a Delphi interface with which to
> input data.
> If so, are there any tools that may assist me in developing this graphic
> interface?
This link seemed enteresting to me.
http://www.netbeans.org/download/f
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> Hello,
>
> For those who were too square to be there,
For us squares, are there any pictures avaliable of this event?
Regards,
Richard Broersma Jr.
---(end of broadcast)-
.vim file?
TIA,
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
#x27;t...it simply says "PostgreSQL database dump" which is only
> helpful to a point. :-)
If you need to, you can append your own timestamp to the dump file if you need
it.
I rolled this functionality into a .bat file.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
esTable( col1, col2 )
WHERE col2 < 5
GROUP BY col1
ORDER BY col1;
col1 |avg
--+
1 | 1.5000
2 | 3.5000
(2 rows)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP
ne of the links that
describe the method
of implementing a materialized view.
http://www.jonathangardner.net/PostgreSQL/materialized_views/matviews.html
other useful docs like this one can be found here:
http://www.postgresql.org/docs/techdocs.2
Regards,
Richard Broersma Jr.
--
your query this way:
SELECT *
FROM your_table
WHERE item_nbr > [: last item on previous page :]
ORDER BY item_nbr
LIMIT 15;
This method was discuss on the list a couple of months ago.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
command prompt. The one you want is "\timing". However, notice
that "\timing" and
explain analyze do not exactly agree on the results they produce.
IIRC, "\time" also counts its own overhead.
Regards,
Richard Broersma Jr.
---(end of bro
uld turn fsync back on.
My guess is that a join and update on 6 million records in just going to take a
while. Hopefully
this isn't an operation that you will need to preform regularly.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
tributes
\d+ [table_name]
to see additional table attributes
Regards,
Richard Broersma Jr.
---(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
UNKNOWN;
-- return all people who might meet this criteria if their null field where
known.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
ability for the users to "Refresh"
the exported data
directly from excel using the build in functionality of excel.
http://pgfoundry.org/projects/psqlodbc/
http://www.sls.psi.ch/controls/help/tutorials/Excel_ODBC/index.html
Regards,
Richard Broersma Jr.
---
(cost=0.00..3.41
> Time: 2.990 ms
Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the
logged select
statement times?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
er_id ~ '[a-z]'
IIRC, for any index like this to work, doesn't the REGEXP need to be anchored
to either the start
or end of the string?
Regards,
Richard Broersma Jr.
---(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
gresql.org/docs/8.2/static/sql-notify.html
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
NULL;
> > $$
>
>
> SELECT movie_id, movie_name FROM get_movies ();
> => returns a SETOF of (int4 NULL, text NULL)
I don't know if this will work, but here is another idea:
SELECT movie_id, movie_name
FROM get_movies() AS ( int4 NOT NULL, text NOT NULL );
Re
t's not even valid syntax...
It isn't valid SQL spec syntax but it is a Postgresql-ism for functions return
types:
http://www.postgresql.org/docs/8.2/interactive/sql-select.html
notice this "from-type" listing for functions.
Regards,
Richard Broersma Jr.
--
x.html#SQL-CREATEINDEX-STORAGE-PARAMETERS
I found this:
"B-trees use a default fillfactor of 90, but any value from 10 to 100 can be
selected."
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the pl
at plbat
doesn't exist :-).
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
oblem with the
performance of disk
writing though-put from UPDATEs and INSERTs?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
.4 or
8.2.5. Does any know if it was?
Regards,
Richard Broersma Jr.
---(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
or. Larger
indexes require more
time to be read from disk to memory and will require more time to sequentially
scan to find to
find the cross-reference table page location of records of interest. So the
net effect is that
larger indexes will make SELECT statement slower.
This is my under
> Not quite. Once a page has reached it's fill factor percentage full,
> no more inserts will happen on that page, only updates. Also, I
> think you have large/small backwards wrt fill factor. If you have a
> fill factor of, say, 40% then once a page has reached 40% full no
> more inser
);
Primary key will create an implied index. Fill factor is applied to that
implied index.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--- Erik Jones <[EMAIL PROTECTED]> wrote:
> Also, note that once we have HOT...
I am not sure what the acronym "HOT" stands for. Does it have something to do
with MVCC?
Regards,
Richard Broersma Jr.
---(end of broadcast)-
I thought I would give this question a second try.
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> A while back it was pointed out the that the Windows version of 8.2.3 had a
> bug that prevented
> auto-vacuum from working correctly.
>
> http://archives.postgresql.o
and I will check to see if I notice a difference.
Ofcourse, maybe auto-vacuum does work but I have a configuration error some
where.
Thanks for the consideration!
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 1: if posting/readi
But to start off with, here are the settings that I currently have in my
postgresql.conf for
auto-vacuum. I will post-back with any results whether I see auto-vacuum
working in 8.2.5 or not.
Thanks!
Regards,
Richard
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> I will dis-able the hourly manual vacuum/analyze script that I implemented as
> a work-around to
> this problem to see if auto-vacuum is ever triggered.
it appears to be working fine in 8.2.5:
proj02u20411=> begin
is type of operation is the
Hierarchical Nested
Set data model. Inserting/updating/deleting nodes and branches into the table
requires updating
the primary key of a lot of records.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In ver
--- John Smith <[EMAIL PROTECTED]> wrote:
> what does this mean?
> {postgres=arwdRxt/postgres,username=r/postgres}
This link describes each of the letters:
http://www.tldp.org/LDP/intro-linux/html/sect_03_04.html
Regards,
Richard Broersma Jr.
---(end
st. Doing this is impossible.
For this to work, you should be using an INSERT query to _ADD_ records to foo
that do not yet
exist.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ign
e seem email I've
posted has produce broken lines. I am not sure if this was already mentioned,
does anyone know of
non-html windows email clients that work well for this mailing list?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TI
id
AND R.synset2id = S.synsetid
INNER JOIN synset AS T
ON S1.synsetid = T.synsetid
WHERE W.lemma = 'scramble'
AND R.linked = 1
AND R.pos='v'
ORDER BY lemma;
Regards,
Richard Broersma Jr.
---(end of broadcast)--
OOPs!
--- Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> --- "John D. Burger" <[EMAIL PROTECTED]> wrote:
> > My question is, should the planner have figured this out, and we're
> > just losing out because we're stuck in 7.4? Or is there some s
Y ( SELECT gender
FROM Children AS C1
WHERE C1.parentid = P.parentid );
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subs
oy'. Under the
> obvious assumptions about gender, the result is of course empty -
> except it's not clear to me what should happen for childless people ...
Thanks everyone that makes sense!
Regards,
Richard Broersma Jr.
---(end of broadcast)---
sible for any LEFT or RIGHT to have to same
> > value.
> a check constraint ought to do it
True, but how do I insure that one record's left does not equal another
record's right?
Regards,
Richard Broersma Jr.
---(end of broadcast)-
Is it possible to constraint both the LEFT and RIGHT fields of a record to use
the same index? I am looking for a way to ensure for all LEFTs and RIGHTs in a
table, that is it is impossible for any LEFT or RIGHT to have to same value.
---(end of broadcast)---
rks with
adjacency list model?
If the nested set model is chosen, would having a table and index fill factor
of 50% be a good idea in this case if periodic updates were expected?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 3: H
--- On Sun, 10/21/07, Adrian Klaver <[EMAIL PROTECTED]> wrote:
> > I have a column with data structured as follows.
> >
> > 32TT - 0002
> > 32LT- 0004
> > 32PT-0005
> >
> > Is there a way of selecting all of the rows containing
> LT in that column??
> >
> > I have attempted variations of ' *LT* ' w
ata stored in the database. :-)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
--- On Mon, 10/29/07, Ow Mun Heng <[EMAIL PROTECTED]> wrote:
> (Raid1 = No Fault tolerance since 3 drives)
Raid1 with three drives will have fault tolerance. You will have three disks
with the same image. This is triple redundancy. This could greatly improve
select performance.
Having said th
ectory supposed to be marked read only?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--- On Thu, 11/15/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> My desktop (acting as a db-server for my discipline's
> group) has it power cycled this morning at 4:10
>...
> C:\Program
> Files\PostgreSQL\8.2\bin>2007-11-15 06:36:13
> PANIC: could
ver configuration, you might also try posting
to the PostgreSQL-ODBC mailing list.
Also, why did you choose the ANSI driver over the Unicode driver?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 1: if posting/reading through Usene
an the US. It seems
sized like a medium to large and it fits me like a glove. But there is a plus
side to its sizing, I never need to iron out the wrinkles to give it that nice
pressed look. :o)
Regards,
Richard Broersma Jr.
---(end of broadcast)--
be used.
I personally wouldn't even mind having a PG polo that has 3rd part vendor logos
on the sleeves if that would help make PG polo shirts available.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ts( part_nbr, part_type)
ON DELETE CASCADE
ON UPDATE CASCADE);
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
ll odbc linked tables (to any
flavor of RDBMS) will incorrectly treat nulls and false. All attemps to update
a record using MS-access and ODBC with a null boolean will result in failure.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 1
plicated VIN numbers or if the VIN's
they are given do not pass the the VIN check-sum (if such a think exists).
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
sub
sent to you, how many columns should I try to create
in a table in order to reproduce the problem you where having with needing OIDs
created in your tables?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner wi
--- On Wed, 11/28/07, Richard Huxton <[EMAIL PROTECTED]> wrote:
> Name lookups. Something is trying to look up a name,
> failing and it's
> timing out after 60 seconds.
It seems the OP's connection string was set to localhost. Would this still
indicate a Name Loopup problem?
Regards,
Richard
After a type is created, is it possible to view the definition of this type?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--- On Thu, 11/29/07, Usama Dar <[EMAIL PROTECTED]> wrote:
> See if they help you
> http://www.postgresql.org/docs/8.3/static/functions-enum.html
I will give this a try, thanks!
Regards,
Richard Broersma Jr.
---(end of broadcast)---
--- On Mon, 12/10/07, Ted Byers <[EMAIL PROTECTED]> wrote:
> but how do you
> do it using SQL in an RDBMS?
I believe that there is an ANSI SQL command "MERGE" that is yet to be
implemented into PostgreSQL.
Regards,
Richard Broersma Jr.
--
re-written:
INSERT INTO SP
SELECT a, b
FROM VALUES ( 'cesp', 'sp' ) AS tmp( a, b )
LEFT JOIN Sp
ON (Sp.col1,Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;
Regards,
Richard Broersma Jr.
---(end of broadcast)
Sp.col2)=(tmp.a,tmp.b)
WHERE (Sp.col1,Sp.col2) IS NULL;
I forgot the parentheses that the FROM clause requires when using the VALUES
predicate. Also remember that this only works in PostgreSQL versions >= 8.2.
Regards,
Richard Broersma Jr.
---(end of broadcast)
Is it possible to create aggregate functions using pl/pgsql?
If not possible in plpgsql, is there any other way to create these types of
functions?
If anyone could point to the correct documentation I would be most appreciative.
Regards,
Richard Broersma Jr.
---(end of
ind the
hypotenuse after having summing of multiple 2 coordinate vectors(the EEs use
the term phasers) for each MCC cubical.
It seems they have need for quite a few other little aggregate functions that
they would like me to make if I can get this one done first.
Anyway thank for the push in the
--- On Tue, 12/11/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> Also see the overview at
> http://www.postgresql.org/docs/8.3/static/xaggr.html
Thanks Tom!
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
Are there any planes in the works for a booth and talks for PostgreSQL?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED
banks to help offset the effective of reactive power inherent in the
inductive load of motors.
This is a perfect problem for a custom aggregate.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
with the adjacency list model.
Regards,
Richard Broersma Jr.
---(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 to the mailing list cleanly
--- On Thu, 12/13/07, Jorge Godoy <[EMAIL PROTECTED]> wrote:
> Actually pF is measured from -1 to 1.
>
> they tell you that they want doing some pF correction on
> the facility and you
> need to have that counted as well.
Thanks for the correct, and good point. :-)
Rega
u20411=> select cast( 1 as boolean),
proj02u20411=> cast( 0 as boolean),
proj02u20411=> cast( -1 as boolean ),
proj02u20411=> cast( 2 as boolean);
bool | bool | bool | bool
--+--+--+------
t | f| t| t
Regards,
Richard Broersma Jr.
---(en
I noticed from the 8.3 manual (CREATE TABLE section):
NOT DEFERRABLE
... Only foreign key constraints currently accept this clause. All other
constraint types are not deferrable.
Does this imply that custom CONSTRAINT TRIGGERs are not DEFERRABLE? Or are they?
Regards,
Richard Broersma Jr
What is the proper way for the function of a constraint trigger to signal where
or not referential integrity was compromised?
Should it return some sort of value? Should it raise an exception?
Regards,
Richard Broersma Jr.
---(end of broadcast
--- On Fri, 12/21/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote:
> Does this imply that custom CONSTRAINT TRIGGERs are not
> DEFERRABLE? Or are they?
Sorry dumb question. They must be deferrable since their create statement
allows for differable.
Regard,s
Richard
How does a Constraint Trigger react to a referenced table when the constraint
is created implementing the FROM clause?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
--- On Sat, 12/22/07, Michael Glaesemann <[EMAIL PROTECTED]> wrote:
> > Should it return some sort of value? Should it raise
> an exception?
>
> The latter.
Thanks Michael!
Regards,
Richard Broersma Jr.
---(end of broadcast)--
the ON table or FROM table to raise the
raise the trigger.
So is the purpose of knowing the reference_table OID, is that it helps to
generalize the logic of the CONSTRAINT TRIGGERS?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: exp
n triggers on the two tables?
Regards,
Richard Broersma Jr.
---(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 throug
--- On Sat, 12/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> There's always the source code:
> src/backend/utils/adt/ri_triggers.c
Thanks. I will do my best and give it a try :-)
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
table" predicated of the CREATE Constraint Trigger enable this
kind of notification to perform cross checking even though there are strictly
no pk/fk relations between these table?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
traints that have ON UPDATE or ON DELETE actions
set.
I think I understand now. Thanks for the clarification.
Regards,
Richard Broersma JR.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
-+--
history | managers| table | teaminst <--where is project.managers
project | project_tls | table | teaminst
project | projects| table | teaminst
(3 rows)
instrumentation=>
Regards,
Richard Broersma Jr.
---(end of broadcast)--
ut any particular schema specification will
> only show tables
> that could be accessed by an unqualified table name.
I see. Thanks for the clarification.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
to alter the column on your table to
use your new type.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
, my Postgresql directory was altered to be READ-ONLY. Once
this happened, postgresql wan't able to start.
After spending a quited a bit of time to dicover the cause of the problem, I
simply had to reset the directoy to be write-able.
I hope this helps.
Regards,
Richard Broersma Jr.
using a select statement.
This way the contents of the type can be seen in a base table. and it would be
easy to recreate the type when new elements are added or removed. I'll have to
play around with this in the future to see what is possible.
Regards,
Richard Broersma Jr.
--
ys use tar to spit the
file up to make the transition a bit more manageable.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
, my Postgresql directory was altered to be READ-ONLY. Once
this happened, postgresql wan't able to start.
After spending a quited a bit of time to dicover the cause of the problem, I
simply had to reset the directoy to be write-able.
I hope this helps.
Regards,
Richard Broersma Jr.
to alter the column on your table to
use your new type.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 6: explain analyze is your friend
these posts are prefixed with [TLM].
I've noticed that one of the emails that I posted a couple of days ago was
reciently reposted with the prefix [TLM].
I wonder if someone is bouncing emails from this list.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
input syntax for type date: "infinity"
instrumentation=> SELECT 'infinity'::timestamptz;
timestamptz
-
infinity
(1 row)
instrumentation=> SELECT 'infinity'::timestamptz::date;
date
--
<-- this blank I determine was a NULL.
(1 row)
Is
e non-committed records. I.E.
the record that should be equal to NEW is in fact equal to OLD.
Is this correct? Is there a way to adjust the visibility so that yet-to-be
committed tuples are seen by the trigger function?
Regards,
Richard Broersma Jr.
---(end of broa
Sees NEW.
I was my understanding that all single DML statement are wrapped in their own
transaction so I thought that these two statements should preform the same.
Regards,
Richard Broersma Jr.--
-- PostgreSQL database dump
--
-- Started on 2008-01-02 05:35:55
SET client_encoding =
t; case that's the UPDATE, in the second it's the COMMIT.
Thanks for the help Tom. This information is good to know.
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
1 - 100 of 299 matches
Mail list logo
