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)

0
votes
2answers
2k views

PostgreSQL - pgAdmin error creating a server

I have the latest versions of PostgreSQL and its GUI-frontend, pgAdmin, installed. When I try to register a new server a fatal error appears when pgAdmin tries to connect to the newly created server - ...
5
votes
1answer
503 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
0
votes
1answer
241 views

Problems using “Excel PostgreSQL Import, Export & Convert Software” [closed]

I want to export data from Postgresql, and I tried many ways then find the tutorial of Excel PostgreSQL Import, Export & Convert Software on Youtube. I installed the software, but I could not ...
8
votes
2answers
2k views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
1
vote
1answer
798 views

PostgreSQL FTS and Trigram-similarity Query Optimization

I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
0
votes
0answers
1k views

hstore vs redis performance wise (without durability) [closed]

How do PostgreSQL's Hstore and Redis compare performance-wise? This question was asked here, but I would like to compare with a PostgreSQL configuration without durability guarantee, i.e. with ...
1
vote
2answers
233 views

Vacuuming database termination for postgreSQL

I was doing database vacuuming to free up disk space for postgreSQL database. The corresponding table size was large and it was taking long time for vacuuming to get complete. But unfortunately ...
0
votes
1answer
485 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.
2
votes
1answer
321 views

Asynchronous commit on Postgres over iSCSI with BBU storage

It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller. In my environment I am on a VM whose block storage is ...
1
vote
1answer
2k views

PostGIS “could not open extension control file” when creating extension

I've followed the instructions here to install PostGIS http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS21UbuntuPGSQL93Apt but when I went to create the extension I got the following error. I'm ...
3
votes
3answers
3k views

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; ...
2
votes
0answers
1k views

Postgres Case statement not return else value in this query

I'm creating a SQL query with a some nested queries and I'm trying to use the CASE statement but it is behaving weirdly. I've been at it for at least 6 hours without any luck... This is my query at ...
0
votes
1answer
861 views

Even after permissions are updated, I still get the error permission denied for relation users

I'm getting the error ERROR: permission denied for relation users and have tried to update privileges using both grant all privileges on all tables in schema ops to my_user GRANT ALL PRIVILEGES ON ...
5
votes
2answers
3k views

What is the search_path for a given database and user?

I can see the current search_path with: show search_path ; And I can set the search_path for the current session with: set search_path = "$user", public, postgis; As well, I can permanently set ...
0
votes
1answer
334 views

Automatically generate ALTER TABLE to syncronize schema on PostgreSQL

i Have 2 different database schema in PostgreSQL sql file from pg_dump, is there automated way to generate alter table commands so it would be in sync, without inspecting one by one? this is the ...
5
votes
4answers
10k views

Select COUNT of days between two dates except weekends

I am trying to get the number of days between two different dates, except the weekends. I don't know how to achieve this result.
1
vote
1answer
733 views

Postgres - Optimizing an view dependent on an aggregate function?

I have a table of about 15 million records. Whenever information about a specific charge_id is changed, a new row is added with the current timestamp and the changes. This results in multiple rows ...
-1
votes
2answers
235 views

What database should I use to create a webmail service like gmail? [closed]

I planned to use python for my frontend with django framework. But for backend, I have no idea which database I should use. Since it involves plenty of reads and writes can someone point me in the ...
3
votes
1answer
2k views

PostgreSQL list of integers separated by comma or integer array for performance?

As per subject, I have a field which is varchar in which i store some numbers separated by commas. The list grows and shrinks based on clients choices. I then use it in queries to check if a ...
1
vote
1answer
113 views

Does this query always correctly retrieve the types of array parameters

The following query seems to work, but I am not sure that it is the best/always correct way: select parameter_name, CASE WHEN data_type='ARRAY' THEN SUBSTRING(udt_name, 2)||'[]' ELSE ...
3
votes
1answer
465 views

PostGIS ERROR: operator does not exist: postgis.geometry @ geometry

