I have been using pandas (python library) to modify some data that I take from one database (MS SQL) and load it to another one for reporting (Postgres).

One of the steps sums up a pretty large list of columns (40+) and if the result is > 0, that row is retained, otherwise it is dropped. This all takes place in memory in a pandas dataframe. This reduces the row count by 80%.

dimension1, ..., dimension20, count1, count2, ..., count89, count90

What is the best way to accomplish this using pure SQL? Can I do it directly from the SELECT query, or do I need to load everything into a temporary table (perhaps) and then only insert the rows which sum up to > 0?

  • SELECT .. from what table? What is your actual data source? A CSV file or a table in the DB? What are the data types? Cant there be NULL values? Negative values? Your Postgres version? – Erwin Brandstetter Jul 21 '18 at 0:51
  • Select from MS SQL (raw replication source). Destination is a Postgres 10 table. All values of columns that I would sum from the source are not null integers with no negative values. – trench Jul 23 '18 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.