Tagged Questions
All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
0answers
8 views
Diagnosing why postgresql query is going in a seemingly endless processing loop
I maintain a social forum built with Django and postgresql 9.3.10. There's a maintenance query I run via psql, to clean up my postgresql table. It goes like this:
begin;
DELETE FROM links_seen WHERE ...
0
votes
1answer
35 views
How to convert postgresql dump file into mysql dump file
I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance
0
votes
1answer
14 views
Top N Results Ordered by Joined Table Column
I'm trying to optimize a fairly simple query that just gets a list of the top N users ordered by the number of followers they have. I am running into performance issues when I run the query a certain ...
1
vote
2answers
48 views
UPDATE several rows same SQL - PostgreSQL 9.2 [on hold]
I need to do an two updates in just one SQL:
UPDATE public.ja_jobs AS t2
SET time_arrival = t1.time_arrival,
SET time_job = t1.time_job
FROM junk.19637 AS t1 WHERE t2.id = t1.id AND t2.time_job ...
1
vote
1answer
25 views
Alias Column Name [on hold]
I am trying to run a basic select Query in PostgreSQL, but I am having trouble using the column alias. I tried to create the alias as randomfield As [Space In Name] but it gave an error of
Error: ...
1
vote
0answers
22 views
Aggregate count over variable number of json keys
How can one get a list of json objects like this:
[
{"band": "beatles", "bass": 1, "drums": 1, "guitar": 2},
{"band": "metallica", "bass": 1, "drums": 1, "guitar": 2, "vocals": 1}
]
from ...
1
vote
0answers
8 views
parallel pg_restore with materialized views
I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS.
To troubleshoot the restore process, I have split the restore ...
0
votes
1answer
8 views
Only describing postgres tables with “\d” (not sequences, indexes, views, …)
I want to describe every table in a postgres database.
Currently I have this:
psql -d db -c "\d *" > db_description.txt
But this also includes all sequences, views, etc.
Is there a command ...
1
vote
1answer
18 views
Trigger for a particular row?
I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like:
CREATE ...
0
votes
1answer
32 views
Updating column from another table - PostgreSQL 9.2 [on hold]
There are two tables:
ja_jobs
junk.test_test1
I need to update the time_job column from junk.test_test1 to ja_jobs.
Here is what I'm doing now:
UPDATE ja_jobs t2
SET time_job = t1.time_job
FROM ...
0
votes
1answer
9 views
select duplicated rows by modified_date - PGSQL 9.2
Query:
SELECT j.id INTO junk.ja_jobs
FROM (
SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id
FROM ja_jobs
WHERE clientid = 33731 AND creatortype = 'legacyrec' AND ...
0
votes
0answers
9 views
Sequence Unique value - PostgreSQL 9.2
On the table users there is a code column:
code CHARACTER VARYING,
The customer can add any value he wants into that column, but he also can chose by not adding any value.
That column can't be ...
1
vote
1answer
18 views
Remove accidental duplicates in PK table, cascade changes to FK table
Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and ...
2
votes
1answer
17 views
Multiple columns from one table rely on one single table
I'm working with PostgreSQL 9.2 and have the following relation between 2 tables:
The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost ...
0
votes
1answer
14 views
Start, stop, configure PostgreSQL through Mac terminal
I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into ...
1
vote
0answers
19 views
When was a table created? [duplicate]
Is there any way to find out when a table was created in PostgreSQL or Greenplum, other than finding the underlying table and looking at the OS creation timestamp?
0
votes
0answers
9 views
Remedying psycopg2:OperationalError in postgresql 9.3.10 database
I have a Django app + postgresql 9.3 DB, hosted on Ubuntu VM. Recently my VM ran out of disk space, resulting in no space left on device errors showing up in my logs. I cleared the space on the device ...
0
votes
0answers
5 views
Ubuntu: How to soft-link to the latest version of PostgreSQL CLI tools [duplicate]
I have just upgraded my RDS PostgreSQL version from 9.3 to 9.5. In order to access the server, I need to upgrade the Ubuntu CLI tools as well.
I have upgraded the tools using the apt.postgresql.org ...
0
votes
1answer
8 views
Postgresql error: failed to re-find parent key in index
I haven't seen this before and am stumped.
I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently I VM ran out of disk space (resulting in no space left on device ...
1
vote
1answer
12 views
Delete duplicated rows by modified_date - PostgreSQL 9.2
I've got some duplicated rows on a table called ja_jobs:
To find those duplicated data, I'm running this Query:
select * from ja_jobs WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted ...
0
votes
0answers
14 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
16 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
14 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
22 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
24 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 ...
0
votes
0answers
42 views
how to generated dynamic date and IDs [on hold]
I'v this table
id|poste_charge_id|equipement| date |indi_dispo|stop|reele
----+-----------------+------------+------------+------------+------+--------
2| 1| ...
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
23 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
...
0
votes
1answer
31 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
24 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
13 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
201 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 ...
4
votes
2answers
89 views
+50
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
27 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
49 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
11 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
110 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
0answers
19 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
8 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
28 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
40 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
20 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
15 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 ...
