postgresql
Writing JSON column to Postgres using Pandas .to_sql
I’ve been searching the web for a solution but couldn’t find any so here is what we came up with (there might be better ways but at least this is a start if someone else runs into this). Specify the dtype parameter in to_sql. We went from:df.to_sql(table_name, analytics_db) to df.to_sql(table_name, analytics_db, dtype={‘name_of_json_column_in_source_table’: sqlalchemy.types.JSON}) and it … Read more
PostgreSQL: how to install plpythonu extension
for postgres 11.2 (Debian based) I needed to install: apt-get update && apt-get install postgresql-plpython3-11
PostgreSQL Clob datatype
The clob data type is unsupported in Postgres. However, it can be easily defined as a synonym to the text type: create domain clob as text;
migrate data from MS SQL to PostgreSQL?
I don’t know why nobody has mentioned the simplest and easiest way using robust MS SQL Server Management Studio. Simply you just need to use the built-in SSIS Import/export feature. You can follow these steps: Firstly, you need to install the PostgreSQL ODBC Driver for Windows. It’s very important to install the correct version in … Read more
postgres – comparing two arrays
figured it … there’s an && operator http://www.postgresql.org/docs/current/static/functions-array.html “&& overlap (have elements in common) ARRAY[1,4,3] && ARRAY[2,1]”
Keep PostgreSQL from sometimes choosing a bad query plan
If the query planner makes bad decisions it’s mostly one of two things: 1. The statistics are inaccurate. Do you run ANALYZE enough? Also popular in its combined form VACUUM ANALYZE. If autovacuum is on (which is the default in modern-day Postgres), ANALYZE is run automatically. But consider: Are regular VACUUM ANALYZE still recommended under … Read more
How to execute *.sql file using psql
Is this what you mean? \i e:/myFolder/index.sql;
Optimal chunksize parameter in pandas.DataFrame.to_sql
In my case, 3M rows having 5 columns were inserted in 8 mins when I used pandas to_sql function parameters as chunksize=5000 and method=’multi’. This was a huge improvement as inserting 3M rows using python into the database was becoming very hard for me.