Re: [SQL] changing multiple pk's in one update
On 2009-04-08, Stuart McGraw wrote: > Hello all, > > I have a table with a primary key column > that contains sequential numbers. > > Sometimes I need to shift them all up or down > by a fixed amount. For example, if I have > four rows with primary keys, 2, 3, 4, 5, I > might want to shift them down by 1 by doing: > > UPDATE mytable SET id=id-1 > > (where "id" is the pk column) so that the pk's > are now 1, 2, 3, 4. > > When I try to shift them up by using +1 in the > above update statement, I get (not surprisingly) > a duplicate key error. I also realize that the > -1 case above works only by luck. > > So my question: > Is there some way, perhaps with ORDER BY, that > I can achieve the change I want with a single > update statement? (If I have an unused key > range large enough, I suppose I could update > all the keys to that range, and then back to > my target range but the requires two updates > (there are a lot of foreign keys referencing > these primary keys) and requires that I have > an available range, so a single update statement > would be preferable.) > > Thanks for any enlightenment. begin a transaction suspend the constraint (use SET CONSTRAINTS ... DEFERRED) drop the index do the update(s) recreate the index commit the transaction. I see no reason to keep the index (and its associated UNIQUE constraint) during the update, AFAICT all it does is slow the process down. -- 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] How would I get rid of trailing blank line?
On 2009-04-02, Tena Sakai wrote: > I am using postgres 8.3.4 on linux. > I often use a line like: > psql -tf query.sql mydatabase > query.out > > -t option gets rid of the heading and count > report at the bottom. There is a blank line > at the bottom, however. Is there any way to > have psql not give me that blank line? I ask postgres to format it for me instead of relying on psql psql db_name -c "copy (select * from foo ) to stdout;" this gives me postgres style tab separated values, but I can have CSV (or any otther format COPY can do) if I want. I usually use a more complex subquery with a list of columns and expressions, where, order by clauses, etc... ) I use this in a script that pulls data from one database and inserts in into another. Requires postgres 8.2 or later -- 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] changing multiple pk's in one update
2009/4/7 Stuart McGraw : > Hello all, > > I have a table with a primary key column > that contains sequential numbers. > > Sometimes I need to shift them all up or down > by a fixed amount. For example, if I have > four rows with primary keys, 2, 3, 4, 5, I > might want to shift them down by 1 by doing: > Generally speaking, when you need to do this more than once or twice in the lifetime of your data, there's something wrong with your data model. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: SQL to Check whether "AN HOUR PERIOD" is between start and end timestamps
On 2009-04-02, Alvaro Herrera wrote:
> James Kitambara wrote:
>> Dear Srikanth,
>> You can solve your problem by doing this
>>
>> THE SQL IS AS FOLLOWS
>> ASSUME TIME INTERVAL 2008-12-07 07:59:59 TO 2008-12-07 08:58:59 AND THE
>> TABLE NAME time_interval
>>
>> COUNT (*) FROM
>> (select customer_id, log_session_id, start_ts, end_ts , end_ts-start_ts
>> as "Interval" from time_interval
>> where end_ts-start_ts >= '1 hour'
>> and '2008-12-07 07:59:59' between start_ts and end_ts)
>> AS COUNT ;
>
> Another way to phrase the WHERE clause is with the OVERLAPS operator,
> something like this:
>
> WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07
> 08:59:59')
>
> What I'm not so sure about is how optimizable this construct is.
>
http://www.postgresql.org/docs/8.3/interactive/xindex.html
if you gave the apropriate GIST index on (start_ts, end_ts) the
overlaps may be optimisable. the subquery will run to completion
and count will count the results. - but this form gives different results.
beter to do
select COUNT (*) AS COUNT FROM time_interval
WHERE (start_ts, end_ts) OVERLAPS ('2008-12-07 07:59:59', '2008-12-07
08:59:59')
or
select COUNT (*) AS COUNT FROM time_interval
where end_ts-start_ts >= '1 hour'
and '2008-12-07 07:59:59' between start_ts and end_ts;
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
