Tag Info

Hot answers tagged

4

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice: CREATE TABLE item ( item_id serial PRIMARY KEY, grp_id integer NOT NULL REFERENCES grp(grp_id) ); If you have an item_id, better make it unique and ideally a surrogate ...


4

You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc. Then work towards meeting those targets. That is the ...


3

Try changing you statement to match this: FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)') This uses the .value xml method to solve your entitization problem.


3

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


3

The main recommendations appear to be: If you have the install discs / files, then you should be able to install just the Client Tools. Download SQL Server 2012 (SP1) Express (I would try the "SQL Server Management Studio Express (Tools only)" version first) Check out: http://www.mssqltips.com/sqlservertip/1807/sql-server-2008-client-tools-installation/ If ...


2

I've never heard of comments causing a problem. The code you captured from the running server and the posted source are for different SPs. It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code. For testing you could add another INSERT to the ...


2

An array representing the path from the root up to the leaf should achieve the desired sort order: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL LIMIT 10 ) UNION ALL SELECT r.depth + 1, r.path || n.node, n.* FROM node_rec r JOIN nodes n ON n.parent = ...


2

The comment from @ChristianAmmer is the key to answering your question. Why ??? First look the the WHERE clause DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month ) You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds ...


2

I took the liberty of editing your SQL so the clauses are on separate lines. I violated CodeReview group discipline by doing that. Sorry! My suggestion to you is to format your queries so the various clauses are clearly visible, as I did. This kind of formatting, in my experience, makes it easier to understand the logic of queries and to spot errors. ...


2

You cannot use a parameter name (_t) as return type. Use polymorphic types instead: CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp) RETURNS SETOF anyelement AS $func$ BEGIN RETURN QUERY EXECUTE format(' SELECT DISTINCT ON (gid) * FROM %s WHERE vrs_start_time <= $1 ORDER BY gid, ...


1

Use COALESCE to catch and replace NULL values: SELECT f.name AS foo , 'Bazzes: ' || COALESCE(string_agg(b.baz, ', '), '') AS bazzes FROM foo f LEFT JOIN bar b ON b.fooid = f.id GROUP BY 1; concat() is another convenient option as you found yourself, in particular to concatenate multiple values. I suggest the variant concat_ws() ("with ...


1

The simplest method I have found to accomplish this is to swap out the string concatenation operator for the string concatenation function concat(). For some reason the former apparently coerces the entire result to NULL if one operand is null, as opposed to the latter which effectively casts any NULL arguments to ''. So this query performs as desired: ...


1

Yes, if you know what data to insert after corrupt data pages are deleted, you should be fine. make sure you find out the root of corruption to prevent it from happening. also, once everything is fixed, then do a full back and restore it on a different server to make sure everything is fine. Your plan sounds good to me.


1

Since you edited your post here's another try. select orders.customer, temp.order_type, orders2.price from helper temp cross join orders left outer join orders orders2 on orders.customer = orders2.customer and temp.order_type = orders2.order_type order by orders.customer


1

I think Binaya Regmi is correct. When deleting in SQL-Server explicitly name the table you want to delete the rows from - especially necessary when using a join. So in your case change the top row from DELETE category.* to DELETE category


1

Either you run an additional UPDATE (best in one transaction): UPDATE site_stats s SET total_users = 0 , monthly_users = 0 , weekly_users = 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id) Or use this instead (I would do that): UPDATE site_stats s SET total_users = COALESCE(u.total, 0) , monthly_users = ...



Only top voted, non community-wiki answers of a minimum length are eligible