sql
How can I get a hash of an entire table in postgresql?
I know this is old question, however this is my solution: SELECT md5(CAST((array_agg(f.* order by id))AS text)) /* id is a primary key of table (to avoid random sorting) */ FROM foo f;
How to allow only one row for a table?
A UNIQUE constraint allows multiple rows with null values, because two null values are not considered to be the same. (Except when using NULLS NOT DISTINCT in Postgres 15 or later.) Similar considerations apply to CHECK constraints. They allow the expression to be true or null (just not false). Again, null values get past the … Read more
Percentage from Total SUM after GROUP BY SQL Server
You don’t need a cross join. Just use window functions: SELECT P.PersonID, SUM(PA.Total), SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER () AS Percentage FROM Person P JOIN Package PA ON P.PersonID = PA.PackageFK GROUP BY P.PersonID; Note that you do not need the JOIN for this query: SELECT PA.PersonID, SUM(PA.Total), SUM(PA.Total) * 100.0 / SUM(SUM(PA.Total)) OVER … Read more
How do I merge two tables in postgresql?
You should use UNION. select * from table1 union select * from table2 To insert into table 1: INSERT INTO TABLE1 select * from table2 where not exists( select * from table1 where name=TABLE2.Name and count=TABLE2.Count )
conversion of a varchar data type to a datetime data type resulted in an out-of-range value
Ambiguous date formats are interpreted according to the language of the login. This works set dateformat mdy select CAST(’03/28/2011 18:03:40′ AS DATETIME) This doesn’t set dateformat dmy select CAST(’03/28/2011 18:03:40′ AS DATETIME) If you use parameterised queries with the correct datatype you avoid these issues. You can also use the unambiguous “unseparated” format yyyyMMdd hh:mm:ss
Is it bad to rely on foreign key cascading?
I’ll preface this by saying that I rarely delete rows period. Generally most data you want to keep. You simply mark it as deleted so it won’t be shown to users (ie to them it appears deleted). Of course it depends on the data and for some things (eg shopping cart contents) actually deleting the … Read more
How does one write a DELETE CASCADE for postgres?
Postgres foreign keys support the CASCADE deletes: slice_id integer REFERENCES slice(id) ON DELETE CASCADE etc
Can SQL Server SQL_Latin1_General_CP1_CI_AS be safely converted to Latin1_General_CI_AS?
Here is a more complete answer: https://www.olcot.co.uk/revised-difference-between-collation-sql_latin1_general_cp1_ci_as-and-latin1_general_ci_as/ The key difference between these collations is in how they apply character expansion rules. Certain Latin characters may be expanded into multiple characters. The SQL_xxxx collations may ignore these character expansions when working with non-unicode text, but apply them for unicode text. As a result: joins, sorts, and … Read more
Update one of 2 duplicates in an sql server database table
Try This with CTE and PARTITION BY ;WITH cte AS ( SELECT ROW_NUMBER() OVER(PARTITION BY Column1 ORDER BY Column1 ) AS rno, Column1 FROM Clients ) UPDATE cte SET Column1 =Column1 +’ 1 ‘ WHERE rno=2