Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.
10,137
questions
0
votes
0answers
18 views
PostgreSQL takes long time to start
I using Postgre 10.9 version:
$ psql --version
psql (PostgreSQL) 10.9 (Ubuntu 10.9-0ubuntu0.18.04.1)
Recently I just stop my postgresql:
$ ...
0
votes
2answers
8 views
Postgres Initial Setup and User Login
Full disclosure, new to postgres and new to linux. I am having trouble understanding the user management side of postgresql. I installed on a Ubuntu 19.04 based os using
sudo apt update
sudo apt ...
1
vote
0answers
16 views
Postgres Trigger
I have two tables
table.foo:
id
latest_bar_id
table.bar
id
foo_id
datetime
value
foo has_many bar. I'd like to have a postgres trigger (or maybe something else postgres has built in) so that ...
0
votes
1answer
18 views
PostgreSQL naming conventions
Maybe double post but I could not find any answer here.
I saw couple of posts how to name my tables and I understood that (semi-officially) it should contain lower cases, should be underscored and ...
-1
votes
0answers
15 views
How to Return Unique Ranked Results?
I have a database with the results of races. Each race has the name of the competitor, and the time they got in that rank. If I would like to return the top 10 competitors by time, how can I do that? ...
-1
votes
0answers
13 views
PostgreSQL self-join query condition not to return repeated results
I have this simplified query :
SELECT a.p_key, b.p_key
FROM test AS a, test AS b
WHERE
a.p_key < b.p_key -- symmetry breaker
Below is the returned data set, the * marks already 'retrieved'...
0
votes
0answers
13 views
pg_dump not recognized, even though I've confirmed the binary is present
I'm trying to do a dump of a table in PostgreSQL via pgAdmin3. When I try to run the the following command:
pg_dump -t schema.table_name database_name > chosen_save_name.sql
it errors out and ...
0
votes
1answer
34 views
How to make PG query faster even when it is using indices
I have a PG table with about 30M records. I'm noticing that when I query the table with IN clause that would fetch lot of rows, the query is taking a long time to respond (~3 minutes) Question ...
2
votes
2answers
26 views
Is it possible to prevent data loss with Postgres WAL archiving?
As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to archive_timeout.
So if the database crashes, all the data from the incomplete, currently ...
3
votes
3answers
52 views
Any drawbacks of using GIN PostgreSQL index for an integer foreign key?
I have a large table (600 millions rows) with a foreign key other_id of type integer. A single value of the foreign key is repeated about 100 times on average. I need to have an index on that FK ...
1
vote
1answer
23 views
Optimize postgresql query with multiple subqueries
I'm trying to optimize this query taking between 3-4 minutes (it's running on hdd right now and it will go as low as 10s on sdd when going to production I guess but it's still very high).
Table ...
0
votes
2answers
16 views
Split read/write requests over different read/write datasources
Recently I have run some performance tests on the application I work on, and it turns out that it didn't do really well ( the problem is mainly between the back and the DB). So as investigating the ...
0
votes
0answers
13 views
Unit postgresql.service failed to load: No such file or directory
When i try to connect to my postgres database, i get the error below:
could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting ...
0
votes
3answers
67 views
Array containment queries in Postgres
Question on GIN index on array.
I have 2 million rows in a work table.And I need to find work that a user can do, based on the skills he has and also the highest priority work. User always will have ...
0
votes
0answers
16 views
Execute query against similar tables across schemas
I have a postgres database with several schemas each of which contain a table with the same definition and name. I want run a cleanup statement against all of these tables without manually specifying ...
-1
votes
1answer
57 views
Retrieving Number of Users with deposit using Window function [on hold]
I have user's balance mutation data in the following Postgresql structure (see table below)
column 1: Activity month
column 2: User
column 3: Nett unit after mutation
month | user_id | nett_unit
...
0
votes
1answer
28 views
How to design a table/ unique index where uniqueness is temporal?
I have a table that records observed movies in a cinema:
CREATE TABLE cinema_movie_name (
id integer DEFAULT nextval('cinema_movie_name_id_seq'::regclass) PRIMARY KEY,
cinema_id integer NOT NULL ...
0
votes
2answers
35 views
Group by arbitary monthly time period
I want to group the following data by an user defined period:
+------------+--------+
| DATE | Amount |
+------------+--------+
| 2019-03-12 | 300 |
| 2019-03-15 | 1500 |
| 2019-03-25 | ...
1
vote
0answers
33 views
How to create and insert a complexe data in postgresql?
I have a complex data, i have created a type and a table in postgres, and i want to insert this data in my table.
my type :
CREATE TYPE orderline AS (
productId int,
asin varchar,
title varchar,
...
1
vote
1answer
14 views
DatetimeFieldOverflow when inserting more than three decimals precision
TIMESTAMP type should be fine (docs)
timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time zone) 1 microsecond / 14 digits
Create the table like:
CREATE TABLE IF NOT ...
0
votes
0answers
15 views
Materialized view bigger than table it is based on?
I have a database that's ~130 GB and one table makes up for most of this (85 GB). Because this table holds a lot of duplicates, we have a materialized view which basically uses a window function to ...
0
votes
1answer
32 views
Efficient SELECT for array of tuples in PostgreSQL
Assume I have a table records with the following structure
id (unique int)
updated (timestamp)
I have an input_array with values [[id_1, timestamp_1], [id_4, timestamp_4], ...]. I'll refer to each ...
0
votes
1answer
48 views
Optimize a postgresql database view query
Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum):
Client has_many Deals ...
1
vote
1answer
22 views
ALTER TABLE ADD CONSTRAINT failing when executed from function
So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were.
The drop constraints function works ...
0
votes
3answers
26 views
Postgres permission issues for new views
I have a RDS postgres database that only uses the public schema.
I expect the webservice user to get access to all newly created views. When I create a view using the master user, I see all the ...
0
votes
0answers
8 views
replication & load balancer with postgresql configuration
I configured a master-slave replication server with postgres 9.6 following this doc and all worked fine. When I make changes on master server immediately are replicated to slave server and I cannot ...
0
votes
1answer
13 views
How to rank an aggregation in each newly formed group?
The problem below that I would like to solve sounds quite simple but it is quite difficult for me to arrive at the solution with SQL.
Let's say I have a simplified ERD that looks as follows:
This ...
0
votes
1answer
14 views
Create a foreign key to a table that doesn't exist in postgres
I'm trying to put together a solution where I have a bunch of script files that contain my schema creation. Something similar to Visual Studio's SQL Project, but for Postgres.
My problem is that the ...
0
votes
3answers
25 views
How to prevent trigger from overwriting values that were explicitly set?
I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):
create temp table ...
0
votes
0answers
27 views
Sql, get data/rows for more selected ids in one condition, from file
I am using PostgreSQL 11.5 on Ubuntu
Question
How can I get data/rows for more selected ids in one where condition? Ideally from file with list of ids.
Example
|product_id |product_name ...
0
votes
1answer
27 views
How can I run a pgAgent job every 10 second?
I have a PostgreSQL 11.3 Database. I installed pgAgent for creating jobs. Now I want to run my job every 10 second, but in GUI there is possibility to write with precision of minutes, not with ...
0
votes
0answers
27 views
Postgres uses sequential scan with OR condition, but index scan with AND condition
We are doing a full text search on an object. Unfortunately, the object is normalized across different tables, and the full text search results in a condition with several OR, because the term can ...
0
votes
1answer
15 views
Postgresql - how to list indexes on a table that is partitioned?
This command works for non-partitioned tables:
select * from pg_indexes
But for partitioned tables, it does not list the original index, but the indexes that postgresql created for each partition.
...
1
vote
1answer
22 views
Unable to alter partition table to set logged
I have a partition table that I created UNLOGGED. I'm using pg_partman to manage the partition. I then proceeded to load data into it with a COPY command.
Afterwards, I ran an alter table set logged ...
0
votes
1answer
20 views
Multiple versions of PostGIS and PgRouting on CentOS 7
I'm trying to install multiple versions of PostGis and PgRouting in the same instance of PostgreSQL v9.6, using CentOS 7.5.
I've been fighting with dependencies and shared libraries, to make all ...
1
vote
0answers
87 views
Postgres table with 35m rows slow queries. how can i improve the performance?
SELECT count(DISTINCT c.id) as clickCount
FROM clicks c
LEFT JOIN links l on c.link_id = l.id
LEFT JOIN user_agents ua on c.user_agent_id = ua.id
AND ua.robot IS NULL
...
0
votes
0answers
23 views
Grant Privileges on Production Database Throws “Error: tuple concurrently updated”
I would like to grant read access to all tables in my schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO XXX;
While it works on our alpha environment, it fails with our production database:
[...
0
votes
1answer
60 views
How to pivot multiple columns in PostgreSQL 9.5?
I have the sample data as follows and want to find if I can use crosstab function in this case.
Sample Data:
ITEM | CD | TYPE |PARTS | PART | CNT |
Item 1 | A | AVG |2 | 1 | 10 |
Item ...
2
votes
2answers
27 views
Summing the count of two columns in postgres that contain the same value
I want to sum the total number of occurrences of one value across two columns in the same table.
So, an example would be:
id |node1 |node2
1 | 111 | 123
2 | 122 | 124
3 | 111 | 125
4 ...
0
votes
2answers
32 views
When to ANALYZE/VACUUM/REINDEX when adding primary keys and indexes
I have a recurring job that creates a Postgres (v11) database from a template and loads it with data. The scale of the data is on the order of 10s or 100s of millions of rows, so for performance ...
0
votes
1answer
18 views
Postresql dynamic index creation for many tables
How can I create an index in Postgres dynamically over many tables but always with different names.
Lets assume table x has a column y with all table names of hundreds tables which need to get an ...
0
votes
1answer
23 views
Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2) [closed]
I have a few tables and I am facing this issue most likely with the table "item".
When I remove restaurant_id and category_id, it works fine.
CREATE TABLE IF NOT EXISTS restaurant
(
id ...
0
votes
1answer
36 views
Do database actions inside a transaction become slower as the transaction grows? [closed]
I have a PostgreSQL database running a transaction. Inside that transaction I process around 160 records from an Excel file. As it runs, I see the processing becomes slower with each record (0.05 secs ...
0
votes
2answers
37 views
How to count decimals without zeros
How can I count all decimal place up to the first zero
For example I have a column named x with an entry of numeric datatype value 1.955000000
I want to count the decimal places without the zeros. ...
0
votes
0answers
23 views
CSV -> Python -> Pandas.DF -> PostgreSQL (UPSERT, THEN Cull data) [closed]
Problem Statement:
I have multiple machines all running the same software, they output CSV files. I need to load this information into a database. The challenge is the user can, with permission, ...
-1
votes
0answers
26 views
It seems `conditions` run before 2 tables is joined? [closed]
explain analyze select
"MdChapter"."id",
"MdComic"."id" as "MdComic.id"
from
"md_chapters" as "MdChapter"
left join "md_comics" as "MdComic" on
...
0
votes
1answer
29 views
Schema and Query design for table with multiple languages [closed]
I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
0
votes
1answer
22 views
How to insert values to one table from the table stored in another database in postgresql?
Here is the structure of table:
`* Table 1 of database A contains mobile number and userId and both field cannot be blank
I want to fetch data from table 1 of database A and insert that with some ...
1
vote
3answers
51 views
Take average of multiple nullable columns
I have an example table (year) as below in PostgreSQL 9.5:
Name | 2010 | 2011 | 2012
-------------------------
A | 10 | | 40
B | 10 | 20 | 30
Now, if I write a simple query as ...
1
vote
2answers
41 views
postgresql - add the missing rows to each group of year/date
i have this table --> https://pasteboard.co/IrVXlRe.png
and there is another table --> https://pasteboard.co/IrVXNPh.png
what i need is to show ALL categories regardless of the quantity. if there ...
