PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...

learn more… | top users | synonyms (1)

1
vote
1answer
35 views

How to maintain fixed number of rows per foreign key?

I would like to keep a record of the last N (say, 10) objects a user has viewed. I'm thinking a basic way to store this information might be the following table: CREATE TABLE history ( id ...
1
vote
1answer
74 views

Sync postgreSQL with SQL Server database- on data change event

I have one PostgreSQL database and its data are being changed regularly. I want to sync all its data with my SQL Server database. One thing I can do is use query to check all required tables and ...
-4
votes
1answer
52 views

Postgresql create database with size & resize?

My question regards PostgreSQL database size. How can I create new database with fixed size? (e.g. 5 GB) Once I have created fixed sized PostgreSQL database, how can I resize it ?
3
votes
1answer
48 views

Does it make sense to upgrade to a newer PostgreSQL JDBC driver?

I would like to know if it could be possible to gain any performance benefits from upgrading the default PostgreSQL JDBC drivers from applications like Confluence or JIRA. Assume that you cannot ...
3
votes
1answer
66 views

pg_dump does not finish

I am trying to backup our postgres database (8.4.17), which is about 25GB in size. pg_dump database_name > database_db_dump_2014-05-05.sql unfortunately, the backup stops after about 600MB in, ...
-2
votes
0answers
43 views

Cluster index or Partition in postgresql [closed]

I have a products table with 20,000,000+ and it's increasing. It takes alot of time to fetch a row from that table with that query select * from products where user_id = 1. So i made a partition on ...
1
vote
1answer
42 views

Postgresql reset_xlog

I just mistakenly moved the pg_xlog folder of a live, running postgres server. When I restarted the server shortly after, it failed to start because of a WAL problem (obviously). For non-technical ...
0
votes
3answers
70 views

Trigger or Stored Procedure?

Let's say I have two tables, foo and bar: CREATE TABLE foo( foo_id serial NOT NULL, count integer NULL, PRIMARY KEY(foo_id) ); CREATE TABLE bar( bar_id serial NOT ...
-1
votes
1answer
78 views

Access table field on creating a trigger postgresql

How can I access table field on creating a trigger? CREATE TRIGGER foo_trigger AFTER INSERT OR UPDATE OR DELETE ON foo FOR EACH ROW EXECUTE PROCEDURE proc(foo.field);//here goes the parameter
0
votes
1answer
46 views

Check if data from database is up to date

I'm storing some data from database, and want to check if that data is up-to-date from the database version. What is the best way to do this? My ideas: Getting checksum from the table and comparing ...
5
votes
0answers
84 views

Modification to GEQO (Genetic Query Optimization) of PostgreSQL

I need to implement a functionality that is in line with the GEQO functionality of PostgreSQL. I understand that GEQO approach is to encode query plans as integer strings and GEQO generates these ...
0
votes
0answers
57 views

Historical DISTINCT over temporal data

I have some data that changes over time. To represent this, my tables have a valid_range column that stores the time-range when the row was valid. Here I'm using a range of ints to simplify things. ...
0
votes
1answer
42 views

Postgres rewriting entire table

We have a database that is just over 200 gb in size, with around 430 million rows, a primary key and a spatial index. Every few weeks we get an update which involves around 3 million rows, deleting ...
0
votes
0answers
40 views

Can I speed up the psqlodbc driver?

I have a SQL Server Integration Services 2012 package that copies data from SQL Server 2012 to Postgres 9.3. I'm using the latest version of the psqlodbc driver (9.3.210) but I only can transfer ...
1
vote
1answer
67 views

Multi-valued range subtraction

