How to log PostgreSQL queries?

How to enable logging of all SQL executed by PostgreSQL 8.3? Edited (more info) I changed these lines : log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_statement...

ORDER BY the IN value list

I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sorted list of values to the IN construct in the WHERE clause: SELECT * FROM comments WHERE (comments.id IN...

Java JDBC ignores setFetchSize?

I'm using the following code st = connection.createStatement( ResultSet.CONCUR_READ_ONLY, ResultSet.FETCH_FORWARD, ResultSet.TYPE_FORWARD_ONLY ); st.setFetchSize(1000); ...

How to declare a variable in a PostgreSQL query

How do I declare a variable for use in a PostgreSQL 8.3 query? In MS SQL Server I can do this: DECLARE @myvar INT SET @myvar = 5 SELECT * FROM somewhere WHERE something = @myvar How do I do the...

PostgreSQL Full Text Search case study?

Is there any case study available of any project that uses PostgreSQL 8.3+ Full Text Search on a large amount of data?

Spring and Hibernate configuration

Here is my hibernate config, but for some reason it fails to load those properties that I have added to <property name="hibernateProperties"> and log says in startup that INFO...

Where are my postgres *.conf files?

I have recently reinstalled postgresql 8.3 on my Ubuntu 8.04 after update. Used EnterpriseDB package. I can connect to the database locally, I see system DB postgres but I can't configure it...

PostgreSQL without password prompt : .pgpass ignored

I'm trying to enable root (Ubuntu 8.04) to use psql command without password prompt (for scripting purpose). Everything worked fine with PostgreSQL 8.3, but I migrate to PostgreSQL 8.4 and the...

Can't install pg gem on Windows

I've got 2 Ruby versions: 1.8.7 and 1.9.2 and PostgreSQL 8.3. I cant install pg gem on any of them. Getting this error: C:/Development/Ruby187/bin/ruby.exe extconf.rb checking for pg_config......

Specifying distinct sequence per table in Hibernate on subclasses

Is there a way to specify distinct sequences for each table in Hibernate, if the ID is defined on a mapped superclass? All entities in our application extend a superclass called DataObject like...

PostgreSQL: VACUUM FULL or CLUSTER?

I'm nearly out of disk space because of a query that tried to update every row in a huge table. I don't have enough space for CLUSTER (though it would barely fit if I dropped indexes first and...

merge DATE-rows if episodes are in direct succession or overlapping

I have a table like this: ID BEGIN END If there are overlapping episodes for the same ID (like 2000-01-01 - 2001-12-31 and 2000-06-01 - 2002-06-31) I would like the rows to be merged,...

Can't find the PostgreSQL client library (libpq)

I'm trying to install PostgreSQL for Rails on Mac OS X 10.6. First I tried the MacPorts install but that didn't go well so I did the one-click DMG install. That seemed to work. I suspect I need to...

FATAL: could not create lock file "postmaster.pid": Permission denied

I receive error FATAL: could not create lock file "postmaster.pid": Permission denied when trying to execute command pg_ctl restart --pgdata "D:\Program Files\PostgreSQL\8.3\data" Full rights...

PostgreSQL column 'foo' does not exist

I have a table that has 20 integer columns and 1 text column named 'foo' If I run query: SELECT * from table_name where foo is NULL I get error: ERROR: column "foo" does not exist I have...

Cannot update view in PostgreSQL?

My site was developed using Drupal 6 running on a Postgresql 8.3 server on Ubuntu 11.10. Also webmin version 1.590. Now I want to update records in a table, but when I run: UPDATE...

Find difference between timestamps in seconds in PostgreSQL

I have a table in PostgreSQL 8.3 with 2 timestamp columns. I would like to get the difference between these timestamps in seconds. Could you please help me how to get this done? TableA ( ...

How to delete a column from parent table and propagate this to all child tables

Can someone help me with this? I have a set of tables that are inherited from a single parent table (now all these tables are containing data). I need to drop a particular column from the parent...

How can I export the schema of a database in PostgreSQL?

My computer broke down but fortunately I backed up the folder C:\Program Files\PostgreSQL. Now I'm working in a new computer and I would like to import the previous Postgres databases that are...

Implementing a total order ranking in PostgreSQL 8.3

The issue with 8.3 is that rank() is introduced in 8.4. Consider the numbers [10,6,6,2]. I wish to achieve a rank of those numbers where the rank is equal to the row number: rank |...

How to profile plpgsql function in PostgreSQL 9.2

I'd like to find the bottlenecks in a plpgsql function, so I'm looking for a profiler. https://stackoverflow.com/questions/430123/how-do-i-enable-the-postgresql-function-profiler suggests loading...

Npgsql: Old floating point representation for timestamps not supported

I've written a basic script that syncs data from a PostgreSQL database to another system, which executes in my test/development environment without issue, using: PostgreSQL 9.4 Npgsql dll version...

Powershell stopping services with dependencies

cannot understand why I can't stop a service with the powershell 'stop-service' as I can stop it with the 'net stop' command. The service has one dependent service which is stopped (not a...

Would it be possible to do replication using bucardo between different postgresql versions?

We are having 2 database servers (Debian 8.3) with postgresql. 1st server having postgresql 8.4 and 2nd server is having postgresql 9.4. Would it be possible to setup bucardo between this 2...

PostgreSQL: starting a sequence at MAX(the_column)+1

I want to add a sequence to a column that might already have data, so I'm trying to start it beyond whatever's already there. Assuming there already is data, I would like to have done it this...

postgres query where values contain backslash

I'm in the process of migrating a db from MySql to PostgreSql I have one last thing outstanding: In the table steps there is a column overlay_custom_css. Example of data in...

does phpadmin4 support postgresql 8.3?

does phpadmin4 support postgresql 8.3? currently in use greenplum 5.19 ERROR: missing FROM-clause entry in subquery for table "typ" LINE 62: typ.typrelid AS typoid,

Laravel passport production : The resource owner or authorization server denied the request

i build an API with Laravel and i get error only in prod : log file The resource owner or authorization server denied the request {"exception":"[object]...

How to externalize the Postgresql column encryption key with Spring Boot JPA?

One of my Postgresql entities has an encrypted column that I want decrypted when I read it with Spring Boot JPA. The encrypted entity fiels is decrypted with an hardcoded password...

How to install a PostreSQL terminal client using microdnf install?

What PostgreSQL clients are available through microdnf install? I'm trying to install a client via my Dockerfile. I've tried multiple commands I've seen recommended and several guesses, but none...