Re: [SQL] .psql_history": No such file
Guillaume Lelarge wrote: Hi Ivan, Le vendredi 26 juin 2009 à 17:53:15, ivan marchesini a écrit : [...] I have installed a postgres db using a datadir different from /var/lib/pgsql/.psql_history. then: su postgres psql postgres All went fine but when I exit from psql from a db I obtain: ___ could not save history to file "/var/lib/pgsql/.psql_history": No such file or directory ___ how can I fix this problem? where can I say psql that it must write .psql_history into the datadir? psql tries to write there because the home directory of the postgres user is /var/lib/postgres. Probably better to use HISTFILE to change it (\set HISTFILE '/path/to/histfile'). Regards. You do know that you don't need to su postgres to start psql? Only the server needs to be run with the postgres user account. If you run psql from your normal user account the history file will be saved into your home folder not the postgresql install/data file dir. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz -- 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 magical advice for counting NOTHING
Andreas wrote: Hi, The source select counts log-events per user. All is well when a user has at least one event per log_type in the log within a given timespan. If one log_type is missing COUNT() has nothing to count and there is expectedly no result line that says 0. BUT I need this 0-line because of a crosstab. :( I need to know how to prevent in my crosstab categories on the right to slip to the left, when the left category is emptyy. Server 8.3.5 3 tables log (log_id, log_type_fk, user_fk, ts timestamp, ...) users (user_id, user_name, ...) log_type (log_type_id, log_type) There are 3 events as log_type. I naively tried SELECT user_name, log_type_fk, COUNT(log_type_fk) FROM log JOIN users ON (user_id = user_fk) WHERE (ts IS BETWEEN sometime AND another) GROUP BY user_name, log_type_fk ORDER BY user_name, log_type_fk This results e.g. to user1, type1, 2 user1, type2, 3 user1, type3, 7 user2, type1, 11 user2, type3, 17 but I needed also user2, type2, 0 How would I get there ? Regards Andreas SELECT user_name, log_type_fk, COUNT(log_type_fk) FROM log RIGHT JOIN users ON (user_id = user_fk) WHERE ts BETWEEN sometime AND another OR ts IS null GROUP BY user_name, log_type_fk ORDER BY user_name, log_type_fk -- Shane Ambler pgSQL (at) Sheeky (dot) Biz -- 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] Inserting data in composite types!
Rodrigo Sakai wrote: Hi, I have a question about how to insert data in composite types! Imagine the exemple: CREATE TYPE t_time AS ( a date, b date ); CREATE TABLE salary ( salary numeric(10,2), t_date t_time ); I know that if I want to insert data in the table SALARY I just have to do like: INSERT INTO salary VALUES (1000.00, '(2006/10/10, 2006/12/10)'); But if I have another table: CREATE TABLE employee ( employee_id int, name varchar(30), emp_salary salary ) I am thinking that with the salary type here you are thinking of your salary table defined above? If so and you want them in a separate table to record salary histories then you want to create a foreign key to link them. You would end up with - CREATE TABLE employee ( employee_id int PRIMARY KEY, name varchar(30) ); CREATE TABLE salary ( emp_id int REFERENCES employee(employee_id) ON DELETE CASCADE, salary numeric(10,2), t_date t_time ); then - INSERT INTO salary VALUES (1, 1000.00, '(2006/10/10, 2006/12/10)'); Otherwise you will want to change the CREATE TABLE salary... to CREATE TYPE salary... Probably as CREATE TYPE salary AS( salary numeric(10,2), a date, b date ); You can then INSERT INTO employee VALUES (1,'Hard Worker','(1000.00, 2006/10/10, 2006/12/10)'); -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] consistent random order
Jeff Herrin wrote: I don't think cursors are going to help in this case. The order by random() is still going to give different result sets on different pages. Jeff A cursor will maintain the order it was created with until it is disposed of. It won't work with a web app though as each page will come from a different connection in the available pool (or created for each page) meaning you will loose the cursor between pages. I would think you want to look at having a sort column that has a random number in it that is used for sorting. mysortcol integer default random() or maybe update the column a couple of times a day to keep the variety you seem to be after. - Original Message - From: Andreas Kretschmer <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Wednesday, November 29, 2006 12:27:42 PM GMT-0500 US/Eastern Subject: Re: [SQL] consistent random order Jeff Herrin <[EMAIL PROTECTED]> schrieb: I am returning results ordered randomly using 'order by random()'. My issue has to do with page numbers in our web application. When I hit the 2nd page and retrieve results with an offset, ordering by random() isn't really what I want since I will often receive results that were on the 1st page (they get re- randomized). I'm looking for a way to order in a controled random order. Maybe a UDF. I think you are searching for CURSORs. 18:25 < akretschmer> ??cursor 18:25 < rtfm_please> For information about cursor 18:25 < rtfm_please> see http://www.postgresql.org/docs/current/static/plpgsql-cursors.html 18:25 < rtfm_please> or http://www.postgresql.org/docs/current/static/sql-declare.html With a CURSOR, you get one result-set and can walk through this result. Andreas -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Autovaccum
Alvaro Herrera wrote: Ezequias Rodrigues da Rocha wrote: Hi list, I would like to know if it is necessary to set my database to autovaccum if the intent of my DB Manager is do not make any deletion in any time. If there is no deletions why autovaccum ok ? > You need to vacuum from time to time anyway, even if you don't delete anything. The easiest way to do it is let autovacuum do it for you. One thing that vacuum/autovacuum does is mark space used by deleted rows to be reused. Without deletes this won't be necessary in table data files. But when you update a record an index may also be updated and have the same effect within the index storage space. There are other things that vacuum does to keep your database running optimally. One is to update planner statistics about how many rows are in each table which effects the query planning and optimizing. Without deletes a plain vacuum won't achieve a great deal, but a regular VACUUM ANALYZE (as done by autovacuum) will make a difference to the performance of your database. If no data in your db changes then you won't have to bother vacuuming. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Autovaccum
Ezequias Rodrigues da Rocha wrote: Could you tell me if only this both options are ok (attach) ? If I don't mark the interval of vacuuns what will be the interval of each vacuum ? The default settings may be fine for you, it depends a bit on how many insert/updates you get in a given time frame. http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html will give a definition of each parameter that can be set. http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM will explain in more detail. Basically every autovacuum_naptime seconds autovacuum looks at estimates of how much the database has changed since the last run. If the amount of change is greater than the thresholds determined from the other settings then a vacuum/analyze will be done. If you have 100 records added/updated per day then you may only need it to run 1 or 2 times a day using smaller thresholds. If you are adding 10,000 records an hour then you will want it to run more often. 2006/11/30, Shane Ambler <[EMAIL PROTECTED]>: Alvaro Herrera wrote: > Ezequias Rodrigues da Rocha wrote: >> Hi list, >> >> I would like to know if it is necessary to set my database to >> autovaccum if the intent of my DB Manager is do not make any deletion >> in any time. >> >> If there is no deletions why autovaccum ok ? > > You need to vacuum from time to time anyway, even if you don't delete > anything. The easiest way to do it is let autovacuum do it for you. > One thing that vacuum/autovacuum does is mark space used by deleted rows to be reused. Without deletes this won't be necessary in table data files. But when you update a record an index may also be updated and have the same effect within the index storage space. There are other things that vacuum does to keep your database running optimally. One is to update planner statistics about how many rows are in each table which effects the query planning and optimizing. Without deletes a plain vacuum won't achieve a great deal, but a regular VACUUM ANALYZE (as done by autovacuum) will make a difference to the performance of your database. If no data in your db changes then you won't have to bother vacuuming. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Conditional SQL Query
M.P.Dankoor wrote:
> devil live wrote:
>> how can I write a query to get right ingredients of a product basis on
>> production_no field
>>
>> such as;
>>
>> first check production_no if product_tree_special table if not found
>> then look at template table...
>>
>> What are your suggestions?
>>
I think M.P. Dankoor's suggestion is close but I believe the conditional
part you are looking for would make it into this -
select PRD.product_code
,PRD.product_name
,NULL::intAS production_no
,PTT.stock_code_to_make_product
,PTT.amount
from product PRD
,product_tree_template PTT
where PRD.product_code='searchcode'
AND PRD.product_code = case
when (select production_no from product_tree_special ts
where ts.product_code=PRD.product_code) is null
then PTT.product_code else '' end
UNION
select PRD.product_code
,PRD.product_name
,PTS.production_no
,PTS.stock_code_to_make_product
,PTS.amount
from product PRD
,product_tree_special PTS
where PRD.product_code='searchcode'
AND PRD.product_code = case
when (select production_no from product_tree_special ts
where ts.product_code=PRD.product_code) is not null
then PTS.product_code else '' end
ORDER BY 1,2,4
So if we entered the following -
INSERT INTO product VALUES ('one','test one');
INSERT INTO product VALUES ('two','test two');
INSERT INTO product_tree_special VALUES (1,'one','special list',1.1);
INSERT INTO product_tree_template VALUES ('two','template parts',2.2);
change both WHERE clauses to PRD.product_code='two' you will get -
two test twotemplate parts 2.2
then change both WHERE clauses to PRD.product_code='one' you will get -
one test one1 special list1.1
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] How to union table without union statement?
calendarw wrote: Hi, I am using the following query now, but the time is too slow. could anyone can help me? CREATE OR REPLACE VIEW alllogview AS ((( SELECT alarmdtl.tagname, a_alarmtbl.occurtime, a_alarmtbl.restoretime, a_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM a_alarmtbl, alarmdtl WHERE a_alarmtbl.tagname::text = alarmdtl.tagname::text UNION ALL SELECT alarmdtl.tagname, b_alarmtbl.occurtime, b_alarmtbl.restoretime, b_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM b_alarmtbl, alarmdtl WHERE b_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, c_alarmtbl.occurtime, c_alarmtbl.restoretime, c_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM c_alarmtbl, alarmdtl WHERE c_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, d_alarmtbl.occurtime, d_alarmtbl.restoretime, d_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM d_alarmtbl, alarmdtl WHERE d_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, e_alarmtbl.occurtime, e_alarmtbl.restoretime, e_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM e_alarmtbl, alarmdtl WHERE e_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, f_alarmtbl.occurtime, f_alarmtbl.restoretime, f_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM f_alarmtbl, alarmdtl WHERE f_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, g_alarmtbl.occurtime, g_alarmtbl.restoretime, g_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM g_alarmtbl, alarmdtl WHERE g_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, h_alarmtbl.occurtime, h_alarmtbl.restoretime, h_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM h_alarmtbl, alarmdtl WHERE h_alarmtbl.tagname::text = alarmdtl.tagname::text) UNION ALL SELECT alarmdtl.tagname, i_alarmtbl.occurtime, i_alarmtbl.restoretime, i_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc FROM i_alarmtbl, alarmdtl WHERE i_alarmtbl.tagname::text = alarmdtl.tagname::text ORDER BY 1; Have you done an EXPLAIN on the query? Is there an index on the tagname columns? If so does the EXPLAIN show them being used? How many rows do you have in each table (roughly)? Have you considered other structure options like partitioning? Is there a real need to have these tables separate? or could you have them all in one table with an column to identify the source of the log entry? On 2/28/07, Hiltibidal, Robert <[EMAIL PROTECTED]> wrote: Can you provide a schema? -- *From:* [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] *On Behalf Of *calendarw *Sent:* Wednesday, February 28, 2007 4:33 AM *To:* pgsql-sql@postgresql.org *Subject:* [SQL] How to union table without union statement? Hi, I need to combine 10 tables which contain same table structure and join an "other table" to show the latest 200 record, I am join the "other table" first and using union statement to select all record now but the collection time is super slow, how can I improve the collection speed? Thanks. -- Jr. P calendarw PRIVILEGED AND CONFIDENTIAL This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:[EMAIL PROTECTED] Thank you. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to store a password encripted in a user defined table
Andrej Ricnik-Bay wrote: On 3/1/07, Eugenio Flores <[EMAIL PROTECTED]> wrote: Hello, I wonder if somebody knows how to store passwords in a column that is part of a user defined table. Assuming that your passwords are application specific use a sha1 or md5 algorithm (depending on how sensitive your data is) and store that in a varchar or char field. When the user authenticates the password gets hashed in the app and compared against the stored hash. If you want the server to take care of it look at pgcrypto - you will find it in the contrib folder of the source distro. This doesn't give you an encrypted data type (but you could set that up if you wish) it will give you functions that you can use. Of course that would mean they get sent through the client connection as clear text unless you are using an SSL client connection. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to store a password encripted in a userdefinedtable
Ezequias Rodrigues da Rocha wrote:
I am just passing the database owner password (postgresql
autentication) to the statement:
Select md5('the password I have in my mind') and compare with the
password pgAdmin3 shows me.
They are completely different.
Try SELECT 'md5'||md5('the password I have in my mind'||'userlogin');
Ezequias
2007/3/1, Bart Degryse <[EMAIL PROTECTED]>:
It doesn't do that for me. I've tried it on three different databases (of
two different versions) as three different users and the result is always
the same (as it should be):
select USER, md5('password')
current_usermd5
bigdbuser 5f4dcc3b5aa765d61d8327deb882cf99
current_usermd5
bigdbsys5f4dcc3b5aa765d61d8327deb882cf99
current_usermd5
logstocksys 5f4dcc3b5aa765d61d8327deb882cf99
Show us some statements.
>>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]> 2007-03-01
16:02 >>>
Just another thing.
Why md5 function return a different string from user role of postgresql ?
It allways put an md5 string concated with another sequence of string.
Why does it occurs ?
Ezequias
2007/3/1, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]>:
> I know it. Thank you so much.
>
> Ezequias
> Grettings from Brazil.
>
> 2007/3/1, Bart Degryse <[EMAIL PROTECTED]>:
> >
> >
> > update yourtable set passwordfield = md5(passwordfield)
> >
> > watch out: md5 is irreversable! you can't "un_md5"
> >
> >
> > >>> "Ezequias Rodrigues da Rocha" <[EMAIL PROTECTED]>
2007-03-01
> > 15:08 >>>
> >
> > John,
> >
> > That was what I was looking for for a long time.
> >
> > Now I will change my teller password account to md5.
> >
> > Could someone suggest me how to change all passwords (PLAIN) to md5 ?
> >
> > My real best regards
> > Ezequias
> >
> > 2007/3/1, John DeSoi <[EMAIL PROTECTED]>:
> > > MD5 is built-in to PostgreSQL. It is what PostgreSQL itself uses to
> > > hash passwords. For example:
> > >
> > > select md5('this is my password');
> > >
> > > md5
> > > --
> > > 210d53992dff432ec1b1a9698af9da16
> > > (1 row)
> > >
> > >
> > >
> > > On Mar 1, 2007, at 6:06 AM, Eugenio Flores wrote:
> > >
> > > > Thanks Andrej. But how can I use such algoritms in postgresql?
arey
> > > > they defined in a function that I can call?
> > > >
> > > > Or, do I have to code one of those algorithm to use it in my
> > > > application?
> > >
> > >
> > >
> > > John DeSoi, Ph.D.
> > > http://pgedit.com/
> > > Power Tools for PostgreSQL
> > >
> > >
> > > ---(end of
> > broadcast)---
> > > TIP 9: In versions below 8.0, the planner will ignore your
desire to
> > >choose an index scan if your joining column's datatypes
do not
> > >match
> > >
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente
(Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---(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
Re: [SQL] inheritance
chester c young wrote: --- Greg Toombs <[EMAIL PROTECTED]> wrote: I'm trying to figure out how to nicely implement a C++ class-likesystem > > with PostgreSQL. Consider the following: Tables Fruit, Apple, Orange you can do this traditionally or through pg inheritance, although I do not think inheritance is well supported before 8.2. Inheritance will most likely fit your C++ class-like system better and will not need foreign keys. Inheritance has been available in postgresql for many years and I think you will find it quite stable. (Not sure if pg6.x had it but it was available in 7.x) Historically a Table in postgresql was called a class (pre-sql) which is still reflected in the system catalogs with pg_class containing the list of tables and other classes like indexes http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html You would then have table fruit as your base class and table oranges that inherits from fruit, giving it all the columns that fruit has plus any that are added to table oranges as well. selecting from table fruit will allow you to get all rows from table apples and table oranges but not the columns unique to the apples or oranges tables. Maybe then you'll add a table basket that has a foreign key to the fruit table... ;-) -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] inheritance
Richard Broersma Jr wrote: Maybe then you'll add a table basket that has a foreign key to the fruit table... ;-) From the inheritance link: ... A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. ... You can create a foreign key to the fruit table to a table basket, but this foreign key will only work for fruit that was directly inserted into the fruit table. Any fruit inserted into the Apples or Oranges table can not be referenced by the table basket. I believe that this limitation in table inheritance will not work for Greg's requirements. Having said this, it would make me very happy if I am wrong. I hate modeling data the hard way when there is a better way of doing it. ;) You can get and store related records but the issue is that you need to maintain referential integrity yourself instead of postgresql doing it for you. So currently your right, next release or two maybe not. There is a current discussion (on hackers list) on partitioning that has been going over ways to tackle the primary / unique constraints across multiple child tables and that could lead to a solution that can be applied to this as well. Partitioning is using inheritance to spread data across multiple tables. eg you may have one table for each month's worth of data. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Format intervall as hours/minutes etc
Andreas Joseph Krogh wrote:
On Sunday 16 September 2007 13:14:27 Andreas Kretschmer wrote:
Andreas Joseph Krogh <[EMAIL PROTECTED]> schrieb:
Hi all. Any hint on how to format this interval as number of hour/seconds
etc? select age('2007-09-22 17:00'::timestamp, '2000-02-20
18:00'::timestamp); age
---
7 years 7 mons 1 day 23:00:00
You can use extract(epoch, from ...) like this:
test=*# select extract(epoch from '2007-09-22 17:00'::timestamp) - extract
(epoch from '2000-02-20 18:00'::timestamp); ?column?
---
239407200
(1 row)
Now you can calculate the hours and so on.
Yes, this works fine for dates >= 1970, but I'm looking for a more general
solution which takes an arbitrary interval as input. The reason why I'm using
PG to calculate this is 'cause it takes 25/23 hour days, leapyears etc. into
account when calculating intervals.
Is that all you use it for?? ;-)
You may want to add the timezone to get the effect of daylight savings.
postgres=# select age('2007-03-25 7:00:00'::timestamptz, '2007-03-25
1:00:00'::timestamptz);
age
--
06:00:00
(1 row)
postgres=# select age('2007-03-25 7:00:00+9:30'::timestamptz,
'2007-03-25 1:00:00+9:30'::timestamptz);
age
--
05:00:00
(1 row)
I haven't used intervals much so I may be missing something.
I get the idea you want the interval to be expressed as 2,765 days and
23 hours or 66,383 hours, which I think would be useful (more so for
shorter intervals).
I am thinking the exact function you are after isn't there - from what I
can find a larger interval is always given as x years y months z days...
which is why extracting the epoch is the easiest point to start your calcs.
Maybe this can be a feature request - functions to give an interval in
total number of days/hours/minutes instead of years months days
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Why does the sequence skip a number with generate_series?
Stephan Szabo wrote: On Tue, 2 Oct 2007, Jeff Frost wrote: I expected these numbers to be in sync, but was suprised to see that the sequence skips a values after every generate series. CREATE TABLE jefftest ( id serial, num int ); INSERT INTO jefftest (num) values (generate_series(1,10)); INSERT INTO jefftest (num) values (generate_series(11,20)); INSERT INTO jefftest (num) values (generate_series(21,30)); It seems to do what you'd expect if you do INSERT INTO jefftest(num) select a from generate_series(1,10) as foo(a); INSERT INTO jefftest(num) select a from generate_series(11,20) as foo(a); INSERT INTO jefftest(num) select a from generate_series(21,30) as foo(a); I tried a function that raises a notice and called it as select f1(1), generate_series(1,10); and got 11 notices so it looks like there's some kind of phantom involved. That's interesting - might need an answer from the core hackers. I am posting this to pgsql-hackers to get their comments and feedback. I wouldn't count it as a bug but it could be regarded as undesirable side effects. My guess is that what appears to happen is that the sequence is created by incrementing as part of the insert steps and the test to check the end of the sequence is - if last_inserted_number > end_sequence_number rollback_last_insert This would explain the skip in sequence numbers. My thoughts are that - if last_inserted_number < end_sequence_number insert_again would be a better way to approach this. Of course you would also need to check that the (last_insert + step_size) isn't greater than the end_sequence_number when the step_size is given. I haven't looked at the code so I don't know if that fits easily into the flow of things. The as foo(a) test would fit this as the sequence is generated into the equivalent of a temporary table the same as a subselect, then used as insert data. The rollback would be applied during the temporary table generation so won't show when the data is copied across to fulfill the insert. Maybe the planner or the generate series function could use a temporary table to give the same results as select from generate_series() -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(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
Re: [SQL] Is there anything special about pg_dump's compression?
Jean-David Beyer wrote: Tom Lane wrote: Jean-David Beyer <[EMAIL PROTECTED]> writes: I turned the software compression off. It took: 524487428 bytes (524 MB) copied, 125.394 seconds, 4.2 MB/s When I let the software compression run, it uses only 30 MBytes. So whatever compression it uses is very good on this kind of data. 29810260 bytes (30 MB) copied, 123.145 seconds, 242 kB/s Seems to me the conclusion is obvious: you are writing about the same number of bits to physical tape either way. I guess so. I _am_ impressed by how much compression is achieved. Plain text tends to get good compression in most algorithms, repetitive content tends to improve things a lot. (think of how many CREATE TABLE COPY FROM stdin ALTER TABLE ADD CONSTRAINT GRANT ALL ON SCHEMA REVOKE ALL ON SCHEMA .. are in your backup files) To test that create a text file with one line - "this is data\n" Then bzip that file - the original uses 13 bytes the compressed uses 51 bytes. now change the file to have 4000 lines of "this is data\n" the original is 52,000 bytes and compressed it is 76 bytes - it uses 25 bytes to indicate the same string is repeated 4000 times The physical tape speed is surely the real bottleneck here, and the fact that the total elapsed time is about the same both ways proves that about the same number of bits went onto tape both ways. I do not get that. If the physical tape speed is the bottleneck, why is it only about 242 kB/s in the software-compressed case, and 4.2 MB/s in the hardware-uncompressed case? The tape drive usually gives over 6 MB/s rates when running a BRU (similar to find > cpio) when doing a backup of the rest It would really depend on where the speed measurement comes from and how they are calculated. Is it data going to the drive controller or is it data going to tape? Is it the uncompressed size of data going to tape? My guess is that it is calculated as the uncompressed size going to tape. In the two examples you give similar times for the same original uncompressed data. I would say that both methods send 30MB to tape which takes around 124 seconds The first example states 4.2MB/s - calculated from the uncompressed size of 524MB, yet the drive compresses that to 30MB which is written to tape. So it is saying it got 524MB and saved it to tape in 125 seconds (4.2MB/s), but it still only put 30MB on the tape. 524MB/125 seconds = 4.192MB per second The second example states 242KB/s - calculated from the size sent to the drive - as the data the drive gets is compressed it can't compress it any smaller - the data received is the same size as the data written to tape. This would indicate your tape speed. 30MB/123 seconds = 243KB/s To verify this - 524/30=17 - the compressed data is 1/17 the original size. 242*17=4114 - that's almost the 4.2MB/s that you get sending uncompressed data, I would say you get a little more compression from the tape hardware that gives you the slightly better transfer rate. Or sending compressed data to the drive with it set to compress incoming data is causing a delay as the drive tries to compress the data without reducing the size sent to tape. (my guess is that if you disabled the drive compression and sent the compressed pg_dump to the drive you would get about 247KB/s) I would also say the 6MB/s from a drive backup would come about from - 1. less overhead as data is sent directly from disk to tape. (DMA should reduce the software overhead as well). (pg_dump formats the data it gets and waits for responses from postgres - no DMA) And maybe - 2. A variety of file contents would also offer different rates of compression - some of your file system contents can be compressed more than pg_dump output. 3. Streamed as one lot to the drive it may also allow it to treat your entire drive contents as one file - allowing duplicates in different files to be compressed the way the above example does. -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(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
Re: [SQL] join on three tables is slow
Gerry Reno wrote: I have a join that uses three tables but it runs rather slow. For example, the following command takes about 10 min. to run. It gets the correct result but what should I do to increase the performance of this query? This query is the end result of some python code hence the big id list. myfile has 600 records, res_partner has 600 records, res_partner_address has 1000 records select p.addr, p.name, p.name2 from myfile as p join res_partner as e on e.id in ( 3214, 3213, 3212, 3211, 3210, 3209, 3208, 3207, 3206, 3205, snip 3179, 3178, 3177, 50, 49, 48, 47, 22, 25 ) join res_partner_address as a on a.partner_id = e.id and (a.type = 'default' or a.type IS NULL) and (p.name != a.name or p.name2 != a.name2) and p.addr = e.addr where e.active = '1' and p.date = e.date and e.date = (select max(date) from res_partner as msd where msd.addr = p.addr) To start with - You have join res_partner as e on e.id in (... big list...) That list should be the contents of a where clause not a join. You want that first part to be join res_partner as e on e.id=p.something So as a first step that join will link all 523 res_partner rows listed with every myfile row - that means you will get 313,800 rows from this join with your other joins and where clause then trim that down to the final result. I would also say that the rest of your joins don't appear to be what you really want. (but part of them may belong in the where clause) -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] sql query question ?
Trilok Kumar wrote: Hi All, I have a table called vehicle_duty_cycle_summary vehicle_master_id | starting_odometer | ending_odometer | login_time | logout_time ---+---+-++ 4 | 53379.00 |53504.00 | 2006-12-19 16:19:16.584547 | 2006-12-20 07:12:57.716907 I would like to compute the following on this table. Idle time of vehicel=(ending_odometer reading of the previous day - starting_odometer reading of the present day) for every vehicle I would think your naming may be confusing and may not be implemented(recorded?) very well. I think Idle Time is a misleading name by your explanation - Idle time would be defined as (logout_time - previous login_time) which gives you the time the vehicle was sitting in the garage. What you want may be better called unmetered_travel and would be the distance traveled between login_time and logout_time This would simply be select vehicle_master_id, (ending_odometer - starting_odometer) as unmetered_travel from vehicle_duty_cycle_summary; Going by the naming you have used it would appear that you are recording the time spent in the garage (going by the data you have shown I would say this is a company car garage not a repair shop) One record would appear to record the time the car is in the garage - login_time would be the time the employee returned the car and logout_time would be when the car next went out to someone. I would think you want the opposite of that - the time and odometer reading when an employee takes the car and the time and odometer of when it is returned and the employee_id of who had it. This will give you who used the car at what time and what distances they travelled (which of course would be work related travel) Going with those changes - The distance traveled by an employee is easy to workout, if you wanted to workout the unmetered (non-work) distance traveled you could try something like (untested) - select v1.vehicle_master_id , v1.starting_odometer - (select v2.ending_odometer from vehicle_duty_cycle_summary v2 where v2.vehicle_master_id = v1.vehicle_master_id and v2.login_time < v1.logout_time order by v2.login_time desc limit 1) as unmetered_travel from vehicle_duty_cycle_summary v1 where v1.vehicle_master_id = 4; I would calculate idle time as - select v1.vehicle_master_id , v1.logout_time - (select v2.login_time from vehicle_duty_cycle_summary v2 where v2.vehicle_master_id = v1.vehicle_master_id and v2.login_time < v1.logout_time order by v2.login_time desc limit 1) as unmetered_travel from vehicle_duty_cycle_summary v1 where v1.vehicle_master_id = 4; If this isn't the way it should work you should be able to adapt the query to match your definition of idle time. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(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
Re: [SQL] SQL question: Highest column value of unique column pairs
Kevin Jenkins wrote: Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John, Doe,60 Bill, Gates, 25 Jan 3. So columns 1 and 2 hold the first person. Column 3 holds his score. Columns 4 and 5 hold the second person. Column 6 holds his score. I want to return the most recent score for each person (be they an opponent or myself). And the resultant table shouldn't care if they are person 1 or 2. So the end result would be FName, LName, Score, Date John,Doe, 120Jan 5. John,Archer 90 Jan 5. Bob, Barker 70 Jan 8 Bill,Gates 25 Jan 3 Calvin Klien 8 Jan 8 Thanks for any help! First I would say you should have one person in a row and have another table to join them like you want. Try (untested just guessing) - select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable order by 3 -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(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
Re: [SQL] SQL question: Highest column value of unique column pairs
Kevin Jenkins wrote: Thanks! How would I find the highest score in the union of the two tables? I tried this but it can't find unionTable: SELECT * FROM (select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable) as unionTable WHERE unionTable.score= (SELECT max(unionTable.score) FROM unionTable); the (select max(score)...) doesn't see the unionTable change the last line to order by score desc limit 1 SELECT * FROM (select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable) as unionTable order by score desc limit 1 Shane Ambler wrote: Kevin Jenkins wrote: Hi, I have the following table which holds the result of 1 on 1 matches: FName1, LName1, Score1, FName2, LName2, Score2, Date John, Doe,85 Bill, Gates, 20 Jan 1. John, Archer, 90 John, Doe,120 Jan 5 Bob,Barker, 70 Calvin, Klien 8 Jan 8 John, Doe,60 Bill, Gates, 25 Jan 3. So columns 1 and 2 hold the first person. Column 3 holds his score. Columns 4 and 5 hold the second person. Column 6 holds his score. I want to return the most recent score for each person (be they an opponent or myself). And the resultant table shouldn't care if they are person 1 or 2. So the end result would be FName, LName, Score, Date John,Doe, 120Jan 5. John,Archer 90 Jan 5. Bob, Barker 70 Jan 8 Bill,Gates 25 Jan 3 Calvin Klien 8 Jan 8 Thanks for any help! First I would say you should have one person in a row and have another table to join them like you want. Try (untested just guessing) - select fnam1 as fname,lname1 as lname, score1 as score from myscorestable union select fnam2 as fname,lname2 as lname, score2 as score from myscorestable order by 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Serial not nulla
Shavonne Marietta Wijesinghe wrote:
Hello
I am working with a database that has a Index number defined as Serial NOT NULL
I used this because,
1. I want to make sure that when many users login at the same time the Index
number won't be repeated.
2. I don't have to increment it by programming (I use ASP)
But now i have a situation that i need to index numbers. For Example i could
have a structure like this
INDEX1 - N_SHEET - TOT_SHEET
1 - 1 - 1
2 - 1 - 3
2 - 2 - 3
2 - 3 - 3
N_SHEET and TOT_SHEET are filled by the user (via web) but i need to hold on to
the INDEX. And while userA is filling the 3 row if userB loggs in i need to
provide the INDEX1 with 3.
Any idea??
As well as using the "Serial NOT NULL" you have also defined this column
as PRIMARY KEY (or a unique index) which is what is preventing the
duplicates in that column. (A primary key is enforced with a unique index)
From the sample shown you can use all three columns as the primary key
with something similar to -
ALTER TABLE my_user_sheets DROP CONSTRAINT my_user_sheets_pkey;
ALTER TABLE my_user_sheets ADD PRIMARY KEY ("INDEX1", "N_SHEET",
"TOT_SHEET");
(this implies that for each user they will have only one row for each
combination of N_SHEET and TOT_SHEET) If you need to allow them to
select the same 2 sheet numbers more than once then I would suggest you
have an extra column for a primary key and redefine INDEX1 as the
user_id. (or just add a user_id column and leave the INDEX1 as it is)
It's not recommended but you could also have the table without a primary
key allowing duplicate value combinations. This would prevent you
updating a single row though.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] Postgres roles
Pascal Tufenkji wrote: My questions are: 1. how do I identify the users assigned to this role : (in the older version) SELECT grolist from pg_group where groname = 'sti'; "The view pg_group exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows the names and members of all roles that are marked as not rolcanlogin, which is an approximation to the set of roles that are being used as groups." Use pg_roles to get the user and group info. Use pg_auth_members to get the list of members that belong to each group role. (any role can be used for a group but it is usually a role that has rolcanlogin set to false, and has members recorded in pg_auth_members) http://www.postgresql.org/docs/8.2/interactive/user-manag.html can explain it better - or more specifically http://www.postgresql.org/docs/8.2/interactive/role-membership.html 2. how do I differ granting permissions on a table to the user sti from the whole members of the group sti (in the older version) GRANT SELECT ON table TO group sti; GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti; Use a more descriptive name for the group or simply sti_group. I am guessing that you have an issue because you now have one role called sti - that has carried the group members from the old version - this is the admin userid used to login but because it is used as a group it passes it's privileges to all members of sti. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Postgres roles
Pascal Tufenkji wrote: Hi Shane, You are exactly right. My issue is that, I now have one role called sti - that has carried the group members from the old version - So what do you think my options are, so I can separate them? I have only one option in my mind: - Revoke the members from the role sti - Create a new role (that has rolcanlogin set to false) called sti_group - Assign the members to it - Finally, fix all the permissions for all the tables (add the permissions to the new group sti_group) which seems like a huge amount of work. In that case I'll be able to give permissions such as : GRANT SELECT ON table TO sti_group; GRANT SELECT,INSERT,UPDATE,DELETE ON table TO sti; Is there a better solution ? That is the solution and it does seem like a lot if you have lots of users and/or tables - I can think of a couple of ways to make it easy - 1. Use pgAdmin - it has a Grant wizard that will generate the sql for the grants and revokes on all the tables/functions etc for you. It can do an entire schema in a few clicks. 2. Generate the list of commands yourself - fill a text file with them and send them to psql. "REVOKE sti FROM "+username+";" "GRANT sti_group TO "+username+";" "GRANT SELECT ON "+tablename+" TO sti_group;" ... ... The second may be the way to go at least for the removing and adding group memberships from sti to sti_group as I don't see any helpers in pgAdmin for that. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Export Access 97 to PostgreSQL
Shavonne Marietta Wijesinghe wrote: Hello I have a db in MS Access 97 and now i have to import the data in PostgreSQL. I can create the table structure in PostgreSql but in what format can i export the table from Access so Postgresql can read it? csv would be the most common and easiest. Basically any structured text file can be used as you have the option of specifying what characters are used to separate fields etc when you import to postgresql. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] connections between servers
Marcin Krawczyk wrote: Hi all. I was wondering if it's possible for a trigger to perform operations on a database on different server? I saw somewhere that there's a piece of software that allows conneciotns between different databases, but what about different servers? I also thought about using perl, would it be possible to connect to different server from within perl trigger? Thanks in advance. regards mk Yes it is possible. A PL/Perl trigger is one option you have. dblink (in contrib) and dbi-link, dblink-tds, oralink and odbclink are other options at pgfoundry.org depending on your needs. There was a recent discussion in the general mailing list about this. http://archives.postgresql.org/pgsql-general/2008-03/msg01343.php -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Curious about wide tables.
Jean-David Beyer wrote: In another thread, the O.P. had a question about a large table with over 100 columns. Is this usual? Whenever I make a database, which is not often, it ends up with tables that rarely have over to columns, and usually less than that. When normalized, my tables rarely get very wide. Without criticising the O.P., since I know nothing about his application, I am curious how it comes about that such a wide table is justified. Depends on the application. Something like drivers license db will have a few things like name, address, type, dob, restrictions and end date Then something like an insurance policy where each record needs to know who it is for, the item(car - rego make model... house - address suburb state), effective date, end date, date of inception, type of cover, value of cover, excess amount, base premium, agent fees, gov fees, total premium, invoice sent, who entered it and when.. Sometimes you can have a lot of data that makes up one instance. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] Parallel updates on multiple cores
Andrei wrote: The function above updates the rows between the ids start_id and end_id. I have a quad core procesor so i run two separate connections to the database: select populate_test_data(5000,1) and another select populate_test_data(5000,2). In this case each function runs on one core doing the inserts in parallel, but when i try to run select select_unprocessed(1,5001) and from another connection select select_unprocessed(5001, 10001), one of the processes locks the table so the other one has to wait until the table is unlocked. Each process updates different parts of the table. Is there a way to do the updates in parallel on multiple cores? Wait until the other is done or wait until it has done what it needs to? If it appears to not update the records I would look at the id ranges you are passing. You insert 5000 rows with the first function then you tell the update function to update row id's 1 to 50001 - have you reset the sequence for the id column? or do you drop and create the table before each test? My guess is no updates appear to happen as the id's entered by the serial type are larger than 1. Also you update with processed='n' - is that what you want? Is that the only column you look at to see that it is done? Transactions would be the only cause of the problem you describe. I am guessing that you use bigger numbers than 5000 in your tests and the examples above use overlapping id's. If the first updates row 5001 early then the second may need to wait until it commits to update it again. This can work the other way 5001 is updated by the second locking it until it finishes and the first waits until the second commits to update it again. With 5000 rows I wouldn't expect to see a time difference. Without an order by in the select the rows can be returned and updated in any order. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] truncate vs. delete
Emi Lu wrote: Thank you. I am quite sure that I will not use "delete" now. Now I a question about how efficient between (1) truncate a big table (with 200, 000) vacuum it (optional?) drop primary key load new data load primary key vacuum it (2) drop table (this table has no trigger, no foreign key) re-create table (without primary key) load new data setup primary key vacuum it suggestions PLEASE? Thanks a lot! Shouldn't be a noticeable difference either way. A quick test - postgres=# \timing Timing is on. postgres=# create table test (id serial primary key,data integer); NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for serial column "test.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" CREATE TABLE Time: 26.779 ms postgres=# insert into test (data) values (generate_series(1,20)); INSERT 0 20 Time: 4604.307 ms postgres=# truncate table test; TRUNCATE TABLE Time: 31.278 ms postgres=# insert into test (data) values (generate_series(1,20)); INSERT 0 20 Time: 4545.386 ms postgres=# drop table test; DROP TABLE Time: 45.261 ms postgres=# shows a 10ms difference between truncate and drop. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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] wired behaviour
Lutz Steinborn wrote: Hello Paul, thanks for the quick answer. NULL values? Jepp, thats it. I've supposed this but can't believe it. So NULL is something out of this dimension :-) Kindly regards Lutz NULL refers to an unknown value - it cannot be said to equal or not equal anything other than NULL It is a concept that catches those new to databases (and sometimes not so new). -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- 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 to update 400 000 register not at the same time?
John Dizaro wrote: I Have um very big table with primary key and all i nead. When i update same register from this table it comes sj=low. Can i Update all my 400 000 register not at the same time? By steps? Thanks Yes - provided you can come up with a definite way to separate your records into smaller groups. Use a WHERE clause in your UPDATE - UPDATE mytable SET col3=56 WHERE col1 IS BETWEEN 1 AND 1 UPDATE mytable SET col3=56 WHERE col1 IS BETWEEN 10001 AND 2 ... ... Of course you need to beware that it won't speed things up and you could run into having other users looking at some old rows at the same time as some updated rows. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
