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

learn more… | top users | synonyms (1)

0
votes
0answers
40 views

Improving query performance involving similarity and full-text search

I have been trying to improving the performance of this query involving similarity and to_tsquery. I have a table (440,000 rows, PostgreSQL 9.5.2) with the following characteristics: test_db=> \d ...
0
votes
1answer
19 views

Schema size [PG TOAST] - PostgreSQL 9.2

I've got the following query to measure the schema's size on my DB: WITH schema_size AS ( SELECT tab.table_catalog AS database_name, tab.table_schema AS schema_name, tab.table_name, ...
0
votes
1answer
46 views

Grant SELECT on all tables in Redshift

I am trying to assign SELECT privilege to a group in Redshift. So I created a group and a user in that group: CREATE GROUP data_viewers; CREATE USER <user> PASSWORD '<password>' IN GROUP ...
0
votes
1answer
38 views

How do I find the day of week of a date-time field in postgres?

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While SELECT EXTRACT(DOW FROM '2011-07-07') works, the ...
1
vote
1answer
22 views

Why does my PostgreSQL query cost so much when I tack on an additional group-by?

I need to extract two fields from a table. Here's the query with just one of the fields: set schema 'data'; explain (analyze, verbose) select count(example9), example9, ...
1
vote
1answer
36 views

postgresql: PANIC: cannot operate with inconsistent data

I'm trying to setup a replica using repmgr: repmgr -D /var/lib/postgresql/9.3/main -p 5432 -U repmgr -R postgres \ --verbose standby clone psql.master.example.com repmgr --verbose standby register ...
1
vote
1answer
37 views

Slow Query Postgres 9.2

I've got the following query: SELECT j.id, concat(c.company, ' ', c.name_first, ' ', c.name_last) AS client, c.email AS client_email, ...
1
vote
1answer
42 views

pg_dump fails to create a backup when running from System User in Windows Task Scheduler

I am trying to run a pg_dump through a scheduled task in windows. I have two batch files. One creates the task. The other one runs the pg_dump. Even though the pg_dump works with the current user with ...
0
votes
0answers
20 views

Hibernate not using the max id while inserting record

I have a table with over 54000 entries. I had imported this data from a CSV file. When I use hibernate and try to insert a new row into this table, its not picking up the max id adding one and ...
5
votes
4answers
219 views

How can I represent discrete ages in a relational database?

I am using PostgreSQL to store data on a Person. I need to store whether each Person is capable of teaching a Child of each age year, from 0-17. The ages are discrete values, and a Person can have any ...
6
votes
2answers
133 views

Creating relationships between tables that where not specified in the given UML diagram

Good evening! I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately): I'm at the point of creating the relational sketch of it which ...
1
vote
1answer
46 views

Group permissions in postgreSQL not inheriting to table level

I am moving to a group role from a lot of individual users and have one table that needs to have permissions SELECT, INSERT only. Lets say I have: user1 user2 newgroup Both of the users are in ...
3
votes
3answers
60 views

Create unique sequence per set of rows sharing the same foreign key value

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...
1
vote
1answer
40 views

Strategies for reducing data to summaries within a table over time?

I have a large data set where large numbers of entries differ only by timestamp. In the short term it's beneficial to maintain individual entries so that we can correlate by time for individual data ...
1
vote
1answer
23 views

Set a default value for a jsonb property in PostgreSQL

Let's say that I have a jsonb column with documents like: { "lead": "The lead", "video": "An URL to the video" } In some cases I also need to store the name of a picture that's uploaded to ...
3
votes
2answers
118 views

Two names for a column