I have a table (actually a query, but I think it's the same thing) with two range columns. I need to perform a 'subtraction' on the two ranges. Precisely, I need Intersection(a, Complement(b)). Since ...
0
votes
1answer
37 views

group by and display contents of last row for each result

I've got some data of two objects and I want to retrieve the last row of both the objects, preferably in one query. Table structure: CREATE TABLE data ( pid serial NOT NULL, dt timestamp without ...
0
votes
1answer
62 views

ORDER BY that doesn't contain repeating values

Relevant SQL Fiddle (postgres 9.2) How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column? That is, for rows of the column "rank" (1,3,3,1) how do I get a result so ...
2
votes
0answers
41 views

Postgres Replication From Different Masters?

I'm getting a Postgres 9.3.3 install sorted out. My application is Drupal (so read/write), but low-traffic, so all application traffic is sent to the primary master. I have two sites (main and ...
1
vote
1answer
122 views

Is there a way to show a string instead if numeric column has specific value?

I have a float column called "weight". It's default is -1, showing the object wasn't weighted. Is it possible to show string "unweighted" instead of any negative number in the field? As it is ...
1
vote
1answer
94 views

Alternatives to NULL for missing data [duplicate]

I am using PostgreSQL and trying to write value constraints. Is it possible to record missing data as distinct from NULL values in a numeric field without using an arbitrary numeric value? For many ...
2
votes
1answer
62 views

Postgres database level encryption

I want to have the whole database to be encrypted, instead of encrypting the data before storing into database. Is it possible to encrypt the whole postgres database and what best method to use? ...
0
votes
1answer
43 views

Restoring a Postgresql database

Last week one of my sites server crashed. I was not able to access the ssh server and could only use the basic linux console on digitalocean.com. Because I could not get into my ssh server I was not ...
2
votes
1answer
157 views

Create index on very large table with many shared values

I'm looking to create an index on a large table (~50 million rows) on a field with lots of non-unique values. Table schema looks like: Column | Type | Modifiers | Storage | Stats ...
0
votes
1answer
53 views

ERROR: loop variable of FOREACH must be a known variable or list of variables

I have few FOREACH loops in my functions. The loops seem to perfectly agree with the official postgresql documentation. This error message is returned: ERROR: loop variable of FOREACH must be a known ...
1
vote
0answers
59 views

Postgres SQL Database Recovery

I am recovering a corrupted database, which may not really of had problems, but the server did. The host has restored a backup of the VM image (Ubuntu 12.04 LTS), however, its 10 days old, which is ...
1
vote
2answers
91 views

How to delete rows from a table when we're joining and return data?

I have two queries: The first one is as below: SELECT * FROM batch bp INNER JOIN sender_log sl ON sl.id=bp.log_id AND bp.protocol='someprotocol' The second query is used to delete rows: ...
1
vote
1answer
142 views

I have an INSTEAD OF trigger, but PostgreSQL still complains while I insert into view

I have created a view and an INSTEAD OF trigger for inserting/ updating/ deleting from that view. Now I have tried to insert some data to the view, and PostgreSQL returns following error (translation ...
0
votes
0answers
24 views

When enable authenticating in MongoDB, how to use mongo_fdw?

I test mongo_fdw these days, It's ok when mongodb disable the auth authenticate, but if the mongo enable the auth authenticate, should we create a mapping user for the foreign table? what shoud we ...
0
votes
2answers
55 views

Rows into Column

I want to make the above view vConsolPrchOrd like below: Database: PostgreSQL 9.2 I want to create a Crystal Report with this view (I asked about this on Stack Overflow).
1
vote
1answer
64 views

PostGis getting rid of the lat long from table

This question is related to this question : Order by distance Now my tables have changed a bit. Table cafes now doesn't have latitude, longitude columns instead there are table addresses which ...
2
votes
1answer
232 views

Update all columns from another table

I need to update a table from another one, and I need to update all columns. Besides listing every column in the SET clause, is there a way to update them all at once? Like this: update tableA set * ...
0
votes
1answer
37 views

Change posgresql 9.3 slave roll password

I need to change the password on my slave posgresql server, because the person who set it up left the password the same as the password for the super user account on our other server. When I run ...
2
votes
2answers
93 views

High volume data aggregation

I need to aggregate incoming data and save the resulting aggregates to a table. A sample of the data is below. id,time 1967,2014-04-12 00:42:55+00 1967,2014-04-12 00:42:51+00 1972,2014-04-12 ...
0
votes
1answer
96 views

WAL archiving from a standby backup strategy Postgresql 9.3

I am trying to figure out if wal archiving from a standby is possible and a viable option for a backup strategy on Postgresql 9.3. The idea is to be able to take regular base backups and wal ...
3
votes
0answers
133 views

Is there a First() aggregate function in postgresql that returns the exact same type as the input?

Full question re-write I'm looking a First() aggregate function. Here I found something that almost works: CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement ...
0
votes
0answers
56 views

Connecting Posgresql server using Pgpool

I am running two postgresql servers on two different linux machine and also running the pgpool-II-93 on the both machine.I simply want to check whether syncing between Databases at different machine ...
0
votes
1answer
59 views

What happens when we call pg_dump while users are still using the system?

We have a web application with PostgreSQL as our database and use celery background tasks for somewhat complex task. At the very beginning of this complex task we take a backup of the database using ...
1
vote
1answer
74 views

PostgreSQL - Out of memory on count group by query

I'm trying to execute the following statement in PostgreSQL database: create table tmp_nodes as select node_id,count(way_id) as count from way_nodes group by node_id; This query fails because the ...
0
votes
0answers
56 views

Adding additional condition significantly degrades performance

Have the following query: SELECT * FROM table_A INNER JOIN table_B ON /* conditions */ INNER JOIN table_C ON /* conditions */ INNER JOIN table_D ON /* conditions */ WHERE table_B.state = 'active' ...
1
vote
1answer
153 views

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now ...
0
votes
1answer
77 views

Database design for small startup

For a small startup that doesn't have much expected traction in the very beginning. We are currently working with only one postgres database without replications and at risk of failures. I've done ...
4
votes
0answers
115 views

Postgresql JDBC Table Valued Parameters [closed]

MSSQL has a great feature called Table Valued Parameters. It allows you to pass a table of a custom data to stored procedures and functions. I was wondering what is the equivalent in PostgreSQL, if ...
1
vote
2answers
129 views

Returning empty string when string_agg has no records

I am trying to return a text field in a PostgreSQL query that is of the form 'stringOne' || string_agg(field, ',') || 'stringTwo' where for certain elements in the group clause, field is always ...
2
votes
1answer
63 views

How to create function(table_name) that returns a set of the table

I have a set of tables that share the same fields (some timestamps and users names used for version control). I need to create a function with dynamic sql that uses a table_name and a timestamp as ...
0
votes
0answers
47 views

How to pass log file in pg_dump

I am using this command to export. export PGPASSWOD=${PASSWORD} pg_dump –i –b -o -host=${HOST} -port=5444 -username=${USERNAME} -format=c -schema=${SCHEMA} --file=${SCHEMA}_${DATE}.dmp ${HOST} ...
0
votes
1answer
36 views

Merge left outer join column

I have a query that use left outer join. something like that : select issues.id, journal.notes from issues left outer join on (issues.id = journal.issue_id and journal.notes != '') So far it ...
0
votes
4answers
639 views

PostgreSQL 9.3 on Ubuntu Server 12.04 v.s. MS SQL Server 2008 R2 on Windows 7 Ultima

I have searched many articles in Google about the performance of PostgreSQL vs MS SQL 2008 R2 and found that many of those articles say the performance of PostgreSQL is better than that of MSSQL 2008 ...
1
vote
0answers
71 views

Is there a psql equivalent of bash's reverse-search-history?

I am very fond of bash's reverse-search-history (C-r) (command-line feature): Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental ...
1
vote
1answer
119 views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
0
votes
2answers
110 views

postgresql being slow on count distinct for dates

I have a very simple, but very big, table. Its schema is like this (yadda int, yadda1 int, yaddate date, ... other stuff). Now, yaddate has an index by itself and it is also in other indexes ...