Re: [SQL] replace text occurrences loaded from table

. Since you have not shared the true context of your request no alternatives can be suggested. Also, your ability to implement certain algorithms is influenced by the version of PostgreSQL that you are running and which you have also not provided. David J. -- 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 regexp_matches in the WHERE clause

turns its own record. Even though only one record is ever returned without the "g" option the function itself is the same and still is defined to return a set. David J. -- 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] Joining several rows into only one

Joining several rows into only one > > You need to use the array_agg() and array_to_string() functions. > Or you can skip directly to the "string_agg(expression, delimiter)" function. See http://www.postgresql.org/docs/9.2/interactive/functions-aggregate.html for more informa

Re: [SQL] deciding on one of multiple results returned

a 0 b 0 c 1 a 1 b How do I just choose a preferred element say value 'a' over any other elements returned, that is the value returned is from a subquery to a larger query? Thanks. ORDER BY (with a LIMIT depending on circumstances) David J.

Re: [SQL] Query execution based on a condition

UNION SELECT fields FROM tblTable WHERE condition Respectfully, Jorge Maldonado Not using pure SQL. pl/pgsql provides you access to conditionals and flow control so you should be able to create a function to do precisely what you need. David J.

Re: [SQL] pivot query with count

SELECT num_ads, sum(...), sum(...), FROM ( your query here ) GROUP BY num_ads; BTW, While "SELECT '1' "num_ads" is valid syntax I recommend you use the "AS" keyword. '1' AS "num_ads" David J. -- View this message in context: h

Re: [SQL] pivot query with count

My prior comment simply answers your question. You likely can rewrite your query so that a separate grouping layer is not needed (or rather the group by would exist in the main query and you minimize the case/sub-select column queries and use aggregates and case instead). David J. -- View

Re: [SQL] Select statement with except clause

"tbl" within the query inside the where clause GROUP BY col1, col2, col3 I do not follow your example enough to provide a more explicit example/solution but this should at least help point you in the right direction. David J. -- View this message in context: http://postgresql.10

Re: [SQL] Select statement with except clause

SELECT fldA, fldB, fldC, sum(fldD) AS sum_positive) q1 NATURAL FULL OUTER JOIN (SELECT fldA, fldB, fldC, sum(fldD)*-1 AS sum_negative) q2 WHERE <...> Food for thought. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Select-statement-with-except-clau

Re: [SQL] UNNEST result order vs Array data

the usual output. This is, I am pretty such, a usability enhancement that makes easier something that can be done today using CTE/WITH and/or sub-queries. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-result-order-vs-Array-data-tp5760087p5760126.ht

Re: [SQL] Update a composite nested type variable

read and in assign. > How can I workaround this and update my values? > > Luca. This may be a pl/pgsql limitation but you should probably provide a complete self-contained example with your attempt so that user-error can be eliminated. David J. -- View this message in context: ht

Re: [SQL] Update a composite nested type variable

Luca Vernini wrote > 2013/7/8 David Johnston < > polobo@ > > > >> >> This may be a pl/pgsql limitation but you should probably provide a >> complete >> self-contained example with your attempt so that user-error can be >> eliminated. >>

Re: [SQL] Criteria to define indexes

e, composite primary key definitions but in this kind of star-schema setup simply have each foreign key and whatever other searching fields you require maintain their own individual index. David J. P.S. Arguably, having a separate column for each kind of person is a poor design at face value

Re: [SQL] Using regexp_matches in the WHERE clause

ot;text[]" instead of a "setof text[]". It makes coding these kinds of queries easier if you know/understand the fact that your matching will never cause more than 1 row to be returned. If zero rows are returned I return an empty array and the normal 1-row case returns the match

Re: [SQL] removing duplicates and using sort

otherwise your use of "DISTINCT ON" begins to mal-function. I dislike DISTINCT ON generally but do not wish to ponder how you can avoid it, so I'd suggest just turning your query into a sub-query like I show above. David J. -- View this message in context: http://postgresql.104569

