sql – Page 42 – Tarik Billa

SQL: Unique constraint when column is a certain value

December 15, 2023 by Tarik

PostgreSQL can address your needs via it’s “Partial Index” feature. In practice this is accomplished by adding a where clause to the create index statement. Sample: CREATE INDEX my_partial_ix ON my_sample_table (my_sample_field) WHERE (my_sample_field = ‘rows to index’); Take a look here: http://www.postgresql.org/docs/current/interactive/indexes-partial.html Pay particular attention to the section Example 11-3. Setting up a Partial … Read more

How to programmatically create a Java ResultSet from custom data with no database

December 15, 2023 by Tarik

Create your own AbstractResultSet class, one that (like AbstractQueue) implements all methods by throwing UnsupportedOperationException (Eclipse autogenerates these methods in a split second). Now extend AbstractResultSet. The subclass can override only the methods you’re interested in implementing.

TSQL: Call a stored procedure from another stored procedure and read the result

December 15, 2023 by Tarik

You can create a temp table and then use INSERT INTO #MyTable EXEC procedure @param. There are some other techniques listed here.

Is there a difference between Surrogate key, Synthetic Key, and Artificial Key?

December 15, 2023 by Tarik

Surrogate key, synthetic key and artificial key are synonyms. Technical key is another one. They all mean “primary key which doesn’t have a business meaning”. They are distinct from natural or business keys which have a meaning beyond the system at hand. For instance, consider the SO user account. We are identified by two keys. … Read more

large amount of data in many text files – how to process?

December 15, 2023 by Tarik

PostgreSQL: NOT IN versus EXCEPT performance difference (edited #2)

December 15, 2023 by Tarik

Query #1 is not the elegant way for doing this… (NOT) IN SELECT is fine for a few entries, but it can’t use indexes (Seq Scan). Not having EXCEPT, the alternative is to use a JOIN (HASH JOIN): SELECT sp.id FROM subsource_position AS sp LEFT JOIN subsource AS s ON (s.position_id = sp.id) WHERE s.position_id … Read more

Alter column default value

December 15, 2023 by Tarik

I think issue here is with the confusion between Create Table and Alter Table commands. If we look at Create table then we can add a default value and default constraint at same time as: <column_definition> ::= column_name <data_type> [ FILESTREAM ] [ COLLATE collation_name ] [ SPARSE ] [ NULL | NOT NULL ] … Read more

How to sort by count with postgresql?

December 15, 2023 by Tarik

You’ve aliased the table and column as the same thing, so don’t do that. It’s not invalid, just tough to follow. Anyway, include all columns that you’re selecting that aren’t aggregates in your group by: select count(w.id) as mycount, w.company_id, c.company_name, c.city from companies c left join workers w on c.id=w.company_id group by w.company_id, c.company_name, … Read more

How to implement a do-while loop in tsql

December 15, 2023 by Tarik

This is effectively a Do-While loop: WHILE (1=1) BEGIN — Do stuff… IF (some_condition is true) BREAK; END But as @Joel Coehoorn noted, always try to use a set based approach first. I would only resort to a loop if I can’t think of a way to solve using set operations.

Update multiple rows in a single MySQL query

December 14, 2023 by Tarik

This is most clear way UPDATE test SET col2 = CASE col1 WHEN ‘test1’ THEN 1 WHEN ‘test2’ THEN 3 WHEN ‘test3’ THEN 5 END, colx = CASE col1 WHEN ‘test1’ THEN ‘xx’ WHEN ‘test2’ THEN ‘yy’ WHEN ‘test3’ THEN ‘zz’ END WHERE col1 IN (‘test1′,’test2′,’test3’)