Database Administrators Stack Exchange Newsletter – Monday, December 5, 2022

Top new questions this week:

Why won't this UDF work in this check constraint?

I’m trying to use a UDF as an alternative to using a SELECT command within a check constraint. I need to put a constraint on the report table to ensure the signedBy field is the PK of a personnel …

sql-server functions check-constraints

asked by agerber85 Score of 7 answered by ypercubeᵀᴹ Score of 14

Make two columns unique both ways

I’m creating a database which stores football matches. Each round, any given football club may only play once (home or away). For example, after the insert of INSERT INTO Match (Round, Home, Away) …

postgresql database-design index trigger constraint

asked by pear Score of 5 answered by Ergest Basha Score of 4

Postgres stuck with `FATAL: the database system is starting up ` for hours. Should we wait or is this a sign for a corrupted DB?

I found answers to this problem suggesting that it might take a while to restart postgres. But now we are already waiting for over 2 hours which seems extremely long compared to the 5 minutes it …

postgresql

asked by Alex Score of 4 answered by Alex Score of 4

sp_executesql Not Working with Parameters

I am running into a strange problem with dynamic sql that I believe is some sort of formatting issue, and I’ve been tearing my hair out the last few hours over this. I would really appreciate any …

sql-server t-sql dynamic-sql

asked by Austin Score of 4 answered by Erik Darling Score of 11

Two nodes share tokens Cassandra 3.11

After getting some inconsistent query results we noticed that two nodes are claiming ownership of two tokens (-5930834448474026108 and 1653676441334745669). When a node comes up we flip between …

cassandra

asked by Philip Seidel KCF Score of 4 answered by Paul Score of 2

How to delete the remaining binlog files after disabling binlog in MySQL?

I use this solution to delete my binlog before I disable the log. After I disabled it, I tested the below commands to remove the remaining files, but without success.(The purpose of deleting is to …

mysql mysqlbinlog

asked by Niyaz Score of 3 answered by RolandoMySQLDBA Score of 3

UPSERT based on unique combination of (INTEGER, NULL), (NULL, INTEGER)

My table, for simplicity, looks like this: id | foreign_key_1 | foreign_key_2 | value As clear from the names, the fields foreign_key_1 and foreign_key_2 refer to PKs in two other tables. The tricky …

postgresql database-design constraint unique-constraint upsert

asked by Don Draper Score of 3 answered by Erwin Brandstetter Score of 3

Greatest hits from previous weeks:

Case with multiple conditions

I need to change returned value, from select statement, based on several conditions. I tried something like that: ,CASE i.DocValue WHEN ‘F2’ AND c.CondCode IN (‘ZPR0′,’ZT10′,’Z305’) THEN c.CondVal …

sql-server

asked by Muflix Score of 26 answered by Mark Sinkinson Score of 44

How to update a table from a another table

I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2) Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. …

oracle

asked by zjffdu Score of 28 answered by a_horse_with_no_name Score of 30

How to update one table based on another table's values on the fly?

I have a table in the name of ips as below: CREATE TABLE `ips` ( `id` int(10) unsigned NOT NULL DEFAULT ‘0’, `begin_ip_num` int(11) unsigned DEFAULT NULL, `end_ip_num` int(11) unsigned DEFAULT …

mysql update

asked by Alireza Score of 51 answered by Cade Roux Score of 88

PostgreSQL not running on Mac

The error in its entirety reads: psql: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket “/tmp/.s.PGSQL.5432”?…

postgresql mac-os-x

asked by Michael P. Score of 131 answered by Michael P. Score of 56

What's the difference between a temp table and table variable in SQL Server?

This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?

sql-server t-sql temporary-tables

asked by Martin Smith Score of 477 answered by Martin Smith Score of 710

How to easily convert utf8 tables to utf8mb4 in MySQL 5.5

I have a database which now needs to support 4 byte characters (Chinese). Luckily I already have MySQL 5.5 in production. So I would just like to make all collations which are utf8_bin to utf8mb4_bin….

mysql

asked by geoaxis Score of 131 answered by Mathias Bynens Score of 160

Select which has max date or latest date

Here are two tables. SCHOOL_STAFF SCHOOL_CODE + STAFF_TYPE_NAME + LAST_UPDATE_DATE_TIME + PERSON_ID ================================================================= ABE Principal …

oracle greatest-n-per-group

asked by riz Score of 16 answered by Taryn Score of 30

Can you answer these questions?

Polybase In SQL server 2019 and 2022 take so long time

I’m using polybase to load data from PostgreSQL and have some problem The PostgreSQl database and SQL server (for my case is SQL server 2022) are in same machine. I create an external table in SQL …

sql-server postgresql polybase

asked by Duc Trinh Score of 1

deadlock on too many requests and DoS occurred, Why?

I ran into a strange monitoring DBA error “Deadlock” when I try to Pentest my web app. (Send lots of request to DB then Deadlock Occurred.) Why did this occur? I add the Deadlock XML Too: …

sql-server performance query-performance locking deadlock

asked by user355834 Score of 1

Identify unique trips for each user where walking is followed by trip start

I have a table trips with the following information Row User Timestamp Event 1 1 29/06/2022 17:18:03 Walking 2 1 29/06/2022 17:18:02 Walking 3 1 29/06/2022 17:18:01 Start 4 1 28/06/2022 19:21:03 …

postgresql gaps-and-islands sorting

asked by Ruan Score of 1 answered by dwhitemv Score of 0