0

I have a postgresql table with 8 million rows. I need to copy it over to MSSQL. I do not have access to the console so I am unable to use PG_Dump. I have the ODBC driver, and have successfully linked up the servers. I was then able to copy the table over month by month. This was slow and painstaking. I need to create a query in postgresql that will list the year, month and total rows per month. The date field was actually created as text. I have tried this but do not get the results I am looking for.

SELECT distinct 
        date_part('Year',case 
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end) as Year
    ,date_part('Month',case 
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)
         as month
    ,count(date_part('year',case 
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)) as "trandate sum"--date_part('Day',case 
    --  WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
    --  else
    --  to_date(trandate, 'MM/DD/YYYY')
    --  end))
  FROM SomeSchema.transaction_detail

  Group By trandate
  order by date_part('Year',case 
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end)
        ,date_trunc('Mon'  date_part('Month',case 
        WHEN left(trandate,4) = '2014' then to_date(trandate, 'YYYY-MM-DD')
        else
        to_date(trandate, 'MM/DD/YYYY')
        end))

Is there a better way to do this?

  • Please use the editing tools provided to make your posts more legible. – Vérace Jul 28 '16 at 18:36
  • There is no 2012 R2 version of SQL Server. There are 2008, 2008R2, 2012, 2014, 2016. – ypercubeᵀᴹ Jul 28 '16 at 18:54
0

This is more readable and may perform better:

SELECT date_part('year', month) as year,
       date_part('month', month) as month,
       count(*) as "trandate sum"
FROM (
  SELECT date_trunc('month', to_date(trandate, CASE
             WHEN left(trandate, 4) = '2014' THEN 'YYYY-MM-DD' ELSE 'MM/DD/YYYY' END
                    ))::date as month
  FROM SomeSchema.transaction_detail
  ) td
GROUP BY month
ORDER BY 1, 2

Note that you could install PgAdminIII in your windows desktop, and use its pg_dump (in C:\Program Files\PostgreSQL\9.3\bin), or start the console from PgAdmin (menu Plugins/PSQL Console). Depending on the type of data you're trying to import, a CSV format export file could be a good alternative. Otherwise, an SQL file via pg_dump with individual INSERT statements and processed via MSSQL's osql.exe or sqlcmd.exe tools to run it.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.