thing is wrong with my postgres configuration.
Really appreciate any help!
Thanks!
Ken
---(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
what hashjoin or sort-and-mergejoin are but I will look
into them.
Thanks!
Ken
Tom Lane wrote:
Ken <[EMAIL PROTECTED]> writes:
I have postgres 8.1 on a linux box: 2.6Ghz P4, 1.5GB ram, 320GB hard
drive. I'm performing an update between two large tables and so far
it's been ru
ny clauses here...
if you've got a lot of clauses, you're probably not
normalized
as much as you should be.)
If you have indexes on the relevant fields, you shouldn't get a table
scan and this should return rather quickly, right?
-Ken
&q
ft would be multiplying
by 2.
I don't know off the top of my head about AND and OR. Doesn't the
manual cover this?
-Ken
In trying to use a plpgsql stored proc, I'm getting an error I don't
understand.
When the select at the bottom of this email is executed, I'm getting the
message:
ERROR: parser: parse error at or near "$1"
Any ideas?
--
Ken Corey, CTOAtomic Interactive, Lt
Wow! Answering emails on a Sunday? Someone should be giving you an award or
something.
On Sunday 04 February 2001 8:13 pm, you wrote:
> Ken Corey <[EMAIL PROTECTED]> writes:
> > When the select at the bottom of this email is executed, I'm getting the
> > message:
Hi All!
Are the BEGIN/END; seen in a typical PL/PGSQL function a transaction wrapper,
or do I need to add another BEGIN/END block?
Should I just put a 'rollback' in the function, or do I need to do something
special?
Thanks!
-Ken
My apologies to the lists...in trying to stop my own spam, I spammed you all.
This has now been fixed.
Again, sorry for any inconvenience.
-Ken
?
Thanks
Ken
?
Thanks
Ken
ome up with but it doesn't seem
to work is this for an older version or am I just overlooking
something simple?
thanks
Ken
DECLARE emp_cursor CURSOR FOR
SELECT Salary, Title, Start, Stop
FROM Employee;
OPEN emp_cursor;
loop
example of a cursor
http://www.postgresql.org/docs/aw_pgsql_book/node142.html
it does not explain before hand
1) the format of an anoymous block
2) how to loop a cursor
3) how to reference columns froma cursor row (ie rec.column_name)
thanks
Ken
Tom Lane wrote:
> Ian Lance Taylor <[EMAIL PRO
Hello,
another brain twister, at least for me...
i have a table of varchar and one of the values I want
to insert into another table, one of the columns is
defined as INTEGER in destination table, column...
and none of these statements seem to work
INSERT INTO pledge_classes (semester, year)
S
st type 'varchar' to 'int4'
Ken Kline wrote:
> Hello,
> another brain twister, at least for me...
> i have a table of varchar and one of the values I want
> to insert into another table, one of the columns is
> defined as INTEGER in destination table
here you go, thanks in advance, ken
Table "temp"
Attribute |Type | Modifier
---+-+--
pseason | varchar(15) |
pyear | varchar(5) |
adx=# \d pledge+ _classe4s s
Table "pledge_classes"
rder
)
and customer_id is not null;
should work
Ken
Frank Joerdens wrote:
> When doing a subselect with NOT IN, as in
>
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
>
> (from Bruce Momjian's book)
>
>
my apologies if this is not the coreect list
but I cannot seem to install the
package DBD-Pg-0.73-1.i386.rpm
it complains that it needs libpq.so.1
i have the following installed from
a source package rebuild:
postgresql-7.0.3-2
..server
..devel
..perl
..tk
..odbc
..tcl
thanks as always
Ken
;s the best approach here?
--
Ken Corey, CTOAtomic Interactive, Ltd. [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
from that, but
that sounds like much busywork for the database.
It sounds easiest to me to just punt and number the rows as they are returned
in my calling application...
What's the best approach here?
--
Ken Corey, CTOAtomic Interactive, Ltd. [EMAIL PROTECTED]
---
7;s 3 digits or less.
Caveat: this will only work as long as you have less than 40 million users.
;^)
-Ken
create FUNCTION new_request_id()
RETURNS INT4
as
'
DECLARE
timeportion INT4;
serialportion INT4;
BEGIN
timeportion := cast
(date_part('
VALUES (tempvar,$1, $2);
-- Everything has passed, return id as pk
RETURN tempvar;
END;
' LANGUAGE 'plpgsql';
WARNING: this is not guaranteed to be the correct syntax, I didn't
create the tables and the function to test it
turns 17.
That means that unless the results of process B depend in some way upon
the results of process A, there's no problem.
-Ken
--
Ken Corey CTO http://www.atomic-interactive.com 07720 440 731
---(end of broadcast)
items are:
select * from pg_class order by relpages desc;
If your tables are active with lots of inserts/deletes, the biggest
things will likely be indexes.
The only way that I know to recover this space is to drop the indexes
and recreate them. Vacuum didn't touch them for me.
-Ken
On Sat, 2
7;
anddeleted_p = 'f'
kenzoid-# kenzoid-# kenzoid-# kenzoid-# ;
ERROR: parser: parse error at or near "max"
I kinda figured that.
So I'm stuck, without making two calls. If I call to the db and get
max(posted_date), and then turn around an
On Sat, Nov 02, 2002 at 09:17:14AM -0500, Tom Lane wrote:
> Ken Kennedy <[EMAIL PROTECTED]> writes:
> > [ date_part('epoch') is wrong for a timestamp value ]
>
> The epoch value is really only correct for a TIMESTAMP WITH TIME ZONE
> value. If you apply date_pa
On Thu, Nov 07, 2002 at 05:12:20PM +0800, Prime Ho wrote:
> Hi,
>
> Could you tell me how to get view field's source table and field name?
> another word, how could I know the view field come from?
>
\d view_name should give you the view definition in pqsl.
--
Ke
Well, for the docs to list every possible conditional-statement for an
order by clause would just about include them all, so be all the more
confusing. Sub queries, IN, aggregate functions, aliases.. the list goes
on and on. I'd say that knowledge (that most conditionals can be used in
an order
So it should be both calendar and P.I.T. And you
wouldn't need the TZ storage if the date-number and number-> translation
itself takes the TZ arg so that it can localize the Human String for you.
Ken
---(end of broadcast)---
TIP 7: don&#x
itself takes the TZ arg so that it can localize the Human String for you.
Ken
In fact, I would suggest that if there is any function, or field, that
takes a TZ-less argument (*especially* if it takes only the number),
that its name should be made to contain 'UTC' so clearly disam
led function that could modify different columns for each trigger
that called it, where each trigger specified the target column by a calling
argument (or by any other viable mechanism).
~ TIA
~ Ken
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
uld
modify different columns for each trigger that called it, where each trigger
specified the target column by a calling argument (or by any other viable
mechanism).
~ TIA
~ Ken
ed rows get assigned effective_date_and_time
= ' 2005-12-14 11:00:16.749616-06 ', which in addition to
being wrong leads to uniqueness constraint violations.
~ TIA
~ Ken
Thanks, Tom (also Keith Worthington and Bricklen Anderson). That works.
~ Ken
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 14, 2005 1:15 PM
> To: Ken Winter
> Cc: PostgreSQL pg-sql list
> Subject: Re: [SQL] Defaultin
the rule is structured so that the current row's "effective_date_and_time"
gets updated to 'now' *before* the new row is inserted, making its value
different from the old "effective_date_and_time". So the uniqueness
conflict shouldn't occur.
I figure either the
ns was as rules.
~ Ken
> -Original Message-
> From: Richard Huxton [mailto:[EMAIL PROTECTED]
> Sent: Monday, December 19, 2005 4:08 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>
> Ken Winter wrote:
> >
that record stays in place.
(The other change, adding the lines
AND effective_date_and_time <= CURRENT_TIMESTAMP
AND expiration_date_and_time >= CURRENT_TIMESTAMP;
to the UPDATE, was necessary to keep updates to the "my_data_now" from
updating the expired rows as well.)
Tha
and their attributes? I
can’t seem to find any.
~ TIA
~ Ken
Thanks, George. What you say fits with what I was finding. I think that's
the way I will go.
~ Ken
> -Original Message-
> From: George Pavlov [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 27, 2005 12:11 PM
> To: pgsql-sql@postgresql.org
> Cc: [EMAIL PROTECTED
array
local to a PL/pgSQL function. I tried the following guess, but it only
won me a “syntax error at or near VARCHAR:
DECLARE
my_array VARCHAR [];
…
~ TIA
~ Ken
Bricklen ~
That works. (Odd that the initialization seems to be necessary to make it
work.) Thanks! Yes, I'm using version 7.4.
~ Ken
> -Original Message-
> From: Bricklen Anderson [mailto:[EMAIL PROTECTED]
> Sent: Thursday, December 29, 2005 12:53 PM
> To
;t attach
a trigger to a view.
I considered doing it with a trigger function on the person_i table, but I
don't know how that could be made to cause an insert of the person_h table
record - and the assignment of h table values such as "name" from the app's
query.
Suggestions?
1, table2
WHERE (table1.key100 != table2.key100);
But the query is very slow and I finally just cancel it. Any help is very much appreciated.
-Ken
On Wed, 2006-02-08 at 16:27 -0500, Frank Bax wrote:
At 04:10 PM 2/8/06, Ken Hill wrote:
>I need some help with a bit of SQL. I have two tables. I want to find
>records in one table that don't match records in another table based on a
>common column in the two tables. Both
Is there a performance trade-off between column indexes and record inserts?
I know that in MS Access there is such a trade-off. This being indexes make SQL queries perform faster at the cost of record insert speed. Put another way, the more column indexes in a table, the slower a record insert
This has been something I've been trying do so that I can do some column comparisons as part of "data-cleaning" work. I'll let you know if this helps me accomplish my task!
On Wed, 2006-02-08 at 15:20 -0800, Bricklen Anderson wrote:
[EMAIL PROTECTED] wrote:
> Greetings,
>
> the following is
On Wed, 2006-02-08 at 21:04 -0500, george young wrote:
[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
I'm designing a completely new schema for my database. A major
criterion is that it facilitate ad-hoc queries via MS-access, excel and
OpenOffice, presumably with O
On Fri, 2006-02-10 at 07:38 +0100, A. Kretschmer wrote:
am 09.02.2006, um 22:18:09 -0800 mailte superboy143 (sent by Nabble.com) folgendes:
>
> Hello,
>
> How can I write an sql query in postgresql so that I can insert a date into
> a table in the format DD-MM-, and when I select the d
On Fri, 2006-02-10 at 00:11 -0600, Bruno Wolff III wrote:
On Tue, Feb 07, 2006 at 01:45:50 -0800,
"superboy143 (sent by Nabble.com)" <[EMAIL PROTECTED]> wrote:
>
> I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202
I have the following perl script that reads a fixed-width file and replaces values in various sections of the file.
---
open (IN, '< in.txt');
open (OUT, '> out_test.txt');
while () {
chomp;
$first_section = substr $_, 0, 381;
Oops. I posted this to the wrong support list. Sorry.
-Ken
On Fri, 2006-02-10 at 09:52 -0800, Ken Hill wrote:
I have the following perl script that reads a fixed-width file and replaces values in various sections of the file.
---
open
I'm experiencing a very slow query. The table contains 611,564 rows of data. I vaccumed the table:
VACUUM ANALYZE ncccr10;
SELECT count(*) FROM ncccr10;
count
611564
(1 row)
When I try to analyze the query plan with:
EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
las
On Mon, 2006-02-13 at 22:17 -0700, Michael Fuhr wrote:
[Please copy the mailing list on replies.]
On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > How many rows does the condition match?
>
> csalgorithm
On Wed, 2006-02-08 at 22:31 +0100, Markus Schaber wrote:
Hi, Ken,
Ken Hill schrieb:
> I need some help with a bit of SQL. I have two tables. I want to find
> records in one table that don't match records in another table based on
> a common column in the two tables. Both
On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote:
> Here is my query SQL:
>
> SELECT key100 FROM ncccr10
> WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);
>
> It is is running after 30 minutes. Here is the query plan:
>
I would try an outer join:
select a.key100
from nccc
On Tue, 2006-02-14 at 16:07 -0500, Tom Lane wrote:
Ken Hill <[EMAIL PROTECTED]> writes:
> Seq Scan on ncccr10 (cost=0.00..20417160510.08 rows=305782 width=104)
>Filter: (NOT (subplan))
>SubPlan
> -> Seq Scan on ncccr9 (cost=0.00..65533.71 rows=494471
On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote:
Hello pgsql-sql,
Is anybody know how create field in a new table with data type accuiring from a field in other table?
For example:
create table new_table
(
name other_table.name%TYPE
);
Have you tried inheritance from o
On Tue, 2006-02-14 at 23:03 +0100, Maciej Piekielniak wrote:
Hello Ken,
Tuesday, February 14, 2006, 10:30:34 PM, you wrote:
KH> On Tue, 2006-02-14 at 22:12 +0100, Maciej Piekielniak wrote:
>> Hello pgsql-sql,
>>
>> Is anybody know how create field in a new table wit
On Tue, 2006-02-14 at 13:24 -0800, Ken Hill wrote:
On Tue, 2006-02-14 at 13:08 -0800, chester c young wrote:
> Here is my query SQL:
>
> SELECT key100 FROM ncccr10
> WHERE ncccr10.key100 NOT IN (SELECT key100 FROM ncccr9);
>
> It is is running after 30 minutes.
On Tue, 2006-02-14 at 15:05 -0800, Bryce Nesbitt wrote:
Ken Hill wrote:
>> also (hate to be obvious) have you analyzed lately?
>>
I'd say that's fair game, not obvious. Vacuum/Analyze is ar so aparent
to a person moving
to Postgres from other DB's.
---
is topic. I'm fairly confident that I can tweak the postgresql.org file; given that I was successfuly tweaking my xorg.conf file.
Any help/guidance is very much appreciated.
-Ken
On Wed, 2006-02-15 at 11:20 -0500, Andrew Sullivan wrote:
On Wed, Feb 15, 2006 at 08:15:46AM -0800, Ken Hill wrote:
> It has been suggested to me to increase my work_mem to make queries
> preform faster. I believe I do this in the 'postgresql.org' file. I
> seem to have
On Wed, 2006-02-15 at 18:09 +0100, Peter Eisentraut wrote:
Ken Hill wrote:
> Can someone point me in a
> direction as to where I can learn how to modify the postgresql.org
> file to increase work_mem?
RTFM
I apologize for my lack of knowledge, but what is "RTFM"?
bungsu,
That does make the query work a bit faster. Thanks!
On Mon, 2006-02-27 at 09:42 +0700, Bungsuputra Linan wrote:
Hi Ken,
I used to have the same problem. In my computer, using date_part in WHERE
clause will always slow down the system when the table has plenty of rows.
My
. The Postgres documentation also didn't provide much
> help ( the examples in C). I was mainly looking for example showing
> how to refer 'OLD' and 'NEW' rows using PL/pgSQL.
>
> Can someone please direct me to some such examples?
>
&
a new default behavior (or just config option added), my
vote would, without a doubt, be for case-insens (yet case preserving)
mode... even when using quoting identifiers. This case sen. behavior
doesn't seem to offer any advantage/safety.
ken
---(end of broadcast)
Martijn van Oosterhout wrote:
On Sat, Dec 02, 2006 at 12:41:37AM -0700, Ken Johanson wrote:
1: It seems like this behavior of case sensitive-or-not-identifiers
could/should be a config option -- either globally for the server,
database, or at the connection/session level. Other databases *do
Dennis Bjorklund wrote:
Ken Johanson skrev:
Has your experience with PG been different? If so I presume you have
have found a config that allows?:
SELECT
pers.firstName,
pers.lastname,
As long as you don't create the columns using quotes you can use that
kind of names. For ex
haracters are. A rowid is also a rowId
(or ROWID). Who really intentionally mixes them? (only 3-4GL
*programmers* who consider all-caps to represent constants in my
experience).
thoughts,
Ken
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Martijn van Oosterhout wrote:
On Sat, Dec 02, 2006 at 11:08:51AM -0700, Ken Johanson wrote:
And my vote is to not have such an option. But I'm not the one who
decide so don't worry about what I think :-) I would like to have an
option to upper case the identifiers instead of lower c
Tom Lane wrote:
Ken Johanson <[EMAIL PROTECTED]> writes:
-*If* the option to turn on case-insenetive behavior were selectable at
the DB or session level, the existing apps could continue to use the
case sensitve mode and be completely unaffected.
Ken, you clearly fail to understa
t.
So that is the reason there was an idea proposed per database or per
connection SQL conformance controls (like what Mysql has). They'd allow
other apps to elect "less standard" modes (not just this one), for the
sake of compatibility (beit old PG modules or and other DB). You cod
ad Oracle and others work as PG does now, but
my point in discussing this, is that the first two DBs have enough
market, that offering a compatibility mode to ease the burden of porting
apps would have substantial value (I know this from experience)
Ken
---(end of broad
eciated. I hope I am naively missing some obvious
alternative strategy, since this sort of operation must be common in databases.
Thanks,
Ken
--
Ken Simpson, CEO
MailChannels Corporation
Reliable Email Delivery (tm)
http://www.mailchannels.com
---(end of
oing a "self join"
and that sped things up enormously (query took on the order of 30
seconds to compare two million-row table slices, resulting in a 20K
row result). I will also try re-ordering the unique constraint to get
speedier indexing out of it and will look at table partitionin
'pros' side I think it eases migration to PG, shortens code, is
similar syntax to shorthand table aliases, and some users might argue it
has become defacto syntax among DBs.
Regards,
Ken
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Paul Lambert wrote:
Ken Johanson wrote:
I notice PG doesn't allow shorthand column labels -- it requires the
'AS' operand.
SELECT col1 foo, ...; -> ERROR: syntax error at or near "foo"
Briefly discussed a couple of weeks ago.
See http://archives.postgres
I'd like to see a list o t he various approaches, and a poll as to which
are best and why, for naming table and columns and constraints. We've
all seen several variations, but the most common (and pg used) seems to be:
columns:
primary key: _pk OR _id OR _rowid
foreign key: _fk OR _join
indexe
Here's one Mysql developer's response to adding (fixing) the
integer/bigint/tinyint types to their CAST function:
http://bugs.mysql.com/bug.php?id=34562
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropria
78 matches
Mail list logo
