Skip to content

Instantly share code, notes, and snippets.

@TomHoenderdos

TomHoenderdos/mysql to postgres.sql

Created September 28, 2015 13:13
Show Gist options
  • Save TomHoenderdos/6f54286a743be85bb6bd to your computer and use it in GitHub Desktop.
Save TomHoenderdos/6f54286a743be85bb6bd to your computer and use it in GitHub Desktop.
Download ZIP
MySQL to Postgres migration
Raw
mysql to postgres.sql
In MYSQL I can do something like this:
SELECT activities.*, @num := if(@item = CONCAT(item_type, item_id), @num + 1, 1) AS row_number, @item := CONCAT(item_type, item_id) AS item FROM activities
And that gives me an output like this:
item | row_number
--------|-------------
Idea1 | 1
Idea1 | 2
Idea1 | 3
Idea2 | 1
Idea2 | 2
Idea3 | 1
... | ...
Now how should I do something like this in postgres?
I Have tried stuff like this:
CREATE TEMP TABLE item AS SELECT concat(activities.item_type, activities.item_id) as item FROM activities ORDER BY item DESC;
SELECT *, row_number() over () from item
Which returns:
Idea5 1
Idea5 2
Idea5 3
Idea5 4
Idea43 5 <--! I want to start counting from 1 here :)
Idea43 6
Idea43 7
@TomHoenderdos
Copy link
Author

I want to count the number of occurrences that I have already seen:
My input looks something like this:
Idea5 |
Idea5 |
Idea5 |
Idea5 |
Idea43 |
Idea43 |
Idea43 |
Idea43 |
Idea43 |
Idea42 |
Idea42 |
Idea42 |
Idea42 |
Idea41 |
Idea41 |
Idea41 |

I want to transform it to this:
Idea5 | 1
Idea5 | 2
Idea5 | 3
Idea5 | 4
Idea43 | 1
Idea43 | 2
Idea43 | 3
Idea43 | 4
Idea43 | 5
Idea42 | 1
Idea42 | 2
Idea42 | 3
Idea42 | 4
Idea41 | 1
Idea41 | 2
Idea41 | 3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment