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

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

[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] 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] 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

[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

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

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/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, 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

[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] constraint and ordered value

Daryl Richter wrote: >> No. A constraint only applies to one row at a time. If you try to >> work around >> this by calling a function that does queries it isn't guarenteed to >> work. >> And if you are thinking of calling a function that does a query, you >> aren't >> looking at saving time ove

[SQL] constraint and ordered value

Is it possible to use only CHECK constraint (and not triggers) to completely enforce ordered value of a column (colx) in a table? By that I mean: 1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on; 2. When deleting (or updating), "holes" must not be formed, e.g. if there a

[SQL] order and order line item

Suppose we have the usual order & order line item entities: CREATE TABLE "order" ( id INT PRIMARY KEY, date DATE NOT NULL ); CREATE TABLE orderlineitem ( id INT PRIMARY KEY, orderid INT REFERENCES "order"(id), seq INT NOT NULL, CONSTRAINT con1 UNIQUE (orderid, seq), produc

[SQL] Interpolation and extrapolation in SQL

On my first try, interpolation and extrapolation turns out to be pretty easy to do. In psql: -- the "lookup" table CREATE TABLE p ( x DOUBLE PRECISION NOT NULL UNIQUE, y DOUBLE PRECISION NOT NULL ); INSERT INTO p VALUES (1,1); INSERT INTO p VALUES (2,5); INSERT INTO p VALUES (5,14); INSERT I

Re: [SQL] olympics ranking query

Bruno Wolff III wrote: On Fri, Aug 20, 2004 at 23:40:08 +0700, David Garamond <[EMAIL PROTECTED]> wrote: Challenge question: is there a simpler way to do query #1 (without any PL, and if possible without sequences too? You could use a subselect to count how many countries had a lower

Re: [SQL] olympics ranking query

Tom Lane wrote: Challenge question: is there a simpler way to do query #1 (without any PL, and if possible without sequences too? Can't without sequences AFAIK, but you certainly can do it simpler: select setval('seq1', 0); select nextval('seq1'), * from (select count(*) as numranker, gold, silve

[SQL] olympics ranking query

See http://www.athens2004.com/en/OlympicMedals/medals?noc=MGL . create table countrymedal ( countryid CHAR(3) PRIMARY KEY, gold INT NOT NULL, silver INT NOT NULL, bronze INT NOT NULL); COPY countrymedal (countryid, gold, silver, bronze) FROM stdin; ITA 5 6 3 FRA 5

Re: [SQL] Displaying two tables side by side

Andreas Haumer wrote: You could try to use PosgreSQL's ctid system column to join on like this: test=# select *,ctid from t1; a | b | ctid - ---+---+--- 2 | 2 | (0,1) 3 | 5 | (0,2) 4 | 7 | (0,3) 9 | 0 | (0,4) test=# select *,ctid from t2; c | d | ctid - ---+---+--- 4 | 5 | (0,1) 7 |

[SQL] Displaying two tables side by side

How can you display two tables side by side? Example: > select * from t1; a | b ---+--- 2 | 2 3 | 5 4 | 7 9 | 0 > select * from t2; c | d ---+--- 4 | 5 7 | 3 3 | 2 1 | 1 2 | 0 Intended output: a | b | c | d ---+---+---+--- 2 | 2 | 4 | 5 3 | 5 | 7 | 3 4 | 7 | 3 | 2 9 | 0 | 1 | 1 |

Re: [SQL] surrogate key or not?

Josh Berkus wrote: > Given: Surrogate keys, by definition, represent no real data; > Given: Only items which represent real data have any place in > a data model > Conclusion: Surrogate keys have no place in the data model But, once a surrogate key is assigned to a row, doesn't it become

[SQL] determining how many products are in how many categories

# product table (simplified): create table p ( id char(22) not null primary key, name text, desc text ); # product category table (simpl.): create table pc ( id char(22) not null primary key, name text, desc text ); # table that maps products into categories: create table p_pc ( id ch