Topics created by guilherme

An ORDER BY applies to the entire UNION result set. You cannot sort individual SELECT results within the UNION.

You'll have to rewrite the queries so that they can use the GIN index. FOr the first query, that would be: SELECT id, tags ->> 'tag_1' as tag_1 FROM test_tb WHERE tags @> '{ "tag_1": "val_1" }'; For the second query, no index can help, since there is no WHERE or ORDER BY clause. The third query is tricky, because it contains OR, but since the result set contains the primary key, you can rewrite it to use the index: SELECT id, tags ->> 'tag_1' FROM test_tb WHERE tags @> '{ "tag_1": "dv_0", "tag_3": "dv_15", "tag_15": "dv_22" }' UNION SELECT id, tags ->> 'tag_1' FROM test_tb WHERE tags @> '{ "tag_1": "dv_0", "tag_3": "dv_15", "tag_5": "dv_6" }' UNION SELECT id, tags ->> 'tag_1' FROM test_tb WHERE tags @> '{ "tag_12": "dv_9" }'; Here, UNION is used instead of the OR. The query becomes longer, but each of the subqueries can use the index.

SELECT SUM(col1 IS NOT NULL), -- if "empty" means NULL SUM(col2 != ''), -- if "empty" means empty-string ... FROM tbl; A non-zero result means that the column is 'not empty'.

Your query seems to from v10 and thus is missing two reg types. 'pg_catalog.regcollation' 'pg_catalog.regnamespace'

First your query contains https://dev.mysql.com/doc/refman/8.0/en/keywords.html Condition and Current_date please avoid using reserved words. and secondly you are using SET twice. Try: LOAD DATA LOCAL INFILE 'C:\Users\Shrey\OneDrive\Desktop\assignments\Samyak\Comorbidity Covid-19.csv' INTO TABLE `comorbidity` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 ROWS (@Current_date_var, @Start_Date_var, State, Condition_Group, `Condition`, Age_group, Covid19_deaths, Number_of_mentions) SET `Current_date` = STR_TO_DATE(@Current_date_var, '%m-%d-%Y') , Start_Date = STR_TO_DATE(@Start_Date_var, '%m-%d-%Y') ; https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1850517047b1e27ca4c4f5ed73e81414 Be aware that STR_TO_DATE(@Current_date_var, '%m-%d-%Y') returns the date in the yyyy-mm-dd format, so you should have a date type column. For example: SELECT STR_TO_DATE('02-21-2022', '%m-%d-%Y'); Returns: STR_TO_DATE('02-21-2022', '%m-%d-%Y') 2022-02-21 https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=481b7be3a0cc6056668870921d30bd7c Example with the updated data: mysql> CREATE TABLE `comorbidity` ( `State` varchar(20) NOT NULL, `Condition_Group` varchar(50) NOT NULL, `Condition` varchar(45) NOT NULL, -> `Current_date` date NOT NULL, -> `Start_Date` date NOT NULL, -> `State` varchar(20) NOT NULL, -> `Condition_Group` varchar(50) NOT NULL, -> `Condition` varchar(45) NOT NULL, -> `Age_group` varchar(15) NOT NULL, -> `Covid19_deaths` int NOT NULL, -> `Number_of_mentions` int NOT NULL -> ) ; Query OK, 0 rows affected (0.57 sec) mysql> show variables like '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 2 rows in set (0.00 sec) mysql> mysql> LOAD DATA INFILE '/var/lib/mysql-files/comorbidity.csv' LINES TERMINATED BY '\n' IGNORE 1 ROWS (@Current_date_var, @Start_Date_var, State, Condition_Group, Condition, Age_group, Covid19_deaths, Number_of_mentions) SET Current_date = STR_TO_DATE(@Current_date_var, '%m-%d-%Y') , Start_Date = STR_TO_DATE(@Start_Date_var, '%m-%d-%Y') ; -> IGNORE INTO TABLE `comorbidity` -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> IGNORE 1 ROWS -> (@Current_date_var, @Start_Date_var, State, Condition_Group, `Condition`, Age_group, Covid19_deaths, Number_of_mentions) -> SET `Current_date` = STR_TO_DATE(@Current_date_var, '%m-%d-%Y') , -> Start_Date = STR_TO_DATE(@Start_Date_var, '%m-%d-%Y') -> ; Query OK, 3 rows affected (0.04 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> mysql> select * from comorbidity; +--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+ | Current_date | Start_Date | State | Condition_Group | Condition | Age_group | Covid19_deaths | Number_of_mentions | +--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+ | 2022-02-06 | 2020-01-01 | United States | Respiratory diseases | Influenza | 0-24 | 1167 | 1127 | | 2022-02-06 | 2020-01-01 | United States | Respiratory disease | pneumonia | 25-51 | 2254 | 5458 | | 2022-02-06 | 2020-02-02 | United States | Respiratory disease | pneumonia | 52-76 | 5545 | 2222 | +--------------+------------+---------------+----------------------+-----------+-----------+----------------+--------------------+ 3 rows in set (0.00 sec)

