I would suggest you try to do something along the line of:
SELECT count(*) FROM mytable WHERE my_primary_key < value ;
... and find at which point it fails. [This may be tedious, but you can just cut in half repeatedly, until you find the value that first fails.]
You will most probably need to alter the different settings that affect index usage, because you actually want to force the database to use the index, even if it actually must scan 99% of the table. You want it not to scan the page that booms.
If you can get SELECT
to give you most of the data, you can then do something such as:
CREATE TABLE my_table_2 AS
SELECT * FROM my_table WHERE my_primary_key < value;
and later on:
ALTER TABLE my_table RENAME TO my_table_old ;
ALTER TABLE my_table_2 RENAME TO my_table ;
... and you'll have all the data that could be retrieved. I wouldn't drop the old table, in case someone finds later on a better method of retrieving the missing info.
Best of luck.
Your problem is that your query is using new.columnname
to mean different things.
In the SELECT
list of the subquery, the first new.columnname
is intended to use the actual value stored new.columnname
. The second new.columnname
, the one in the COUNT()
and the one in the GROUP BY
are all intended not to be the literal value stored in new.columnname
, but a reference to the actual OrderDetails
column named in new.columnname
. In other words, the first is the string literal "format", while the second is the database column OrderDetails.format
.
Unfortunately, the engine will view each of new.columnname in the query as a string value "format", and will never interpret it as OrderDetails.format
.
There are at least two potential ways to get around this problem:
Explicit INSERT
s for each column
For each of the columns in OrderDetails
, write a separate INSERT
statement in your trigger:
if old.isdropdown <> new.isdropdown then --if there was a change
if new.isdropdown = true then --and the change was from false to true
if new.columnname = 'first_column' then
insert into dropdownstats
(orderid,columnname,columnvalues,countofcolumnvalues)
select
orderid
, 'first_column'
, first_column
, count(*)
from orderdetails
group by orderid, first_column;
elsif new.columnname = 'format' then
insert into dropdownstats
(orderid,columnname,columnvalues,countofcolumnvalues)
select
orderid
, 'format'
, format
, count(*)
from orderdetails
group by orderid, format;
elsif ...
end;
else --the transition was from true to false
delete from dropdownstats as dd where dd.columnname = new.columnname;
end if;
end if;
(If there's special handling you want to do if new.columnname
doesn't match any of the values, you can use CASE ... WHEN ... ELSE ... END;
instead of IF ... THEN ... ELSIF ... THEN ... END;
, if you like.)
Dynamic SQL
Unless PostgreSQL has some restriction against it, you could use dynamic SQL to build the SQL statement you need each time through:
if old.isdropdown <> new.isdropdown then --if there was a change
if new.isdropdown = true then --and the change was from false to true
EXECUTE format( 'insert into dropdownstats '
'(orderid,columnname,columnvalues,countofcolumnvalues) '
'select '
'orderid '
', %L '
', %I '
', count(*) '
'from orderdetails '
'group by orderid, %I '
,new.columnname, new.columnname, new.columnname);
else --the transition was from true to false
delete from dropdownstats as dd where dd.columnname = new.columnname;
end if;
end if;
CAVEATS: Code is untested, and PostgreSQL is not my primary SQL language, so I may have included typos or misunderstood some subtleties.
Best Answer
Create a
UNIQUE
index or constraint on(col1, col2, col3)
- if you don't have one already.Then use
INSERT ... ON CONFLICT ... DO NOTHING
. That's simpler, faster and more reliable, and unlike the route you had in mind, it's also safe against race conditions.Related:
Be aware of NULL handling: