Rails Migrations: tried to change the type of column from string to integer

I quote the manual about ALTER TABLE: A USING clause must be provided if there is no implicit or assignment cast from old to new type. What you need is: ALTER TABLE listings ALTER longitude TYPE integer USING longitude::int; ALTER TABLE listings ALTER latitude TYPE integer USING latitude::int; Or shorter and faster (for big tables) … Read more

PostgreSQL: FATAL – Peer authentication failed for user (PG::ConnectionBad)

“Peer authentication” means that it’s using a unix socket and expecting the connecting unix user to have the same unix username as the postgresql username. Since your local unix username is funkdified and you’re trying to connect as user goodsounds over a unix domain socket (local) connection where your pg_hba.conf specifies peer authentication, Pg correctly … Read more

Migrating existing auth.User data to new Django 1.5 custom user model?

South is more than able to do this migration for you, but you need to be smart and do it in stages. Here’s the step-by-step guide: (This guide presupposed you subclass AbstractUser, not AbstractBaseUser) Before making the switch, make sure that south support is enabled in the application that contains your custom user model (for … Read more

Can’t install pg gem on Windows

The message you’re getting is a clear indication that you lack something for the correct installation of that gem: Could not create Makefile due to some reason, probably lack of necessary libraries and/or headers. Check the mkmf.log file for more details. You may need configuration options. There is no Windows native version of latest release … Read more

PostgreSQL – dynamic value as table name [duplicate]

You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION … LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant. DO $$ BEGIN EXECUTE format(‘CREATE TABLE %I AS SELECT * FROM backup’, ‘backup_’ … Read more

Use binary COPY table FROM with psycopg2

Here is the binary equivalent of COPY FROM for Python 3: from io import BytesIO from struct import pack import psycopg2 # Two rows of data; “id” is not in the upstream data source # Columns: node, ts, val1, val2 data = [(23253, 342, -15.336734, 2494627.949375), (23256, 348, 43.23524, 2494827.949375)] conn = psycopg2.connect(“dbname=mydb user=postgres”) curs … Read more

Why isn’t row level security enabled for Postgres views?

Basically because it wasn’t possible to retroactively change how views work. I’d like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists. You can filter access to the view its self with row security normally. The tables accessed by the view will … Read more

How do I speed up counting rows in a PostgreSQL table?

For a very quick estimate: SELECT reltuples FROM pg_class WHERE relname=”my_table”; There are several caveats, though. For one, relname is not necessarily unique in pg_class. There can be multiple tables with the same relname in multiple schemas of the database. To be unambiguous: SELECT reltuples::bigint FROM pg_class WHERE oid = ‘my_schema.my_table’::regclass; If you do not … Read more

Insert Python Dictionary using Psycopg2

from psycopg2.extensions import AsIs song = { ‘title’: ‘song 1’, ‘artist’: ‘artist 1’ } columns = song.keys() values = [song[column] for column in columns] insert_statement=”insert into song_table (%s) values %s” # cursor.execute(insert_statement, (AsIs(‘,’.join(columns)), tuple(values))) print cursor.mogrify(insert_statement, (AsIs(‘,’.join(columns)), tuple(values))) Prints: insert into song_table (artist,title) values (‘artist 1’, ‘song 1’) Psycopg adapts a tuple to a record … Read more