All Questions

Filter by
Sorted by
Tagged with
0
votes
4answers
19 views

How to check 'contains' in sql?

Suppose 'allotedTo' column contains multiple values like 'ABC/xyz/RAM' How do I load all rows, with 'allotedto' column containing any one of given input values? (eg: all rows alloted to RAM or RAJ) ? ...
0
votes
1answer
84 views

Where does Attunity's CDC services write error data?

We use Attunity's CDC services to move data from Oracle to SQL Server. Using the Oracle CDC Designer snap-in tool, I can see the current status of the service and an error message if a problem has ...
0
votes
0answers
11 views

Sql Server - Get database constraints of all tables ( default and check constraints)

I need to get constraints of all columns, tables in my database result like this : DatabaseName | SchemaName | TableName | ColumnName | ColumnType | ConstraintName | - ConstraintType | Definition | ...
0
votes
0answers
3 views

OpenProject pg_restore - input file does not appear to be a valid archive

I'm trying to restore my OpenProject's database created with the command sudo openproject run backup this command created the file postgresql-dump-20190722131834.pgdump which I'm trying to restore ...
0
votes
0answers
6 views

slow count queries - what are we doing wrong?

Using Postgresql 9.6.14, running the following query: SELECT COUNT(*) AS "__count" FROM "crm_client" INNER JOIN "crm_role" ON ("crm_client"."role_ptr_id" = "crm_role"."id") INNER ...
1
vote
1answer
193 views

Merge two rows letting the second one override the first in PostgreSQL

Say that you want to build an append only table in Postgres, where each modification is actually just a new row added to the table. Say that you have the below simple, and disregard all questions ...
0
votes
1answer
18 views

How do writers not block reads in PostgreSql

Since readers do not block writers and writers do not block readers is what MVCC is all about, how does postgresql prevent writers who are in the process of committing their changes to blocks/pages ...
21
votes
2answers
21k views

How to list all constraints of a table in PostgreSQL?

How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?
1
vote
1answer
5 views

Db2 REORGCHK returns the SQL2310N (Error “-668”) message

I have a Db2 database on my Linux laptop. The database is not heavily loaded. I access it from time to time for the development purposes. I removed a column: db2 "alter table SCHEMA.TABLE drop ...
2
votes
2answers
47 views

Backup on Share NFS fails - Windows Server 2008 R2

I've a network disk where backups are stored every night, but after some hour the backup fails with the following error: Executing the query "BACKUP DATABASE [foo] TO DISK = N'\\XXX.XX.XX...." ...
0
votes
2answers
20 views

should i create a new table or save static data in all the tables

I'm using postgres database, currently I have countries table, which has only 4 countries (might add 1 or 2 more countries later) Table 1 : countries country_name India USA UK Australia I have ...
0
votes
0answers
6 views

Good reference for “database assertion” or “multi-row-constraint”?

These days I am writing some technical documentation. I am looking for a good reference where "database assertion" or "multi-row-constraint" is well-described. Explained in simple terms. I just want ...
0
votes
1answer
8 views

How to check and change function volatility in Postgres?

I have a function that I think is defined as volatile, but I now know I want it stable. How can I check the function volatility (volatile, stable, immutable) of a certain function? When a function ...
1
vote
3answers
71 views

T-SQLRevoke permissions to all tables in the sql server in one go

How can we revoke a certain permission to all tables? I've tried (T-SQL): REVOKE SELECT on schema::DBO TO [public] AS [dbo] But nothing happens.. All tables still have Select permission. I can ...
2
votes
2answers
2k views

Execute `sp_spaceused` for all tables in all databases

How to execute sp_spaceused for all tables in all databases? i.e. how to execute sp_spaceused inside sp_msforeachdb. e.g: sp_msforeachdb 'sp_msforeachtable'sp_spaceused"?"''
1
vote
1answer
121 views

PgAdmin 3 crashed when font size was increased

I am using PgAdmin 3 on Postgresql 9.4 on Windows 7. All things work well until I resized browser fonts from 12 to 18. I cant create database, rename table and even change the font size to what it ...
0
votes
0answers
7 views

