Re: [SQL] exclusion constraint for ranges of IP

[Herouth Maoz]

My thanks to everyone who replied.

I have decided not to implement that constraint at this time. Using a compound 
type will make the system more complicated and less readable, plus requires 
installing the package which is beyond vanilla PostgreSQL.

Now I have another exclusion constraint I'm thinking about in another and I 
want to verify that this will do what I mean it to do:

CREATE TABLE invoice_definitions
(
id  SERIAL  PRIMARY KEY NOT NULL,
customer_id INTEGER NOT NULL REFERENCES customers(id),
is_default  BOOLEAN NOT NULL DEFAULT FALSE,
bill_descriptionVARCHAR(100)NOT NULL,
itemized_description VARCHAR(100)   NOT NULL,
EXCLUDE USING GIST  ( customer_id WITH =, is_default WITH AND )
)
;

Basically, each customer can have several rows in this table, but only one per 
customer is allowed to have is_default = true. Is this exclude constraint 
correct?

TIA,
Herouth

[SQL] WITH RECURSIVE question

[Julien Cigar]

Hello,

I have a classic parent -> child relation (id, container_id) and I would 
like to find the full hierarchy for a child, something like


  A
 / \
B   C
|
D

given D I want {A,B,C}

WITH RECURSIVE hierarchy(level, container_id, titles, containers) AS 
(SELECT 1 AS level, container_id, ARRAY[title::text] AS titles,

ARRAY[container_id] AS containers
 FROM content
 WHERE id=984
UNION ALL
 SELECT hierarchy.level + 1, c.container_id,
array_prepend(c.title::text, titles),
array_prepend(c.container_id, containers)
 FROM content c
 JOIN hierarchy ON hierarchy.container_id = c.id
) SELECT titles, containers
  FROM hierarchy
  ORDER BY level DESC LIMIT 1;

which give me something like:

titles|  containers
--+--
 {Home,Templates,IAS} | {NULL,1,983}
(1 row)

This is exactly what I want, but I wondered if there are better ways to 
do it? Is it scalable?


(The final idea is to put that in a trigger, so that it will be executed 
only when a new row is added, or when the row is moved from a container 
to another one ..)


Thanks,
Julien

--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
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] exclusion constraint for ranges of IP

[Samuel Gendler]

On Tue, Aug 23, 2011 at 1:27 AM, Herouth Maoz  wrote:

> My thanks to everyone who replied.
>
> I have decided not to implement that constraint at this time. Using a
> compound type will make the system more complicated and less readable, plus
> requires installing the package which is beyond vanilla PostgreSQL.
>
> Now I have another exclusion constraint I'm thinking about in another and I
> want to verify that this will do what I mean it to do:
>
> CREATE TABLE invoice_definitions
> (
> id  SERIAL  PRIMARY KEY NOT NULL,
> customer_id INTEGER NOT NULL REFERENCES customers(id),
> is_default  BOOLEAN NOT NULL DEFAULT FALSE,
> bill_descriptionVARCHAR(100)NOT NULL,
> itemized_description VARCHAR(100)   NOT NULL,
> EXCLUDE USING GIST  ( customer_id WITH =, is_default WITH AND )
> )
> ;
>
> Basically, each customer can have several rows in this table, but only one
> per customer is allowed to have is_default = true. Is this exclude
> constraint correct?
>

You can validate this yourself with 3 insert statements into the table
declared in your email.

Re: [SQL] Confused about writing this stored procedure/method.

[Jasen Betts]

