All Questions

Filter by
Sorted by
Tagged with
0
votes
1answer
60 views

How to pivot multiple columns in PostgreSQL 9.5?

I have the sample data as follows and want to find if I can use crosstab function in this case. Sample Data: ITEM | CD | TYPE |PARTS | PART | CNT | Item 1 | A | AVG |2 | 1 | 10 | Item ...
1
vote
1answer
64 views

Is it useful to index a column if it is already the first column in a composite index? [closed]

Suppose I have this table (simplified from my project): create table BillTax ( BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID), TaxCode varchar(20) not null,...
-1
votes
1answer
33 views

How to loop data month wise for the targeted dates [on hold]

I need to load data from a primary table to an archive table by month or week. As an example, I have a table called transactions with a column named return_datetime where I have 5 years or data. I ...
0
votes
0answers
5 views

Where can I find “pgadmin_histoqueries.xml” equivalent for pgAdmin 4 on Mac?

I am dealing with a frustrating issue that is described in this question. The accepted answer gives the solution of deleting a file that was corrupted, but I can't find the file on Mac. I suspect that ...
2
votes
2answers
27 views

Summing the count of two columns in postgres that contain the same value

I want to sum the total number of occurrences of one value across two columns in the same table. So, an example would be: id |node1 |node2 1 | 111 | 123 2 | 122 | 124 3 | 111 | 125 4 ...
7
votes
2answers
343 views

Can I bulk insert into an empty page-compressed table and get full compression?

I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, ...
0
votes
1answer
61 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
0
votes
1answer
14 views

Implementing gMSA account for SQL FCI

I have a question on implementing gMSA account for managing authentication to SQL server. Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's ...
0
votes
0answers
19 views

Import folder with Multiple CSV's into SSMS w/ out SSIS

I am wondering what's the best and most effective way to import csv's stored in a local folder into DB w/ in SSMS. Folder path- C:\Users\ngutierrez\Desktop\Open_Air_Tables Folder contains about 30 ...
0
votes
1answer
23 views

For MySQL, how to INSERT the result of a SELECT statement? [closed]

For MySQL, how to INSERT the result of a SELECT statement? I tried SET @input1 = 1; SET @input2 = 2; INSERT INTO my_table (col1, col2) SELECT @input1 AS col1, @input2 AS col2 WHERE @...
0
votes
1answer
24 views

Is it possible to commit a transaction that is waiting to RollBack?

I'm using Aurora MySql 5.6.10a and a query that was loading a table with 700 million rows crashed and now it's rolling back. I turned autocommit off to try to speed the data load up, but now it's ...
0
votes
0answers
39 views

Find full name from AD alias in SQL Server

We have recently migrated from Sybase/SAP ASE to MSSQL Server. master..syslogins in sybase has two columns name and fullname. Name would be AD alias name and fullname is Full Name of the user. ...
0
votes
2answers
32 views

When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes

I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance ...
0
votes
1answer
16 views

implementing MySQL Natural Key [closed]

I'am having difficulties implementing a natural key in MySQL. It keeps asking for the surrogate key. Create Table IF NOT EXISTS Employer ( idEmployer int not null, nameEmployer varchar(45), ...
-1
votes
0answers
12 views

MYSQL group the columns and rank [closed]

I'd. Regno. Class. Subject Score 1. 111. JSS1. Mth. 60 2. 112. JSS1. Mth. 50 3. 111 JSS1. Eng. 60 4. 112. ...
0
votes
0answers
32 views

Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file (to send to a consultant). When I run it in BIDS 2008r2 it runs just fine, no ...
0
votes
0answers
8 views

Can two or more versions of MongoDB run concurrently as Windows services?

I'm working on two .Net projects which use different versions of MongoDB (3.4 and 4.0). By using separate data and log directories (as per this article) I can run both versions concurrently on demand ...
8
votes
1answer
540 views

How to disable “Completion time:…” in SQL Server Messages window

Every query I run in SSMS append the annoying message: "Completion time:...". How can I disable that text?
0
votes
0answers
8 views

Would MySQL `x.example.%` host match the `x.example.attacker.com` domain?

According to the mysql documentation the host x.example.% matches connecting from x.example.net, x.example.com, x.example.edu, and so on. I don't have the setup to test, so do you know if % also ...
0
votes
0answers
8 views

Best way to update user credits for API?

I am constructing an API that will use credits to determine if an API call can be made and the credits will be updated after a successful API request. The simple procedure after an API call is made ...
0
votes
0answers
12 views

Write aggregate function in select predicate in relational algebra

Can i write aggregate function in select predicate in relational algebra? I am trying to find the maximum salary in the table Employee(name,salary) I am doing this to get max salary. σₛₐₗₐᵣᵧ=Gₘₐₓ₍...
0
votes
1answer
14 views

MySQL replication without baseline data import

I've been reading about MySQL replication and all the resources I've looked at say that a data import (be it a mysqldump, or a raw data dump import) is needed before replication can start. I'm trying ...
0
votes
1answer
18 views

Postresql dynamic index creation for many tables

How can I create an index in Postgres dynamically over many tables but always with different names. Lets assume table x has a column y with all table names of hundreds tables which need to get an ...
1
vote
1answer
24 views

remove Analysis service from a SQL 2014 cluster

Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed now
0
votes
1answer
17 views

Why pg_wal dir does not clean up while logical replication

my main question - why pg_wal dir is extremely growing up? I have two postgresql instances. The first was set up as master: wal_level: "logical" max_wal_senders: 5 max_replication_slots: 10 Also, ...
0
votes
1answer
23 views

Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2) [closed]

I have a few tables and I am facing this issue most likely with the table "item". When I remove restaurant_id and category_id, it works fine. CREATE TABLE IF NOT EXISTS restaurant ( id ...
0
votes
1answer
36 views

Do database actions inside a transaction become slower as the transaction grows? [closed]

I have a PostgreSQL database running a transaction. Inside that transaction I process around 160 records from an Excel file. As it runs, I see the processing becomes slower with each record (0.05 secs ...
0
votes
1answer
29 views

Please help me optimize MYSQL query

This query is very slow: SELECT ( SELECT SUM(ile.gross) FROM item_ledger_entry AS ile WHERE clr.docNr=ile.docNr AND clr.serialNo=ile....
0
votes
0answers
15 views

SQL Server Windows backup restore on centos

I have installed sql server 2017 on my centos 7 machine. I need to restore my database backups from windows machine to this machine. I could successfully run filelistonly. However, for restore, I'm ...
0
votes
1answer
14 views

Log/Show number of (select) queries per table

I used the following view to summarize some metrics in my db. Is there any way to log the select queries per specific table instead? create or replace view _dba_query_stats as select SUBSTRING(...
0
votes
2answers
65 views

Tools for SQL Deadlock Statistics [closed]

One of my company's SQL database generates a lot of deadlocks per day recently (someday >100) I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same ...
0
votes
3answers
34 views

Select youngest record

I have a MariaDB table "mdata" with the following schema: +-----+-----------+---------------------+------+ | id | room | date | temp | +------+-----------+---------------------...
0
votes
2answers
37 views

How to count decimals without zeros

How can I count all decimal place up to the first zero For example I have a column named x with an entry of numeric datatype value 1.955000000 I want to count the decimal places without the zeros. ...
1
vote
1answer
24 views

Incorrect “Sensitive data columns should be classified” report

Every week I'm getting a failed "Vulnerability Assessment" email report from my Microsoft Azure SQL Database that is incorrect. It complains about columns that have is_computed column in sys.columns ...
0
votes
1answer
29 views

Removing MySQL Orphan Tables in Google's Managed MySQL

I have a number of orphan tables that I am unable to remove in Google managed MySQL instance. https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html describes how to find them ...
0
votes
1answer
23 views

Connectivity issues in replication process

I've been trying to add a new node to a replica set without success due to these errors: 2019-08-12T06:48:15.031+0200 I ASIO [NetworkInterfaceASIO-RS-0] Ending connection to host mongo03:...
-1
votes
0answers
6 views

PHP 7 Warning: Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead [migrated]

When i upgrade my php from 5.6 to 7.3 i got this problem on my code: "PHP 7 Warning: Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead". This is my ...
1
vote
1answer
80 views

SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement

To take backup of a big table (using SELECT .. INTO .. ) took me almost 4 hours in a machine with 4 CPUs and 16 GB of RAM. No external application/process were accessing the table during the operation....
0
votes
1answer
17 views

How to install mysql/mariadb on NIXOS?

I installed mariadb via configuration.nix. For the installation I made the following entries in my configurastions.nix. services.mysql.enable = true; services.mysql.package = pkgs.mariadb; ...
0
votes
0answers
23 views

CSV -> Python -> Pandas.DF -> PostgreSQL (UPSERT, THEN Cull data) [closed]

Problem Statement: I have multiple machines all running the same software, they output CSV files. I need to load this information into a database. The challenge is the user can, with permission, ...
0
votes
1answer
60 views

How to alter database hour and date in Oracle?

How can I alter the date and hour of the database? I don't want to alter the hour and date from the server, only in database.
0
votes
0answers
16 views

How would I model a database that selects from a group of tables? [duplicate]

this is the first time I have ever tried to design my own database, so I'm sorry if this is a bit of a stupid question. I have been trying to figure out how to create a database to sort an inventory ...
0
votes
0answers
7 views

link two tables with sqlalchemy [migrated]

I have built two tables in my postgres CLI (test2_table: id, name, age, profession, city, country). The second is referenced to my 1st table's id and has a column called wage. When I run the syntax ...
-2
votes
1answer
44 views

SQL Server Event Log Summary [closed]

I want to generate a daily summary of SQL Server Event Logs which will be sent via email. I have currently been able to set up SQL email alerts for error logs,but they are sent individualy when an ...
-1
votes
0answers
26 views

It seems `conditions` run before 2 tables is joined? [closed]

explain analyze select "MdChapter"."id", "MdComic"."id" as "MdComic.id" from "md_chapters" as "MdChapter" left join "md_comics" as "MdComic" on ...
1
vote
1answer
25 views

MariaDB/MySQL SSL Replication Failure

After searching for a solution for the last 6 hours, I have come up dry in my attempt to add SSL to the replication. I managed to get it to connect with SSL via the mysql command line tool without ...
1
vote
2answers
48 views

Unable to retrieve query plan of running query because of blocking

I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes. Probably some plan caching issue. But the problem is ...
0
votes
1answer
29 views

Schema and Query design for table with multiple languages [closed]

I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
0
votes
1answer
20 views

Add a new node to Mongo Replicaset using Rsync

I have a 3 shard mongo cluster. Each shard has a 3 node replica set. On each shard, the data size is 3 TB. I want to add 1 more node to each replica set. But its taking lot of time. The initial sync ...
1
vote
3answers
55 views

Compare 2 columns in 2 different tables with non uniques values

I want to compare and check whether the values in columnA of table A exist in column A of Table B. But the columnA consist of duplicate values in both the table.

15 30 50 per page