I want to join the tables on item_id You're successfully doing this. You're justing using the wrong type of join. I also want data from bar_recvd_details, even if recvd_value IS NULL You can't do this with an INNER JOIN. Instead, you need to use a LEFT JOIN to be able to return NULL from a table on the RIGHT side of the JOIN, where no data is present. For example, LEFT JOIN bar_recvd_details b instead of INNER JOIN bar_recvd_details b I want all data from each table, where the = '2019-06-18' from the respective table You' successfully doing this already. However, when moving to a LEFT JOIN, to meet the above criteria, you need to move that WHERE condition to the JOIN condition. I.E. ON a.item_id=b.item_id AND b.recvd_date = '2019-06-18' SELECT a.item_cid, a.item_id, a.op_value, a.close_val, a.op_date, a.close_date, b.recvd_value FROM bar_opening_details AS a LEFT JOIN bar_recvd_details AS b ON a.item_id = b.item_id AND b.recvd_date = '2019-06-18' WHERE a.close_date = '2019-06-18'; tem_cid item_id op_value close_val op_date close_date recvd_value 2 78 2 88 2019-06-18 2019-06-18 null 2 79 28 103 2019-06-18 2019-06-18 null 2 97 0 96 2019-06-18 2019-06-18 null 6 456 30 48 2019-06-18 2019-06-18 24 6 457 130 155 2019-06-18 2019-06-18 48 6 484 1 46 2019-06-18 2019-06-18 48 6 495 15 61 2019-06-18 2019-06-18 null 6 523 1 12 2019-06-18 2019-06-18 12 6 529 9 32 2019-06-18 2019-06-18 24 6 530 54 98 2019-06-18 2019-06-18 null 6 533 0 24 2019-06-18 2019-06-18 24 6 630 35 77 2019-06-18 2019-06-18 null 6 631 31 122 2019-06-18 2019-06-18 null 6 635 5 47 2019-06-18 2019-06-18 48 6 636 34 71 2019-06-18 2019-06-18 48 https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=abede993a01a9b0493c370a2aa33be85

IIRC there is no way with apt directly, but you can more manually as in the answer to this question: https://askubuntu.com/a/482936 The process would be: Install the dependencies Get and unpack the package with apt-get download ; sudo dpkg --unpack *.deb Edit /var/lib/dpkg/info/.postinst (the linked answer just deletes it, but there may be other tasks you still require it to perform) Complete the instead with sudo dpkg --configure ; sudo apt-get install -yf #To verify/fix dependencies That final step might be sufficient to allow you to skip installing dependencies beforehand in step 1, if that it the case then there is also the advantage that they'll be automatically removed if you remove pg and nothing else requires them. A manual approach like this might be fine for a one-off, and personally when upgrades happen the post-install script won't see the need to run something like initdb as things are already configured being the point where that is needed. If you need this a lot, as part of a product install perhaps, then it could be cumbersome, the other options then are to have a script to undo what initdb does after the fact, or maintain your own apt repo containing modified versions of this package (which in itself is a chunk of admin).