On 2011-08-22, JavaNoobie  wrote:
> Hi All,
> I'm trying to write a stored procedure /function to re-order a set of
> calendar months.I have a set of calendar months stored from January to
> December in my tables. And as of now when I do order by on this column  ,
> the data is ordered alphabetically , starting April, august  etc. and so on
> I want to order these months starting from April through March in order to
> sync with the financial calendar . I'm trying to write a stored procedure to
> do the same (I'm not aware of any other method that Postgres offers  this
> reordering , if there's any , please do let me know!).

 order by (case month when 'January' then 1 when 'February' then 2 
 ...[I'm too lazy to type the rest]...  when 'December' then 12 end)
   
get the idea? (except change the numbers to match financial calendar)

you can index on that expression too

if you have them as numbers instead of words you can use an array
instead of the case.

For as task like this an SQL function may be more efficient than a
PLPGSQL function.


-- 
⚂⚃ 100% natural


-- 
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] exclusion constraint for ranges of IP

[Jasen Betts]

On 2011-08-23, Herouth Maoz  wrote:

> EXCLUDE USING GIST  ( customer_id WITH =, is_default WITH AND )


> Basically, each customer can have several rows in this table, but only =
> one per customer is allowed to have is_default =3D true. Is this exclude =
> constraint correct?

I don't really understand exclude, but instead of EXCLUDE... I would do

CREATE UNIQUE INDEX "invoice_definitions-unique-default" 
  ON invoice_definitions(customer_id) WHERE is_default;

Which would create a smaller (and probably faster) BTREE index 
containing only the rows with is_default true.

There seems to be no way to create this in the create-table 
command. (using 8.4 here)


-- 
⚂⚃ 100% natural


-- 
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] exclusion constraint for ranges of IP

[Herouth Maoz]

On 23/08/2011, at 13:31, Jasen Betts wrote:

> On 2011-08-23, Herouth Maoz  wrote:
> 
>>EXCLUDE USING GIST  ( customer_id WITH =, is_default WITH AND )
> 
> 
>> Basically, each customer can have several rows in this table, but only =
>> one per customer is allowed to have is_default =3D true. Is this exclude =
>> constraint correct?
> 
> I don't really understand exclude, but instead of EXCLUDE... I would do
> 
> CREATE UNIQUE INDEX "invoice_definitions-unique-default" 
>  ON invoice_definitions(customer_id) WHERE is_default;
> 
> Which would create a smaller (and probably faster) BTREE index 
> containing only the rows with is_default true.

This is an interesting concept.  It's a different angle on the same condition.

> 
> There seems to be no way to create this in the create-table 
> command. (using 8.4 here)

Yes, it's curious that exclude constraints are the only ones which are allowed 
to be partial in a table definition.

Thank you.

Herouth
-- 
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] WITH RECURSIVE question

[Julien Cigar]

mmh I made a little mistake: it was {A,B} of course, not {A,B,C}

On 08/23/2011 12:03, Julien Cigar wrote:

given D I want {A,B,C}



--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
<>
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] need magic to shuffle some numbers

[Andreas]

Hi,

there is a table that has among others a integer primary key "id" and 
another integer column "prio" as well as an integer "group_id".


I'd like to invert the values of the prio-column for one of the groups.
The prio numbers start with 3 and there are 1159 different prios in this 
group.

At least every value appeares only once.   :)

Is there an elegant way to switch the prio values around so that every 
record with the first prio gehts the last and vice versa?
Then the records with the second smallest prio get the second-to-last 
biggest value and v.v.

...


regards

--
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] need magic to shuffle some numbers

[Samuel Gendler]

I don't have time to experiment with actual queries, but you can use the
rank() window function to rank rows with prio sorted ascending, and do the
same thing to rank rows with prio sorted descending, and update rows with
the value from the second where the rank matches the rank from the first.
 I'm guessing that you can't use a window function in an update directly, so
you'll likely need to structure things as several subselects, but the basic
principle is sound, I think.  If you do have more than one row with the same
value, you can use row_number() instead of rank() in order to get unique
'rank' for rows that have the same value.

Window functions are here:
http://www.postgresql.org/docs/9.0/static/tutorial-window.html.

Build a query that returns primary_key1, rownum1, prio1 with prio sorted
ascending.  Do the same for rownum2, prio2 with prio sorted descending.
 Then join those two queries in an outer query on rownum1 = rownum2.  Use
that query as part of an update statement to set prio = prio2 where
primary_key = primary_key1.  You can likely do it more efficiently by
combining things into a single query, but you are only going to run this
once, and that was easier to describe, textually.



On Tue, Aug 23, 2011 at 12:49 PM, Andreas  wrote:

> Hi,
>
> there is a table that has among others a integer primary key "id" and
> another integer column "prio" as well as an integer "group_id".
>
> I'd like to invert the values of the prio-column for one of the groups.
> The prio numbers start with 3 and there are 1159 different prios in this
> group.
> At least every value appeares only once.   :)
>
> Is there an elegant way to switch the prio values around so that every
> record with the first prio gehts the last and vice versa?
> Then the records with the second smallest prio get the second-to-last
> biggest value and v.v.
> ...
>
>
> regards
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql
>