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)

2
votes
1answer
20 views

How can I add a numeric version_id column to an existing table?

I have a table in a postgres db with rows versioned with a timestamp that I'd like to add a numeric version_id column to. eg. existing: ID VERSION_DATE -- ----------------------- A 2015-02-05 ...
0
votes
0answers
16 views

Postgres shared_buffers utilization

Hi is it possible to show how much of the shared_buffers postgres is using or how can i know it is too small?
1
vote
1answer
69 views

Reuse SELECT query by adding results in array?

I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
-2
votes
0answers
17 views

error no server supplied when importing sql file

I am trying to import an SQL file which have size about 24Mb, but it's failings. No Server Supplied What could be the problem?
1
vote
2answers
47 views

Reset the sequence of a serial PK with dynamic SQL

How can I dynamically reference a sequence name (as below) using a combination of strings and DECLARE variables? Is the code below proper or is there another method of doing this? My goal is to do ...
0
votes
1answer
30 views

PostgreSQL function thinks table reference exists when it does not

So I run this query: SELECT f_copy_tbl('parts','parts1'); ...while working on code based off of this answer and I get this error message: ERROR: relation "parts1" already exists pgAdmin III ...
0
votes
0answers
14 views

PostgreSQL plpgsql math increment a DECLARE to fix table sequence [duplicate]

The answer to this question will help me post a full (hopefully) working answer to my earlier question about replicating tables and their primary key. I'm new to plpgsql so the context of this ...
1
vote
1answer
63 views

Multi table GROUP BY query optimisation

Currently I have a multi table/column GROUP BY query which is pretty slow. Below is the tables schema used by the query: report Table ...
1
vote
1answer
25 views

Rename the constraint of a possibly referenced primary key in PostgreSQL

Is it safe to use this command just like that, even though the table might be referenced from other tables as foreign key? ALTER INDEX foo_pkey RENAME TO pk__foo__id; I have just tried it and it ...
1
vote
0answers
19 views

Combining date and date/time ranges in PostgreSQL

In a PostgreSQL table, I'd like to store events which have a range telling when the event is happening. However, some events span whole days (i.e., the user does not care about time), while others ...
1
vote
0answers
29 views

Implementing a consistent mailbox

