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
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 ...
-1
votes
0answers
16 views

add new reference column

I have a Hotel table and a City table. If I'd like to add a new column city in Hotel table which is a foreign reference of City, is there a way to automatically update the city reference of existing ...
0
votes
0answers
19 views

How to increase the max_locks_per_transaction value for a PostgreSQL instance on Azure?

I am receiving this error, but the max_locks_per_transaction setting is not an option in Azure. Reason: SQL Error [53200]: ERROR: out of shared memory Hint: You might need to increase ...
1
vote
1answer
35 views

Copying a DB or tables from a local Postgres database to Heroku [closed]

I'm almost a novice with databases. I created a PostgreSQL database with the python programming language. I am pleased to note that the database was created as I wished. I could easily verify it from ...
0
votes
0answers
56 views

How to partition a table without time data?

I am using postgresql to store information about event ticket sales. I have two tables: event_sale and event_sale_seat: event_sale (id integer, created_at timestamp with time zone) event_sale_seat (...
0
votes
0answers
15 views

Postgres - Storing Attributes in 1:0 related object, performance improvements?

In my DB's central table, "Profiles," I want to collect activity statistics that will be updated often, although not all profiles will have statistics. The "Profiles" table is already heavily updated ...
0
votes
0answers
38 views

PostgreSQL 9.6 won't start: “FATAL: could not write lock file ”postmaster.pid“: Input/output error”

I am using PostgreSQL 9.6 running on Debian 9.2. I cannot get my PostgreSQL up and running after the disk it was writing to became read only (it's on a separate disk from the OS). Here is the log ...
0
votes
1answer
38 views

Table with only one column because of many-to-many relationship?

Right now I have three tables: person, alias, and person_alias. As suggested by the names of tables, one person can have multiple aliases and one alias can be used by many people, and hence I add a ...
2
votes
2answers
24 views

PostgreSQL prevents HDD from spin-down

I am using my router with OpenWRT as a small server. To experiment a little with more advanced database administration in my programming projects I set up psql on it. The.server uses my HDD, which is ...
0
votes
1answer
30 views

How can I get a unique array in PostgreSQL?

The following SELECT ARRAY[a,b,c,d] FROM ( VALUES ('foo', 'bar', 'foo', 'baz' ) ) AS t(a,b,c,d); Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one ...
0
votes
0answers
20 views

AWS Aurora Database Corruption

I'm running Aurora Postgres and noticed that metric Maximum Used Transaction IDs is increasing a lot. Trying to manually vacuum the tables that's was not automatically vacuumed lead me to the ...
3
votes
2answers
138 views

How does the recovery strategy of PostgreSQL exactly work?

I'm testing and trying to figure out how the backup and recovery strategy in PostgreSQL 11 works but it does not work as expected. When I recover the backup, I don't get the correct state. I get any ...
0
votes
0answers
14 views

PostgreSQL - Optimize query with multiple subqueries

I have 2 tables, users and sessions. The tables look like this: users - id (int), name (varchar) sessions - id (int), user_id (int), ip (inet), cookie_identifier (varchar) All columns have an index....
-3
votes
1answer
25 views

Database design for multiple tenant application

I am building a web application where we have the concept of groups. A group can be roughly thought of like a Facebook group where you have a number of people attached to it. The DB for the app is ...
2
votes
1answer
57 views

PostgreSQL performance with (col = value or col is NULL)

This question deals with PostgreSQL 9.5 query performance. The table is: CREATE TABLE big_table ( id integer NOT NULL, flag bigint NOT NULL, time timestamp with timezone NOT NULL, val ...
1
vote
1answer
66 views

Adding LEFT JOIN to a query has increased execution time 10 times

On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages: postgresql10-...
0
votes
1answer
26 views

Cannot restore postgres dump file

When i try to restore a database from a dump file using the following command: \i C:/pathname/dumpfile.txt from within my sql shell, it runs the first few lines of the dump file and then I get the ...
2
votes
1answer
41 views

Can I use hypertables in timescaledb merely to get better insert rates?

I have a PostgreSQL db that I have a large bulk load running into. I wish this load to be as fast as possible. I'm already using the copy command etc. I have been reading about timescaledb and how ...
0
votes
0answers
30 views

Postgresql view that calculate if payment is paid based on previous rows

I have go DB structure like here: db-fiddle I'm looking for some solution that in View for charge_line_tab table will add information if charge line is paid or not, based on previous records in ...
0
votes
0answers
24 views

Merging database entries and keeping history

I have a postgres database backend to a webapp, with a table of users, and those users have an id which is used in other database tables (things like orders, tasks etc). I have a situation where a ...
0
votes
2answers
22 views

postgres simple number to string mapping

I have a an Enum at the programming language level which is stored as a simple integer on the table. Think: APPLE = 1 GOOGLE = 2 MSFT = 3 AMAZON = 4 ... (100s more) I just wanna query the table and ...
-1
votes
0answers
21 views

Postgresql: invalid regular expression: quantifier operand invalid

I have a table of filters create table filter ( "id" serial PRIMARY KEY, "user_id" serial REFERENCES public.user (id) ON DELETE CASCADE, "keywords" text [] ); I want to make regex search for a ...
0
votes
2answers
59 views

Check if value exists in postgres text column

I have a direct_selector column direct_selector ---------------------------------------------- a:2:{i:0;s:7:"transit";i:1;s:9:"departure";} a:1:{i:0;s:9:"departure";} a:1:{...
1
vote
1answer
22 views

How to represent the array of String in Entity Realtionship Diagram?

I am creating a database using PostgreSQL. I am using the Entity Relationship (ER) diagram. I have a table named LiCLSituation as shown in the Figure below, where I am using Circumstance as array of ...
0
votes
1answer
29 views

Installing extension as non superuser on Amazon AWS RDS

I would like to allow non super users to install a specific postgres extension. Accordingly to the documentation at https://www.postgresql.org/docs/current/extend-extensions.html that is possible if I ...
1
vote
0answers
17 views

Azure Database for PostgreSQL and Event Triggers

I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as ...
1
vote
0answers
30 views

Statistics injection for calculating the cost of a query

I want to find out the cost of a query by "EXPLAIN" query in Postgres. As I know, Postgres calculate this cost by using the statistics of the table. (e.g: relpages, reltuples in pg_class or some ...
-1
votes
0answers
13 views

Group by getting correspondent next column

I'm facing an issue which I cannot solve and would require some assistance. Having a table as follows: date ts isactive val description "31-Dec" "2018-12-31"...
1
vote
0answers
32 views

Postgresql 10: Can not establish DB connection

I have created a new database and user in Postgresql 10 named 'reportingdashboardproject' and 'repadmin' respectively. I used the following commands to create and grant privileges: postgres=# create ...
1
vote
1answer
44 views

Delete from table causes the PostgreSQL database to enter recovery mode

It's becoming common to see this problem in the development environment. My application is not in production yet, but I'm worried this problem could happen. As I develop new features, I commonly ...
1
vote
0answers
24 views

Logical replication sync time

I'm trying to create a logical replication between 2 Postgres 10 databases in separate clusters. All the process seem to be working, no error and \dRs+ shows the subscription correctly. The master ...
0
votes
1answer
20 views

PostgreSQL results of conditional insert

I've got a simple query which inserts data to table when value (unique) does not exist: DO $$ BEGIN IF NOT EXISTS ( SELECT 1 FROM mytable WHERE hash = 'somelonghash' ) THEN ...
0
votes
1answer
39 views
+100

Postgres 9.6 - Taking differenctial backup

I have a few CentOS boxes running Postgres 9.6. I am planning a central server which can make local copies of all DBs on the other servers. These servers are accessible via SSH on Internet. What is ...
0
votes
0answers
25 views

Filter rows by objects in jsonb array in PostgreSQL

I have a json document for each row in my table (PostgreSQL 10) with the following structure { "partyboat": { "participants": [{ "name": "Bill Lambeer", "role": "Lessee" }, { ...
1
vote
1answer
35 views

How to change owner of a table within a PostgreSQL Database on Azure?

I attempting to restore a database into a PostgreSQL instance on Azure. I am issuing these statements as postgres and attempting to change owner to another role that was a superuser. My restore ...
2
votes
1answer
29 views

How to get query output in addition to EXPLAIN ANALYZE output?

Is it possible to get EXPLAIN ANALYZE output (e.g. as a notice) in addition to the regular query output? I am designing a postgresql client and I would like to add a feature that automatically turns ...
0
votes
3answers
45 views

How to check if the current time is between two times?

postgresql documentation gives an example timerange definition: CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS 'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE; ...
0
votes
1answer
21 views

PostgreSQL pg_basebackup from several port number

I have one slave server which used as a replication server (let's called it slave1) from several master servers. The `slave1' server is set up to receive replication from several postgresql server, ...
0
votes
1answer
19 views

Looking for a query that eliminates near-repeats

I have a database with four rows: county_a, county_b, flow_a_to_b, and flow_b_to_a. Essentially, the data is repeating in other rows, but the values are switched around. Here's an example: Entry 1: ...
1
vote
0answers
23 views

Guarding against CTE infinite loops in JOIN condition considered bad practice?

In docs, and pretty much everywhere else on the net, to guard against loops in recursive CTEs people use cycle column. The problem with this solution (as opposed to doing the ANY(path) check in join ...
0
votes
2answers
38 views

What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice? CREATE TEMPORARY TABLE folders (...
4
votes
3answers
264 views

Unicode storage of \u202b RLE and \u202c PDE in a Unicode-aware database?

I'm building a new product for toponyms and in it the Arabic shows kinda like this: ^IArabic^I<202b>ﺰﻤﺑﺎﺑﻮﻳ<202c>^I<202b>ﺞﻫﻭﺮﻳﺓ ﺰﻤﺑﺎﺑﻮﻳ<202c>$ Actually not quite. This is a ...
0
votes
0answers
31 views

error SQL STATE 42P01

I have to take data from 4 different tables and insert them in a fifth table. insert into leggeapprovata (titolo, relatore, numerolegislatura, testo, votisi, ...
3
votes
1answer
42 views

MySQL and PostgreSQL has JSON data types, is it the same as NoSQL database? [closed]

MySQL and PostgreSQL introduced JSON data types, is it the same as NoSQL database? Why? NoSQL here is a class of data bases similar to MongoDb and DynamoDB.
2
votes
1answer
26 views

How to send only updated values of a row?

CREATE OR REPLACE FUNCTION notify_col() RETURNS trigger AS $BODY$ BEGIN PERFORM pg_notify('channel', row_to_json(NEW)::text); RETURN NEW; END; $BODY$ LANGUAGE plpgsql ...
0
votes
0answers
38 views

PostgreSQL - pg_dump fails (out of memory) - queries to a table fail for memory

Version: PostgreSQL 9.2.1 - 32-Bit Windows We have a database which is fairly small (~1.4GB) that is unable to be backed up. It seems to fail on the same table (attachment_data - has about 3500 rows)...
0
votes
0answers
25 views

sql state 42P01 (postgresql) [duplicate]

Query: insert into leggeapprovata (titolo, relatore, numerolegislatura, testo, votisi, votino, astenuti, datapromulgazione, ...