I have had PostgreSQL 9.3.1 and PostGIS 2.1.0 installed (via https://github.com/PostgresApp/PostgresApp/releases/tag/9.3.1.1-RC1) and working on my laptop for some time, and have been doing Ruby ...
1
vote
2answers
105 views

Clobbered default database in postgreSQL

I created a script to drop a particular database and then reload just the structure. I created it using phppgsql. I'm still new to pgsql, so I simply ran the script using \i myCreateScript.sql with ...
3
votes
2answers
2k views

Database consistency checker in postgresql

Are there any DBCC(Database consistency checker) commands in PostgreSQL? I could find SQL server DBCC commands but not for Postgres? I read that postgresql has inbuilt functionality of perfomance ...
1
vote
1answer
58 views

Create a runtime table in PostgreSQL

Suppose that I have two tables: tableA (id, name); tableB (id, name); I also have a textual file with a list of couples of ids: id1a, id2a id1b, id2b ... I would like to run the following query: ...
5
votes
1answer
3k views

How to duplicate huge postgres table?

I have huge postgres table (10GB of data - 160M records). Table is static and there are no write operations on it performed. I want to duplicate it, perform writes, reindex it and then with single ...
1
vote
1answer
153 views

Retrieve title from rows with a maximum count in other columns

Given the following table archived: CREATE TABLE archived ( sid serial -- row id showurl text -- can be: btg, tmwwt, gcaf, emda title text reference text -- title id ...
0
votes
0answers
70 views

How big of the parameter “shared_buffers” setting on postgresql [closed]

According the document from http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server. Even on a modern Linux system, the stock kernel will likely not allow setting shared_buffers to over 32MB ...
16
votes
5answers
13k views

How to efficiently copy millions of rows from one table to another in Postgresql?

I have two database tables. One contains hundreds of millions of records. Lets call that one history. The other one is calculated on daily basis and I want to copy all of its records into the history ...
3
votes
1answer
1k views

Select row at the nth percentile

I have two tables, table1 and table2. Let the two tables contain date, id and latency column. I have a simple query that performs a join on the two tables and returns a set of rows: Select ...
1
vote
1answer
473 views

How can I get data from broken PostgreSQL server? [closed]

I have installed CentOS on a virtual machine and installed the latest version of PostgreSQL server 9.3. I have added PostGIS extension and some data. For some reason, the PostgreSQL service now fails ...
1
vote
1answer
133 views

PostgreSQL (9.3) restore won't finish

I've been running a restore job on 38m row table for over 24 hours that doesn't seem to want to come to an end. When I check pg_stat_activity it tells me that the restore job is still running, and the ...
0
votes
1answer
87 views

Complex “activity” based query optimization

I'm working on a query in the same style as Facebook's feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user's ...
1
vote
1answer
65 views

Formulating a Join Query for PostqreSQL

I have a quick question, if someone can help, I would be grateful I have the following tables User, Video, User_video user has userdetails (user_id, name, email) video has videoDetails ...
3
votes
2answers
716 views

Set random value from set

I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.
0
votes
1answer
106 views

Postgres cannot rename table because of AccessExcusiveLock

I'm trying to rename table. But it had error: LOG: sending cancel to blocking autovacuum PID 11911 Detail: Process 10377 waits for AccessExclusiveLock on database 21819191 relation 24338310 ...
0
votes
1answer
31 views

How to specify the primary key number space for a postgres table?

I have a postgres table that contains data where the id has been set by the code that injected the data. So lets say the id is 0..49. How can I instruct postgres to start with 50 on the id column ...
1
vote
1answer
2k views

Copy PostgreSQL database from a remote server

When I was using MySQL, I could run a command that SSHs into my server and copy the database to my local machine. ssh -t remoteserver 'mysqldump --compress -u dbuser --password="password" db_name' | ...
2
votes
3answers
1k views

How to query efficiently from Postgres to select special words?

Let's say that I have a table called words with very many records. Columns are id and name. In the words table I have for example: 'systematic', 'سلام','gear','synthesis','mysterious', etc. NB: ...
3
votes
1answer
107 views

Geographic Synonyms

Let's say I have a table representing principal country divisions (ex States): create table principal_country_divisions ( id int primary key, name text not null, country_code char(2) ); insert ...
22
votes
3answers
10k views

Can a single PostgreSQL query use multiple cores?

In recent versions of PostgreSQL, can we share a query between two or more cores to get a performance boost? Or should we get faster cores?
7
votes
1answer
1k views

PostgreSQL PREPARE query with IN () parameters

I'm trying to prepare a query from PHP like: pg_prepare($con, "prep", "select * from test where tid in ($1)"); and then execute it with: $strpar = "3,4,6,8,10"; pg_execute($con, "prep", ...
3
votes
2answers
2k views

Merging two backups form a postgresql database

I have a kind of big postgresql database and two backup files (each uncompressed dump file ~56 mb) representing 2 states of the data in time. 1st state - backup from a couple days ago 2nd state - ...
1
vote
1answer
155 views

numbering rows consecutively for a number of tables

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to ...
3
votes
2answers
662 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
2
votes
1answer
160 views

Postgresql behavior at end of sequence

As per subject when a sequence hits its max val what happens? What is the behavior? I have a big table with a sequence as a primary key and would like to know. Thanks.
0
votes
2answers
120 views

calling a function with parameters in insert

Is it possible in SQL ellegantly achieve the following: insert into my_table (the_geom) VALUES geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)), ...
1
vote
1answer
483 views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
1
vote
1answer
156 views

scenario to update or insert big amount of data

I try to find the best scenario to update or insert 1 million contacts in my postgres database. My actual scenario is: read contacts 1000 per 1000 and try to insert multiple rows with one request ...
1
vote
0answers
852 views

importing sql into postgresql database

In pgadmin, I selected a table from my production database I want to export. I right clicked and selected backup. I set the filename to devices.sql and selected plain as format. Then I clicked the ...
7
votes
2answers
6k views

How can I update a json element in postgresql json datatype

I can't figure how can I update an element in a postgresql 9.3 datatype. My exemple the db CREATE TABLE "user" ( id uuid NOT NULL, password character varying(255), profiles json, gender ...