Subquery caching in join

I have very complex MySQL queries that use the same subquery over and over again. I was wondering if MySQL will run the subquery multiple times or use the previous resultset. Is there any way to tell ...
1
vote
1answer
9 views

How to find resource intensive SQL operations in Postgresql? High CPU usage spike

I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period. ...
0
votes
1answer
30 views

All my database on the fail over server showing in “Synchronizing” state

Current Configuration: I have two SQL server, which is connected on the failover mechanism which means one is primary and one is secondary. What did I did? I just restarted both the services on both ...
0
votes
1answer
20 views

Is it possible for a node to host multiple cluster in relation to Always On AG

In my example, I have 3 database servers with their own DR servers, own availability group, own clusters. Is it possible to setup another server which can be part of the 3 clusters and be a secondary ...
2
votes
3answers
52 views

Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, …?

We have items of three types to be stored and queried according to some business logic. create table a_table ( item_a varchar2(30), item_b varchar2(16), item_c varchar2(2), -- other ...
0
votes
0answers
11 views

Psql, cte twice for same thing return bad value

What I need. Same values for category tree as in first case, first case is for name (cte), second is for id (cte2). What I tried. with recursive cte as( select category_id,category_parent,...
0
votes
1answer
31 views

Non Clustered Index using INCLUDE

I am working on Consolidation INDEX. I have 3 index on a table Create NONCLUSTERED INDEX IX1 on TableA(a asc,b asc) INCLUDE(U,V,X,Y,Z) Create NONCLUSTERED INDEX IX2 on TableA(a asc,b asc) INCLUDE(K,L,...
1
vote
1answer
366 views

Streaming replication version between different minor versions

I want to know few details about streaming replication. I want to setup streaming replication between version 9.4.4 and 9.4.20 ? I have the master server in centos6.6 with postgres version 9.4.4 ...
0
votes
2answers
40 views

how to use the same timestamp along several tables?

Lets say a have 2 tables and both have a datetime column. On table A, on datetime column, lets say, ts1, is non null, default is CURRENT_TIMESTAMP and on update CURRENT_TIMESTAMP; The other table, B,...
0
votes
1answer
11 views

TSQL - use columns as date range

How can I retrieve the cto column using an @variable? For example, for @Variable = '2019-03-02' the expected output is'MAR 1 - MAR 7'.
0
votes
1answer
2 views

ibdata1 back to it's original size after deleting it

I have followed the following steps in order to save space but I am surprised after restarting MySQL ibdata1 gets back to it's original size. As you want to reclaim the space from ibdata1 you ...
0
votes
1answer
92 views

SQL SERVER Checking for Compatibility of the Database File Locations on the Server Instance that Hosts Secondary Replica

I am using SQL Server 2017 and SSMS 18.0 Version. During creation of availability group at validation step at primary Server (DBN1), i am facing that error i.e. the following required directories do ...
0
votes
0answers
28 views

Setting up TempDB for more than one instance of SQL Server

I've been given the task of transferring the contents of a SQL Server instance from one Windows server to another Windows Server. One of the many things I need to do on the new server is set up the ...
0
votes
1answer
159 views

PRKP-1001 : Error starting instance,CRS-0215 : could not start resource

We have 3-node Oracle 10g R2 RAC database. Following is the status of various RAC components:- ora....b2.inst application ONLINE OFFLINE ora....db2.srv application ONLINE ONLINE ...
0
votes
0answers
3 views

Elastic search data synchronization with different databases

We are using elastic search hosted in AWS for apps wide search in our organization. We had initially dumped our existing data using logstash. However, we are struck at keeping the Elastic DB in sync ...
0
votes
1answer
118 views

postgresql revoke access to login role

I executed this query that gave schema access to a login role called test GRANT ALL ON SCHEMA open_waters TO test; I want to now revoke the access to test
0
votes
1answer
89 views

ADD and DROP triggers frequently on a database

I have a table from which I am deleting the data. Whenever a row is deleted, the row matching the data from other tables must also be deleted. So I am planning to achieve the same using triggers. The ...
0
votes
0answers
9 views

Distributed Database and Centralised Database performance with increasing concurrent connections [on hold]

I want to know what kind of trend you would expect to see in the performance of distributed and centralised databases as you increase the number of concurrent connections? I will be running an ...
0
votes
0answers
9 views

MSSQL transactional replication is out of sync

I have setup transactional replication in MSSQL and I am using PULL to retrieve the data from the subscriber. Recently, I noticed that the data in my subscriber is not synced up with the database on ...
-1
votes
1answer
27 views

Performance using a jsonb column in PostgreSQL?

The current schema at my company uses jsonb columns heavily (which get queried on) which although works, I have heard is not good design. Someone told me querying on a field in a jsonb column would ...
1
vote
1answer
99 views

Slow MySQL searching from millions record

My table looks like this. id | hash | date I've an index on hash which is in binary mode. I have about 3 million rows right now. But if I query with the following query, it takes about 4 seconds. ...
0
votes
1answer
11 views

Sharding a MongoDB instance by a spatial key and a date

This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries ...
1
vote
1answer
104 views

Cities and States (a few data): single table or splitted?

I am designing a DB schema and I need to store cities and states. I expect only a few rows, let's say, 20 cities. Example: id | city | state 1 | Rio de Janeiro | RJ 2 | Niteroi |...
1
vote
2answers
292 views

Not able to Create Database or Restore Database on the Azure SQL Managed Instance

I am trying to restore database on the Azure SQL Managed Instance using Azure blob container URL. Reference CREATE CREDENTIAL [https://zzdevbackup.blob.core.windows.net/dbbackup] WITH IDENTITY = '...
0
votes
1answer
21 views

How do I find a list of customers that have not placed an order in the past 1 year?

I'm having trouble figuring out how to write this query: We have a sales table but the sales table only represents customers that have purchased. We have a customers table that represents all of the ...
4
votes
1answer
109 views

SQL Server Replication

We had an issue with replication, where a large (7.5 million row) update was done on a 33 million row table. Replication converted that to 7.5 million individual update statements. When my alerting ...
-2
votes
0answers
13 views

Which database to use? Storing one string every second for several days

I would like to observe some cryptocurrency prices and for that, I want to store a string, everey second in a database for ca. 3 days. The string will vary in length, it will be 2500 characters max. (...
0
votes
1answer
25 views

CREATE TABLE in Postgres locks pg_class forever

I have a PostgreSQL cluster with few databases. In one of the DBs, if I try to create a new table, the command never ends until I kill the the linux proccess. I checked pg_lock and found that pg_class ...
3
votes
1answer
3k views

How do you monitor slow query in AWS RDS Postgres

My RDS Postgres has 5 read replica and it's the maximum number of read replica and cannot scale out by increasing read replica. In this situation, some of the read replicas occur too much high CPU ...
3
votes
1answer
11k views

How do I insert record only if the record doesn't exist?

I have a PaymentInformation table ID NAME Start End 1 Tina 1/7/2014 1/17/2014 2 Alice 3/7/2014 3/17/2014 3 John 5/7/2014 5/17/2014 4 Michelle 7/...
0
votes
0answers
11 views

PANIC HELP Locked out of Postgres Databases and unable to find pg_hba.conf

I am using PgAdmin 4 to access a number of local databases created and hosted on postgresql 11. In a recent update, I set a global password for my databases, but forgot to set passwords for my user ...
9
votes
3answers
31k views

Using PostgreSQL 8.4, how to convert bytea to text value in postgres?

In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly ...
0
votes
1answer
88 views

Affected rows after update with OpenQuery

Is there a way to get the affected row count, when updating through an connection server? I'm using MSSQL connected to MySQL with ODBC. UPDATE OPENQUERY (MyConnection, 'UPDATE recordid, status FROM ...
9
votes
1answer
113 views

Attempts to reclaim unused space causes the used space to increase significantly in SQL Server

I have a table in a production database that has a size of 525 GB, of which 383 GB is unused: I'd like to reclaim some of this space, but, before messing with the production DB, I'm testing some ...

15 30 50 per page