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
19 views

How can I restrict users to see only tables they can SELECT from

I have a redshift database with many schemas and tables. I created a group of users and granted them with permissions to view (select) tables in some particular schema: CREATE GROUP data_viewers; ...
0
votes
0answers
10 views

postGIS out-of-db still has error when environment file is updated

I'm running postGIS 2.1 with a PostgreSQL 9.1.21 and am trying to get rasters back out of a database and am running into the error: ERROR: rt_raster_load_offline_data: Access to offline bands ...
2
votes
1answer
14 views

PostgreSQL read-only user for pg_dumpall

I'm running a PostgreSQL instance which is used by several related applications, each of which has its own database. For backups I want to create snapshots of the complete data in the instance and I ...
1
vote
1answer
18 views

How to make trigger function to make daily entering-exiting report?

I have a table called daily_report that register what time/date worker goes out from the building and what time/date he/she comes in. CREATE TABLE daily_report ( id serial, ...
0
votes
0answers
34 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
15 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
30 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
28 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
28 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
31 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
14 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
212 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
123 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
2answers
34 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
56 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
37 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
17 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
113 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
30 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
9 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
33 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
44 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
30 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
18 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
18 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, ...
0
votes
0answers
15 views

Change tablespace for busy tables - PostgreSQL 9.2

I'm planning to do a Tablespace on my DB. To do that, I need to know which tables are the most busiest... Which ones have more access... I was trying to do that by using: select schemaname, ...
1
vote
1answer
24 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
47 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
32 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
36 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
39 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
40 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
27 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 ...
3
votes
1answer
83 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
15 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
53 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
55 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
103 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
28 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
57 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 ...