[SQL] Select For Update and Left Outer Join

o zero doesn't work on PostgreSQL because it's apparently still using zero-terminated string functions. :( Aside from those two things, it looks like PostgreSQL is going to be passing all the tests soon, so that's good news. :) Patrick Earl -- Sent via pgsql-sql mail

Re: [SQL] generating date sequences

Hi! >> Is there a simple way to generate sequences of dates like the following? > Sure: > test=# select '2008-07-03'::date + s * '1day'::interval from > generate_Series(1,10) s; Thanks! Thats what I was searching for. You saved my day from manually adding missing dates in a huge excel sheet! S

[SQL] generating date sequences

items for each day (each item has a timestamp). For some days however there are no items, resulting in no row instead of a row with zero items. I'd like to fill these empty rows. I hope I could make my problem clear?! Best regards Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgres

[SQL] fast insert-if-key-not-already-there

append(v_ip_addr_ids, v_ip_addr_id); END LOOP; END; $$ LANGUAGE 'plpgsql' STRICT; Now I'm wondering if there is a better solution, since I'm doing ~20 inserts at once and every time I'm doing single lookup's for the IDs. regards patrick -- Sent via pgsql-s

[SQL] index for group by

id2; takes some time (~10 minutes) and return about 1.000.000 rows. I created an index on both colums id1 and id2 (together) which takes about 800 MB but doesn't speedup things. In fact it even doesn't seem to be used. Is there any way to speedup this "group by" or does it seem m

Re: [SQL] order by when using cursors

works, it makes things much easier!: Thank you! Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] order by when using cursors

3 2 4 Which is not ordered by column a!? Is this intended? Am I doing something wrong? I'm using Postgresql 8.3.1 Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] using calculated column in where-clause

Andreas Kretschmer wrote: >> Do I have to repeat the calculation (which might be even more complex > yes. Short and pregnant! :-) Thanks! Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] using calculated column in where-clause

on (which might be even more complex :-) ) in the "where"-clause, or is there a better way? Thanks in advance. Best regards Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning results from an update for joining other tables

gt;> >> RETURNING * >> >> ) AS ta >> >> JOIN someothertable ON ... > > > > It's a know limitation, see <[EMAIL PROTECTED]> Oh, I see. Are there ways to work around this limitation? Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] returning results from an update for joining other tables

JOIN someothertable ON ... > > It's a know limitation, see <[EMAIL PROTECTED]> Oh, I see. Are there ways to work around this limitation? Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] returning results from an update for joining other tables

RETURNING * ) AS ta JOIN someothertable ON ... Can I somehow select some rows and do multiple operations on exactly this resultset? In my case update columns, then join columns from other tables and then return the resultset with the joined columns? Regards Patrick -- Sent via pgsql

[SQL] merge timestamps to intervals

10:17-1018 up I've no clue how to approach this problem. Any ideas/hints? Also suggestions on a feasible better schema are welcome. :-) Thanks Patrick -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Inconsistent sql result

can't have the full size of all indexes attached on a table. Does anyone has a clue of why the same query, on same database gives different result depending on it is included in a python script, or ran from a console? Many Thanks Patrick #!/usr/bin/python2.4 # import sys import pgdb DE

[SQL] Index working, but not inside function

## THE PROBLEM I'm trying to write a function that will select the ID of a row from a very large table (2M rows) using an index (places_autocomplete_idx). When I execute the function the query stalls and is apparently not taking advantage of the index. However, executing the same query outside o

Re: [SQL] simple problem???

ue of B and I obtain: 1|10 2|12 3|23 How can I do for plotting also the value of B??? Thank you very much Ivan Maybe you could try Select distinct on (a,b) a,b,c from(select * from table order by A,C) The distinct on construct is a postgreSql-ism Cordialement -- Patrick --

Re: [SQL] graph plottin engines compatible with postgres

;, "" u 1:2 title "Taille de l'area" fingnuplot This example uses a temporary "plotdata" file. HTH -- Patrick ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] "CASE" is not a variable

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 shouldn't your CASE construct be in the select list, i.e SELECT tbl_item_bom_so.subline, CASE ... END INTO ... FROM ...WHERE ... ? HTH -- Patrick ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Am I crazy or is this SQL not possible

