Re: [SQL] alter table on a large db
On 2009-03-19, Zdravko Balorda zdravko.balo...@siix.com wrote: Hi, I need to make some ALTER TABLEs. It takes about 30min to copy this quite large databse, bat several ours to run a bunch of ALTER TABLE statements. which do you prefer 30 minutes down-time to reload the database or a few hours wait to update it live. Is there any way to make it faster? I wonder what could possibly alter table be doing all this time. drop and reload the table? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Alter Table/Indexing
Hi, I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT does in a sense it may be faster to drop and recreate index than sorting after every row inserted. Does changing type or setting default on an indexed column require sorting? Thanks, Zdravko -- 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-03-17, Srikanth rss...@yahoo.co.in wrote: Dear all, I have a table that records User Login Sessions with two timestamp fields. = Basically Start of Session and End of a Session (start_ts and end_ts). Each= row in the table identifies a session which a customer has used.=A0=20 I have to find out how many User Sessions that were present in any given 1= HOUR TIME PERIOD.=A0 A single User Session can span across many days. select count(*) from session WHERE start_ts TIME + '1 hour'::interval AND end_ts = TIME; (replace both ocurrences of TIME with the time the interval starts) -- 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] cast bool/int
On 2009-03-23, Zdravko Balorda zdravko.balo...@siix.com wrote: Hi, I need a casting operator from boolean to integer, you can use any expression tu put in ALTER TABLE statment after USING. Any ideas? Thanks. CASE WHEN columname THEN 1 WHEN NOT columname THEN 0 ELSE NULL END -- 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] Alter Table/Indexing
Zdravko Balorda zdravko.balo...@siix.com writes: I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT does in a sense it may be faster to drop and recreate index than sorting after every row inserted. ALTER TABLE TYPE already rebuilds the indexes; you won't make the overall process any faster by doing that by hand. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Proper entry of polygon type data
Hello, I would like to use 'polygon' type data and am wondering about the entry format of the vertex coordinates. Are the coordinates of the polygon type to be entered one entry per polygon vertex, or one entry per polygon edge segment? For example: I have a triangle with vertex corners A, B, C. One entry per vertex format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy)) ); One entry per edge format suggests INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Bx,By),(Cx,Cy),(Cx,Cy),(Ax,Ay)) ); Which entry format is the correct one? If per vertex format is the correct one, do I need to 'close' the path by entering the first vertex again at the end of the list? ie: INSERT INTO my_table (my_polygon_column) VALUES ( ((Ax,Ay),(Bx,By),(Cx,Cy),(Ax,Ay)) ); Thanks, Peter -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