Start with one table for each of those two "forms". After you have tentative SELECTs and "reporting" requirements and volume estimates. Then we can discuss normalization, many-to-many tables, etc. Also, learn about EAV schema design and be sure to learn why it is not a panacea. (Then I won't have to lecture you when you come back.) There are some examples of CRM in this forum and in stackoverflow.com

Found the problem .. https://en.wikipedia.org/wiki/User_error Solution is: COLMAP ( usedefaults, TS_UPDATE = @DATE ( 'YYYY-MM-DD HH:MI:SS.FFF', 'YYYYMMDDHHMMSSFFF', TS_UPDATE ) ); (In all my edits/tests/re-runs, I erroneously edited wrong file .. O.o (but yes, all in initial dev testing, so all good ) )

Below is my attempt. See comments in code for details: ;WITH CombinedList AS( -- Select ALL currentry Active records SELECT 0 AS MessageID, ProductID, Country, ChangeDate, Active FROM dest UNION ALL SELECT MessageID, ProductID, Country, ChangeDate, 'Y' AS Active FROM src ), GroupedList AS( SELECT ProductID, Country, MessageID, ChangeDate, -- Calculates contiguous data group numbers MessageID - ROW_NUMBER() OVER ( PARTITION BY ProductID, Country ORDER BY MessageID ) AS rn FROM CombinedList ), Islands AS( SELECT ProductID, Country, ActiveStart = MIN( MessageID ), ActiveEnd = MAX( MessageID ) FROM GroupedList AS a GROUP BY ProductID, Country, rn ), StartStopRecords AS( -- Create Start Records SELECT I.ProductID, I.Country, AStart.ChangeDate, 'Y' AS Active FROM Islands AS I INNER JOIN GroupedList AS AStart ON I.ProductID = AStart.ProductID AND I.Country = AStart.Country AND I.ActiveStart = AStart.MessageID UNION ALL -- Create Stop Records SELECT I.ProductID, I.Country, AEnd.ChangeDate, 'N' AS Active FROM Islands AS I -- Create a Stop record only if subsequent Message has arrived CROSS APPLY( SELECT TOP 1 ChangeDate FROM GroupedList WHERE I.ActiveEnd < MessageID ORDER BY MessageID ) AS AEnd ) -- Final Result SELECT * FROM StartStopRecords ORDER BY ProductID, ChangeDate, Country Some of the code was adopted from https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/gaps-islands-sql-server-data/ by Dwain Camps.

As user changing SQL like ALTER USER etc is replicated as a statement, to prevent the replication of those, use SET SQL_LOG_BIN=0 in the session where the SQL is executed. Sorry, replication filtering won't help. ref: https://mariadb.com/kb/en/replication-and-binary-log-system-variables/#sql_log_bin .

So you have one table with a composite Primary Key, each element of which references the Primary Key of another table. Without specifics, let's take a simplistic example of a Student taking a Class taught by a specific Teacher. Each Entity (Student, Class, Teacher) can exist on its own (i.e. has its own table) and the association of the three is a student actually taking a class (OK, I'm ignoring dates in all this!). create tableA ( id ... primary key ( id ) ) ; create tableB ( id ... primary key ( id ) ) ; create tableC ( id ... primary key ( id ) ) ; create tableD ( a_id ... , b_id ... , c_id ... , foreign key ( a_id ) references tableA ( id ) , foreign key ( b_id ) references tableB ( id ) , foreign key ( c_id ) references tableC ( id ) );

