Database Administrators Stack Exchange Community Digest

Top new questions this week:

How to use aggregate column in where clause when using group by

I've following table CREATE TABLE `books` ( `book_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) DEFAULT NULL, `author_fname` varchar(100) DEFAULT NULL, `author_lname` varchar(100) ...

mysql aggregate where  
user avatar asked by Md. A. Apu Score of 2
user avatar answered by Bill Karwin Score of 3

Why does is JSON_EXTRACT's return value treated differently depending on the operator?

I'm using MySQL 8.0 (inside Azure Database for MySQL Server). I've noticed some strange behavior while querying with the "json_extract" functionality. In the example below, I am querying a ...

mysql json varchar  
user avatar asked by WannabeCoder Score of 2
user avatar answered by Bill Karwin Score of 3

Migrating from PostgreSQL to MySQL (data with \r\n)

I'm trying to migrate a database from PostgreSQL to MySQL on an Ubuntu 23.04 system (latest version of both). So far, it looks like things are fine, except for one problem. In one of the character ...

mysql postgresql migration pg-dump  
user avatar asked by Ray Score of 1

How to order by COALESCE by some fields?

Let says I've this query: DECLARE @QueryString NVARCHAR(MAX) ; SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','') + 'SELECT ' + '''' + ...

user avatar asked by markzzz Score of 1
user avatar answered by Peter Score of 0

Postgres heavy updates cause fast table growth

I have a question related to an incident that happened on one of our Postgres DBs. We have a backend service that keeps track of user's activity in some kind of online event. The service uses Postgres ...

postgresql postgresql-performance  
user avatar asked by Marko Stojakovic Score of 1
user avatar answered by jjanes Score of 1

SQL Trigger Calling out Domain\Group

I am creating a trigger in SQL Server 2019 to secure the amount of connections users can have into my SQL server. I am having an issue getting my SYSDBAs access using the domain group. I would rather ...

sql-server trigger sql-server-2019  
user avatar asked by JukEboX Score of 1
user avatar answered by SergeyA Score of 2

pg_restore drops extension plpgsql

We face a situation where pg_restore drops extension plpgsql and then recreates it. This is done by a CI job we have but i could not reproduce it. These are the commands we use: pg_dump -d $...

plpgsql pg-dump pg-restore  
user avatar asked by Vasileios Giannakidis Score of 1
user avatar answered by Laurenz Albe Score of 0

Greatest hits from previous weeks:

Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect

I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on? I created a MySQL 5.1 database on an Ubuntu server I can access it locally. MySQL ...

mysql mysql-workbench  
user avatar asked by Dizzley Score of 50

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...

postgresql windows permissions  
user avatar asked by Celeritas Score of 37
user avatar answered by dezso Score of 66

Difference between database vs user vs schema

What is the difference (if any) between a database, a user, and a schema?

oracle schema users  
user avatar asked by Ravi Score of 108
user avatar answered by Mat Score of 111

How to select the first row of each group?

I have a table like this: ID | Val | Kind ---------------------- 1 | 1337 | 2 2 | 1337 | 1 3 | 3 | 4 4 | 3 | 4 I want to make a SELECT that will return just the ...

oracle greatest-n-per-group  
user avatar asked by BrunoLM Score of 79
user avatar answered by mik Score of 48

How to see list of databases in Oracle?

Is there an equivalent to MySQL SHOW DATABASES statement? Is it possible to find databases in a cluster? i.e. databases present on the network on some other system? Could I analyze the files present ...

user avatar asked by Kshitiz Sharma Score of 45
user avatar answered by Mat Score of 28

How to represent foreign key in an ER diagram?

Suppose I have a 'Transactions' table which has a column 'Customer ID' (Foreign Key) and a Customer Table having 'ID' (Primary key). How do I show the relation between the two tables and showing that ...

database-design database-diagrams  
user avatar asked by MK Singh Score of 19
user avatar answered by Walter Mitty Score of 20

How to add the Debug button to SSMS v18?

The Debug button is present on this version of SSMS: But it is not present on version 18, preview 4: I have tried in several ways to add the Debug button to my SSMS but not successful. Is there a ...

sql-server ssms  
user avatar asked by Marcello Miorelli Score of 82
user avatar answered by Pred Score of 73

Can you answer these questions?

PostgreSQL - Table partitioned on date doesn't prune partitions when querying with a daterange

Our tables are partitioned by month on the dt date column CREATE TABLE IF NOT EXISTS ta ( link_dir text COLLATE pg_catalog."default" NOT NULL, dt date NOT NULL, tod time without ...

postgresql postgresql-performance partitioning postgresql-15  
user avatar asked by raphael Score of 1

There was a recoverable, provider-specific error, such as an RPC failure. SQL to MySQL linked server no longer working

I have been searching all day and trying every manner of solution that I come across, yet nothing has resolved this issue. Here is my was working code: delete RemoteTable from OPENQUERY (LIGHTSAIL, '...

sql-server mysql linked-server  
user avatar asked by Yourguide Score of 1

Percona XtraDB Cluster: State transfer failed: -13 (Permission denied)

I have started the first node with the bootstrap option, and it is running well and serving queries. sudo service mysql@bootstrap start However, when I try to add the 2nd and 3rd nodes, they both ...

mysql master-slave-replication percona-server xtradb-cluster  
user avatar asked by Cyril Graze Score of 1
You're receiving this message because you subscribed to the Database Administrators community digest.
Unsubscribe from this community digest       Edit email settings       Leave feedback       Privacy
Stack Overflow

Stack Overflow, 14 Wall Street, 20th Floor, New York, NY 10005