All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

3
votes
1answer
47 views

Use large set of data in multiple databases

I have been thinking about this a lot but somehow can't figure out how to deal with it in an efficient matter. Say you have a large set "X" of data that consists of multiple tables. For the sake of ...
2
votes
1answer
78 views

Is there a maximum length constraint for a postgres query?

The app we are building might execute quite a big insert queries. Is there limit that my postgres query can have only a certain number of characters?
2
votes
2answers
63 views

How to prevent PostgreSQL from rewriting OUTER JOIN queries?

My query is: SELECT Acol1, Acol2, Bcol1, Bcol2, Ccol1, Ccol2 FROM tableA LEFT JOIN (tableB FULL JOIN tableC ON (Bcol1 = Ccol1)) ON (Acol1 = Bcol1) EXPLAIN ANALYZE gives ...
2
votes
1answer
65 views

How to debug when OS kills postgres for high memory usage

I am running Postgres 9.4 server in a VPS running Ubuntu. It gets killed often (multiple times in a day). This is the message from dmesg Out of memory: Kill process 1020 (postgres) score 64 or ...
2
votes
1answer
27 views

See the definition of a cursor

From doing select * from pg_stat_activity;, I can see that some process is using a cursor. The query column look like this: FETCH FORWARD 50000 FROM "db" Is there any way to see the SQL that was ...
0
votes
1answer
18 views

Subquery uses ungrouped column “s.student_id” from outer query

I have the following tables: 1 teacher can have 1 or more students 1 student can have 1 or more homeworks I would like to know in a list of teachers, for each teacher if it has any student with ...
6
votes
2answers
124 views

Exists subselect vs inner join?

I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
0
votes
1answer
60 views

Combining 2 SELECT queries and printing the results in PostgreSQL

I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to ...
2
votes
1answer
56 views

set serializable isolation for update query postgresql --9.4

CREATE TABLE xtable(xtableid integer, xcolumn integer) CREATE TABLE ytable(ytableid integer, ycolumn integer) CREATE OR REPLACE FUNCTION foofunction(integer, integer) RETURNS void AS $$ BEGIN SET ...
0
votes
2answers
34 views

Database view listing distinct days