I have a column named foo in a table. I have constructed queries in scripts, which use this name. I would now like to rename the column in the table to bar. This will break the scripts (there are ...
0
votes
1answer
31 views

Is a partition a relation?

Is a partition a relation in its own right, or is a partitioned table a single relation? Specifically, do we need to increase max_fsm_relations when we split tables up into loads of partitions?
0
votes
0answers
15 views

Pg_partman giving problem in partman.check_name_length function

I am using Pg_partman inpostgres to partition table using id. I am using the Partition Per 10 ID Values Starting With Empty Table Example given in the below link ...
0
votes
0answers
6 views

Can a bucardo source be a read-only database?

I'd like to use a wall-shipped read-only database as a source for bucardo replication. Of course, bucardo triggers have been set up in the master. But as the slave is read-only, bucardo will not be ...
3
votes
1answer
26 views

enforcing data integrity for sequential relations

These are my tables: create table trips ( trip_id serial primary key, trip_nm text ); create table trip_segments ( segment_id serial primary key, departure_ts timestamp with time zone, -- ...
2
votes
1answer
39 views

Best practice for working with materialized views in Postgres

As background, I work with many materialized views which are based on PostGIS spatial queries, some of which take days to refresh (these views are infrequently refreshed whenever the underlying ...
1
vote
1answer
49 views

Alter default privileges for role

I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege. Problem ...
1
vote
0answers
41 views

Replication of PostgreSQL cluster from Datacenter to Office with write access

Some information about the cluster: Version: postgres 9.5 Size: /postgres/data$ du -hs . 854G OS: Ubuntu 14.04 LTS Storage: Raid10 SSD's I have a master-slave PostgreSQL 9.5 ...
0
votes
1answer
11 views

Query Last Value in Fixed Length Array With Empty Values

I have a table that generally looks like: Name varchar | Location varchar[] --------------------------------------------- Lemoney | {Home,Work,Home,Stack Exchange,,,,,,} Other Dude | ...
1
vote
0answers
19 views

Pgpool slave node limit

I've started a new database architecture using pgpool-II 3.4.5, I have 3 servers by now, 1 master and 2 slaves. They are configured to use Master/Slave conf and LoadBalancing. The problem is: When I ...
0
votes
1answer
20 views

Incomplete installation with unsupported locale

I was installing postgresql on Ubuntu 12.04 (Digital Ocean droplet). The locale variables in my environment gets transported to the DO box. When postgres is installed via apt-get install postgresql, ...
1
vote
1answer
29 views

PostgreSQL streaming replication stops updating

I have four PostgreSQL 9.5 instances running on EC2 using m4.8xlarge instances with five PIOPS SSD in a RAID0 setup, and a separate XLOG drive. Until this morning, I never had more than a minute or ...
3
votes
3answers
56 views

How to Pivot in PostgreSQL

Having the following data in a table: ID Category Value 1234 Cat01 V001 1234 Cat02 V002 1234 Cat03 V003 1234 Cat03 V004 1234 Cat03 V005 I want to have the ...
0
votes
1answer
40 views

How to change the Write Ahead Log directory

How do you change the defined WAL directory? During the installation I defined a directory, /var/lib/wal and the $PGDATA/pg_xlog has a symbolic link pointing to that directory. How do I change the ...
0
votes
0answers
37 views

Losing connection while refreshing materialised view postgresql-9.5

I'm trying to refresh my materialized view, which is quite big - more than 1 GB. I always get an error, no matter which way I do this. But errors explain nothing to me. When I'm trying to refresh ...
0
votes
2answers
66 views

Filtering special characters in to_tsquery

This question has been asked before here and here. I want to provide full-text support using postgreSQL via to_tsquery. However, since escaping characters leads to complications, I think it is okay to ...
1
vote
1answer
41 views

PostgreSQL Foreign Key

Very basic questions. I have 3 tables, very obvious whats the use of them: -------- CONTACT -------- ID (SERIAL) - PK NAME (VARCHAR) --------------- CONTACT_DETAILS ---------------- ID (SERIAL) - PK ...
0
votes
1answer
29 views

Pooler (pgbouncer) + MasterDB + Slaves

During a spike, my connections go from 30 to over 140, as you can see below: I'm willing to set-up a pooler like pgbouncer, but have some questions as my scenario is not that easy: 1 Load ...
0
votes
0answers
19 views

What is the easiest way to set up automatic replication of tables/schemas on two PostgreSQL servers?

I have two PostgreSQL database servers, one uses SSD, the other HDD. We struggled with the full SSD disc and we didn't need all the data on SSD all the time so we moved the majority of them to the ...
4
votes
1answer
146 views

Upgrade from Postgres 9.3 to 9.5

I’m attempting my first upgrade of Postgres from 9.3 to 9.5. I’ve run into two roadblocks but not sure how to get around them. I have my 9.3 PGDATA set to /data/postgres, which is a separate ...
0
votes
1answer
17 views

Restoring postgresql 9.4 crashed database

Can anyone help me to restore my crashed database. Is there a way to use pg_resetxlog. I read a number of documentations telling about it but i'm always seeing the same log when i restart my ...
0
votes
0answers
29 views

Fast min / max values of column in big table with index [duplicate]

I have a huge table public.data_minutesample: Column | Type | Modifiers ----------+---------+----------- probe_id | integer | not null power | integer | not null minute | integer | not ...
1
vote
1answer
56 views

PostgreSQL 9.5: Permission to deny functions body

I create new role without any grants. New user can't select on tables, but can see functions body (pl/PgSQL) I want permissions for new role: allow INSERT into one table. deny see tables, ...
0
votes
1answer
60 views

open/execute huge sql file

I have SQL file using PostgreSQL with size about 7GB. How can I open/execute the SQL file? I have researched and tried using navicat premium,razor SQL, HeidiSQL, workbench. In all I get out of memory. ...
1
vote
2answers
108 views

Converting rows to columns

I have data like following: created_at | status ---------------------+------------- 2016-04-05 1:27:15 | info 2016-04-05 3:27:15 | info 2016-04-05 5:27:15 | warn 2016-04-05 ...
2
votes
1answer
24 views

How to count multiple values of a column as one group? [duplicate]

I have a table main with the following rows: id | name | rank ----+--------+------ 1 | Ali | a 2 | Sami | b 3 | Khan | c 4 | Kamran | d 5 | Imran | e 6 | Asad | a 7 | ...
1
vote
1answer
29 views

Column must appear in the GROUP BY clause error even though it DOES appear there

I have a table that looks like this: order_number | key_a | key_b | key_c | key_d | quantity -------------|-------|-------|-------|------------------ 14400 | 2 | 3 | 4 | 54 | 40 ...
0
votes
1answer
27 views

How to select one column by two different names in the same query?

I have a table named Main that contains two fields id,name. I want to select the name column by two different names. For ex: Table: Main-> id:int, name:varchar(20) I have inserted the following ...
1
vote
1answer
72 views

Wildcard search using parameters in function with dynamic SQL

What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic SQL? As a starting point, here is an example from Erwin Brandstetter ...
0
votes
2answers
35 views

postgresql function index not being used by query

I am trying to match a text field so I created an md5 function index as it suggested since a full index was not allowed because the text data was too large. But I can't understand why postgres is not ...
2
votes
0answers
41 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
3
votes
1answer
40 views

Postgres restore from WAL-E backup never finishes

Doing a restore on a WAL-E backup to S3, postgres never seems to complete the restore process. The log repeats the following: < 2016-04-08 13:41:55.801 PDT >LOG: connection received: ...
1
vote
1answer
51 views

Creating an Updating trigger

I have to create a trigger that calculate the the quantity in stock and this is my first one on PostgreSQL. When entering the quantity bought at a Provider (Fournisseur) or a delivery at a client, ...
1
vote
1answer
43 views

Shipping big WAL archives to hot standby

I'm looking to extend my PostgreSQL 9.4 master with a few slaves in hot standby read-only for load balancing. The idea would be to update the slaves only at defined times (once every 24/48 hours) to ...
0
votes
0answers
11 views

Find missing associations in many to many relationship [duplicate]

I have client and category tables with a many to many relationship via client_category_association. table: client id name 1 ek 2 do table: category id name 1 un 2 dos 3 tres table: ...