I am trying to implement something like a mailbox for short messages between users. I am using Postgresql 9.4.1 with SQLAlchemy (Python3). There is a table of users: users = Table('users',metadata, ...
0
votes
0answers
14 views

Should I partition table by time or project_id, or both?

I have a table of event data that is getting slightly large. It's ~10 million records. I want to partition the table to help speed things up. I almost always restrict the query by both the time and ...
1
vote
3answers
74 views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
0
votes
1answer
29 views

PostgreSQL utilize data from SELECT in BEGIN or use WITH?

I went through the documentation for 9.3 and did not find anything suggesting that I can or can not use data from a SELECT query within a BEGIN query. Is it possible or will I have to resort to a WITH ...
0
votes
0answers
21 views

Better way to create an Events table?

I working on a medium sized PostgreSQL database with some schemas, each with some tables. In almost every schema there are tables that represent event tables, where something that has "happened to" ...
0
votes
0answers
12 views

How to add entries to tables in PostgreSQL if the attributes are reliant on other tables?

The problem that I have is something like this: Users ========= UserID Name Surname User Group ========= GroupID LeaderID Group Leaders ============= LeaderID UserID GroupID The problem arises ...
-1
votes
1answer
44 views

is camel case for column names safe?

I have a database where column names are all camel cased. No problems encountered. Will that be a problem under some scenarios, as per recent versions of MySQL, Postgres?
0
votes
2answers
77 views

check key if exists in other table without fk constraint

Lets say i have: table a (id_a int); table b (id_b int, id_a int); I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on ...
0
votes
1answer
28 views

Connect to remote EC2 Postgresql database

I want to setup a connection between a webserver and a PostgreSQL 9.3 server. Both are hosted on Amazon EC2 servers and are part of the same security group. I can't get the connection working. The ...
1
vote
1answer
22 views

PostgreSQL : Where is index kept until check point occures ?

In PostgreSQL data is first written to WAL files. When commit is done, data is written to WAL files and then return to client (synchronous commit). When checkpoint reached or WAL buffer becomes full, ...
2
votes
1answer
34 views

Storing floating point values for various units of measurement (kg, ml, mm, pl)

I have a table like the following: order_item ------------ serial | id (PK) integer | order_id ???? | quantity integer | unit_of_measure_id (PK) --> ml, l, kg, mg, mm, m, etc. ...
-1
votes
0answers
25 views

Postgres - Optimizing a View Query

I have a view which gets populated by approximately 150,000 records per day. The data from the view is consumed by another application. While reading the view, it takes almost 10 minutes, which is a ...
1
vote
1answer
29 views

How can I share pg_dump output with my team so they can restore without errors?

I am on a team that is building a Drupal website. All of the settings, including the migration scripts from the old site, will be in code, so in theory all of our work can be replicated just from a ...
2
votes
2answers
72 views

How can one optimize a query involving a sort and a LIKE?

Here is the query: EXPLAIN ANALYZE SELECT * FROM "Users" AS "User" WHERE "User"."name" LIKE 'garr%' AND "User"."id" NOT IN (2449214) AND "User"."hellbanned" IS NULL AND ...
0
votes
1answer
42 views

Is it possible to make database owner read only?

The database owner can revoke certain privileges from themselves such as INSERT but is it possible to revoke further privileges such as DROP for read-only operation?
2
votes
1answer
30 views

Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails?

On the PostgreSQL Concurrency With MVCC page, it says: know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation ...
0
votes
0answers
22 views

How calculate per tenant disk usage in a multitenant database with tenant_id columns?

I would like to provide forum hosting as a service, for forum software I'm developing. The data is stored in a PostgreSQL database, in tables with a forum_id column. That is, all forums share the same ...
1
vote
2answers
58 views

Is it safe to cancel a PostgreSQL ALTER TABLE query that is waiting on a lock?

We started an ALTER TABLE query hours ago and only recently realized (via pg_stat_activity) that it is waiting on a lock. We discovered the other query that is holding a lock on the table we want to ...
2
votes
0answers
47 views

Optimizing a single table, write-once, read-many PostgreSQL database

I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single ...
0
votes
0answers
26 views

Postgres user without password

I'm creating unit tests on a python application that uses digital certs on the server. Since I am not testing the connection between the server and the database, I need to setup the temporary test ...
0
votes
1answer
25 views

Query performance with primary keys of different data types

Is there a significant difference in performance if i decide to use INT or BIGINT as data type of a primary key in a table? Of course if i use BIGINT the value will have twice it's size. But as the ...
0
votes
1answer
29 views

How to know when Postgresql password is changed

My question is how to know the date when a postgresql user password is changed. Is it possible? I know postgresql has account expiry feature, but I'm asked to check if users have changed their ...
0
votes
2answers
45 views

Optimizing a Postgres query with a large IN

This query gets a list of posts created by people you follow. You can follow an unlimited number of people, but most people follow < 1000 others. With this style of query, the obvious optimization ...
1
vote
0answers
65 views

Database performs differently on the two same Xeon E3 1270-v3 Quad Core machines [closed]

Configuration: RAM: 16 GB DDR3. HDD: Seagate 500 GB 7200 3Gb/s 3.5 inch SATA - idential disks. Postgres 9.4, tried with 9.4.1 also Windows 2008 R2 Web Tried these tunings: work_mem = 32MB ...
0
votes
0answers
32 views

Append elements to array only if the element does't exist

WITH upd AS ( UPDATE univ.products SET source = source::int[] || _source WHERE project_id = _project_id AND product_id = _products AND NOT(_source = ANY(source)) RETURNING id ) , ins AS ( ...
-1
votes
1answer
25 views

Postgresql - Stuck building a plpgsql function [closed]

I am a beginner in plpgsql and i am stuck coding a function. I need a function that does the following: Giving a table, a id_field in that table and another field in that table it does: ORIGINAL ...
1
vote
1answer
49 views

PostgreSQL Replication on a daily basis

With PostgreSQL, I want to replicate a subset of all tables from Database A to B, but only once a day. Since I do not know what to search for: is there a technical term for such a scenario? Does e.g. ...
0
votes
1answer
34 views

What is held in memory during the life of a CTE?

Assuming a multi-read/multi-write CTE, what exactly is held in memory during the life of the statement? Is all data except RETURNING data discarded at the end of each sub-statement?
2
votes
1answer
39 views

Can PostgreSQL use multiple partial indexes per query?

I have read that PostgreSQL can use multiple indexes in general, but in the specific case of a query that ranges across two indexes, will it use both? If so, will they be loaded sequentially or ...
0
votes
1answer
21 views

HAVING ANY in Postgres

I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition. For example if I wanted to select a group where at ...
3
votes
0answers
78 views

Slow transactions, many locks

At high load conditions the server app stops responding to client because begin/commit statements are executing very slowly (some take 15 seconds). The queries are simple - insert, update two columns ...
1
vote
1answer
30 views

invalid memory alloc request size on simple query with array_agg on Postgres 9.3

Postgresql 9.3 - Debian 7 54gb RAM 8 cores (On google compute engine) I have a huge table called search_token (~50M rows) and I'm trying to execute a very simple query that is: select ...
0
votes
1answer
16 views

privilages system table in postgres

Is it possible to know all the tables a user has access to using a single query? I tried with pg_role and pg_user with no luck. Can anyone tell me about a system table which has the data of all the ...
0
votes
0answers
14 views

Exception handler on multiple row insert PostgreSQL [duplicate]

I'm trying to create a function which inserts rows from one database table to another, this is the part of the function which goes wrong: BEGIN INSERT INTO suppliers("id","name", "url", "logo", ...
0
votes
0answers
40 views

Query gives not result

I have a quite simple table with different values. When I search a specific column no results are given. What could be the error? For example I search for an "id": db=# select * from table where id ...
0
votes
1answer
25 views

Database Change Management

What are ways in which teams handle database change management? I have a team of 8 developers making database changes concurrently. Each developer is responsible for updating their changes in what we ...
0
votes
1answer
12 views

How to create a citation/quote from a Postgres text field?

I have a database with a lot of text and I want to find the surrounding text for a word that I am searching for i.e. If I search this text for "text" I should get something like (and more consistent ...
0
votes
1answer
71 views

Why is Postgres on RDS maxing out on CPU every few hours?

Using Amazon RDS, we are running ETL scripts to migrate our data. However, every few hours there is a huge CPU spike. Here are the ETL specs (per ETL): 50 records inserted / second pool of 1000 ...
0
votes
1answer
23 views

Querying sums of grouped consecutive rows in PostgreSQL 9

I have data about people traveling in different countries like this: country | begintimestamp | distance Germany | 2015-01-01 00:00:00 | 100 Germany | 2015-01-01 01:12:13 | 30 France | ...
0
votes
1answer
81 views

AWS RDS PostgreSQL dump/restore - syntax error

I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following... 1) Dump EC2 DB: pg_dump --host localhost --port 5432 -Fc ...