Reputation
18,904
Top tag
Next privilege 20,000 Rep.
Access 'trusted user' tools
Badges
4 36 67
Newest
 Enlightened
Impact
~1.4m people reached

Jan
11
comment Migrate Oracle BLOB to Postgres BYTEA or OID
bytea is definitely the better choice.
Jan
10
comment PostGIS - indexing - speed up query
The index on lower(name) won't be used for that query. You need an index on unaccent(lower(name))
Jan
8
comment Postgres Can't Add Foreign Key Constraint
As this doesn't write anything but just reads every row, this shouldn't take hours - maybe it's just waiting for a lock: wiki.postgresql.org/wiki/Lock_Monitoring
Jan
8
comment Postgres Public Synonym Issue
Adjust the search_path - which is the answer you already got on the mailing list: postgresql.nabble.com/public-synonym-td5880945.html
Jan
8
comment Merging two rows when there is a duplicate value in one of the columns
@YperSillyCubeᵀᴹ: good point about the distinct
Jan
8
comment Merging two rows when there is a duplicate value in one of the columns
Why is the column C2 empty in the result? And what should the output be if they had different values for CAD?
Jan
8
comment What is the difference of master/master and master/slave replication?
You should add that Postgres currently only supports master/slave replication
Jan
8
comment New column to a base table
You should have scripts to create your views anyway (stored in a version control system). So I don't see how that would be a lot of work, to just run those scripts. You might want to look into schema migration tools like Liquibase which help a lot in automating these kind of things
Jan
8
comment Replace content of only one table with new version (should disable foreign keys?)
Then you are doing something wrong: imgur.com/zJTnKgf DEFERRABLE INITIALLY is invalid syntax (you need to specify immediate or deferred), so that can't be the statement you used to create the FK. Maybe your SQL client is still running in auto commit mode?
Jan
8
comment New column to a base table
drop view cascade - but you then also need to re-create those views as well.
Jan
7
comment Replace content of only one table with new version (should disable foreign keys?)
Whoever told you that is wrong: Quote from the manual "Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE constraints are affected by this setting"
Jan
7
comment Replace content of only one table with new version (should disable foreign keys?)
Of course deferred works for foreign keys - that's what they were made for. If you need to restore a dump using pg_restore then the only thing I can think of is to drop the foreign key constraint, delete the rows, restore the table from the dump, add the foreign key constraint again. The dropping of the foreign keys can be automated. You just need to generate a script first that re-creates them.
Jan
7
comment Replace content of only one table with new version (should disable foreign keys?)
You can do the delete/insert in a single data-modifying CTE. Or declare the constraints as "deferred" then they are checked when you commit, not when you delete or insert
Jan
7
comment New column to a base table
for such a change you need to drop the view first. Unfortunately create or replace doesn't work in that case
Jan
7
comment New column to a base table
You need to redefine the view, yes. Note that the * is not stored. It is expanded to list all columns of the table at the time when the create view was executed.
Jan
7
comment Why does adding a smallint column to a postgres query increase execution time 60X?
This is really strange. It's only the hash right join that takes so much longer. I wonder if the number of columns exceeds some threshold that causes this. You probably should post that to the Postgres performance mailing list.
Jan
7
comment PostgreSQL: How to get the column named, data typed, and nullabled of tables in a schema:
And: dba.stackexchange.com/q/48451/1822
Jan
7
comment Is it possible to do a VACUUM FULL and allow read-only access to the table?
You can use pg_repack for that: reorg.github.io/pg_repack
Jan
5
comment Deadlock best practices
In case of a real deadlock, Postgres will kill one of them automatically. I agree with Tom that you most probably do not have a deadlock situation when you have to kill the sessions yourself.
Jan
5
comment Does ALTER on PostgreSQL, Oracle table (respectively) create intermediate copy of the table?
No, it does not rewrite the table. Internally it is marked as deleted and the space will be reclaimed when rows are updated.