Re: [SQL] Speeding up schema changes
On Sep 3, 2007, at 7:26 AM, Gregory Stark wrote: Also, incidentally do you have a good reason to use CHAR instead of varchar or text? char(64) will take 64 bytes (actually 68 bytes in 8.2) even if you don't store anything more in it. text or varchar will take only as many bytes as the data you're storing (plus 4 bytes). Hrm, do we actually pad before storing? ISTM we should really do that the other way around... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to manage category-ids as array-fields ?
On Jun 13, 2008, at 11:02 AM, Andreas wrote:
I get from an outside source tables as simple textfiles to import.
Those are obviously results of views with joined tables.
Among the normal stuff are columns that have one or a list of id-
numbers devided by a semicolon.
In the next column there is the corresponding text for this ids.
It looks like this:
17, ... , "1; 2;", "cat; mouse;", ...
23, ..., "3;", "dog;", ...
42, ..., "2; 7;", "mouse; horse;", ...
Obviously the meaning is that some entity has those listed
attributes. Most likely they are stored as a n:m-relation like:
17, 1
17, 2
23, 3
42, 2
42, 7
Is there a way to reproduce the output in the form above (as array) ?
SELECT array_to_string(array(1,2), '; ');
Is there a way to import the data in the form above, so it gets
neately stored in a n:m ?
Well, you can easily turn it into an array:
SELECT string_to_array('1; 2', '; '); (You'll need to strip the
trailing ;'s.
After than you can convert the array to a recordset if you want.
There's some examples in the archives of how to do that (I think it's
in the archives for -general; I know I was in one of the threads so
searching for decibel might help narrow things down).
Is it seen as a conceptual good solution to store such information
within a text-column or array?
I'd rather doubt that PG would watch the integrity of those ids then.
I wouldn't do text. You could enforce some loose RI via triggers
pretty easily if you used arrays.
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
Re: [SQL] Cross Tab Functions
Try using extract instead of to_char. date_trunc might also be of use.
On Jun 20, 2008, at 8:05 AM, Chris Preston wrote:
Hello All,
I have used the cross tab function to setup tables that display
months' data, however i need to display years data instead of the
months.. so i would display 2006 as a column, 2007 as a colum and
2008 as a column. when i tried to modify the simple example of the
cross tab (shown below)
SELECT i.item_name::text As row_name, to_char(if.action_date,
'year')::text As bucket,
SUM(if.num_used)::integer As bucketvalue
FROM inventory As i INNER JOIN inventory_flow As if
ON i.item_id = if.item_id
WHERE (if.num_used <> 0 AND if.num_used IS NOT NULL)
AND action_date BETWEEN date '2007-01-01' and date '2008-12-31 23:59'
GROUP BY i.item_name, to_char(if.action_date, 'year'), date_part
('year', if.action_date)
ORDER BY i.item_name, date_part('year', if.action_date);
basically i changed the mon and the month to be year.. the system
displayed something that i am not sure about. is there a command
that converts the year similar to the mon
Help again...Please
--
Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
Give your computer some brain candy! www.distributed.net Team #1828
smime.p7s
Description: S/MIME cryptographic signature
Re: [SQL] COPY equivalent for updates
On Jul 15, 2008, at 1:10 AM, Ivan Sergio Borgonovo wrote: I'd like to UPDATE t1 (col1, col2, col3) from file with @1 as primary key; or UPDATE t1 (col1, col2, col3) from file where @1=id; sort of... Sorry, there's nothing like COPY for UPDATE. Otherwise what is the fastest approach? I can think of 2 approaches: 1)load a temp table with COPY update t1 set col1=temp_t1.col1, col2=temp_t1.col2 where t1.id=temp_t1.id; 2) use awk to generate update statements. Supposing I could neglect the awk execution time, will COPY + UPDATE be faster than executing a list of UPDATE? Almost certainly... databases like dealing with sets of data; what your proposing with AWK turns it into a ton of single-row statements. Depending on what you're doing, it might well be fastest to... BEGIN; COPY temp_table FROM 'file'; DELETE FROM real_table WHERE real_table_id IN (SELECT real_table_id FROM temp_table); INSERT INTO real_table SELECT * FROM temp_table; COMMIT; Considering I've to deal with a where clauses anyway... when (and if) should I create an index on the id of temp_t1? t1 will contain 700-1M records while I may update a maximum of 20K a time. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