Collin Peters wrote: I am having some serious mental block here. Here is the abstract version of my problem. I have a table like this: unique_id (PK) broadcast_id date_sent status 1 1 2005-04-0430 2 1

Re: [SQL] some error when executing query in pgAdmin tool

Penchalaiah P. wrote: I created one table in pgAdmin tool but when I am executing query it is giving error…. CREATE TABLE "ADV" ( "T-Section_Id" varchar(10) NOT NULL, "CDA_No" varchar(7) NOT NULL, "Imp_Schedule_Id" int4 NOT NULL, "Sanction_No" varchar(20) NOT NULL, "Sanction_Date" date N

Re: [SQL] insert related data into two tables

[EMAIL PROTECTED] wrote: Hello, I have two tables like these: TABLE_1: people registry fields: ID_T1, SURNAME, NAME ID_T1 is primary key TABLE_2: work groups fields: ID_T2, TASK ID_T2 is foreign key related to ID_T1 the first table is the list of employees, the second the task. Sometime I

Re: [SQL] Referential integrity broken (8.0.3), sub-select help

[EMAIL PROTECTED] wrote: Hello, I've got 2 tables, "url" (U), and "bookmark" (B), with "bookmark" pointing to "url" via FK. Somehow I ended up with some rows in B referencing non-existent rows in U. This sounds super strange and dangerous to me, and it's not clear to me how/why PG let this ha

Re: [SQL] Need help: Find dirty rows, Update, Delete SQL

Janning Vygen wrote: Am Samstag, 18. Februar 2006 18:41 schrieb [EMAIL PROTECTED]: Hello, I need a bit of help with some SQL. I have two tables, call them Page and Bookmark. Each row in Page can have many Bookmarks pointing to it, and they are joined via a FK (Page.id = Bookmark.page_id).

Re: [SQL] SELECT on a to-be-determined table

garhone wrote: Hi, I'm a new at this. So please forgive if I mess up. Also, if there is already a reference/tutorial somewhere, feel free to point me to it. Here's my situation: db=# select * from projects; projid | projname +-- 1 | cars 2 | houses 3 | pets (3 ro

Re: [SQL] Non Matching Records in Two Tables

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 tables have a column named 'key100'. I was trying something like: SELECT count(*) FROM table1,

Re: [SQL] problem referencing an attrib which is not unique

Vic Rowan wrote: -- Forwarded message -- From: *Vic Rowan* <[EMAIL PROTECTED] > Date: Feb 7, 2006 2:31 PM Subject: problem referencing an attrib which is not unique To: pgsql-sql@postgresql.org hello everybody, I ne

Re: [SQL] Matching several rows

Michael Glaesemann wrote: On Jan 18, 2006, at 21:48 , Volkan YAZICI wrote: AFAICS, the bottleneck in above query is ANY(ARRAY[]) clause usage. Instead of that, if you replace «rights = ANY(ARRAY[2,5,10])» with «rights IN (2,5,10)» it's overhead decreases to 0.200-0.300ms domain. explain an

Re: [SQL] Need SQL Help Finding Current Status of members

Richard Huxton wrote: Michael Avila wrote: I have a table which keeps track of the status of members. In the table is member_id int(8) status_code char(1) status_date date KEY member_id (member_id,status_code,status_date) Each member can have multiple records because a record is added each

Re: [SQL] RETURN SET OF DATA WITH CURSOR

grupos wrote: Hi Richard, Thanks for your promptly answer. I don't have experience returning refcursor but my choice would be it. I read the documentation but I didn't find any example with my necessity... Could you give me a small example on the same "basis" that I want? Thanks, Rodrigo

Re: [SQL] Just 1 in a series...

Mark Fenbers wrote: What would have to be done if I needed a standard SQL solution? Mark ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Maybe you could t'ry something like : Se

Re: [SQL] automatic update or insert

tobbe wrote: Hi. I have a little problem. In a system of mine i need to insert records into table [tbStat], and if the records exist i need to update them instead and increase a column [cQuantity] for every update. I.e. the first insert sets cQuantity to 1, and for every other run cQuantity i

[SQL] how to select custom value when exists otherwise select default

I use a select like this: SELECT sometables WHERE field0='a' or field0='b' or field0='c' and field4='m' or field4='n' ORDER BY field0; m.field4 is default value n.field4 is custom value I want only n(custom) if it exists ortherwise I want m(default) but I do not want both! I mean I have this re

Re: [SQL] vacuum analyze slows sql query

On Wed, Nov 03, 2004 at 07:01:00AM -0500, Andrew Sullivan wrote: > On Tue, Nov 02, 2004 at 06:50:31PM -0800, patrick ~ wrote: > > We have a nightly "garbage collection" process that runs and > > purges any old data. After this process a 'vacuum analyze' > >

[SQL] vacuum analyze slows sql query

ected behavior? The .sql file that will create the tables and stored function follows. It has been processed by uuencode to avoid Yahoo! mail wrapping lines and messing things up. The data that I'm testing this db with is somewhat large; even in its "stripped down," "sanatiz

[SQL] Is there a faster way to do this?

cdmperct; RETURN \'DONE\'; END; ' LANGUAGE 'plpgsql' IMMUTABLE; Patrick Hatcher Macys.Com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Please help me to slove this SQL statements

-Message d'origine- De : Freshman [mailto:[EMAIL PROTECTED] Envoyé : jeudi 30 octobre 2003 13:38 À : [EMAIL PROTECTED] Objet : [SQL] Please help me to slove this SQL statements There are three table in database which is suppliers, projects, and shipments suppliers contain suppliers id,

[SQL] change a field

triggers but with no success. It works only when inserting new records and not when updating a field. What is the best way to solve this problem ? Kind regards Patrick Meylemans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[SQL] Are sub-select error suppressed?

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 selecting entries that may be purged. My delete statement uses the view to delete data from one of the tables. Like so

Re: [GENERAL] [SQL] Database Design tool

it is under active development. Patrick > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Dan Hrabarchuk > Sent: Wednesday, October 30, 2002 7:31 AM > To: Johannes Lochmann > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > S

Re: [SQL] sub-select trouble: wrong SQL or PostgreSQL issue?

Thank you both for your prompt responses. Bug #526 (as Tom pointed out) does look very similar to my problem. I'll attempt to upgrade my development PostgreSQL and eventually my production servers if all goes well. Thanks again, On Wed, Oct 23, 2002 at 11:28:01PM -0400, Tom Lane wrote: > >> I

[SQL] sub-select trouble: wrong SQL or PostgreSQL issue?

Greetings, I have 3 tables. Two of which (ta and tb) are different "attributes" the third table (tc) can have. tc is allowed up to three of each kind of "attributes". e.g., 3 ta values and 2 tb values. By assigning ta and tb attributes to each entry in tc you are also "matching" tb attributes

[SQL] Extremely slow query

ol float8, wtd_returnu int4, wtd_returndol float8, total_curoh int4, total_curoo int4, curr_date date, lw_1_demand int4, lw_2_demand int4, lw_3_demand int4, lw_4_demand int4, option4_flag int2, option3_flag int2, price_original float8, price_ticket float8 ) Patric

