How to join tables on regex

How to join tables on regex

Say I have two tables msg for messages and mnc for mobile network codes.
They share no relations. But I want to join them
SELECT msg.message,
msg.src_addr,
msg.dst_addr,
mnc.name,
FROM “msg”
JOIN “mnc”
ON array_to_string(regexp_matches(msg.src_addr || ‘+’ || msg.dst_addr, … Read the rest “How to join tables on regex”

How to provide a password for PostgreSQL’s createdb non-interactively?

How to provide a password for PostgreSQL’s createdb non-interactively?

I have a task in phing where before tests I drop the database if exists and create it. This is run on Jenkins.
I want to do it with createdb like this:

The thing is that … Read the rest “How to provide a password for PostgreSQL’s createdb non-interactively?”

PostgreSQL query taking too long

PostgreSQL query taking too long

I have database with few hundred millions of rows. I’m running the following query:
select * from “Payments” as p
inner join “PaymentOrders” as po
on po.”Id” = p.”PaymentOrderId”
inner join “Users” as u
On u.”Id” = po.”UserId”
INNER JOIN … Read the rest “PostgreSQL query taking too long”

Connect to remote postgresql server on amazon ec2

Connect to remote postgresql server on amazon ec2

I started an amazon ec2 instance, and installed postgresql 9.1 over it. I then went to the
Security Group: quicklaunch-1(there was one moredefault` which i did not change) and opened the 5432 TCP Port, the table looks … Read the rest “Connect to remote postgresql server on amazon ec2”

Can NLTK be used in a Postgres Python Stored Procedure

Can NLTK be used in a Postgres Python Stored Procedure

Has anyone done or even no if its possible to use NLTK within a Postgres Python Stored Procedure or trigger

Solutions/Answers:

Solution 1:

You can use pretty much any Python library in a PL/Python stored

Read the rest “Can NLTK be used in a Postgres Python Stored Procedure”

How to migrate complex Rails database to use UUID primary keys Postgresql

How to migrate complex Rails database to use UUID primary keys Postgresql

I have a database I would like to convert to use UUID’s as the primary key in postgresql.
I have roughly 30 tables with deep multi-level associations. Is there an ‘easy’ way to … Read the rest “How to migrate complex Rails database to use UUID primary keys Postgresql”