6,474 reputation
31436
bio website
location Berlin, Germany
age 38
visits member for 2 years, 6 months
seen 1 hour ago

Jun
18
answered Difference between pg_column_size(table.*) and pg_column_size(table.col1) + pg_column_size (table.col2)
Jun
17
comment Official PostgreSQL Capitalization conventions
Well, camelCase and PascalCase in PostgreSQL are a bit painful. You have to quote these if you really want to have the names like that, otherwise the system silently lowercase them (I nearly wrote decapitalize, whatever associations it may arouse).
Jun
16
revised How do I set and get custom database variables?
deleted 1 character in body
Jun
16
comment database not restoring from dump file
In your command for restoring, you don't specify which DB to use. In this case, psql connects to the database named as the user, in this case postgres. Your objects are created there.
Jun
16
comment Using the correct postgres server
It's not very likely, 8.4 usually creates its files in a .../8.4/... directory. Which version did you install recently?
Jun
16
comment Using the correct postgres server
Hm. That means that you have to find out where the old data files are.
Jun
16
comment Postgres reported table / db size varies wildly from dump size
This is definitely not true. It very much depends on the data. I've done a short test on one of my toy databases, it reports 8554 kB in psql, the text format dump is 2279 kB.
Jun
16
comment Using the correct postgres server
You have to su - postgres and start the server like that. The root user is not allowed to do that for security reasons.
Jun
16
comment Postgres reported table / db size varies wildly from dump size
Your question related to LOBs is answered here: stackoverflow.com/questions/18636850/…
Jun
16
comment Implementing Permissions Database structure on a PHP site to restrict the Access in Multiple levels
The PostgreSQL role system is currently unable to handle row-level permissions (that, if I understood correctly, is what the OP tries to implement).
Jun
11
awarded  Popular Question
Jun
10
revised Longest IPv4 Prefix Match using PostgreSQL
added 61 characters in body
Jun
5
comment Better way to restore to existing pg database
Use the -c and -C options of pg_restore. At the same time, I am wondering why you need to restart the server...
Jun
5
answered How can I get PostgreSQL to not advance sequences when COPY fails?
Jun
5
comment How can I get PostgreSQL to not advance sequences when COPY fails?
The sequences are designed like that, the are not supposed to produce contiguous numbers. However, you can reset them before (re)trying the COPY with SELECT setval('sequence_name', (SELECT max(id) FROM table));
Jun
3
comment trigger_file doesn't work
Do the logs tell you something happening when you create the file?
Jun
3
comment Recursive calls in stored procedure and temporary tables
How about moving the 'non-recursive' functionality (including the temp table creation) out to an other function and call the recursive magic from it? At the same time, some indentation in your code could help readers understanding what you try to achieve. At at still the same time, are you sure that floating point variables are good for this sort of calculation?
Jun
3
revised Recursive calls in stored procedure and temporary tables
added 283 characters in body
Jun
3
comment Organizing Database Objects in a Database that are not part of the systems original Database Objects?
On one hand, a sensible naming convention can solve part of your problem. A more sophisticated solution is to use schemas: they are a good way to organize your stuff and also make you able to set access rights at this level.
Jun
2
revised Change column type from varchar to text in all tables at once
added 42 characters in body