Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

1
vote
1answer
20 views

How to fetch those records who appears in many records in many to many relationship in PostgreSQL?

I have the following three tables: create table users(id int primary key, name varchar); create table groups(id int primary key, name varchar); create table user_groups(uid int references users(id),...
0
votes
1answer
20 views

Caching results of query for part of table that almost never change

In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below): Query1 ...
1
vote
1answer
23 views

Better way to select a matching subset of rows?

DB Fiddle link. I have a many to many table like this: CREATE TABLE house_to_cats ( id SERIAL PRIMARY KEY, house_id INTEGER, cat_id INTEGER ); -- house with cats 1 and 2: too small INSERT INTO ...
4
votes
0answers
53 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
-1
votes
1answer
30 views

Load a database into a host or in place to which I can access from different applications [closed]

I've just started working with databases. I create them and modify them using Python as a means to create dinamically a good amount of data. Moreover all the applications that I develop are written ...
0
votes
2answers
31 views

PostgreSQL: WHERE ID IN file_with_ids

I have a file with ids which looks like this: 10555 16452 17005 17511 17512 17513 17515 17516 17517 17518 17520 17521 17522 17523 17524 17525 This file was created with unix tools like grep and cut. ...
0
votes
1answer
27 views

Trouble updating column with matching data from another table (3 diff. attempts: 1 w/out errors but no results, 2 with errors I don't understand) [closed]

Table 1 is 'z_scrapestorm_rentals' and table 2 is 'pcpao_cols'. They both have address columns named 'address_1_line_trunc' and 'address_one_line' respectively. Table z_scrapestorm_rentals: column ...
0
votes
0answers
28 views

Postgresql 9.6 to 10 upgrade issues [on hold]

Trying to upgrade 9.6->10. I have run the following pg_createcluster 10 main /usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin/ -B /usr/lib/postgresql/10/bin/ -d /var/lib/...
0
votes
0answers
21 views

postgresql: live tuples not matching table row count

We're experiencing poor performance with a table. The table is regularly updated with insert/updates, and is a critical table in an application. In looking into the problem I've noticed that the row ...
0
votes
0answers
21 views

PostgreSQL decrease performance after migration

After postgresql database server v9.3 migration to v9.6 I noticed a decreace in the performance of the entire system. The config parameters are the same as in v9.3 taking in to account the next ...
1
vote
1answer
54 views

What is the status of the PostQUEL source code?

Recently as part of my answer to "Is the keyword “ALIAS” actually used?" I wanted to go back further than Postgres95. Postgres95 is the first version of Postgres that was migrated to use SQL rather ...
8
votes
3answers
2k views

Is the keyword “ALIAS” actually used?

According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved ...
1
vote
3answers
77 views

PostgreSQL: Correct way for subset calculations

I have a huge table (around 10 million items). For simplicity, let's say it has only 2 columns: user_id and activity_id like this user_id | activity_id --------------------- 1 | 1 1 | 2 1 ...
0
votes
0answers
14 views

Failed to populate indices of table table_name

I need to design my PostgreSQL database and I use SQL Power Architect to do it. But after I import it to SQL Power Architect and drag the table to workspace, I got error message : Failed to ...
1
vote
1answer
210 views
+250

What is a “PARTITIONED OUTER JOIN”?

This just came up in a question on Reddit, and I'm wondering What is a PARTITIONED OUTER JOIN in Oracle? (definition) What does a simple example look like? (use) How would you write it in terms of ...
0
votes
1answer
64 views

Loop-like sql join

Would like to build a materialised view in postgres but have only one issue. I have a query which works if I specify a user directly. So this query would return me the results for user == 'reee' ...
0
votes
1answer
16 views

archive_command failed causing master to run out of disk space. Is there a command that prioritizes master's health

I had replication set up with a simple archive_command in postgresql.conf: archive_command = 'rsync -a %p _postgresql@serverip:/var/postgresql/wal_archive/%f' Which worked great for 2+ years until ...
0
votes
1answer
33 views

Natural join output doesn't appear in table [closed]

I used this query: select * into airbnb_parcels from pcpao_res natural join AIRBNB_PARCELS; and got: Error: relation "airbnb_parcels" already exits. In DBeaver I used the following query: ...
0
votes
1answer
31 views

How to SELECT last row of every hour?

I have a table (in database) with one minute data in each row. Like this: Date_time Value 2018-11-01 10:58:00 189.0 2018-11-01 10:59:00 189.0 2018-11-01 11:00:00 189.19 2018-11-01 11:...
0
votes
1answer
12 views

Syntax error when trying to add columns to table in DBeaver (postgresql)

I've tried this a few different ways but every time I get a syntax error when trying to add columns to a table. I'm new to databases so it's probably something basic that I'm doing wrong, but I just ...
0
votes
0answers
22 views

Is there a way to always call stored procedure on transaction commit?

I want a particular store procedure / function to always be called before a transaction commit happens. Is there a way to enforce this somehow?
0
votes
0answers
15 views

PostgreSql limit user rights [duplicate]

I created an user pgviewer in Postgres and didn't give it any rights except "can login" and the default "inherit from parent". Yet through Visual Studio Npgsql.EntityFrameworkCore.PostgreSQL it could ...
0
votes
0answers
17 views

Can postgres rules on insert to parent get used when inserts are made to child tables?

I want inserts made on a child table to cause a rule to be used that is defined on its parent table. Is this possible in PostgreSQL? Example follows: create table master_ids(id bigint primary key); ...
2
votes
0answers
43 views

Does a view use a partition in PostgreSql?

I'm using a view based on two partitioned tables (they are partitioned based on same field/value) . When trying to access to a specific partition and using the explain analyse I can see postgres ...
0
votes
2answers
52 views

How to get sum of last previous X days of everyday?

Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday. For example my graph will include Sum of Day - 60 to 30 Sum of Day - 59 to 29 Sum of ...
0
votes
2answers
24 views

Complex JSON query

I have a table like that: id | lat | lon | date | speed 1 | 45 | 66 | 2018-02-01 | 139 1 | 44 | 63 | 2018-04-01 | 129 2 | 41 | 56 | 2018-01-01 | 109 2 | 44 | 45 | 2018-04-01 | 99 I ...
0
votes
3answers
47 views

How to find out those users which have two different access right in postgresql

Thanks for your support and help, Dear the scenario is like this i have 3 tables 1: users 2: users_groups 3: users_groups_rel users data id name 1 zubair 2 ali 3 bob 4 ...
2
votes
1answer
31 views

JSON Query in PostgreSQL

I have a table: CREATE TABLE foo (id,lat,lon) AS VALUES ( 1, 34, 45 ), ( 1, 45, 56 ), ( 2, 56, 67 ), ( 2, 58, 64 ); How can I get JSON like that: {"1":[[34,45],[45,56]], "2":[[56,67],[58,64]...
1
vote
1answer
34 views

Despite of using GRANT … ON ALL TABLES (or ALL SEQUENCES) TO user, user cannot access newly created tables (or sequences)

For a production server, I wanted to separate the super user, admin and app roles to limit scope of mistakes. So I've created an account that our app uses to access the DB, and an admin account. The ...
1
vote
1answer
14 views

Extract all of the keys, including nested, from postgres JSONB column

I have a table, my_table, in postgres with a column, d, that is of a jsonb type. An example of the data in that column is: { "logo": "a", "name": "b", "status": "c", "address": { "city": "d", ...
1
vote
2answers
46 views

How to remove partial duplicates from a table

I have a database that contains some erroneous results that are essentially but not technically duplicates. The structure is like this: id_page (PK), id_site, label, create_date There should only be ...
2
votes
2answers
100 views

Containment check tstzrange @> timestamptz not using btree or gist index

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
1
vote
3answers
112 views

Is it safe to insert the newest row with SELECT INTO?

Trying to keep rows consistent then referencing previous rows. CREATE TABLE transactions ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, account_id bigint ...
0
votes
1answer
21 views

Did PostgreSQL write amplification reduction WARM efforts get released in 10 / 11?

The MariaDB vs PostgreSQL debate continues to rage in my firm :-( and its fuelled by this somewhat famous uber blog post. I found some interesting discussions about write amplification mitigations ...
0
votes
0answers
18 views

Query error when trying to SELECT data in PostgreSQL 10 [migrated]

I'm just getting started with PostgreSQL and have a bit of a problem. I am using Psycopg2 and hosting the PostgreSQL database on AWS RDS. Here is the query I am trying to execute: cursor.execute('...
-1
votes
1answer
22 views

Performance: storing list of thousands of items in postgresql's JSON field vs having separate table for items?

Let's say I have some table A which have json-field items: A: foo: 'foo' items: [ {a: 1, b:2}, {a: 3, b:4}, ... ] There could be couple of thousands of items in one A object. Is there any ...
0
votes
0answers
13 views

MSSQL to Postgres Replication and Syncing tools

Use case: We use a postgres instance for our application but we need to transfer 100tb of data from a 3rd party db to postgres and maintain any updates to the third-party db. We need a tool that works ...
1
vote
1answer
32 views

Return more columns than were inserted inside a PostgreSQL CTE

I would like to use a modifying CTE to insert some values. The insert statement inserts data from a select statement. I use the returning keyword to return the values inserted (including auto-...
0
votes
0answers
30 views

PostgreSQL occasional slow insertion speed because of FK trigger

I have two tables in master-child relationship (several children per master). In one use-case a master with all of its children needs to be copied as new master. For this I'm using two select+insert ...
0
votes
0answers
30 views

Cannot revoke permissions or drop user in PgSQL AWS RDS

I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
0
votes
1answer
24 views

How to use a record variable in the from clausule

I write a SQL function that receives and return a record. I need to use the received record in a from clause. I am using unnest(array[the_variable]). Is there a specific function or structure to do ...
0
votes
1answer
44 views

Database Design - multiple tables

I am trying to build a database that has all of our employees, their skills, and the level of their skills. The idea is that a manager could go in, type in a skill, and relevant employees are returned....
0
votes
1answer
23 views

Removing duplicate-ish paths generated from recursive CTE

I have some multi-parented data, that I run a recursive CTE against to find common ancestors: item_id |item_name |path_name |id_path_array | --------|----------|-------------|--------------| 1 ...
6
votes
1answer
142 views

What does the vertical pipe slash operator do?

I'm wondering what pipe slash |/ does in, SET equal_area_radius = |/area/pi(); The statement works and it definitely changes the value: SELECT |/125.555/pi(); -- returns: 6.32181918120139 SELECT ...
0
votes
1answer
27 views

How can I disable parallel queries in PostgreSQL?

I want to benchmark some things in PostgreSQL. When I do this I normally turn off some of the features the database is currently using, just for my session, to see what the second-best plan is. ...
0
votes
1answer
41 views

Postgresql 10 There is no unique or exclusion constraint matching the ON CONFLICT specification

I currently have a table which looks like this: CREATE TABLE "PDPC".collection ( col_no bigint NOT NULL DEFAULT nextval('"PDPC".collection_col_no_seq'::regclass), q1 character varying(10000) ...
1
vote
1answer
27 views

Regarding GIN Index in Postgresql

I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time. select email from customer where lower(email) like '%gmail'; CREATE INDEX idx1 ON customer USING gin (...
0
votes
1answer
15 views

locking question around INSERT during UPDATE query

Say I have a large table products id, color, cost and I want to run a query to update the cost of all 'red' items UPDATE products SET cost = cost + 1 WHERE color = 'red' Let's pretend that there ...
1
vote
0answers
41 views

Creating index on a big text column in postgres

We are talking about postgresql 9.5 on windows with a database which has UTF8 encoding and "English_United States.1252" (default) LC_COLLATE and LC_CTYPE. The column is of type text and it is used ...
0
votes
0answers
10 views

postgresql backup DB restore error

PostgreSQL 10 with PostGIS 2.4 linked to a Django (1.11.18). Currently moving my DB to a remote server and going through the backup and restore process. Backup Process PGAdminIII - backing up with ...