Ezequiel Tolnay
  • Member for 7 years, 1 month
  • Last seen more than a week ago
Help with Access many-to-many relationship (beginner)
Accepted answer
0 votes

Your design seems almost correct. I can pinpoint the following problems though: You're using the table Owners for two purposes: Have a list of owners (as entities/companies/societies) Have a list of ...

View answer
select string that doesn't contain a substring/field
0 votes

Your subquery will likely return several rows, but will be expected to return only one and will give an error. Also, the in sql operator is intended to work on sets rather than strings. If your ...

View answer
Multiple one-to-one relations
0 votes

The following schema is what I came up as a fully denormalised set of tables. There are pros (e.g. flexibility) and cons (e.g. user interface complexity) to this approach, but you may find it useful, ...

View answer
How to write a SQL query to find the range from column values?
0 votes

As follows (replace yourtable and each of the two instances of rangefield): SELECT * FROM `yourtable` WHERE ? BETWEEN CAST(`rangefield` AS int) AND CAST(MID(`rangefield`, LOCATE('-', `rangefield`)+1) ...

View answer
Delete duplicated rows by modified_date - PostgreSQL 9.2
Accepted answer
0 votes

You're mixing grouping criteria twice while creating junk_test, first in the GROUP BY subselect by having fewer conditions in the WHERE, and then in the PARTITION BY by having one extra partitioning ...

View answer
Matching string with LIKE derived from sub-query
0 votes

You can improve that query significantly: SELECT * FROM issues i, admin_users u WHERE u.uid = 11 AND (i.assigned_to_dept LIKE Concat('"', u.sec_dep, '"') OR i.assigned_to_dept LIKE ...

View answer
Slow Query Postgres 9.2
Accepted answer
0 votes

It looks like the join between ja_feedlog and ja_jobs is the culprit (it appears to be taking most of the time doing a filtered indexed scan on ja_jobs for each ja_feedlog resulting row). In cases ...

View answer
voluntarily produce an error with DBCC CheckDb
0 votes

You could create a stored producedure for this purpose: CREATE PROCEDURE CheckDBRaiseError(@db varchar(255)) AS SET NOCOUNT ON IF OBJECT_ID('TEMPDB..#CheckDB') IS NOT NULL DROP TABLE #CheckDB CREATE ...

View answer
Best practice for working with materialized views in Postgres
0 votes

I use a number of scripts that work as installation packages, and I open and run from PgAdminIII. For example, one of them would be all my materialized views, with CREATE OR REPLACE, allocation of ...

View answer
Upgrade from Postgres 9.3 to 9.5
0 votes

Are you sure /data/postgres95 is empty? If there's nothing of value in there, then run PG95=/data/postgres95 sudo rm -rf $PG95 mkdir $PG95 chown postgres:postgres $PG95 su - postgres -c 'PGDATA=/...

View answer
Execute Mysql queries using the same file more than once
0 votes

I resort to maintenance scripts like this all the time (in PostgreSQL). What you can do is using IF [NOT] EXISTS where supported (e.g. DROP TABLE IF EXISTS adbproject_old), and conditionals otherwise....

View answer
relation between customer and product with review
Accepted answer
0 votes

Your relationships are correct, however, they would allow for a customer to have records of products they've never bought. Is this something you want? Otherwise, you'd be better off establishing the ...

View answer
Eliminate duplicates and optimize performance for big count and small LIMIT
0 votes

@Erwin's response is on the money (there was an error regarding the placement of DISTINCT ON but it is fixed now). This variant assumes that message.messageid is a primary key (or unique): SELECT ...

View answer
How to not "duplicate" a table if it has a unique constraint over many columns?
0 votes

With PostgreSQL 9.5 you can enjoy a BRIN index (which will make the index very small, yet functional), and handle the exclusions via a trigger, like this: CREATE INDEX ON medi_cal_base_eligibility ...

View answer
To partition or not to partition?
0 votes

It won't hurt if you implement partitioning now, but use a single partition until your system really requires a new one. Performance-wise, there will be only a tiny overhead, to deal with primary keys ...

View answer
Read only postgres table, except by trigger
0 votes

You can use SECURITY DEFINER in the function trigger, and create it with a privileged user. Give the user privileges for inserting into stock_transaction_log and executing the trigger, but not for ...

View answer
SQL query for matrix multiplication
-1 votes

Just join by i and j, and multiply val. If the samples are incomplete in either matrix, you may want to grab the union of both matrices and left join. with A AS (...), B as (...), C AS (select i,...

View answer
1 2 3 4 5
6