On Thu, 17 Jul 2003, Terence Kearns wrote:
> CREATE FUNCTION base.fn_fkey_check(text, text, int4) RETURNS bool AS
> 'DECLARE
> BEGIN
>RETURN (SELECT count($1) FROM $2 where $1 = $3)::bool;
> END;'
> LANGUAGE 'sql';
>
> produces this error
> ERROR: parser: parse error at or near "RETURN" at c
On Tue, 15 Jul 2003, Matt Tenenbaum wrote:
> Say I created a couple tables A and B, where A contains a column p
> which B references as a foreign key. Further say that, when I created
> these tables, I meant to specify 'on delete cascade' for p in the
> definition of B but didn't. What's the prope
On 8 Jul 2003, Markus Bertheau wrote:
> when you have
> select expensive_expression(column), * from table offset 20 limit 40
>
> can you somehow save the cost for the first 20 calculations of
> expensive_expression?
Right now the only way I can think of that might work is to push the
offset/limit
On 7 Jul 2003, markus brosch wrote:
> I was searching the archive and was wondering why nobody asked this
> strange(!) question (or I've not found it?):
>
> "What is the max allowed length of a sql statement or query?"
AFAIR in recent versions it's effectively limited only by resources (how
much
On Wed, 2 Jul 2003, Rudi Starcevic wrote:
> Hi,
>
> I know that if you have a trigger and function then drop/replace the
> function the trigger needs
> to be drop/replaced too so that it can see the new function.
>
> Is it the same for Ref. Integ. on table's too ?
>
> If table B's foreign key refe
On Mon, 30 Jun 2003, Erik Erkelens wrote:
> new_max_records ALIAS FOR $1;
> BEGIN
> DROP SEQUENCE my_sequence;
> --CREATE SEQUENCE my_sequence MAXVALUE 4
> CYCLE;
> CREATE SEQUENCE my_sequence MAXVALUE
> new_max_records CYCLE;
Most of the creates/drops/etc...
On 30 Jun 2003, Robert Treat wrote:
> create table foo (a int, b int, c int, d text);
>
> create table bar (a int, b int, c int);
>
> insert into foo values (1,2,3,'a');
> insert into foo values (1,2,4,'A');
> insert into foo values (4,5,6,'b');
> insert into foo values (7,8,9,'c');
> insert into
On Wed, 25 Jun 2003, Anagha Joshi wrote:
> 4. the file '2078989' corresponds to 'cdrrec' file (table)
>
> $ ls -l 2078989
> -rw--- 1 postgres postgres 2359296 Jun 24 17:18 2078989
>
> This means table 'cdrrec' takes approx. 2030.2626953125 KB i.e. approx.
> 2MB
>
> Table is like
On Mon, 16 Jun 2003, Tony G. Harris wrote:
> Hello. I'm using PostgreSQL to learn SQL and SQL-based languages. The
> text I'm using (SAMS Teach Yourself SQL in 21 Days, 3rd.ed.) references
> Oracle in its examples, though I can usually get PostgreSQL to work
> almost as well.
>
> Well, I'm almo
On Fri, 13 Jun 2003, Demidem Mohamed Amine wrote:
> hello,
>
> Can anyone help me create a function that creates a
> table, in this way for example :
See EXECUTE for a way to execute a query that you've built into
a string, for example, something like:
EXECUTE ''create table '' || $1 || '' (id i
On Thu, 12 Jun 2003, Ludwig Lim wrote:
> I would like to ask the following questions:
> a) Are foreign key constraint triggers guaranteed to
> execute first before any ordinary "BEFORE
> INSERT/UPDATE/DELETE" trigger is executed? (This is
> assuming that the foreign keys are declared as "NOT
On 12 Jun 2003, Robert Treat wrote:
> Does anyone know if there is support for "IF x OR y THEN" syntax in
> plpgsql? The docs just say IF [boolean expression] then. which loosely
> interpreted could allow for an OR, but I couldn't seem to get it to
> work. TIA,
Can you give a full example of wha
On Fri, 6 Jun 2003, Richard Huxton wrote:
> On Thursday 05 Jun 2003 3:55 pm, [EMAIL PROTECTED] wrote:
> > hi ,
> >
> > Is there any way to enforce fkeys only on subset of
> > the table something on the lines of unique partial indexes
>
> I'm afraid not (AFAIK). What might be a solution in your ca
On Fri, 30 May 2003, jtx wrote:
> Hi everyone, I'm trying to do a left join on two tables, mainly because
> data from table 'b' (lists) may or may not exist, and if it doesn't I
> want results. However, if data from table lists DOES exist, I want to
> run a conditional on it, and then return dat
On Thu, 29 May 2003, Brian Knox wrote:
> Given a variable of the "record" data type in pl/pgsql, is it possible to
> get the names of the columns ( attributes ) of that record?
>
> eg, given record "NEW" for table "foo", is there a way to get information
> concerning the columns that make up that
On Thu, 29 May 2003, Chad Thompson wrote:
> I have never been very good at reading these query plans, but I have a bit
> of a problem w/ my query. So any help is appreciated.
>
> The query is fairly self explanitory i think. 2 tables, call_results ( 6.5
> Million records ) and lists ( 11 Million
On Mon, 12 May 2003, Didrik Pinte wrote:
> Here is the query :
>
> SELECT profiles_et.username, profiles_et.name, profiles_et.firstname,
> profiles_et.email, company_et.name AS company, count(logs_et.dt) AS cnt,
>
On Fri, 4 Apr 2003, Claude wrote:
> I have a table a field with timestamps in seconds since epoch and I would
> like to get a human readable date... but it seems that postgresql 7.3 does
> not support the datetime(), timestamp(), timestamptz() functions...
I'd say probably something like:
CAST(
On Wed, 26 Mar 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On 26 Mar 2003, Greg Stark wrote:
> >> Can you show an example where the join order would affect the result set? I
> >> can't think of any.
>
> > I can think of a f
On Fri, 21 Mar 2003, Mario Alberto Soto Cordones wrote:
> CREATE or replace FUNCTION get_nombre(numeric,numeric,text) RETURNS setof
> cumaalu AS '
> SELECT cunomalu,cuappalu,cuapmalu FROM cumaalu
> WHERE siempalu = $1 and
> cusesalu = $2 and
> curutalu = $3;
> ' LANGUAGE SQL;
>
On Tue, 25 Feb 2003, Natasa Bulatovic wrote:
> Select statement returns as a result concatenated all varchar, text
> and char columns of a table as one single column ...
>
> select col1||col2||col3||.||col100 from table
>
> However, when the number of concatenated columns is bigger than 22 no
On Wed, 26 Feb 2003, Grignon Etienne wrote:
>
> test=# SELECT createuser('toto', true, true, true);
> NOTICE: Begin Of Update Permissions
> NOTICE: End Of Update Permissions
> NOTICE: TRIGGER
> createuser
>
> 0
> (1 row)
> test=#
>
>
> My problem is that the trigger for
On Tue, 25 Feb 2003, Rudi Starcevic wrote:
> I'm trying to create this table with a Check that fails.
>
> I'm using 'Check' instead of 'References' due to poor db schema ( before
> me .. )
>
> Here is my error message:
> ERROR: cannot use subselect in CHECK constraint expression
>
> CREATE TABLE
On Sat, 22 Feb 2003, Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Fri, 21 Feb 2003, Tom Lane wrote:
> >> Anyone care to offer a gloss on the spec to prove that this behavior
> >> is correct or not correct?
>
> > Hmm, I'd read SQ
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
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 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 Tue, 11 Feb 2003, Wilkinson Charlie E wrote:
> Greetings,
> Can anyone enlighten me or point me at resources concerning use of pgsql
> with
> very large datasets?
>
> My specific problem is this:
>
> I have two tables, one with about 100 million rows and one with about 22,000
> rows. My plan
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 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 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 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, 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 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 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 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 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 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:
> > 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:
> 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, 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, 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 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, 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 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 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, 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, 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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, 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 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 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 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, 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 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 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
> 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 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
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 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 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 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 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.
> 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 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
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, 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:
> 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, 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:
> 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 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 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, 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 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
301 - 400 of 732 matches
Mail list logo