PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
1answer
128 views

Function quote_nullable(timestamp without time zone) is not unique

When I run this query on "enterprisedb 9.2", it raises an error: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date; Error: LINE 1: select quote_nullable(to_date('09-02-2014 ...
-1
votes
0answers
56 views

Bloating effect in Postgresql [closed]

I've done some performance analysis on my DB and I have releved that: Read head 8192 CPU power used 25% Bloating 56% (84GB of 150) 20% occupied by index unused Now I have find that 99% of ...
2
votes
2answers
589 views

QGIS PostGIS Authentication Failure with “trust” option

I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, ...
0
votes
0answers
388 views

Postgres Backup on linux server [closed]

I am trying to take backup of postgres from InstallAnyWhere which is installer for linux and windows machine. I am calling pg_dump from Java process builder as command line below cmdLine: ...
3
votes
2answers
259 views

How can streaming replication be kept lagged by a certain 'delay' time?

Postgres has had streaming replication since 9.0 and since 9.1 it is possible to pause and resume the application of the streamed WAL records using pg_xlog_replay_pause() and pg_xlog_replay_resume(). ...
3
votes
1answer
120 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
2
votes
2answers
88 views

SELECT DISTINCT with another table

I want to use distinct to display my data. But I don't know how to do it.This is my code.I want to display table gtest_version field max(poly_version) select distinct on (rule_name) ...
0
votes
1answer
121 views

Postgres Point-In-Time Recovery with a limited point in time

I'm configuring PostgreSQL WAL-based backup for point-in-time recovery. Actually, I don't really need any point in time beyond 1 month ago and don't want to waste time for the logs older than that. ...
2
votes
2answers
222 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
0
votes
1answer
89 views

How can I get the latest five scores per user per subject?

There is a Postgres table in our system called "scores" which records test scores. It has test takers (users of our system), scores from 0-10, depts and dates each time a test was taken, like this: ...
1
vote
2answers
88 views

Should I create an index for non key columns?

I have a table in a PostGreSql database defined as following: CREATE TABLE public."MATCH"( "ITEM_A_ID" bigint DEFAULT 0, "ITEM_B_ID" bigint DEFAULT 0, "OWNER_A_ID" bigint DEFAULT 0, ...
0
votes
0answers
63 views

How large should a database table grow?

I have been experiencing some very slow queries for a specific table. This table grows daily. I ran the pg_relation_size function, which gives the size of a table in the database: mydb=# select ...
2
votes
3answers
1k views

Replicating only significant records to specific database

I have a remote database which holds data for various databases in various locations. I want to do database replication, but only data specific to a certain database should be replicated to it. I can ...
6
votes
1answer
1k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
1
vote
1answer
607 views

How do I return JSON structured as the transpose of json_agg() in PostgreSQL?

I'm trying to convert an arbitrary subquery to JSON in PostgreSQL. json_agg() seems like the obvious tool for the job. For example: select json_agg(a) from ( select count(lead_source), ...
3
votes
1answer
444 views

Why does this limit make the postgres planner use a much slower index scan instead of a much faster bitmap heap/index scan?

I have a table of about 700k rows in Postgres 9.3.3, which has the following structure: Columns: content_body - text publish_date - timestamp without time zone published ...
10
votes
3answers
450 views

Can IS DISTINCT FROM be combined with ANY or ALL somehow?

Is a postgres way of combining IS DISTINCT FROM with ANY or some other neat way of getting the same result? select count(*) from (select 'A' foo union all select 'Z' union all select null) z where ...
4
votes
1answer
2k views

How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL?

I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege. db=> create role replicator login password 'something'; CREATE ROLE db=> alter ...
9
votes
4answers
6k views

PostgreSQL designing tool [closed]

I am about to design a database which is going to run with PostgreSQL. I am used to the magnificent tool called MySQL Workbench for MySQL database. It is useful and it looks good which I kind of ...
0
votes
2answers
87 views

How to determine how much disk-space postgresql 9.1 requires as temp_space to run a query?

Given a query q to execute. Is there a way to determine how much disk-space postgresql will (or used) as temporary_space in order to execute it (during its execution) ? I'm under Ubuntu 12.0.4 with ...
2
votes
0answers
2k views

Unable to install postgresql 9.3 on windows 8.1

I have been trying, without success, to get posgresql 9.3 up and running on Windows 8.1 on and off over the last ten days. Here is what I have done so far: I got the windows installer from ...
0
votes
0answers
99 views

Converting a Ruby on Rails site from MySQL to PostgreSQL? [closed]

Whats the best way to convert a MySQL database to PostgreSQL so that it does not create errors, and safely transferred? My developer is redeveloping my site and he suggested it use PostgreSQL ...
0
votes
2answers
283 views

25M x 25M inner join (postgresql) performance

I have a one time need to do an inner join of 25M rows on 25M rows. The box is a Alienware area 51, 4 cores 25GB memory and SATA drive (non system disk). So far it has taken 22 hours. I did btree ...
1
vote
1answer
103 views

Best approach on storing/displaying sanitized data

Surfing the web I find two approaches on storing data in databases. Approach 1: Store data in the database with the html-specialcharacters sanitized $userdata = $_POST['field']; ...
2
votes
1answer
596 views

What is the difference between MySQL and PostgreSQL in terms of writing SQL queries? [closed]

Other than performance and security is there really any difference between MySQL and Postgres like SQL queries, built-in functions, procedures and so on? I'm asking this because I'm familiar with PHP ...
1
vote
1answer
100 views

Some SQLs sometimes take all available RAM

I'm running postgresql server on Windows Server 2008, which has 6GB of ram. My dataset is something near 1,5GB big. I have problems, when I run specific SQLs in table, which has 51mio of records. ...
0
votes
1answer
175 views

can't connect to postgresql with md5 [closed]

I can connect to db with trust, but it is not letting me to connect with md5. It says : "password authentication failed for user "postgres"" Here what I have in pg_hba.config host all all ...
0
votes
1answer
111 views

Removing records from database periodically that surpass timeline

I have a reports table which has literally millions, if not hundreds of millions of report records, which date back to 2011. I think it's slowing down my queries exponentially. I only want to keep ...
1
vote
3answers
338 views

Clever repeatable benchmarking and bottleneck detection test bench - PostgreSQL

Has anyone usefull ideas, inspration or literature for me to design a good test bench for PostgreSQL. I need repeatable results, which are applyable in realworld and production environement (no ...
0
votes
1answer
241 views

How to copy the postgresql's xlog files safely

I mean how to safely copy xlog files when postgresql is running? Any reply will be appreciated.
1
vote
1answer
34 views

Has my 'cluster' operation succeeded?

I ran this command: cluster verbose address using address_locality_lookup; And got this result: INFO: clustering "dba.address" using sequential scan and sort INFO: "address": found 0 removable, ...
0
votes
0answers
31 views

Changed password on Postgres but I still can log in with the old one [closed]

I changed my password via alter user, but can still log in with the old one. In the 9.1.6 version of postgresql I resolved this problem by killing all the processes through pg_terminate_backend, but ...
2
votes
2answers
1k views

Postgres - Create table with foreign key constraint too slow

I have a table called "Account" that is a heavily used table, has 17 columns and has upwards of 300,000 rows. I am trying to create a new table, "NewTable", that has a foreign key constraint to ...
2
votes
1answer
743 views

Debugging why postgres is snowballing with system cpu usage

I am having an issue where my postgres database which run on a dedicated machine is getting hammered with kernal level cpu usage. This essentially takes down the web service that run on this database. ...
0
votes
0answers
156 views

requested shared memory size overflows size_t PostgreSQL

I am running PostgreSQL 9.2.3 on SUN Solaris 9. This is 64 bit system with 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to ...
0
votes
1answer
245 views

PostgreSQL - Return unique combinations of columns based on where clause

This is quite a long question, please bear with me. So I'd like to first explain I have a database of firewall logs created using the following command: CREATE TABLE firewall_logs_mapped ( ...
5
votes
1answer
1k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
0
votes
1answer
214 views

Effective duplication of DELETE … LIMIT in Pg

I have an unusual problem: I need to delete a certain number records from a table, of a particular type; in certain non-compliant DBs it would be easy to accomplish this with DELETE FROM sometable ...
1
vote
1answer
135 views

complex update query postgres

I've got a bunch of ids from table messages and I want to update the table activities. This is how I imagined my query would work (so you get better idea what I'm trying to accomplish): update ...
0
votes
1answer
484 views

Postgres pgpool increase num_init_children

I am trying to tune postgre to allow many concurrent queries from clients as i am checking how many parallel connections with there queries it can handle. I read A good PgPool II configurataion , ...
0
votes
0answers
183 views

Postgres vs MySQL Database Maintenance

I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
1
vote
2answers
250 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
3
votes
2answers
912 views

Streaming Replication Log is puzzling me

I have a streaming replication hot-standby setup, which seems to be running all good. I can login, execute selects and the result seems to be up-to-date with the master. However in the log of the ...
2
votes
1answer
906 views

Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3

I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB. These DB are running on ...
12
votes
1answer
25k views

How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?

I´m trying to setup an PostgreSQL database on an Ubuntu machine. I would like to be able to access it using pgAdmin3 from a remote machine. How do I setup this? I have installed the PostgreSQL ...
5
votes
1answer
2k views

How to reclaim disk space on PostgreSQL?

I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued delete from command to delete all records from it (I ...
10
votes
2answers
6k views

Can't rename columns in PostgreSQL views with CREATE OR REPLACE

In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names. This works CREATE OR REPLACE VIEW gfam.nice_builds AS SELECT ...
2
votes
2answers
227 views

Postgres point() index not being used

\d aggregate Materialized view "public.aggregate" Column | Type | Modifiers ------------------+-----------------------------+----------- ...
3
votes
1answer
179 views

PostgreSQL - VACUUM FULL does not free space back to the OS

On a Postgres 9.1 installation, I'm trying to reduce the amount of Postgres' disk usage by dropping records from tables and then running VACUUM FULL on those tables. My understanding of VACUUM FULL is ...
1
vote
1answer
150 views

Logging Postgres startup errors on Windows

I have been experimenting with Postgres configuration parameters and noticed that when I use settings that prevent Postgres from starting nothing shows up in the pg_log directory logs. I have ...