Nested Loop (cost=0.00..147.11 rows=1 width=148) (actual
time=84.00..12323.00 rows=67 loops=1)
The planner estimate doesn't seem to match reality in that particular
step. Are you sure you've run:
ANALYZE oscar_node;
ANALYZE oscar_point;
And you could even run VACUUM FULL on them just to make
I have two very similar queries which I need to execute. They both have
exactly the same from / where conditions. When I execute the first, it takes
about 16 seconds. The second is executed almost immediately after, it takes
13 seconds. In short, I'd like to know why the query result isn't being
c
Hi guys,
If I have a table that is just a single column full of numbers, how can
I select all the rows from the table with a second column that is the
running tally so far down the result set?
eg:
Num Tally so far
0.3 0.3
1.2 1.5
2.0 3.5
...
Does this require PL/PgSQL coding?
> As on insert to table one I should get the same insert on table two.
> As on delete to table one I should get the same delete on table two.
> As on update to table one I should get the same update on table two.
>
> Can someone provide the examples i can study ?
Look up 'CREATE TRIGGER' and 'CR
Don't know. I've seen it elsewhere so it might even be "standard".
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic
> Sent: Friday, 13 September 2002 10:50 AM
> To: [EMAIL PROTECTED]
> Subject: [SQL] DISTINCT ON
>
>
> Hi,
>
> SELECT
> DISTINCT phone,
> company_name,
> address,
> state,
> idc_desc_1,
> street_number,
> street_name,
> street_xtra,
> suburb,
> postcode,
> area_code,
> ac_phone,
> fax_area_code,
> fax_phone,
> fax_ac_phone,
> latitude,
>
But in Postgres, you can have as many "autoincrement" columns in a table as
you want - how will you handle that?
And what's wrong with select currval()?
Chris
> I am wondering if I can get some input on the logic that I would need to
> code a function that would mimic mysql's last_insert_id()..
> if the primary key is table_id, with default value
> "nextval('table_seq') - then these two statements:
>
> insert into table (field1,field2,field3) values (value1,value2,value3)
> select currval('table_seq')
>
> work to get me the value I need. Except, of course if someone else
> has inserted a
> Is it true, that I can't define unsigned integers in a table definition?
>
> CU Roger
I'm not aware of any unsigned types in Postgres. You could probably define
your own one though.
Chris
---(end of broadcast)---
TIP 2: you can get off all lis
What if you try this:
SELECT date_part('epoch','2002-08-28'::TIMESTAMP WITH TIME ZONE)
It's probably 8 hours time different to GMT or somethign like that...
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Josh Berkus
> Sent: Wednesday, 28 A
> OK, no one has commented on this, so I guess I am going to have to guess
> the group's preference.
>
> My guess, seeing as very few probably use LIMIT and FOR UPDATE together,
> is to swap them and document it in the release notes. Was I correct in
> my guess?
I'm sure very few people do it -
Hi Scott,
I believe you can hack the catalogs: (disclaimer)
update pg_attribute set atttypmod=104 where attname='email' and
attrelid=(select oid from pg_class where relname='student');
Do this in a transaction and then \d the table to check that it has worked
before committing.
Chris
> -
Hi Jiaqing,
Basically - you can't. There is a program in the contrib/dblink directory
that can help you though.
Regards,
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Jiaqing
> Sent: Tuesday, 20 August 2002 5:53 AM
> To: [EMAIL PROTECTED
http://www3.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-control
-structures.html
Chris
- Original Message -
From: "Fritz Lehmann-Grube" <[EMAIL PROTECTED]>
To: "Christopher Kings-Lynne" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
> The problem is:
> A SQL-Function cannot be recursive because it cannot call itself, and it
can
> perform no loops.
> A PLPGSQL-Function cannot return sets.
It can perform loops. Check the manual- you can do FOR and WHILE loops.
7.3 will be able to return sets from PLPGSQL funcs it seems.
Chri
Hi Devrim,
You can use the EXPLAIN ANALYZE syntax to find out quite easily.
Chris
- Original Message -
From: "Devrim GUNDUZ" <[EMAIL PROTECTED]>
To: "PostgreSQL Mailing Lists-SQL" <[EMAIL PROTECTED]>
Sent: Monday, August 19, 2002 7:22 PM
Subject: [SQL] performance comparison: DISTINCT
> select not count(*) = 0 from my_table;
>
> Basically, for any integer i, convert to boolean with: not i = 0
Or i != 0 of course...
Chris
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "
If art_id is the primary key of the la table, is the latter faster?
SELECT DISTINCT la.* FROM ...
or
SELECT DISTINCT ON (la.art_id) la.* FROM ...
ie. Does this offer a performance improvement by only doing the distinct on
the primary key or not?
Thanks,
Chris
---(
Why not spend 5 minutes reading the documentation and trying to do it
yourself before asking us to do your job for you?
http://www.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql.html
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of
Well, I think it shouldn't disappear for a few releases yet...
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian
> Sent: Wednesday, 14 August 2002 12:43 PM
> To: Christopher Kings-Lynne
> Cc: Hackers; [E
Hi,
I've just done some performance comparisons between contrib/fulltextindex
and contrib/tsearch. Even with every optimisation I can think of for
fulltextindex, tsearch is 300 times faster ;)
Plus it doesn't require a separate table or complicated queries.
I think we should strongly encourage
> Hi ,
>
> Thanx a lot it worked .
>
> Is there any equivalent of dateadd function in postgres ?
I highly recommend you actually read the Postgres manual's entries on date
and time manipulation.
You can just add intervals to dates:
SELECT datefield + INTERVAL '1 month';
Chris
---
> On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote:
> > Is the int4 a UNIX epoch? ie. seconds since 1970?
> >
> > If so, then this will generally work:
> >
> > SELECT CAST(int4field AS abstime);
> >
> > or
> >
> >
Is the
int4 a UNIX epoch? ie. seconds since 1970?
If so,
then this will generally work:
SELECT
CAST(int4field AS abstime);
or
SELECT
int4field::abstime;
Chris
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Lonh SENGSent: Tuesday, 1
> Yeah. This is fixed in current sources, and I back-patched it into
> the REL7_2 branch, but current plans don't seem to include a 7.2.2
> release --- we'll be going straight to 7.3 beta instead.
Is it worth doing a 7.2.2 patch that will dump people's foreign keys as
ALTER TABLE/ADD FOREIGN KEY
That's what your crontab is for.
Chris
- Original Message -
From: "Wei Weng" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Saturday, August 03, 2002 5:40 AM
Subject: [SQL] What about this?
> Why can't postmaster run VACUUM ANALYZE automatically every once in a
> while? Since it is
> I'm running into a performance problem when considering the following
> scenario: I have a fairly large table (1mio rows) related to other smaller
> tables (between 100 and 1 rows) and would like to retrieve the joined
> data (through a view) in random order. In order to do so, the main tabl
Title: Re: [SQL] Please Help me
Michelle,
Have
you tried it with Postgres 7.1 yet, which removed such
limitations?
Chris
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
Behalf Of Michelle MurrainSent: Thursday, 1 August 2002 10:48
PMTo: Chad Thomp
I have
no experience with ColdFusion, but if you ask a question like whether MySQL or
Postgres is better on a Postgres mailing list - we're going to say
Postgres.
Postgres is in many, many ways vastly superior to MySQL. However,
if you are a newbie or are doing this on Win32 platform, you
Postgres is totally, absolutely free. It comes under the terms of
the BSD license which means you have the right to use and modify the source code
in any way you wish, so long as you acknowledge that the code originated in
Postgres. It is even more free than MySQL.
Chris
-Original
> but then to get the entirely new items out i use a sub query which takes
> for ever
>
> SELECT DISTINCT * FROM v_postcode_new WHERE postcode NOT IN ( SELECT
> postcode FROM v_postcode_old ) ORDER BY postcode ASC;
NOT IN is known to be very, very slow in Postgres. Use NOT EXISTS instead:
SELEC
You need to do something like this:
SELECT proj_id FROM t_proj WHERE oid=xxx;
To find value of primary key from oid.
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Atkins
> Sent: Tuesday, 30 July 2002 2:33 AM
> To: '[EMAIL PROTECTED]
Did you know that you can probably change your GROUP BY clause to use a
column ref, rather than repeating the CASE statement:
GROUP BY gmmid, gmmname, divid, divname, feddept, deptname, fedvend,
vendor_name, masterid, master_desc, pageid, oz_description, 13,
price_original, price_owned_retail,
> Can I config the Postgresql so that it can match case
> in-sensitive pattern
> automatically? i.e. I don't need to explicit convert the pattern to lower
> case like this: .WHERE lower(textfield) LIKE lower(pattern)
All you need to do is this:
..WHERE textfield ILIKE pattern
Chris
--
> This SELECT causes a sequention scan 'cause your index
> is not HASH type, but likely a BTREE one.
> BTREE index is to interval searches (station = 'SAMI4%')
> not precise searchs. (station = 'SAMI4').
In Postgres, the hash index is slow and inefficient (it's a bit better
in7.3), and I believe
Have you tried playing with the statistics gatherer?
>From the ANALYZE docs:
"The extent of analysis can be controlled by adjusting the per-column
statistics target with ALTER TABLE ALTER COLUMN SET STATISTICS (see ALTER
TABLE). The target value sets the maximum number of entries in the
most-com
> I inserted the data via 30 "COPY t2002_06 from stdin" (one per
> day) So it
> was grouped by station and then day for each insert. (My script dumped
> the data from each station for the day and then repeated for each station
> and then finally dumped the entire day into the DB. Are you saying
> Can somebody explain me a bit about:
>
> 1. As far as I know column names in Tables are not case
> sensitive. Correct ?
> So I know if I pickup ID is not a clever idea but for this
> example it is ok.
I think your examples have proved that column names are in fact very much
case sensitive. How
> > However the sequence created is named :
> > eselect_maincategory_maincat_id ( the '_seq' is lopped off )
>
> What version are you running? Anything recent will keep the '_seq'
> and lop elsewhere.
Not if he's manually creating a sequence name that's too long - it will just
truncate it me
Hi Rudi,
Unless you redefine it before compiling, postgres has a built-in limit of 31
characters for names. Increasing this has a performance penalty, but it
might happen for 7.3 due to some improvements in the performance area.
BTW, the best way to do a sequence primary key is lik ethis:
crea
Ignore previous half-completed email.
How do you get this to work in 7.2.1?
I'm creating a view of a table, but I'm trying to do something like
this:
create table t (
id integer not null,
amount numeric(7,2)
);
create view v as
select id as v_id,
'paid amount: ' || amount as v_comme
How do you get this to work?
I'm creating a view of a table, but I'm trying to do something like
this:
create table t (
id integer not null,
amount numeric(7,2)
);
create view v as
select id as v_id,
'paid amount: ' || amount as v_comment
from t
;
You get this:
The usual way to install a contrib is to run configure with whatever args
you want in the root postgres source dir. Then:
cd contrib/xml
gmake all
gmake install
Sort of thing.
Chris
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Rudi Starcevic
> > I tried all these. This is what I get
> > SQL> SELECT "interval"(cast(422 as varchar) || ' seconds');
> > SELECT "interval"(cast(422 as varchar) || ' seconds')
> > *
> > ERROR at line 1:
> > ORA-00906: missing left parenthesis
>
> > SQL> select '422 second
> Transactions (well, recently mysql allows them but using propietary
> extensions), foreign key relationships, subqueries, stored
> procedures/triggers. MySQL lacks all of these.
MySQL's InnoDB tables have foreign keys, but they are RESTRICT only - ie.
they're kinda useless.
> On the other side
> can anyone please help?
> i have a to drop a check contstraint from a column. eg
>
> tradein_clients=# \d t_a
>Table "t_a"
>Column | Type | Modifiers
> +-+---
> company_id | integer |
> exp| text|
> imp| text|
> Check
> http://www.ca.postgresql.org/sitess.html
> says that:
> The current version of PostgreSQL is 7.2.1.
> NOTE: An initdb will only be required if upgrading from pre 7.2
>
> So, if my current version is 7.2.0 and I want upgrade it to
> 7.2.1, what file should I download in order to get 'intidb'?
> i
> Forgot to mention that adding
> DROP TABLE v_idx ;
> before the END WORK will fix things. However, I was under the
> impression that
> temporary tables would go away after a transaction in which they
> were created
> was committed.
No - they go away at the end of a _connection_. However, ther
1. ANALYZE both tables. Go 'VACUUM ANALYZE;' to vacuum and analyze your
tables. Analyzing means to update the planner statistics for the tables,
which might make Postgres use your indices.
2. If you tables are very small (eg. only a few hundred rows) then using an
index is usually slower than j
Hi Ricardo,
I assume you're talking about foreign key constraints?
Dropping a constraint is a real pain in all versions of Postgres up to and
including 7.2.1.
You will need to manually drop the RI trigger on the child table and the two
triggers on the parent table.
Techdocs has some informatio
> select case when (current_date > available) then ((end_date -
> current_date) / 30)
> when (current_date < available) then ((end_date - available) /
> 30)
>end
>from listing
>where case > 4
>
> which yields:
> ERROR: parser: parse error at or near ">"
>
> Doe
Well, you can always just UNION them - I don't know if there's a better
way tho.
select * from ...
union [all]
select * from ...
union [all]
select * from ...;
Chris
On Sat, 29 Jun 2002, Kelly wrote:
>
> select * from pg_user where usesysid=(select grolist[1] from pg_group
> where groname='my
> - Oracle is content with data read from an index if that is all that's
>needed. Indeed, I only need bar and baz and those are in the index.
>The benefits of not having to seek the scattered rows from the data
>table saves 35000 back and forth head movements / seeks.
Postgres can't u
Hi Beth,
I wouldn't have thought there'd be any difference to you, the user.
If you used 0, then that is an integer and that will be the default. If you
used '0' then that is a character or string and it will automatically be
cast into an integer when used as a default.
Chris
- Original M
> Now, what I want is to select the b_date which has the minimum "id" and
> active=t.
>
> A normal query would be :
> SELECT b_date,min(id) FROM test WHERE active='t' GROUP BY id LIMIT 1
>
> However, I only want to select b_date. So, if I perform the following
> query, I get the following error:
> Next I tried this one:
>
> SELECT kvvnr, max(lf_sem.semester) AS akt_semester
> FROM lv, lf_sem
> WHERE lv.semester = akt_semester;
>
> Now I got: 'Attribute 'akt_semester' not found'
>
> Is there another way to get what I want?
What about:
SELECT kvvnr FROM lv WHERE semester = (SELEC
> Query :
>
> SELECT *,
> (SELECT rtrim(buildUmfang(zylinder.z_typ), ','::text) AS rtrim) AS umfang
> FROM zylinder;
>
> Thank's in advance for any help jr
Try this:
SELECT *,
(SELECT rtrim("buildUmfang"(zylinder.z_typ), ','::text) AS rtrim) AS umfang
FROM zylinder;
I think it's because yo
> Chris/ Josh-
>
> OK-response to Chris below. Synopsis heresimply by creating a foreign
> key will not create an index. On large tables I SHOULD put a non-unique
> index on the foreign key (right?)
For large tables, I guess you may as well. You can be more scientific about
it if you you u
> Hello PostgreSQl Users!
>
> PostSQL V 7.1.1:
You should upgrade to 7.1.3 at some point...
> I have defined a table and the necessary indices.
> But the index is not used in every SELECT. (Therefore, the selects are
> *very* slow, due to seq scan on
> 20 million entries, which is a test setup u
59 matches
Mail list logo