[SQL] referencing oid impozsible ?

hello all I tried recently (pgsql 7.1.2) to establish the oid of one table as foreign key in another. To no avail : there was no uniqueness constraint on that column Naturally, it seems impossible to add a uniqueness constraint to such a system column. As far as i know, the oid is by nature uniqu

[SQL] 3 options

upgrade to 7.1 (fear to lost my data - i'm not a linux guru) --- thank you patrick, montreal, canada ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] Re: [GENERAL] MySQLs Describe emulator!

On Tue, Mar 06, 2001 at 10:38:43AM -0500, Michael Fork wrote: > try starting psql with the -E option -- this displays all queries used > internally to the screen, i.e.: Sorry, hadn't read this one before posting... Thanks to the "moderating" it'll all be out of s

Re: [SQL] Comparing dates

Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc > PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command >

Re: [SQL] select returns no line

t; 4|test |0PDv7a2EESjZo| > (1 row) > > -- > x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x- [EMAIL PROTECTED] -x hello are you sure the value of the user_id in that line is "test" and not "test " i.e it has not spurious spaces at the end of it ? HTH Patrick

[SQL] Re: [GENERAL] Help with query. (*)

On Tue, Jan 16, 2001 at 01:42:45PM -0700, Diehl, Jeffrey wrote: > I'm having difficulty writing a query which I really can't live without... > > I need to get a list of records from table A for which there are > corresponding records in table B. I've tried to use the intersect clause, > but it

[SQL] Re: [INTERFACES] outer join in PostgreSql

t;xxx", * FROM J1_TBL FULL JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); Cheers, Patrick

Re: [SQL] Query Help

"Brian C. Doyle" wrote: > What do I have to do a query where information in table1 is not in table2 > > I am looking for something like > > Select table1.firstname where table1.firstname is not in table2.firstname > and table2.date='yesterday' > > I tried > Select table1.firstname where table1.fi

[SQL] subqueries as values in updates

hello, list. are subqueries alloved as values in an update command ? e.g. update a set a.attribute1 = a.attribute1 + (select sum(b.attribute1) from b where b.attribute2=a.attribute2); If yes, how is the correct syntax ? If not, is there a hope for it in a future version ?

[SQL] out-subject : thanks

in french... go to - medias - visiteurs thank you again patrick --- the power of internet...

Re: [SQL] join if there, blank if not

Henry Lafleur wrote: > Jacques, > > The problem with using the union in this way is that you get NULLs for a > number weather or not it has an associated record in calls. > > To do a pure outer join, it would be something like this: > > select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc > from

[SQL] order by accents?

hi, if I do a query like this one : SELECT name from medias ORDER BY name name ÉCCC 6 rows Why the record : ÉCCC is at the end? HOW can I fix this? Thank you Patrick

Re: [SQL] confused by select.

is 2.? but there are many > orders with hundreds of items. > > Sorry for the long winded explanation. > But I figured, that the more imformation i gave, the more someone may be > able to help. > > Thanks in advance. > .jtp usually many-to-many relationships are handled by a third table, like this: create table items (item_id,...) create table customers (customer_id, ...) create table orders (customer_id, item_id, quantity_orderered) Hoping it may help Patrick JACQUOT

Re: [SQL] GROUP by finish&&last day of month

Antti Linno wrote: > Ok, if I want to get non-aggregat data in groups, I use order by. This > group by seemed so logical though, but the fruit that u can't have, is > usually the most sweet. > > New question, how to get the last day of month(order data by last day of > month). And to prevent chai

[SQL] temporay table : error is repeated.

1 TEMPORARY table with UNION : problem or not??? Sorry for this long post... but it's the last option of my project! Thank you for let me know what you thinking about that. Sorry for my english, but I get better everyday. Patrick

[SQL] find the number of rows for each table

- ERROR: parser: parse error at or near "distinct" don't work, as you can see. I need help, please. Patrick

[SQL] Outer join in postgresql

I am looking for a way run an outer join in psql. Can anyone help? Informix has an "OUTER" keyword. I don't see anything like this in the docs for psql. Thanks much. -Pat Kay

Re: [SQL] SPEED UP.

> > > I would like to know is there is a specific way to speed up my query to > postgres. Please post the exact command line arguments you are giving when you start the postmaster daemon process. Cordially Patrick Giagnocavo [EMAIL PROTECTED]

RE: [SQL] 7.0 weirdness

ch order is Postgres supposed to give the data??? Patrick Fiche -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]De la part de Jeff MacDonald Envoyé : mardi 30 mai 2000 14:28 À : [EMAIL PROTECTED]; [EMAIL PROTECTED] Objet : [SQL] 7.0 weirdness hi folks, this qu