On Thu, 12 Sep 2002, Andrew Perrin wrote:
> Greetings-
>
> I have a table of participants to be contacted for a study. Some are in
> the "exposure" group, others in the "control" group. This is designated by
> a column, typenr, that contains 1 for exposure, 2 for control.
>
> The complication is
On Wed, 18 Sep 2002, Aaron Held wrote:
> I am running into a serious performance issue with some basic queries.
>
> If I run something like
>
>select * from "Calls" WHERE
> ( ("CallType" = 'LONG DIST' ))
>
> The search takes about 15 seconds
>
> if I run
> select * from "C
On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote:
> I am trying to improve a query to use existing indexes but facing diffculty.
>
>
> Looks like 'between' amd '>=' are not using indexes althoug > and < does.
> all my application code uses between and i am sure it use to work fine
> at one point
On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote:
> Thanks very much for the response.
> set enable_seqscan=off; Definitely helps.
> and for wide date ranges it usees indexes.
>
>
> But with default value of enable_sequence changing date range seems to have effect.
> can you explain me a bit mor
On Thu, 19 Sep 2002, [iso-8859-1] Thorbjörn Eriksson wrote:
> Hello,
>
> I've encountered a strange behavior in postgres 7.2.1 regarding how psql
> handles strings ending with space characters.
>
What locale did you initdb with? Some locales on some systems have
behavior like that. To test, y
On Fri, 20 Sep 2002, [iso-8859-1] Ricardo Javier Aranibar León wrote:
> Hi List,
> First, Thanks for your colaboration Richard Huxton "Do a search on aggregate
> functions and "concat" in the mailing list archives,
> also see the Postgresql Cookbook on techdocs.postgresql.org, I think there
> mig
On Fri, 20 Sep 2002, Tomas Lehuta wrote:
> Hello!
>
> i'm using PostgreSQL 7.2.1 and got strange parse errors..
> could somebody tell me what's wrong with this timestamp query example?
>
> PostgreSQL said: ERROR: parser: parse error at or near "date"
> Your query:
>
> select timestamp(date '1998-
On Tue, 24 Sep 2002, Madhavi wrote:
> How do I pass an array as an aruments in a postgres function? And how do I
> use it in the function??
Well, I've only got a 7.3devel machine to test, but:
create function f(int[]) returns int as
'select $1[1];' language 'sql';
select f('{3,4}');
seems to
On Thu, 26 Sep 2002 [EMAIL PROTECTED] wrote:
> I'm having a problem with postgres on HPUX. My version is:
>
> VERSION = 'PostgreSQL 7.2.2 on hppa2.0w-hp-hpux11.11,
> compiled by aCC -Ae'
>
> I'm trying to do a query and it consistently gives the
> followi
On Fri, 27 Sep 2002, Ian Barwick wrote:
> On Friday 27 September 2002 05:19, Tom Lane wrote:
> > Ian Barwick <[EMAIL PROTECTED]> writes:
> > > Anyone know what the ANSI standard is? I don`t recall any other
> > > database apart from MySQL which default to case-insensitive
> > > CHAR or VARCHAR co
On Fri, 27 Sep 2002, Richard Huxton wrote:
> On Wednesday 25 Sep 2002 2:10 am, Kevin Houle wrote:
> > I have the same issue with a table that currently holds well
> > over 600,000 rows. The case you left out is this:
> >
> > INSERT INTO test (c1,c2) VALUES('a','c');
> > INSERT INTO test (c1,
On Fri, 27 Sep 2002, GB Clark wrote:
> CREATE TABLE sysusers (
> user_id INTEGER DEFAULT nextval('sysusers_user_id_seq')
> UNIQUE NOT NULL PRIMARY KEY,
> username text NOT NULL UNIQUE,
> password
On Sat, 5 Oct 2002 [EMAIL PROTECTED] wrote:
> Because I want to obtain a monthly breakdown, I created a view called
> monthcustomer as this select:
>
> select orders.ord_date, customer.cname,
> date_part('month',orders.ord_date) AS "month",
> date_part('year',orders.ord_date) A
On Sun, 6 Oct 2002 [EMAIL PROTECTED] wrote:
> Hi guys. Thanks for the rapid replies so far.
>
> To answer some of the questions:
>
> >you did not indicate an explicit join - or even a "from" clause for that
> >matter- in the example of your create view statement.
>
> My original post was a simpl
On Mon, 7 Oct 2002, Laurette Cisneros wrote:
>
> I have two tables:
>
> create table table1
> ( vers integer,
> table1_id text NOT NULL,
> desc text,
> PRIMARY KEY (rev, table1)id)
> );
>
> create table tab
On Tue, 8 Oct 2002, Mathieu Arnold wrote:
> I found in an old pgsql dump something like :
>
> UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" ~* 'TABLE NAME';
>
> inserts
>
> BEGIN TRANSACTION;
> CREATE TEMP TABLE "tr" ("tmp_relname" name, "tmp_reltriggers" smallint);
> INSERT INTO "
On Wed, 9 Oct 2002, John Pauley wrote:
> pgsql-sql,
>
> We are porting a database from IBM DB2 to PostgreSQL.
> In several related scripts, there is a SELECT
> statement that never completes in Postgres but
> completes in a few seconds using DB2, for example:
>
> Table row count:
> SELECT count(*
On Wed, 9 Oct 2002, Jose Antonio Leo wrote:
> I have a problem with the index of 1 table.
>
> I hava a table created :
> CREATE TABLE "acucliart" (
>"cod_pto" numeric(8,0) NOT NULL,
>"cod_cli" varchar(9) NOT NULL,
>"mes" numeric(2,0) NOT NULL,
>"ano" numeric(4,0) NOT NULL,
>
On Sat, 12 Oct 2002, Bhuvan A wrote:
> I am using 7.2.
>
> I have problem in deleting records from a particular table in my database.
> Say, if the DELETE command results deleting 10 records, the command is
> blocked at the end and is not returning. FYI, that table donot have any
> triggers/cons
On Wed, 16 Oct 2002, Albrecht Berger wrote:
> Hello,
> I have a statement like this :
>
> INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, SELECT MAX(pos)+1 FROM tab2);
Coalesce is your friend. :)
Coalesce((select max(pos)+1 from tab2), 0)
should work.
---(end of broadcast)
On 16 Oct 2002, Charles Hauser wrote:
> In fits and starts I am working through converting a sybase schema ->
> postgres and am hoping to gain some insight on the use of reference
> tables ('look-up-table') and FK constraints.
>
> In the example below I believe the sybase RULE Phone_type_rule is
On Wed, 23 Oct 2002, Tomasz Myrta wrote:
> Hi
> I want to perform query looking like this:
>
> select
> user_id,
> a/sum_a as percent_a,
> b/sum_b as percent_b
> from
> users join
> (select
> group_id,
> sum(a) as sum_a,
> sum(b) as sum_b
>from users group by group_id) X
On Wed, 23 Oct 2002, Tomasz Myrta wrote:
> Uz.ytkownik Stephan Szabo napisa?:
> > Does using X.group_id=3 in the where clause work better?
> It works better, but not if you want to create a view and make
> "select * from some_view where group_id=3" :-(
But you can'
On Wed, 23 Oct 2002, Tomasz Myrta wrote:
> Uz.ytkownik Stephan Szabo napisa?:
> > But you can't do that anyway, because you don't expose group_id
> > in the original query. I assume user_id was a mistake then and was
> > meant to be group_id or that both were mea
On Wed, 23 Oct 2002, Stephan Szabo wrote:
> Did you see the other two queries I gave? On 7.3, both of those queries
> appear (according to explain output) to do the limiting of group_id
> inside the subquery rather than doing the subquery with all rows.
> The explanation above was w
On Wed, 23 Oct 2002, Tomasz Myrta wrote:
> > On Wed, 23 Oct 2002, Tomasz Myrta wrote:
> >
> > > Uz.ytkownik Stephan Szabo napisa?:
> > Without group_id in the select list you couldn't do a where
> > group_id = if the select was a view.
> I know - it was
On Wed, 23 Oct 2002, andres javier garcia garcia wrote:
> Hello; I've got pluviometric data in a bizarre format (spanish
> administration is rather original) and I need to "transpose" them, to
> be able to use them as time series data for a model.
> As you may see, the date of a rain datum is def
> Strangely, this is the result we were expecting from our
> original query!
>
> Is it possible that the sub-selects are somehow
> affecting the result sets?
>
>
> Seen on:
> % postmaster --version
> postmaster (PostgreSQL) 7.1.3
>
> and
>
> % postmaster --version
> postmaster (PostgreSQL) 7.1.2
On Sat, 12 Oct 2002, George wrote:
> beckerbalab2=> select * from ffix_ability;
>
> ability_name | ability_description |
> type| cost
> beckerbalab2=> SELECT ffix_ability.name, ffix_ability.cost
^^ ffix_ability.ability_name, right? Same below.
>
> beckerbalab2-
On 17 Oct 2002, Charles Hauser wrote:
> Josh & Stephan,
>
> This helps a lot - thanks!
>
> Any idea what datatype 'Identifier_type IDENTITY' specifies? I don't
> see this in the postgres documentation. I'll see what I can find in
> sybase lingo.
As a rough guess, I'd say probably a serial colu
On Tue, 22 Oct 2002, Nicholas Barthelemy wrote:
> I have just installed redhat 8.0. It comes with postgresql rpms for
> 7.2.2. I have been trying to get an
> application I have written to work, but my queries fail whenever I have
> queries that use internal
> date/time formatting functions.
> exa
On Tue, 22 Oct 2002, Dan Langille wrote:
> Can deferrable etc be used when deleting primary key records (master
> table), then reinserting them without losing foreign key records
> (slave table)? I ask because in our testing we can't; we lose the
> foreign key records in the slave table. I'm guess
On Tue, 22 Oct 2002, Peter Galbavy wrote:
> OK, I am now confused; postgresql 7.3beta2 on OpenBSD:
>
>
> photos=# select * from metadata WHERE name = 'Make' and value = 'Canon'
> limit 10;
>
> *bang*, 10 values, sub second response.
>
> photos=# select * from metadata m, images i WHERE m.name = '
On Fri, 18 Oct 2002, Brian Blaha wrote:
> I have a function that operates on two tables A and B, such that B has a
> foreign key on A, as follows:
> INSERT INTO A (...) several times
> INSERT INTO B (...) several times, with foreign keys pointing to the new
> members of A
> DELETE FROM A (...), p
On Fri, 11 Oct 2002, Jeffrey Green wrote:
> > Hello. I was wondering if anybody's run across the problem of
> > creating tables with foreign key constraints out of order. What I
> > mean by this is that say I want a table called that has a
> > foreign key reference to a table . If I define pic
On Fri, 18 Oct 2002, Frank Morton wrote:
> For the SQL gurus, a query where I'm not getting the expected
> results. Trying to write it using sql compatible with both postgres
> and mysql.
>
> There are two tables:
>
> table = profile
> int id
> char name
>
> table = attribute
> int id
> int contai
On Fri, 25 Oct 2002, Reiner Dassing wrote:
> I was trying to adopt a database application to PostgreSQL.
> (It is written for MySQL and Oracle using perl)
>
> During this process I recognized the phenomena that upper case letters
> of table names and column names are not preserved
> in PostgreSQL.
On Mon, 28 Oct 2002, Josh Berkus wrote:
> Odd question: I have a query that returns a set of none to several rows. One
> column in these rows is BOOLEAN. I want the query to return:
> 1) TRUE if *any* of the BOOLEAN values is TRUE;
> 2) FALSE if *all* of the BOOLEAN values are FALSE;
> 3) FALSE
On Mon, 28 Oct 2002, Josh Berkus wrote:
> Stephan,
>
> > Well, you might be better off making a max(bool), but a not
> > super-efficient version might be:
> > max(case when col then 1 else 0 end)=1
>
> Good, I'm not just brain-dead. I did figure out another way to do it, but if
> I wrote a MAX(
On 29 Oct 2002, Philip Van Hoof wrote:
> Note that I have searched A LOT on google about this subject and I do
> know that this probably is a FAQ. But I also have not yet found any
> answer that we can actually use :). We have no problem with using beta
> versions. There is one requirement with b
On Fri, 1 Nov 2002, [iso-8859-1] Maurício Sessue Otta wrote:
> I do daily vacuums on the production server
You haven't said what version you're running, but if you're
using 7.2 and non-FULL vacuums you also will want to make sure
that you have the free space map settings in postgresql.conf
are la
On Mon, 4 Nov 2002, Ben Kassel wrote:
> When I try to create a new row in this table and do not explicitly
> define a unique value for datadefindex I get the following error
> message:
> More information : If I DROP the database, recreate it, and enter
> values into the table manually, the auto
> Hello,
>
> I have two 4 table with referential constraint's that are hanging when I
> try to delete from them.
>
> I have a,
> users table, ( 3 rows )
> suburbs table ( 16000 rows ),
> regions table ( 54 rows )and
> a bus_pc_idc table ( business type ) ( 3 rows )
>
> Here is my integr
On Wed, 6 Nov 2002 [EMAIL PROTECTED] wrote:
> However, for the total deficiencies I am then splitting up the total into
> aging groups, eg <30, 30-60, 60-90, and >90 days old. The query for that
> looks like the below. But before I paste it in, I would like to optimize
> it, if I could do so wit
On Wed, 6 Nov 2002 [EMAIL PROTECTED] wrote:
> Actually, come to think of it, just the implementation of re-querying a
> temporary table could alone significantly improve performance, because the
> temp table would:
> a) have fewer records to scan on the subselects
> b) not require any joins
Yeah,
On Wed, 6 Nov 2002, Achilleus Mantzios wrote:
>
> Hi i think a hit a major problem on 7.2.1.
> I run 3 systems with postgresql 7.2.1.
> Its a redhat 7.1 for development, a redhat 7.3 for production
> and a FreeBSD 4.6.1RC2 for testing.
>
> After long runs (with periodic (daily) vacuum analyze's)
>
On Thu, 7 Nov 2002, Ludwig Lim wrote:
> Hi:
>
> Has anyone encountered this before?
> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
>
>
> returns the following error message:
> Cannot cast type '"char"' to '"numeric"'
It seems to me that it's trying to decide on a type
for the expression NU
On Thu, 7 Nov 2002, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Thu, 7 Nov 2002, Ludwig Lim wrote:
> >> SELECT CAST ( (NULL*NULL) AS NUMERIC(2,0));
> >> Cannot cast type '"char"' to '"numeric"'
>
&g
On Thu, 7 Nov 2002, Zhidian Du wrote:
> Dear All,
>
> I want to dreate a delete cascade in children tables. The primary key of
> parent table is oid, so when I create parent table, there is no apparetly
> key word "primary key". The problem jumps out.
>
> When I create child table using columnn
On Fri, 8 Nov 2002, Ludwig Lim wrote:
> Hi:
>
>Can I have more than 1 trigger of same kind on one
> table? (i.e. 2 AFTER INSERT TRIGGER) on 1 table?
Yes.
>Does having more than 1 trigger of the same kind
> produces some side effect? I mean is the order of the
> trigger firing is always t
On Fri, 8 Nov 2002, Ludwig Lim wrote:
> Hi:
>
> I tried the following:
> CREATE TABLE x(
>a NUMERIC(5,0),
>b VARCHAR(5)
> );
>
> CREATE TABLE y(
>a INTEGER,
>b VARCHAR(5)
> );
>
> INSERT INTO x(b) VALUES ('LUDZ');
> INSERT INTO y(b) VALUES ('TEST')
On Tue, 12 Nov 2002, [iso-8859-1] Cédric Dufour (Cogito Ergo Soft) wrote:
> Hello,
>
> I am trying to figure out how to handle tuple deletion efficiently when ON
> DELETE triggers and referential integrity are involved. The scenario is
> about this one:
>
> I have a MASTER and a SLAVE table, the l
On Fri, 15 Nov 2002, Huub wrote:
> Hi,
>
> When I copied a function call into an SQL-statement and executed it, I
> got the message 'Aggregate function calls may not be nested'. Does that
> mean this is the case in standard SQL or is this specifically PostgreSQL?
Umm, both sort of. It's an entr
On Mon, 18 Nov 2002, Ellen Cyran wrote:
> Is there any way to make this query faster? I have indexes on year,
> msa_code, and sic. I've also tried it with
> an index on the combined group by columns. I've made both sort_mem and
> shared_buffers bigger, but still this query
> takes 40 seconds wh
On Tue, 19 Nov 2002, Chris Gamache wrote:
> > If you want help, you must provide details. The PG version number is
> > relevant also.
>
> Understood. PostgreSQL 7.2.3.
7.3 will be better for this. There were questions about the safety
of pushing clauses down in queries with union and intersect
On Tue, 26 Nov 2002, Richard Huxton wrote:
> On Tuesday 26 Nov 2002 9:43 am, patrick wrote:
> > Greetings,
> >
> > I'm not sure what the correct behavior is here but the observed
> > behavior seems "wrong" (or at least undesirable).
> >
> > I have a few tables and a view on one of the tables selec
On 20 Nov 2002, [EMAIL PROTECTED] wrote:
> i created index already. how can i decrease the query time more.
>
> number of record is over 1 million.
> the following is the query plan.
>
> Group (cost=34082.59..34085.62 rows=61 width=112)
> -> Sort (cost=34082.59..34082.59 rows=607 width=112)
> -> N
On Wed, 20 Nov 2002, Justin Georgeson wrote:
> I'm pretty new to databases in general, and would like to find a spiffy
> way to do something. I want to use two columns from one table to
> populate three columns in two other tables. Assuming t1 is the table I
> want to take the values from, here is
On Fri, 22 Nov 2002, Nathan Young wrote:
> Hi all.
>
> I have a table with members and a table with payments. Each payment is
> related to a member by memberID and each payment has (among other things) a
> year paid.
>
> I can create a join to find a list of members who have paid for a given yea
On Tue, 26 Nov 2002 [EMAIL PROTECTED] wrote:
> > On Tue, 26 Nov 2002, Richard Huxton wrote:
> >
> >> On Tuesday 26 Nov 2002 9:43 am, patrick wrote:
> >> > Greetings,
> >> >
> >> > I'm not sure what the correct behavior is here but the observed
> >> > behavior seems "wrong" (or at least undesirable
On Tue, 3 Dec 2002, Tomasz Myrta wrote:
> Hi
> I use Postgresql 7.2.2.
> Sometimes I have to remove a column from my tables (psql):
> 1. alter table master_table rename to x;
> 2. \i tables.sql
> 3. insert into master_table select f1,f2,... from x
> 4. drop table x
>
> I think, foreign keys refer
On Tue, 3 Dec 2002, Tomasz Myrta wrote:
> > You'll probably end up using ALTER TABLE ADD CONSTRAINT to add the
> > constraints to the new master_table. It's possible that you might
> > be able to hack something with the system tables, but that sounds
> > dangerous. Or you can upgrade to 7.3 whi
On Fri, 6 Dec 2002, FatTony wrote:
> I'm not a db guru by any means so please forgive me if this has an easy
> solution.
>
> Scenario:
>
> Want to sort by an alias for SELECT AGE(). Problem is the timestamps for
> the SELECT AGE will be determined by the value of another column, thus
> the use of
On Mon, 2 Dec 2002, Raymond Chui wrote:
> I created a column, dada type timstamp with time zone
> and with default CURRENT_TIMSTAMP
> it shows me the default is
>
> default ('now'::text)::timstamp(6) with time zone
>
> Then when I insert a row, the default timestamp value is
>
> -mm-dd HH:MM:s
On Wed, 11 Dec 2002, [iso-8859-1] [EMAIL PROTECTED] wrote:
> 'RROR: copy: line 1, Bad float8 input format '-0.123
Looks like you have end of line issues (given the fact that the
second quote is at the beginning of the line. You
probably have carriage returns at the end of lines.
If you get rid
On Thu, 12 Dec 2002, Tim Perdue wrote:
> I'm trying to comb through my database and add ON DELETE CASCADE to a
> number of tables where I already have fkeys in place, but I'm having a
> hard time.
>
> ALTER TABLE project_task DROP CONSTRAINT
> "project_task_group_project_id_f" RESTRICT;
>
> ERROR
On Sat, 28 Dec 2002, Gary Stainburn wrote:
> When I create the database, I use the COPY command. The ranks and jobtypes
> are populated okay but the abilities table is empty. However, if I then
> INSERT the data the inserts work fine.
Do *all* of the inserts work? If any one of the rows fails
On Fri, 27 Dec 2002, Evgen Potemkin wrote:
> Good time of day!
>
> I have two tables
> news(newsid int4, newscltid int4 references clt(cltid) match full,newstext text)
> and
> clt(cltid int4, somedata text).
> after clt is renamed , for ex. to clt_old, newscltid start to reference to
>clt_ol
On Fri, 3 Jan 2003, Pedro Igor wrote:
> Regards .
>
> I hope someone can help me in this query.
> Have a field in a table that needs to check if another table has the value that is
>being inserted.
> Ex:
> table A
> - id int constraint pkey_id primary key,
>
>
> table B
>
On Sat, 4 Jan 2003, Evgen Potemkin wrote:
> > > I have two tables
> > > news(newsid int4, newscltid int4 references clt(cltid) match full,newstext
>text)
> > > and
> > > clt(cltid int4, somedata text).
> > > after clt is renamed , for ex. to clt_old, newscltid start to reference to
>clt_old.
On Mon, 6 Jan 2003, Gary Stainburn wrote:
> create view turn_details as
> select t.*, d.sid as dsid, d.sname as dname,
> f.sid as fsid, f.sname as fname,
> (select sum(r.rmiles) as rmiles from rides r where r.rtid = tid)
> as rmiles
> from turns t
>
On Tue, 7 Jan 2003, Achilleus Mantzios wrote:
> i am just in the stage of having migrated my test system to 7.3.1
> and i am experiencing some performance problems.
>
> i have a table "noon"
> Table "public.noon"
> Column | Type | Modifiers
On Tue, 7 Jan 2003, Thomas O'Connell wrote:
> is this expected behavior? if so, then why?
I'd guess so if the timestamp value's integer part is
10 digits long since I believe trying to fit that into
a numeric(15,6) wouldn't work (9 digits . 6 digits).
> db=# create table foo( col timestamp );
>
On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:
> Since you can overload and define new operators, the parser must - at some
> point in time - lookup the operator definition.
> It seems to me (but this is just an ideea), that the rules should go like
> this :
> ...
> check >$ is a defined operator
On Thu, 9 Jan 2003, Tom Lane wrote:
> Achilleus Mantzios <[EMAIL PROTECTED]> writes:
> > On Thu, 9 Jan 2003, Radu-Adrian Popescu wrote:
> >> Why is that ? Because the >$ does not exist, not in the default operator
> >> list
>
> > i think the parser is built with yacc, (not "from scratch code") so
On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mäkelä wrote:
> I found that Postgres isn't behaving like I thought when using a unique index in
> combination with NULL-values...
> Is this a bug or specified in the SQL-standard? If its a bug, is it fixed in a
> recent version? We are using 7.2.3
AFAIK th
On Mon, 13 Jan 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Mon, 13 Jan 2003, [iso-8859-1] Jimmy Mäkelä wrote:
> > On my dev (7.4devel) box I see it using the composite index three times,
> > but you haven't given explain output for t
On Wed, 8 Jan 2003, David Durst wrote:
> I want to create a function that will return
> a row or rows of a table is this possible?
It is in 7.3.
> If so can someone replay with a complete example?
You can find some examples in:
http://techdocs.postgresql.org/guides/SetReturningFunctions
On Mon, 13 Jan 2003, Josh Berkus wrote:
> Richard,
>
> > I'm confused. How do I get the integer 10102 to come in as the date
> > 2002-01-01?
>
> Hmmm ... isn't this an old post, repeating?
Yep, my guess is that he sent it, wasn't on the list so it went for
approval, he joined and resent, and we
On Wed, 15 Jan 2003, Joseph Shraibman wrote:
> select distinct n.thekey, n.val, t.txt FROM num_tab n LEFT JOIN txt_tab t ON
>n.thekey =
> t.thekey
> WHERE n.thekey < 5 AND t.class = class_tab.tkey AND n.class = class_tab.class;
> produces:
Note that the above uses the non-standard postgres b
On Wed, 15 Jan 2003, Matthew Nuzum wrote:
> Well, this is somewhat of a follow up to my previous post regarding self
> joins. Now what I'm hoping to do is "virtually" combine the results
> from two different record sets into one apparent record set.
Fortunately we have the set functions, specif
On Thu, 16 Jan 2003, Matthew Nuzum wrote:
> I have a number of tables in my database that use the concept of
> display order, which is a field that can be used in an order by clause
> to dictate what order the results should come out in.
>
> I thought I would be crafty and devise a function th
On Wed, 22 Jan 2003, Ries van Twisk wrote:
> Dear PostgreSQL users,
>
> I have a view and a table,
>
> I understand that when a frontend accesses a VIEW that PostgreSQL cannot use
> a index on that view.
> For example when I do this: SELECT * FROM full_cablelist WHERE
> projectocode=5; Correct?
On Wed, 22 Jan 2003, Tomasz Myrta wrote:
> >> Tomasz Myrta <[EMAIL PROTECTED]> writes:
> >> I'd like to split queries into views, but I can't join them - planner
> >> search all of records instead of using index. It works very slow.
>
>
> I think this is the same issue that Stephan identified in h
On Wed, 22 Jan 2003, Tomasz Myrta wrote:
> Let's make some test:
>
> First, let's create some simple view with 2 tables join:
> drop view pm;
> create view pm as
> select
>id_przystanku,
>m.nazwa
> from
>przystanki p
>join miasta m using (id_miasta);
>
> explain select * from pm
On Wed, 22 Jan 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > The filter is applied only to a. So, if you really wanted the
> > c.a=3 condition to be applied for whatever reason you're out of
> > luck.
>
> FWIW, CVS tip is brighter
On Wed, 22 Jan 2003, Tomasz Myrta wrote:
> Stephan Szabo wrote:
>
> >That's not the same join for optimization purposes
> >since postgresql treats explicit join syntax as a
> >constraint on the ordering of joins.
> >
> >The same join would be something
On Wed, 22 Jan 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Wed, 22 Jan 2003, Tom Lane wrote:
> >> Stephan Szabo <[EMAIL PROTECTED]> writes:
> >>> The filter is applied only to a. So, if you really wanted the
> >>
On Thu, 23 Jan 2003, Tom Lane wrote:
> regression=# explain analyze select * from tenk1 a join tenk1 b using(ten)
> regression-# where ten = 3;
> QUERY PLAN
>
>--
On Fri, 24 Jan 2003, jack wrote:
> Is that possible to have a two columns primary key on a table with null
> value on second column?
No, because primary key implies not null on all columns involved
(technically I think it's that a non-deferrable primary key implies
not null on all columns involve
On Tue, 28 Jan 2003, Rodger Donaldson wrote:
> Now, adding a UNIQUE constraint on the pk for add_queue weeds out
> dupes there. However, attempting to add a cross-table UNIQUE check
> with:
>
> alter table add_queue add constraint add_queue_no_dupe_sites unique
> (sites.url);
> ERROR: parser: p
On Wed, 29 Jan 2003, Rodger Donaldson wrote:
> > On Tue, 28 Jan 2003, Rodger Donaldson wrote:
> >
> > > Now, adding a UNIQUE constraint on the pk for add_queue weeds out
> > > dupes there. However, attempting to add a cross-table UNIQUE check
> > > with:
> > >
> > > alter table add_queue add con
On Fri, 24 Jan 2003, Seethalakshmi VB wrote:
> Is it possible to issue a CREATE TABLE statement from inside of a PostgreSQL
> function? If not, then how about from within a PL/pgSQL function? I have
> tried a lot of different permutations but can't seem to get it to compile
> and/or run cleanly
On Wed, 29 Jan 2003, Neal Lindsay wrote:
> I am creating a database that will keep track of several different types
> of 'events'. I am toying with the idea of making a base 'class' table
> for the tables because a lot of the information will be the same (also
> there will probably be times I jus
On Sat, 1 Feb 2003, Matthew Horoschun wrote:
>
> On Saturday, February 1, 2003, at 03:43 PM, Tom Lane wrote:
>
> > Matthew Horoschun <[EMAIL PROTECTED]> writes:
> >> Should I just avoid SEQUENCES altogether and use the OIDs under normal
> >> circumstances and the MAX( id ) + 1 style thing when I
On 14 Feb 2003, Brad Hilton wrote:
> I am hoping someone can help explain why modifying the following query
> can effect such a huge change in speed. The query is:
>
> select * from articles
> where exists
>( select 1 from article_categories
> where
> articl
On Fri, 14 Feb 2003, Michael Weaver wrote:
> How do you get an array of elements to be passed to a stored proc such that
> you can use the array in a SELECT statement in the WHERE clause
> - e.g. WHERE field IN (array) etc...
A straight WHERE field IN (array) doesn't work. That's attempting t
On Thu, 6 Feb 2003, Matthew Nuzum wrote:
> I have a table that uses the NO ACTION action for it's referential
> integrity. I'd like to change it to CASCADE for the ON DELETE event.
> I'm using Postgres 7.2.
I think the best way is to drop all three triggers for the constraint and
use alter table
On Wed, 19 Feb 2003, Matthew Nuzum wrote:
> Sorry for the simple question, but I'm struggling with a join.
>
> I'm creating a view that will show data from 4 tables. The problem is, I
> want the view to show a record for every entry in the "users" table, even if
> there is no matching entry all
On Fri, 21 Feb 2003, Tom Lane wrote:
> Dan Langille <[EMAIL PROTECTED]> writes:
> > This is the query in question:
>
> > SELECT element_id as wle_element_id, COUNT(watch_list_id)
> > FROM watch_list JOIN watch_list_element
> >ON watch_list.id = watch_list_element.watch_list_id
> > WHE
301 - 400 of 732 matches
Mail list logo
