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
=
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
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
ice;
END LOOP;
RETURN i;
Thanks for your input,
--
Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org
"Faith strikes me as intellectual laziness." -- Robert A. Heinlein
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.
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!".
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
---
> 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
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.
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
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.
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
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
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
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
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
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
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
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
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
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
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
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 CAN be associated to a 'cr
On Tue, Feb 16, 2010 at 09:38:19PM +, Tim Landscheidt wrote:
> Louis-David Mitterrand wrote:
>
> > Here is the basic schema:
>
> > -->id_ship>---
> > |
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
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
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
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'?
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
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
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_
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
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
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
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
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
On Wed, Mar 24, 2010 at 03:29:36PM +0100, A. Kretschmer wrote:
> In response to Louis-David Mitterrand :
> > Hi,
> >
> > I have time series data: price(id_price int, price int, created_on
> > timestamp)
> >
> > I'd like to select the latest price befo
On Wed, Mar 24, 2010 at 05:29:46PM +0100, Andreas Kretschmer wrote:
> A. Kretschmer wrote:
>
> Well, and now i'm using 8.4 windowing-functions:
>
> test=*# select * from price order by price_id, d;
> price_id | price | d
> --+---+
> 1 |10 | 2010-03-12
>
On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote:
> Petru Ghita writes:
> > "..immediately replaced with the function value" doesn't mean that the
> > results of a previously evaluated function for the same parameters are
> > stored and reused?
>
> No, it means what it says: the function
On Thu, Mar 25, 2010 at 08:27:27AM +0100, Pavel Stehule wrote:
> 2010/3/25 Louis-David Mitterrand :
> > On Sat, Mar 06, 2010 at 04:31:55PM -0500, Tom Lane wrote:
> >> Petru Ghita writes:
> >> > "..immediately replaced with the function value" doesn't
Hi,
I have times series data in a 'price' table:
price(id_price, price, id_product, created, modified)
Prices are polled daily and a new 'price' row is created only if the
price of id_product changes, else modified is updated to now().
Now, I'd like to make a graph of average prices per week, p
On Wed, Apr 14, 2010 at 08:46:13AM -0700, Richard Broersma wrote:
> On Wed, Apr 14, 2010 at 7:54 AM, Louis-David Mitterrand
> wrote:
>
> > Now, I'd like to make a graph of average prices per week, per
> > id_product. As some prices don't vary much, distribution wou
On Wed, Apr 14, 2010 at 06:06:59PM +0200, Pavel Stehule wrote:
> I don't understand well. Why you don't use a function date_trunc(),
>
> select date_trunc('week', created), count(*)
> from price
> group by date_trunc('week', created)
Because if a price doesn't change for more than a week, then so
Hi,
I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and
am in the process of developping a pager to let users leaf through it
(30K rows).
Ideally I'd like to know when requesting any 'page' of data where I am
within the dataset: how many pages are available each way, etc.
Of
On Wed, May 12, 2010 at 12:26:17PM -0400, Justin Graf wrote:
> oops typos
> On 5/12/2010 1:41 AM, Louis-David Mitterrand wrote:
> > Hi,
> >
> > I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and
> > am in the process of developping a page
On Thu, May 13, 2010 at 11:36:53AM +0400, silly sad wrote:
> On 05/12/10 09:41, Louis-David Mitterrand wrote:
> >Hi,
> >
> >I have a large dataset (page 1 at http://www.cruisefish.net/stat.md) and
> >am in the process of developping a pager to let users l
Hi,
I have this function which swaps primary keys for cabin_types (so that
id_cabin_type ordering reflects natural data ordering):
CREATE OR REPLACE FUNCTION swap_cabin_types(id1 integer, id2 integer)
RETURNS integer
AS $$
declare
tmp integer;
On Mon, May 24, 2010 at 10:51:01AM +0200, Louis-David Mitterrand wrote:
> Hi,
>
> I have this function which swaps primary keys for cabin_types (so that
> id_cabin_type ordering reflects natural data ordering):
Actually this function works fine. My problem was elsewhere. Sorry for
ba
On Mon, May 24, 2010 at 02:38:39PM +, Tim Landscheidt wrote:
> Louis-David Mitterrand wrote:
>
> What does "are not properly updated" mean? Anyhow, why don't
Hi,
I did follow-up on my own post: the problem was elsewhere.
> you use something simple like (untested
On Mon, May 24, 2010 at 07:00:30PM +0200, Louis-David Mitterrand wrote:
> On Mon, May 24, 2010 at 02:38:39PM +, Tim Landscheidt wrote:
> > you use something simple like (untested):
> >
> > | UPDATE cabin_type
> > | SET id_cabin_type =
> > | CASE
> >
Hi,
On time series price data I'm trying to remove consecutive identical
prices and keep only the latest. I tried:
delete from price where id_price in (select t.id_price2 from (select
first_value(p.id_price) over w as id_price1,
nth_value(p.
On Tue, Nov 23, 2010 at 03:31:59PM -, Oliveiros d'Azevedo Cristina wrote:
> Salut, Louis-David,
>
> Can you please state the columns belonging to price table
> and give a concrete example?
> Say, data before and data after you want to do?
Hi Cristina,
Data before:
id_price | price
On Tue, Nov 23, 2010 at 04:19:18PM +0100, Louis-David Mitterrand wrote:
> Hi,
>
> On time series price data I'm trying to remove consecutive identical
> prices and keep only the latest. I tried:
>
> delete from price where id_price in (select t
Hi,
I'm planning to centralize all db maintenance jobs from a single
pl/pgsql function called by cron every 15 minutes (highest frequency
required by a list of jobs). In pseudo code:
CREATE or replace FUNCTION cron_jobs() RETURNS void LANGUAGE plpgsql AS $$
DECLARE
rec record;
BEGIN
/*
On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote:
> On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote:
> > Hi,
> >
> > I'm planning to centralize all db maintenance jobs from a single
> > pl/pgsql function called by cron every 15 minutes (highest fre
On Mon, Nov 19, 2012 at 08:31:10AM +0800, Craig Ringer wrote:
> On 11/19/2012 01:11 AM, Louis-David Mitterrand wrote:
> > On Sun, Nov 18, 2012 at 07:27:54PM +0800, Craig Ringer wrote:
> >> On 11/18/2012 12:19 AM, Louis-David Mitterrand wrote:
> >>> Hi,
> >>&g
Hello,
I've got the following tables:
person:
- id_person
- firstname
- lastname
- type
person_to_type:
- id_person references person
- type references person_type;
person_type:
- type
"person_type" contains differents caracteristics for a person (actor,
director, author, etc.) who can have s
Hello,
I've got a table of shows with different types (movie, theater,
ballet,etc.) and I am looking for a select that can return the 10 last
entered shows AND at most 2 of each type. Is that possible in one query?
The table looks basically like:
created_on | timestamp without tim
On Sat, Feb 17, 2007 at 07:21:40PM +0100, M.P.Dankoor wrote:
> Louis-David Mitterrand wrote:
> >
> I thought of another solution, actually it's of those top n query tricks
> that I picked up somewhere, can't remember
> where.
> Assuming that your table is ca
Hello,
I'm considering implementing a search box on my review web site
http://lesculturelles.net and am looking for a simple way to match
entered words against several columns on related tables: show.show_name,
story.title, person.firtname, person.lastname, etc.
What is the most elegant way to
On Fri, Feb 23, 2007 at 10:01:22AM -0800, chester c young wrote:
> > I'm considering implementing a search box on my review web site
> > http://lesculturelles.net and am looking for a simple way to match
> > entered words against several columns on related tables:
> > show.show_name, story.title,
On Fri, Feb 23, 2007 at 01:31:14PM -0500, Joe wrote:
> Hello Louis-David,
>
> On Fri, 2007-02-23 at 17:27 +0100, Louis-David Mitterrand wrote:
> > I'm considering implementing a search box on my review web site
> > http://lesculturelles.net and am looking for a simple
Hello,
I've got a table that looks basically like:
id_show | serial
show_name | text
show_type | text
created_on | timestamp without time zone
When looking at a row with an id_show of value n, I'd like to have an
Hi,
To build a threaded forum application I came up the following schema:
forum
--
id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
id_parent| integer|
subject | text | not null
message | text |
Each message a unique id_forum and an id_parent pointing to
On Wed, May 09, 2007 at 02:24:22PM +0100, Gregory Stark wrote:
> "Louis-David Mitterrand" <[EMAIL PROTECTED]> writes:
>
> > Each message a unique id_forum and an id_parent pointing to the replied
> > post (empty if first post).
> >
> > How can I buil
On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote:
> Hi,
>
> To build a threaded forum application I came up the following schema:
>
> forum
> --
> id_forum | integer| not null default nextval('forum_id_forum_seq'::regclass)
> id_p
On Wed, May 09, 2007 at 04:30:21PM +0200, Louis-David Mitterrand wrote:
> On Wed, May 09, 2007 at 02:55:20PM +0200, Louis-David Mitterrand wrote:
> > Hi,
> >
> > To build a threaded forum application I came up the following schema:
> >
> > forum
> >
On Thu, May 10, 2007 at 09:49:32AM +1000, Robert Edwards wrote:
>
> Hi Louis-David,
>
> I also have written a forum application using PostgreSQL.
>
> My schema has a "threadid" for each posting, which is actually also the
> "messageid" of the first posting in the thread, but that is irrelevant.
Hello,
In my forum app a new post can be related to several types of objects:
person, location, event, simple text subject, etc. so in my 'forum'
table I plan to add an id_subject column which can contain a reference
to any number of different tables (location, person, etc.). What I need
to kn
On Tue, Jul 24, 2007 at 03:10:44PM +0100, Gregory Stark wrote:
> "Louis-David Mitterrand" <[EMAIL PROTECTED]> writes:
>
> > Can I use a another column to store the type of the id_subject (ie: the
> > tabled it belongs to) ? Then I would be able to query that
Hi,
I'm trying to protect created_by and created_on columns from accidental
update. Most of my tables inherit from a 'source' table that has those
columns, so I was thinking of creating a rule or trigger that does
nothing on update to these columns. But apparently rules and triggers
don't appl
Hi,
I've got this trigger to clean up text entered in web forms:
CREATE or replace FUNCTION sanitize_text() RETURNS "trigger"
AS $$
declare
begin
if old.story is not null and new.story != old.story
then
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote:
> I would do something like this (not tested, but conceptually working):
Hello,
> BEGIN
> if old.story is not null and new.story != old.story then
> new.story = sanitize_text(new.story);
> end if;
> --checks on other field can be inc
On Tue, Nov 20, 2007 at 11:56:02AM -0500, Tom Lane wrote:
> Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> > I'd like to use it on other tables an columns but how can the column
> > name be dynamic inside the procedure.
>
> It can't --- plpgsql has no
On Wed, Nov 21, 2007 at 09:14:14AM +0100, Bart Degryse wrote:
> I would do something like this (not tested, but conceptually working):
>
> CREATE or replace FUNCTION sanitize_text(webtext IN text, cleantext OUT text)
> AS
> $body$
> BEGIN
> cleantext = translate(webtext, E'\x92\x96', '''-');
> c
Hi,
I've got this ugly case statement that I'd like to hide in a function:
select
...
case when
d.start_date <= CURRENT_DATE and
case when w.show_type in ('cinéma','livre')
then d.start_date >= CURRENT_DATE - 21
On Mon, Dec 17, 2007 at 12:27:34PM -0500, Rodrigo De León wrote:
> On 12/17/07, Louis-David Mitterrand <[EMAIL PROTECTED]> wrote:
> > I've got this ugly case statement that I'd like to hide in a function:
>
> Why don't you hide the entire query in a VIEW?
Hi,
is there a way to return a Pg array as a list of single-column row
values?
I am trying to circumvent DBI's lack of support for native database
arrays and return the list of values from an ENUM as a perl array.
Thanks,
---(end of broadcast)--
On Sun, Dec 23, 2007 at 10:19:26PM +0100, Pavel Stehule wrote:
> Hello
>
> try
>
> create or replace function unpack(anyarray)
> returns setof anyelement as $$
> select $1[i]
> from generate_series(array_lower($1,1), array_upper($1,1)) g(i);
> $$ language sql;
>
> postgres=# select * from
On Sun, Dec 23, 2007 at 10:27:09PM +0100, Pavel Stehule wrote:
> On 23/12/2007, Louis-David Mitterrand
> <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > is there a way to return a Pg array as a list of single-column row
> > values?
> >
> > I am tryin
On Wed, Nov 07, 2007 at 10:35:08AM -0500, Tom Lane wrote:
> "Amitanand Chikorde" <[EMAIL PROTECTED]> writes:
> > I want to convert MAX(mydate) from myTable to Epoch.
>
> Do you mean
> SELECT EXTRACT(EPOCH FROM MAX(mydate)) FROM myTable
Is using casts for the same purpose deprecated?
Hello,
I'm looking for a more efficient way of dynamically categorizing some
events. The following view definition looks into each event's latest
event_date object (a theater play can have several, a book only one) to
tell whether the event is current, past or future:
SELECT s.id_event_su
On Thu, Jun 26, 2008 at 05:06:14PM +0200, Marc Mamin wrote:
>
> Hello,
Hi Marc,
> I guess that the time offsets (now-21 and now-28) are evaluated each
> time the corresponding condition is met.
Excellent suggestion, this makes the query ~ 15% faster. Every bit
counts.
> It may be faster to put
Hi,
I've got five related tables:
- person_type:
id_person_type integer
type_fr text
- person:
id_person integer
...
- person_to_event:
id_person -> person
id_person_type -> person_type (e.g: actor, di
On Mon, Sep 22, 2008 at 04:34:14PM +0200, Louis-David Mitterrand wrote:
> Hi,
>
> I've got five related tables:
>
> - person_type:
> id_person_type integer
> type_fr text
>
> - person:
> id_person integer
On Mon, Sep 22, 2008 at 09:39:08AM -0700, Mark Roberts wrote:
>
> Taking your second email into account, I came up with:
>
> select distinct pt.type_fr
> from person_to_event pte
> inner join person_type using (id_person_type)
> where id_person_type in (
> select id_person_type
> from
the id of the event you wanna exclude
> join event_type et
> ON e.id_event_type = et.id_event_type
> where et.type_fr='théâtre'
> GROUP BY pt.type_fr
> HAVING SUM(e2.id_event) IS NULL;
>
> - Original Message - From: "Louis-David Mitterrand"
> <[EMAI
Hi,
Say you have several objects (tables): person, location, event, etc. all
of which can have several images attached.
What is the best way to manage relations between a single 'image' table
and these different objects?
For now each 'image' row has pointers to id_person, id_location,
id_event,
On Mon, Oct 06, 2008 at 09:25:09AM -0400, Dan McFadyen wrote:
> Hello,
>
> Simplest way I can think of is create 3 relation tables, a person/image
> table, location/image table and event/image table.
>
> Each is just made up for 2 foreign keys to the first ID and image ID,
> using both as the PK
On Tue, Oct 07, 2008 at 05:16:39PM -0700, Steve Midgley wrote:
>
> I think the relationship tables method works pretty well but I have
> another suggestion. You could store the Foreign table name within image
> table as well as the Foreign key.
>
> |id|image_url|f_table|f_key
> |1 |url..|pe
Hi,
This query:
select p.id_person, person_name(p), array_accum(distinct pt.type_fr)
from person p
left join person_to_event x using (id_person)
left join person_type pt using (id_person_type)
where person_name(p) ilike '%wil
On Sat, Dec 06, 2008 at 06:26:06PM +0100, Andreas Kretschmer wrote:
> Louis-David Mitterrand <[EMAIL PROTECTED]> schrieb:
> >
> > But if I append this
> >
> > order by pt.type_fr = 'comédien';
> >
> > I get this error:
> >
&g
On Sat, Dec 06, 2008 at 06:24:25PM +, John Lister wrote:
> >(still curious about the "must be used in an aggregate function" error
> >though... because I do use it in an aggregate)
>
> You're original query grouped on the person id and name, therefore you
> can only return (and order by) thes
Hi,
I have an 'event' table and an 'event_date' table pointing to it
containing (potentially) several event dates (start and (optionnaly)
end) for the event in the past, present and future.
I'm trying to build a query to select the most "relevant" date:
'current' or 'next' or 'last' (by order of
On Sun, Dec 14, 2008 at 02:51:24PM -0800, Michal Szymanski wrote:
> On 14 Gru, 20:22, vindex+lists-pgsql-...@apartia.org (Louis-David
> Mitterrand) wrote:
> >
> > I have an 'event' table and an 'event_date' table pointing to it
> > containing
Hi,
I'd like to find a way to archive versions of my data in an elegant and
extensible way.
When a user modifies certain entries I'd like the database to keep the
previous versions (or a limited, definable number of versions).
Wiki-style.
Would that be a good use of postgres' arrays?
So I'm lo
sdfsdfsdfsdf | 2001-04-10 15:00:00+02
(3 rows)
But the problem is that I now get three rows when I only want the max()
item.
How should I do it?
Thanks in advance,
--
Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
"Logiciels libres : nourris au code source sans f
sing:
SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1;
which seems the best solution (I was a bit concerned about performance,
but then again the max() aggregate does a scan of all rows as well).
Cheers,
--
Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
This is Linux Country. On a quiet night you can hear Windows NT reboot.
then if you
omit the clause from the outer select you risk having a false match if
two records have the same stopdate:
SELECT title,login,stopdate
FROM auction
WHERE stopdate = (SELECT max(stopdate) FROM auction WHERE login LIKE 'm%');
> Tom's solution has the drawback, tha
for a "standard" way of doing this if such a thing
exists.
TIA
--
Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.fr
"For a list of the ways which technology has failed to improve our
quality of life, press 3."
100 matches
Mail list logo