First, you seem to be missing a few zeros in the numbers you mentioned before you should start seeing problems (IMO) Second, I've only seen Kafka as part of a data loading solution for ingesting data from multiple IoT devices. In these instances, Kafka was used to solve the IoT problem. ACID Compliant databases have problems ingesting a bunch of single row inserts from multiple clients. This is due to the fact that COMMIT won't return until data is safely written to disk. Insert into live_data value ( ..... ); commit; The solution is to cache the request to save data then bulk load it into the DB. This is where Kafka comes into play. (We're talking scales upto 1M IoT readings per second) If you have problems loading 2GB of data per day, you need to investigating why. The key to performance is Bulk Loading of data and not using slow-by-slow (row by row) methods. I have found that database code (PL/SQL; T-SQL) works faster than ETL tools (eg Informatica) but the ETL Tools are easier to maintain over the long term.

To get TOP 2 from the table described above: SELECT TOP(2) TestID, Priority FROM test_table ORDER BY Priority ASC BUT You need to have an index on the table with definition as follows: CREATE INDEX IX_test_table ON test_table (Priority) INCLUDE (TestId); With this index, the query reads 2 rows and is finished as the execution plan below shows. This applies regardless of table size. TOP operator effectively works as "execution terminator" here, because as soon as he gets 2 rows from the index scan operator, the query is finished. As J.D. said, if there are more than 2 results with lowest prioriry (given you search for lowest priority), these aren't returned, but I bet you are aware of that.

The allowed maximum of backup stripes is 64. I couldn't find where this is documented in the https://docs.microsoft.com/en-us/troubleshoot/sql/welcome-sql-server , but the answer can be found in sys.messages by looking at Error Message 3205. SELECT * FROM sys.messages m WHERE m.message_id = 3205 AND m.language_id = 1033; --Or your favorite language Too many backup devices specified for backup or restore; only %d are allowed. Wait, no! That isn't helpful at all! %d is a substitution string, it doesn't give me the answer. Let's just brute force this. Since you point out Ola's code references 64 as being too many, we can try backing up to 65 backup devices & see what happens. BACKUP DATABASE msdb TO DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL', DISK = 'NUL' --65! Msg 3205, Level 16, State 1, Line 1 Too many backup devices specified for backup or restore; only 64 are allowed. I've checked this on both Express Edition & Enterprise Edition -- both give the same answer: 64. The error message appears to be built in a way that this number can be changed without changing the error message in sys.messages, but the number doesn't appear to be variable based on edition--at least not for the "box" product. Perhaps a cloud edition or in the future version this might change.

As the warning points out, you have an index on the column CityDesc which is called CityDes. Indexes in SQL Server are limited to a https://dba.stackexchange.com/a/122003/150011 in any version of SQL Server prior to 2016 and still only https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15#-objects for version 2016 and later. The https://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15 (roughly). When your CityDesc field was only NVARCHAR(300) the length 300 x 2 = 600 < 900 and therefore was indexable. Now you're trying to increase the length to 500, and 500 x 2 = 1,000 > 900 therefore exceeding the index size limit, making it no longer indexable, hence the warning. Your options are to proceed with the change which will drop the index as the warning specifies, or pick a smaller size for your column such as NVARCHAR(400), which will then keep the index in place.

No, that won't help at all. You have three alternative options: increase wal_keep_size to a big enough value That is a simple, but coarse solution, in that it makes the primary always retain a certain amount of WAL, regardless if it is too much or not enough (because the downtime is longer than expected). set a restore_command on the, so that it can use the WAL archive to catch up That may be the best option if you have a WAL archive. use a physical replication slot Such an object marks a certain position in the WAL stream and prevents the primary from discarding WAL that is still needed by the standby. While being an elegant solution, this bears the risk of breaking the primary if the standby stays down too long. Consequently, it is a good idea to monitor replication in this case and/or to set max_slot_wal_keep_size to put a cap on the amount of WAL retained on the primary.

I would keep the alert tag for the element that really needs it, the new one. The rest are just wrappers that don't need more than a simple indicator. In the example image, the contrast is reinforced with a typographical change from regular to bold in the updated items.

Usually legal agreements in software are expressed through a master service agreement and other contractual documents. They’re usually read and handled by the business’ legal or operations officers, not the end user.

You can have a specialized keyboard that has the main characters and accent marks variational forms and sounds on it. For example keyboard for writing https://en.wikipedia.org/wiki/Malayalam_script (a language spoken by 45M people in Kerala, India) has specialized keys for writing additional character features. https://fr.droidinformer.org/tools/inscript-keyboard-malayalam/ Dotted circles denote the place where basic character is supposed to go. How this keyboard works – in Android at least – is that you first type in the basic character and then the additional feature. When the additional feature is typed, combination of the two is then automatically entered in the text field. Pressing backspace first deletes the additional feature, then the basic character.* If your users need to add more than one additional character they just need to type in more additional characters. Backspace should delete them in the order they were entered. You can test this by adding malayalam in the settings as a secondary language to your phone's keyboard. You might need to add another row to your keyboard to house all the basic characters and add-ons. *) This is different from how western (laptop) keyboards work when additional features are added. For example ü is formed by first typing ¨ and then u. When backspace is pressed it deletes ü as a whole.