Re: [SQL] postgres subfunction return error

on variables. You must manually map the output of the sub-function call query onto the parent variables. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772408.html Sent from the PostgreSQL - sql mailing list ar

Re: [SQL] postgres subfunction return error

and toss the temporary variables but that should not impact the semantics. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772627.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via

Re: [SQL] Help needed with Window function

Create an array of all dates (tags in the example) that match with positive amounts. Negative amounts get their matching tag added to the array as NULL. The provided function looks into the generated array and returns the last (closest to the current row in the frame) non-null date/tag in the

Re: [SQL] Advice on defining indexes

the different text fields. I would then add an index on art_bday and call it done. You can then write a view/function that performs a full-text search against the functional index (or just create an actual column) for most text searches and have separate criteria filters for country/type/birthda

Re: [SQL] Unique index VS unique constraint

should be associated only with constraints. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773427.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-s

Re: [SQL] Advice on defining indexes

ionship. CREATE TABLE list ( lst_source, lst_date, FOREIGN KEY (lst_source, lst_date) REFERENCES source (src_id, src_date) ...; If a unique constraint (in this case I'd suggest primary key) does not exist for source(src_id, src_date) the create table with the foreign key will fail. David

Re: [SQL] Unique index VS unique constraint

not pass a cost-benefit analysis. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Unique-index-VS-unique-constraint-tp5773386p5773434.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postg

Re: [SQL] Question about index/constraint definition in a table

you? Also, the car_id field becomes pointless since your new constraint is the true and natural PK. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Question-about-index-constraint-definition-in-a-table-tp5773924p5773925.html Sent from the PostgreSQL - sql

Re: [SQL] Question about index/constraint definition in a table

David Johnston wrote > > JORGE MALDONADO wrote >> I have a table as follows: >> >> Table Artist Colaborations >> >> * car_id (integer field, primary key) >&

[SQL] autoupdating mtime column

Dear all,Please see SQL below. I'm already satisfied with everything except I wish in #4, mtime got automatically updated to NOW() if not explicitly SET in UPDATE statement. Is there a way to make the mtime column behave more like I wanted? Thanks in advance. create table t1 (  id int primary key, 

Re: [SQL] autoupdating mtime column

On 8/4/06, Richard Huxton <dev@archonet.com> wrote: David Garamond wrote:> Dear all,>> Please see SQL below. I'm already satisfied with everything except I> wish in> #4, mtime got automatically updated to NOW() if not explicitly SET in > UPDATE> statement. Is ther

Re: [SQL] autoupdating mtime column

On 8/4/06, Rodrigo De León <[EMAIL PROTECTED]> wrote: How about:create or replace functionupdate_times()returns trigger as $$beginif TG_OP='INSERT' thennew.ctime = coalesce(new.ctime,now()); new.mtime = coalesce(new.mtime,now()

Re: [SQL] autoupdating mtime column

On 8/4/06, Tom Lane <[EMAIL PROTECTED]> wrote: If you are really intent on having a way to suppress the mtime updateyou could dedicate an additional field to the purpose, egUPDATE t SET foo=..., bar=..., keepmtime = true ...and in the trigger something like if new.keepmtime then

Re: [SQL] autoupdating mtime column

On 8/5/06, Richard Huxton wrote: >> have a real use-case for suppressing mtime updates?> Syncing tables between databases (a la "rsync --times"). Btw, I'm> considering temporarily disabling the update_times() trigger when sync-ing. I'd consider running the sync as a different (pr

[SQL] Unsubscribe

---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

[SQL] unsubscribe

unsubscribe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly

[SQL] How to get text representation of composite value?

ECT textin(point_out('(1,1)'::point))::varchar; Anybody can help me? P.S. pl/pgsql would be best way if it possible. David F ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [SQL] hi

Hi, try this. It's working... SELECT t1.sno, sum(t2.value) FROM temp as t1, temp as t2 WHERE t1.sno >= t2.sno GROUP BY t1.sno; but not completely. The problem is the this suppose your sno is uniqu. But as I see 3 is not. Anyway it may help you. >I have the data like this in temp table > >SQL>

[SQL] NEW to plpythonu function?

etting system messages like: plpythonu don't know argument type recort, anyelement) P.S. I would like to have trigger function in plpgsql and supporting function in plpythonu, not to join them to plpythonu. Thankx a lot David Flegl ---(end of broadcast)---

[SQL] Indexing a field of type point

creating a suitable index? I am aware of PostGIS, but would prefer not to have to rework a whole load of code to use the different geometrical field types it provides. Thanks for any help! David. -- David Cottingham Computer Laboratory, University of Cambridge http://www

[SQL] Indexing a field of type point

ay of creating a suitable index? I am aware of PostGIS, but would prefer not to have to rework a whole load of code to use the different geometrical field types it provides. Thanks for any help! David. -- David Cottingham Computer Laboratory, University of Cambridge http://www

[SQL] query: last N price for each product?

Dear SQL masters, The query for "latest price for each product" goes like this (which I can grasp quite easily): SELECT * FROM price p1 WHERE ctime=(SELECT MAX(ctime) FROM price p2 WHERE p1.product_id=p2.product_id) or: SELECT * FROM price p1 WHERE NOT EXISTS (SELECT * FROM price p2 WHERE p

[SQL] Cursors..

Hi List, I need to convert this cursor of Oracle to Postgres... I wait for your help!! *declare** v_num_dep integer := &v_num_dep; v_cont integer:=0; cursor cursor_dep is select * from hr.employees where hr.employees.department_id = v_num_dep; reg_dep cursor_dep%rowtype; beg

[SQL] grouping/clustering query

Dear all, I have an invoices (inv) table and bank transaction (tx) table. There's also the payment table which is a many-to-many relation between the former two tables, because each invoice can be paid by one or more bank transactions, and each bank transaction can pay for one or more invoices. Ex

Re: [SQL] grouping/clustering query

Tony, Joe, Steve, Thanks for the follow-ups. Yes, the problem is related to double-entry accounting, where one needs to balance total debit and credit (payments and invoices) in each journal/transaction. Due to time constraint, I ended up doing this in the client-side programming language, since

[SQL] Array: comparing first N elements?

I have a "materialized path" tree table like this (simplified): CREATE TABLE product ( id SERIAL PRIMARY KEY, parents INT[] NOT NULL, name TEXT NOT NULL, UNIQUE (parents, name) ); CREATE INDEX name ON product(name); Previously I use TEXT column for parents, but arrays look interes

Re: [SQL] Array: comparing first N elements?

On Tue, May 12, 2009 at 3:28 PM, Pavel Stehule wrote: > Hello > > create or replace function eqn(anyarray, anyarray, int) > returns boolean as $$ > select not exists(select $1[i] from generate_series(1,$3) g(i) > except > select $2[i] from gener

Re: [SQL] Array: comparing first N elements?

2009/5/12 Achilleas Mantzios > you would want to look at the intarray contrib package for index suppor and > many other goodies, > also you might want to write fucntions first(parents), last(parents) and > then have an index > on those as well. > This way searching for the direct children of a no

[SQL] extracting a table description

vance David Craig [EMAIL PROTECTED]

[SQL] protecting a field after creation

Hello, Is there a way (outside of RULEs and TRIGGERs) to make a field read-only once it is INSERTed or assigned its default value? I'm thinking, for example, of the "created" column that I add to most tables, holding the row's creation timestamp. Thanks in advance, -- Lo

Re: [SQL] database design and diagraming book recommendations..

"Francisco Hernandez" <[EMAIL PROTECTED]> > anyone know of a good book or books on database modeling? > like for entity relationship diagrams and such.. To get a good life, go with Chris Date's rather difficult two volume set. -dlj.

[SQL] sum of agreggates in one SELECT?

= b.person_id ; sum - 52 (1 row) But this is wrong because it sums all quantities. I don't know how to apply a valid WHERE clause in that case. Thanks in advance for any help, cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org

Re: [SQL] sum of agreggates in one SELECT?

as it demonstrates that (as in perl) there is sometimes more than one way to do it. Your solution works fine, and along the way I learned to use temporary tables. Thanks a lot for your input, cheers, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org How's my posting? Call 1-800-DEV-NULL

Re: [SQL] sum of agreggates in one SELECT?

On Tue, Sep 19, 2000 at 11:06:06AM -0700, Josh Berkus wrote: > > Unfortunately, Louis-David, I don't see any way around subselects in the > FROM clause as Tom mentions, which are not currently supported. I'd > suggest using a Function to create a temporary table or view

[SQL] no ORDER BY in subselects?

ot;order" Aren't ORDER BY clauses allowed in subselects? -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Black holes are where God divided by zero.

[SQL] ERROR: replace_vars_with_subplan_refs (!?)

ROR: replace_vars_with_subplan_refs: variable not in subplan target list Thanks, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org Marijuana is nature's way of saying, "Hi!".

Re: [Fwd: Re: [SQL] no ORDER BY in subselects?]

On Wed, Sep 20, 2000 at 09:20:25AM -0700, Josh Berkus wrote: > > At 15:23 20/09/00 +0200, Louis-David Mitterrand wrote: > > > > > >ERROR: parser: parse error at or near "order" > > > > > >Aren't ORDER BY clauses allowed in subselects? &g

[SQL] missing "day(s)" in interval (was: Convert from Seconds-Since-Epoch to Timestamp)

--- > 15:05:21 > (1 row) By the way, is it normal that the "day" word doesn't appear in the interval? auction=# select interval(now()::abstime::int4); interval ---- 30 years 9 mons 3 15:42:09 (1 row) -- Louis-David Mitterra

[SQL] knowing which columns have beend UPDATEd inside a TRIGGER?

Hello, Is there a way to know which columns are being UPDATEd or INSERTEd from inside a trigger, either in C or pl/pgsql? Thanks in advance, -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org If at first you don't succeed, redefine success.

Re: [SQL] knowing which columns have beend UPDATEd inside a TRIGGER?

On Tue, Oct 24, 2000 at 06:51:03PM -0400, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > Is there a way to know which columns are being UPDATEd or INSERTEd from > > inside a trigger, either in C or pl/pgsql? > > Huh? An INSERT always inserts

[SQL] Re: Compiling "C" Functions

On Thu, Dec 28, 2000 at 09:36:57AM -0500, Ron Peterson wrote: > Tulio Oliveira wrote: > > > > I appreciate any "C" Function complete samples, including de command > > line for > > the compiler. > > I've attached a generic GNU make snippet for compiling .so files. > Adjust to suite your tastes.

[SQL] Re: Maybe a Bug, maybe bad SQL

On Wed, Mar 21, 2001 at 10:49:41AM -0500, Bruce Momjian wrote: > > Note also that it's a mailing list cultural thing: many lists operate > > in a 'post only to the list' mode. Those of us on the pgsql lists do the > > 'list and person' thing, in response to direct questions, for the reasons > > Br

Re: [GENERAL] Re: [SQL] Permissons on database

On Wed, Mar 07, 2001 at 03:40:44PM -0500, Roland Roberts wrote: > > "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes: > > bk> How do I grant permissions on everything in the selected > bk> databes? > > bk> GRANT doesnt take as on object database name nor does it > bk> accept

[SQL] Alter Table problems

I am attempting to add a few attributes to an existing table that already contains data. The problem that I am having is that the new attributes that I want to add need to be forgein key references to other tables. I can't figure out how to create the attribute with a reference to another table.

[SQL] Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement

NOTE: I did a moderate search through the PgSQL mail list archives, but couldn't find an example of a question where both ORDER BY and aggregation were used. In all examples it was possible to use either one or the other with a possible subselect. [Keep reading...I discovered how to do this wit

[SQL] Re: Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement

, not finding a real-world solution. :^) Thanks! Dave On Mon, May 07, 2001 at 12:27:59PM -0500, David D. Kilzer wrote: > NOTE: I did a moderate search through the PgSQL mail list archives, > but couldn't find an example of a question where both ORDER BY and > aggregation were used

Re: [SQL] Using ORDER BY with AGGREGATE/GROUP BY in a SELECT statement

al_and ( SFUNC1 = booland, BASETYPE = bool, STYPE1 = bool, INITCOND1 = 't' ); Obviously, this concept could be extended for a logical 'OR' function and aggregate. Dave On Fri, May 11, 2001 at 12:33:52PM -0400, Tom Lane wrote: > "David D. Kilz

[SQL] Query optimizing - paradox behave

haracter varying(80) | The times of the processes are escape-eliminated by statistical methods. I determined that the "compare" database is 8% times faster than the new restructured "pacs" database. How can I understand this? Whats my mistake? Anybody who can make some

[SQL] Query optimizing - paradox behave

ying(128) | patientoid | character varying(80) | The times of the processes are escape-eliminated by statistical methods. I determined that the "compare" database is 8% times faster than the new restructured "pacs" database. How can I understand this? Whats

[SQL] Re: Query optimizing - paradox behave

to me? Why is the usertime increased and the whole time is decreased? # Anyway .. Thanks all a lot for Your effort. I will now tune my radiology-database further... Thankful Greetings David "David M. Richter" <[EMAIL PROTECTED]> writes: > The query with the 3 tables is fast

[SQL] Re: Query optimizing - paradox behave

function, wich improves the speed dramatically? Do You know some database options wich I could change for better performance? Thanks a lot for Your suggestions!! David >What version are you using? (dbPG95GetIndex?) begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Rich

[SQL] View consistency

performance problems. Any hints and facts? Thanks in advance David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1

[SQL] I could do with some guidance

Hi All; Trying to set up a database with postgres. below is what I have so far... In the example I want board to be filled with; Secretary|Goble|David|11/05/1970|280198R How do I do this? Also is what I have so far the best way for this database? Later I will want to write an inferface for it

[SQL] table restruction

ged schema and then put the data from the dump back? So this would spent a lot of time, otherwise it works sure. Is there any oppertunity to restructure the existing database without using a dump? Thanks in advance David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:

Re: [SQL] table restruct...

physical order of the rows in a database? Is the order of a database under all circumstances the same? (in pg) That is essential for my further restruction of the database... Thanks a lot David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla

[SQL] Modify column type

Is there a way to modify the type of an existing column? All I actually want to do is extend the length of the type from a VARCHAR(6) to VARCHAR(100). Column | Type | Modifiers +---+ ema

Re: [SQL] how to get decimal to date form

the TO_CHAR() function. I strongly suspect your app is not Y2K-compliant. [I haven't written that for years!] -- Regards, Dave [RLU#314465] == [EMAIL PROTECTED] (David W Noon) Remove spam trap to

[SQL] Insert psql commands inside a script

ht on the same line.  I use very short psql commands and would like to do it all with 1 file. Thank You David THIS COMMUNICATION MAY CONTAIN CONFIDENTIAL AND/OR OTHERWISE PROPRIETARY MATERIAL and is thus for use only by the intended recipient. If you received this in error, please contact the s

[SQL] selecting latest record

Hi, I have a simple table price(id_product, price, date) which records price changes for each id_product. Each time a price changes a new tuple is created. What is the best way to select only the latest price of each id_product? Thanks, -- Sent via pgsql-sql mailing list (pgsql-sql@postgre

Re: [SQL] selecting latest record

On Tue, Sep 22, 2009 at 11:56:54AM +0200, Pavel Stehule wrote: > > there are more ways - depends on what you wont. > > one way is > > SELECT * >FROM price > WHERE (id_product, date) = (SELECT id_product, max(date) >FROM price

Re: [SQL] simple (?) join

required. -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- 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] simple (?) join

On Thu, 24 Sep 2009 16:15:07 -0400, justin wrote about Re: [SQL] simple (?) join: > > >David W Noon wrote:On Thu, 24 Sep 2009 16:16:36 +0100, Gary Stainburn >wrote about [SQL] simple (?) join: > > create table orders ( >o_id serial primary key >... >); > >create

Re: [SQL] simple (?) join

] -- Regards, Dave [RLU #314465] === david.w.n...@ntlworld.com (David W Noon) === -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription:

Re: [SQL] simple (?) join

CT MAX(ol_timestamp) FROM orders_log AS ol2 WHERE ol2.o_id = o.o_id); This will omit orders that have never been logged. You have not specified what you want done with them. -- Regards, Dave [RLU #314465] === david.w.n...@n

[SQL] 'image' table with relationships to different objects

Hello, In my database I have different object types (person, location, event, etc.) all of which can have several images attached. What is the best way to manage a single 'image' table with relationships to (potentially) many different object types while keeping referrential integrity (foreign ke

Re: [SQL] 'image' table with relationships to different objects

On Tue, Feb 09, 2010 at 11:59:14AM +, Richard Huxton wrote: > On 09/02/10 07:49, Louis-David Mitterrand wrote: > >Hello, > > > >In my database I have different object types (person, location, event, > >etc.) all of which can have several images attached. > > &g

Re: [SQL] 'image' table with relationships to different objects

On Tue, Feb 09, 2010 at 08:01:35AM -0700, Rob Sargent wrote: > You can also invert this, making all the image owner share a common base > table and then images are dependent on that base > > base (id, type) where type is an enumeration or some such > person (id, name, etc) where id is FK to base i

[SQL] "left join" not working?

Hi, This query: select c.id_currency,max(p.modified_on) from currency c left join price_line p using (id_currency) where p.id_line=1 group by c.id_currency; doesn't list all c.id_currency's, only those with a price_line. However this one does: select c.id_currency,max(p.modified_on) from curren

Re: [SQL] "left join" not working?

On Fri, Feb 12, 2010 at 11:35:02AM -, Oliveiros C, wrote: > My first guess is that > NULL fails the condition on your WHERE clause, > p.id_line = 1 > > So your WHERE clause introduces an additional level of filtering > that filters out the NULLs coming from the LEFT JOIN... So, if I understan

[SQL] complex join question

Hi, Here is my schema: cruise_line -> ship(id_line) -> cruise_type(id_ship) -> cruise(id_cruise_type) -> price(id_cruise, id_currency) <- currency (USD,GBP,EUR,CAD) (a 'cruise' is a 'cruise_type' + a date) I am trying to display a count of cruise's for each ship and each currency even

[SQL] UNION or LEFT JOIN?

Here is the basic schema: -->id_ship>--- || [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE] It's a database of cruise prices. Each 'price' object has a reference to 'cabin' an

Re: [SQL] UNION or LEFT JOIN?

On Tue, Feb 16, 2010 at 03:33:23PM +, Oliveiros wrote: > Louis-David, > > Please advice me, Hi Oliveiros, > if some cabin doesn't have a price i.e. it is not available, is there some > way to tell > which cruise it belongs to? In fact a cabin belongs to a ship and

Re: [SQL] UNION or LEFT JOIN?

On Tue, Feb 16, 2010 at 09:38:19PM +, Tim Landscheidt wrote: > Louis-David Mitterrand wrote: > > > Here is the basic schema: > > > -->id_ship>--- > > |

[SQL] join with an array

Hi, I'm trying the following query: select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = any(array_agg)) group by t1.col1; but I get this error: ERROR: column "array_agg" does not exist I tried aliasing array_agg(t1.id) without success. Thanks for any suggestions, -- S

Re: [SQL] join with an array

On Wed, Feb 24, 2010 at 02:09:09PM +0100, A. Kretschmer wrote: > In response to Louis-David Mitterrand : > > Hi, > > > > I'm trying the following query: > > > > select array_agg(t1.id) from table1 t1 join table2 t2 on (t2.id = > > any(array_agg

[SQL] check constraint on multiple tables?

Hi, I've got this chain of tables: ship --> (id_ship) --> cabin_type --> (id_cabin_type) --> cabin_category --> (id_cabin_category) --> cabin The 'cabin' table has (cabin_number, id_cabin_category ref. cabin_category) How can I guarantee unicity of cabin_number per ship? For now I adde

Re: [SQL] check constraint on multiple tables?

On Wed, Mar 03, 2010 at 07:14:29AM -0800, Richard Broersma wrote: > On Wed, Mar 3, 2010 at 7:02 AM, Louis-David Mitterrand > wrote: > > > > What is the best solution? Adding an id_ship to 'cabin'? Or check'ing > > with a join down to 'ship'?

Re: [SQL] check constraint on multiple tables?

On Wed, Mar 03, 2010 at 07:29:22AM -0800, Richard Broersma wrote: > On Wed, Mar 3, 2010 at 7:19 AM, Louis-David Mitterrand > wrote: > > > CREATE TABLE cabin_type ( > > > CREATE TABLE cabin_category ( > > > CREATE TABLE cabin ( > > I'm just cur

Re: [SQL] check constraint on multiple tables?

On Wed, Mar 03, 2010 at 10:03:05AM -0600, Little, Douglas wrote: > Hello, > > I would have designed as ship > cabin (PK of ship_id, Cabin_id) > And a separate chain of cabin_type > cabin_category > cabin Ah, now I'm having second thoughts about my schema ;) > Type, and category are group classi

Re: [SQL] check constraint on multiple tables?

On Wed, Mar 03, 2010 at 04:05:43PM -, Oliveiros wrote: > > As your table names seem to imply, type and category are cabin's > characteristics , not ship characteristics. > Am I right? Yes and no :) - I'm scanning cruise line web sites with a spider to collect prices so I'm building cabin_

Re: [SQL] check constraint on multiple tables?

On Wed, Mar 03, 2010 at 10:13:48AM -0600, Little, Douglas wrote: > Hey Louis, > > Ship rooms are just like Hotel rooms. There are lots of ways to > describe. But there needs to be some consistency between the > classifiers for them to have any meaning. > > A junior suite should mean the same th

Re: [SQL] check constraint on multiple tables?

On Wed, Mar 03, 2010 at 10:29:44AM -0600, Little, Douglas wrote: > Louis, > Interesting discussion. Always fun to think about real world stuff. Indeed. > We have a similar problem for comparing hotel rooms. > So the issue is that you aren't originating the data, just classifying it. > I'd mov

[SQL] an aggregate to return max() - 1 value?

Hi, With builtin aggregates is it possible to return the value just before max(col)? Thanks, -- 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] an aggregate to return max() - 1 value?

On Thu, Mar 04, 2010 at 08:53:10PM +, Greg Stark wrote: > SELECT col FROM tab ORDER BY col DESC OFFSET 1 LIMIT 1 > > In 8.4 OLAP window functions provide more standard and flexibility > method but in this case it wouldn't perform as well: > > postgres=# select i from (select i, rank() over (o

[SQL] window function to sort times series data?

Hi, I have time series data: price(id_price int, price int, created_on timestamp) I'd like to select the latest price before, say, 2010-03-10 and the latest price after that date. Using "group by" and self-joins I was able to build a (quite large :) working query. But I wonder if there is a cl

<    1   2   3   4   5   >