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
“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
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
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
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
It turns out that you should grant explicit ownership of all objects in the database to the owner after restore. The owner is not a superuser. It’s not enough to only set the owner at database creation time. The final solution for migration goes like this: on the client: pg_dump -f dump.sql -Ox database on … Read more
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
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
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
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