7,531 reputation
31842
bio website
location Berlin, Germany
age 39
visits member for 3 years, 2 months
seen 1 hour ago

1h
comment Restoring plain-text pg_dump with psql and --disable-triggers
All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data.
11h
comment Best way to drop / recreate a table that has dependent views?
I'd say the easiest way to do this is what I shortly described above. You could also attempt scripting the views programmatically, using the different catalogs and system information functions, dropping and recreating the table and then running the script with the views. BTW, what do you do with the existing data?
11h
comment Best way to drop / recreate a table that has dependent views?
I'd strongly suggest revising your design then. Relying on the order of columns is hardly ever a good practice. I admit that it is nicer to have consistency, but one has to keep an eye on the costs of maintaining the nicety. The only case when I really see an advantage in changing the column order is when you can win a few bytes per row with aligning the columns better - as said, in huge tables.
11h
comment Best way to drop / recreate a table that has dependent views?
There is no such possibility. What you could possibly do is to create the extended table anew in a separate schema, then all the dependent views, and finally swap in the new stuff and out the old. By the way, what's your reason for adding a new column in the middle of the table? There are cases when the column order is important, typically when the row number approaches the billion(s).
17h
comment Restoring plain-text pg_dump with psql and --disable-triggers
You can go and edit your dump in any editor. Just prepend the COPY with an ALTER TABLE table DISABLE TRIGGER ALL and reenable these at the end.
1d
comment Have: Postgres database files. Need: Functioning database
Interesting, I see postgresql-9.1 in my output of apt-cache search postgresql...
1d
comment SQL update multiple columns matching a pattern
postgresql.org/docs/9.4/static/sql-update.html
1d
comment What will be best (for performance) transaction isolation level?
Why are you worried about the performance in any given isolation level? BTW, is your ORM prepared to retry every transaction that would be the victim of a serialization error?
Mar
27
comment Unique Index much bigger than the table itself
@IvanDeSousaPaz the index is organized like a tree, and (provided that the order of columns in its definition makes sense, for example follow the most-selective-first approach) can significantly reduce the number of pages to be visited. Look at this page to have an idea how. And yes, you need the index for maintaining uniqueness.
Mar
26
comment Umlauts in DBeaver
this may help: dbeaver.jkiss.org/forum/viewtopic.php?f=2&t=677
Mar
26
comment Recovering from a partial recover/migration
Unless you can figure out where the restoration stopped, you have to restart. If you have the restart point, you can edit the dump (this may be tricky if it's too big, you may want to use some non-interactive editors) by removing everything that's already there. TBH I would simply restart, it is much cleaner.
Mar
25
comment Use a function(select or insert) in a streaming rep with hot standby setup
Simply don't try to insert/update/delete anything on a slave. Why is this a problem?
Mar
20
comment Does Postgres preserve insertion order of records?
The actual answer aside (which I believe is no) you should not rely on any other order than the one you specify in your queries.
Mar
17
comment upgrading posgresql 9.0 to 9.4 with pg_upgrade on CentOS 6.6
Anyway, the problem looks like pg_upgrade tries to connect via TCP/IP, while the cluster is configured to allow socket connections only.
Mar
17
comment upgrading posgresql 9.0 to 9.4 with pg_upgrade on CentOS 6.6
Do you run pg_ctl start?
Mar
17
comment upgrading posgresql 9.0 to 9.4 with pg_upgrade on CentOS 6.6
Could you show the non-commented lines of the old cluster's postgresql.conf?
Mar
17
comment how to delete all rows with empty field via function/trigger combo in Postgres v 9.3?
A big +2 for the last sentence
Mar
17
comment Tool to find CPU utilization for a particular query in Postgres
Why have you asked this question once again? Have you checked my comment on the other one (which, incidentally, overlaps with @a_horse_with_no_name's answer below...)?
Mar
17
comment How do I set the external editor for PostgreSQL's psql client?
@Tony fixed, thanks.
Mar
13
comment CPU utilization for a particular query in Postgres
Get the PID from pg_stat_activity and look it up in top. Or just use pg_view, for example. (I work for the company, but the tool is free.)