I have a list of sensors of many machines, that stores on a second base (i.e., 86400 records each day for each machine). One of the many columns is the datetime of the value (that can also be stored ...
1
vote
1answer
53 views

High Availability for MariaDB and Postgres : replication or active-passive (SAN)

During the last weeks I did a lot of research on High Availability for Postgres and MariaDB. Both by my own research and also recommended by vendor's, replications seems to have more advantages. Some ...
0
votes
0answers
36 views

postgreSQL FK on delete set null not working

I want to set FK constraint on delete to null but it is not working. In table transactionauditlogentry I have altered the FK constraints for Packet and Credit table to on delete set null. If I ...
1
vote
1answer
35 views

How to optimize view with group by

Have view with query : SELECT min(lbl.id) AS id, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id, sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, ...
2
votes
1answer
38 views

PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off

I got a newly installed database (PG 9.4.6 / RHEL 7) which logfile grows rapidly. Most entries are like 2016-03-03 10:33:29.259 CET [56868] user@db STATEMENT: SELECT field1 AS a1, field2 AS a2, ...
1
vote
2answers
98 views

psql: FATAL: Peer authentication failed for user

I just installed PostgreSQL 9.4 on Ubuntu 15.10. I created a user with createuser -P myuser I created a database with createdb -O myuser mydatabase I edited pg_hba.conf and added local mydatabase ...
2
votes
4answers
218 views

How can I swap two values from particular column in a table in Postgres?

Consider a table test contains an attributes of id and name. The values for that. Id | Name 1 | Raj 2 | Kumar From the above sample table, I know only the id, So through the Id I ...
5
votes
1answer
90 views

How to not “duplicate” a table if it has a unique constraint over many columns? [duplicate]

I have a very large table (35GB) that is unique over a combination of four of its columns. The table isn't very wide and the four columns it is unique over are the columns that are larger (in ...
3
votes
1answer
114 views

Speeding up a GROUP BY, HAVING COUNT query

I'm trying to speed up this query in Postgres 9.4: SELECT "groupingsFrameHash", COUNT(*) AS nb FROM "public"."zrac_c1e350bb-a7fc-4f6b-9f49-92dfd1873876" GROUP BY "groupingsFrameHash" HAVING COUNT(*) ...
0
votes
1answer
37 views

List of nodes types in a query plan in PostgreSQL?

Where can I find an exhaustive list of possible nodes types(e.g. Seq scan, groupAggregate, Nested loop ...) that a query plan could contain in PostgreSQL?
0
votes
1answer
36 views

Get equivalent SQL query for any Postgres meta-command

I hope the title is self-descriptive. I want to be able somehow to translate any Postgres meta-command into its corresponding/underlying SQL query, at least to learn more about Postgres and the way it ...
1
vote
1answer
29 views

Postgres: querying jsonb array with an array

My products table has a jsonb column called 'specs'. This specs hash will often have an array field, let's call it 'spec_options'. When querying this table, I need to find any product that has ...
3
votes
3answers
89 views

Calculate rolling sum over 7 consecutive day period on PostgreSQL

I need to get the rolling sum over a period of 7 days for each row (1 row per day). For example: | Date | Count | 7-Day Rolling Sum | ------------------------------------------ | 2016-02-01 | ...
1
vote
0answers
40 views

Is there any reason to run VACUUM followed immediately by VACUUM FULL?

Because VACUUM FULL completely rewrites a table into a more compact form, it seems obvious that there is never any reason to run VACUUM FULL and then run VACUUM, because the second VACUUM wouldn't ...
0
votes
1answer
23 views

Deleting aged rows from postgresql table which don't have related entries in another table

I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied. The postgresql database is ...
1
vote
0answers
31 views

In Postgres, how to preserve newline character in COPY TO command?

One of the column of my table contains string with '\n'. postgres=# select * from stringtest ; id | inputstr ----+----------------- 1 | Name : NOC\r + | \r + | Detail ...
3
votes
1answer
49 views

Replicating redis foreign table via postgresql synchronous replication

I have synchronous replication between db server, DB1 and DB2. Tested, working. I have a redis server (RD1), integrated with DB1 (master) via redis_fdw, no issues here. I was expecting foreign redis ...
1
vote
1answer
53 views

Postgres Row Level Security and BDR

I am implementing a multi tenant web app using postgres. I want to use Row Level Security (RLS ) for data separation. RLS is available from PG version 9.5. However, at the same time, the HA / DR teams ...
1
vote
1answer
32 views

Is it possible to write a select * view that includes new columns added to table?

I would like to write a view that is essentially something simple select a + b, * from table_name and will always truly include all columns in table_name, even when I add or remove them. If I do the ...
0
votes
0answers
36 views

PostgreSQL 9.5 FOREIGN SCHEMA multiple databases vs one - how big is the performance hit?

In my scenario I'm considering splitting up my one big database into multiple smaller and more specialized databases using IMPORT FOREIGN SCHEMA for cross database joins and queries. I couldn't find ...
1
vote
1answer
25 views

Deleting aged postgresql rows with foreignkey constraints

I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated For instance, if I run DELETE FROM links_reply WHERE ...
0
votes
1answer
38 views

Query bytea column using prefix

Assuming I have a table with a bytea column called data on a table called t, how can I: Write a query that returns all rows which have a specific prefix on their data column. Index the query. I am ...
3
votes
1answer
69 views

How to read postgres.log output?

I need to improve my database performance, in order to achieve that, I enabled the log in my postgresql.conf: log_statement = 'all' log_connections = on log_disconnections = on Why do I have 3 ...
0
votes
1answer
35 views

how to access a table containing . in its name

I have created a database schema as "mytest.us" and inside this schema, I have created a table "a.b.c". When I do select * from select * from "mytest.us"."a.b.c"; year | mydata ------+-------- ...
1
vote
0answers
30 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender ...
0
votes
1answer
46 views

syntax error at or near “IF” [closed]

I try to create a sp that checks if a job that didn't started already exists and if not creates a new one. It should return the id of the job. For now I have the following code: CREATE OR REPLACE ...
3
votes
1answer
89 views

PostgreSQL JSON query array against multiple values

I want to write a query against jsonb type in Postgres that given an array of customers IDs will find corresponding groups. Given this example table: CREATE TABLE grp(d JSONB NOT NULL); INSERT INTO ...
1
vote
1answer
23 views

Postgres odd behavior with indices

I've got a datavalue table with ~200M rows or so, with indices on both site_id and paramter_id. I need to execute queries like "return all sites with data" and "return all parameters with data". The ...
1
vote
1answer
34 views

How to influence the planner on Postgresql

I have the following: a complex query, 'match', that returns about 200 rows (ids) a huge table, 'usernames', indexed on id that maps them to usernames When I try to left join the query (1) with ...
1
vote
1answer
25 views

How Can I get multi-Dimensional array by value

In PostgreSQL 9.5 I use jsonb column called user_tag. This column has multi dimensional array like this: [{"id": 1, "tag": "@xang"}, {"id": 2, "tag": "@wang"}] I want to get when id is equal to 1. ...
0
votes
1answer
53 views

Is it possible to create a SourceURI Column in SQL?

It is possible to create a UUID column in SQL: create table table_name ( uuid_column_name uuid, ); Is it possible to create a SourceURI column in SQL as well? E.g.: create table table_name ( ...
1
vote
2answers
95 views

For a standby workload of long running analytical queries, is it better to turn on hot_standby_feedback or set max_standby_*_delay settings to -1?

When spinning up a hot standby server specifically for BI/Analytics purposes where long running queries may be common, is it better to turn on hot_standby_feedback or set the max_standby_*_delay ...
2
votes
1answer
76 views

How to log connections to the database?

I'm trying to write a small program that is meant to connect to a database over TCP/IP using raw sockets with Python. I can't open a connection so tried to see what's happening on PostgreSQL's side ...
2
votes
1answer
31 views

SETVAL in a Window Function

I'm attempting to do an INSERT into multiple tables using the same key field. According to Sequence Manipulation Functions: Important: Because sequences are non-transactional, changes made by ...
1
vote
0answers
53 views

Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)

Given the folowing view definition create or replace view projects as select id, client_id as clientId from data.projects Is it possible, knowing the view name and the view column to get the name ...
0
votes
1answer
71 views

PostgreSQL Database not opening

I tried opening my PostgreSQL database and I am getting following error: An error has occurred: could not connect to server: No buffer space available (0x00002747/10055) is the server ...
5
votes
1answer
204 views

In PostgreSQL is the row ordering preserved in functions and CTEs?

In SELECT statements the ordering of the returned rows is not guaranteed if the ORDER BY clause is not specified. This is true for "normal" tables. Is this also true for ordered tables generated ...
2
votes
1answer
60 views

Strange UTC offset time zone parsing in Postgres

Postgres exhibits some strange behaviour when parsing time zones, or I just don't understand how it works. From the documentation: SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ...
0
votes
1answer
27 views

Unique values depending on context

So i am implementing a system that has PIN (Personal identification number) codes. The PIN is a simple combination of a few letters and some numbers. The PIN could be something like AX7500. Now, a ...
1
vote
2answers
36 views

PostgreSQL JSONB: How to get an element from Array of Embedded Documents

I am comparing MongoDB with PostgreSQL query abilities. I struggle a bit with finding simple equivalent for the following MongoDB queries. What are the equivalent commands in PostgreSQL JSONB to ...
2
votes
1answer
62 views

Restore superuser role to postgres user

I am looking for clear step by step instructions to restore superuser role to postgres user that comes at install. I had to perform the below instructions (which didn't work thus far), because in ...