Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [postgresql]

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

0
votes
0answers
17 views

Wildfly + Postgres: Relation does not exist where Table does exist in the Database

I am trying to use Wildfly v10 to upload entries in PostgreSQL DB. It is in standalone mode. To do it I configured my standalone-full-ha.xml which was configured for Oracle previously. I changed the ...
0
votes
1answer
28 views

Restoring PostgreSQL tables faster

There's a PostgreSQL PITR system running on a fairly large system, with hundreds of databases with hundreds of tables in each, and we're running into time constraint-based problems when trying to ...
0
votes
0answers
9 views

CentOS7 Virtual Machine postgres10 not initializing

I had postgres9.6 installed and working the other day but the version wasn't compatible. I used YUM REMOVE to remove that one and then followed the instructions here https://www.postgresql.org/...
0
votes
1answer
34 views

make exist table as inherit

i try to create parent table for exists table, by next algorithm: -- CREATE TABLE CREATE TABLE t1 (id serial PRIMARY KEY); CREATE TABLE foreign_t1 (id serial, t1_id integer REFERENCES t1 (id)); -- ...
2
votes
1answer
49 views

What is the name of the part between SELECT and FROM in a SQL query?

I am trying to find the right description for the parts of a query: What is the name of the part between SELECT and FROM? If I only had some columns then I would call it "column identifiers". But if ...
0
votes
1answer
29 views

Preparing query in postgreSQL with variable table name?

I'm trying to do an INSERT INTO a table whose name is in the variable myTable. In MySQL, I'd do it like this: SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...'); ...
1
vote
2answers
57 views

Can a trigger create a TABLE when inserting data?

I have a trigger that keeps track of history of table dev by inserting a row in table dev_hist: CREATE OR REPLACE FUNCTION process_dev_hist() RETURNS TRIGGER AS $dev_hist$ DECLARE action_name ...
0
votes
1answer
19 views

Index-Only versus Covering Index

What’s the difference between an index-only query and a covering index one (in PostgreSQL 11)? Both seem to fit https://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index, based on ...
0
votes
1answer
59 views

Huge DB (> 600M records/table) times out on query or hits memory limits?

Dearest DBA friends of Stackexchange, I am trying to write data from multiple sourcetables into a new table containing compound information that i wish to use as source for a later project. The ...
0
votes
0answers
18 views

Grafana Dashboard for postgres_exporter queries

Who uses postgres_exporter, tell me if you use https://github.com/wrouesnel/postgres_exporter/blob/master/queries.yaml ? if so, what Grafana Dashboard do you use? Thank you
1
vote
1answer
41 views

Postgres: ERROR: operator does not exist: point <@> point

Using PostgreSQL 11 on Windows. Both cube and earthdistance installed and verified by pg_available_extensions. Restarted PostgreSQL. [42883] ERROR: operator does not exist: point <@> point. ...
0
votes
0answers
21 views

How to create multiple tables based on existing tables in postgreSQL?

I have several tables, and I need to create a matching _hist table for each one. For example if I have: CREATE TABLE public.test ( first text, middle text, "last" text, ...
0
votes
1answer
27 views

Postgres select join json querry

I have a problem to make a postgres query with join from data of json. I have 2 tables the first one is media: id as integer path as string and an other table playlist id as integer media as ...
1
vote
2answers
44 views

Is there Such Thing As The Opposite of Unique Constraint

I want to be constrain my database with what is essentially the opposite of a UNIQUE constraint. I want to only be able to add a new row if some combination of fields has been added before. Note: I ...
0
votes
1answer
16 views

How can I write a window function using something like row_number to increment only on discrete values?

Say I want to do something like: select my_date, row_number() over(partition by my_date order by my_date asc) from my_table I want the row numbers to come out as: 2018-10-01,1 2018-...
1
vote
1answer
29 views

Time Series Table ( Many Columns ) Design Recommendations [duplicate]

I am storing Time Series Data in a Postgresql database. I am wondering what the performance ramifications are of the following two proposed design patterns. Option 1: A single table with a ...
1
vote
1answer
23 views

Postgres 10.0 upsert syntax error not sure what to google for

Postgres 10.0 table: pcr=> \d tableFindOld Table "public.tablefindold" Column | Type | Collation | Nullable | Default ----------+--...
0
votes
1answer
50 views

What problems are there in not using a primary key column at all?

What purpose would a primary key column serve on a table such as post_versions here? Nothing refers to it, and there are no queries where I will ever want to select a row by post_versions.id. It'll be ...
0
votes
0answers
19 views

How to fix a bad copy of Postgres dbs to a different machine?

I had to copy over one set of DBs (around 2TB of data) from one machine to another one with 4TB of file system space. All databases are for internal usage only, no replica, no external writes. The ...
1
vote
1answer
48 views

Include constraint for daterange type

Here is the table CREATE TABLE date_ranges ( id SERIAL PRIMARY KEY, user_id INTEGER, start_at DATE, end_at DATE, is_exception BOOLEAN ); ALTER TABLE date_ranges ADD CONSTRAINT ...
1
vote
2answers
33 views

How to get N rows after the first row matching a condition?

I am implementing a cursor based pagination. The requirement is to sort a dataset by an arbitrary condition (e.g. description) and return N rows after X row (where X represents the last item on the ...
-2
votes
1answer
18 views

how to truncate table and set id with 1 default?

After truncate table And Insert any data but in table, table id in not started with 1 TRUNCATE TABLE SOURCE;
1
vote
2answers
22 views

postgresql random function returns duplicate results when order by random used in query

I'm using postgresql 11.1-1 on Windows 10. On my system, if I run a query like the following, I will get a different number for each random column: postgres=# SELECT random() as sortOrder, random() ...
0
votes
1answer
35 views

In BEFORE UPDATE TRIGGER, how do I prevent changing nothing but timestamp?

I'm working on a trigger, and part of its functionality is to prevent UPDATE if the only field being updated is the timestamp field. My current solution is like this: CREATE OR REPLACE FUNCTION ...
0
votes
0answers
15 views

Connection lockup on syntax error Postgresql 9.5 on Ubuntu 16.04 server

I have postgresql 9.5 running on my server with mostly default configuration. Whenever I'm connected to the server through localhost or 127.0.0.1 postgres fails to return the error, be it a simple ...
1
vote
1answer
31 views

Surprising results from assigning multiple variables in FOR loop

CREATE OR REPLACE FUNCTION my_function(input jsonb) RETURNS jsonb LANGUAGE plpgsql AS -- language declaration required $func$ DECLARE _key text; _value text; BEGIN FOR _key, _value IN ...
0
votes
0answers
10 views

Typical use cases for running PostgreSQL in Docker container

PostgreSQL version : 11.2 OS : RHEL 7.x I keep hearing about DBAs running PostgreSQL database on Docker especially in the development environment. What are the typical use cases of running ...
0
votes
0answers
15 views

PostgreSQL 9.6 on linux server gives a different collation than windows installation

I am running into an issue with a script that makes a backup towards the SQL. What happens is that with our script we make a dump of SQL we can read out to restore the PostgreSQL database. Our client ...
2
votes
1answer
35 views

Loop through key/value pairs of a jsonb object in postgresql function

I am trying to create a function in Postgres that can loop through each key/value pair in a given jsonb object. create or replace function myFunction (input jsonb) returns jsonb as $$ BEGIN // ...
0
votes
0answers
35 views

PostgreSQL select jsonb key value from an array while joining table

I want to extract a string value from an object's key inside a jsonb array, coming from a table join_table which is left joined, meaning it could be empty for some values. -- NOT WORKING SELECT ...
1
vote
1answer
64 views

SQL function which returns true/false depending on success of insert query

I want to write a SQL function which returns true if a certain INSERT inserted a row and false if no row was inserted. In this use case, we know that at most one row can be inserted. I came up with ...
0
votes
1answer
24 views

Add previous column alias to calculate sum/total of last column

I have a query which I need to optimize. It is using 3 subqueries 2 times each. Below is the query. select i.itemid, b.itemname, i.batchno, (select sum(qty) from inventtrans ...
1
vote
1answer
49 views

How to parallelise remote queries in Postgres

I am querying N tables remotely using dblink. This queries represent a sort of "sharding" of the data. Each remote query takes roughly the same time (~ 0.5 sec). However when I gather all the data in ...
1
vote
1answer
63 views

Postgres RDS: is there a way to use LDAP authentication?

We have a client interested in using LDAP (specifically Active Directory) for user authentication on a PostgreSQL AWS RDS (Aurora, specifically) database. Anybody doing this successfully?
1
vote
1answer
21 views

Formatting query to respond to past column values

I'm writing a query that follows the process of forms being processed through various SQL functions - they receive status numbers as they go through the process. I want to pull forms that are in a ...
0
votes
1answer
22 views

unnest all columns from a given table

I have a table, which was loaded dynamically from an external job. This table has a changing number of coulmns from time to time. I want to unnest all the columns with the coulmn name as 'Key' and the ...
0
votes
0answers
32 views

Steps to unlock a locked select in postgres

I have tried to update a table in Postgresql 9.6 (AWS) based in two inner joins. Since it was taking too long, I cancelled it. Now I'm trying to run a query to count records from the inner join but ...
2
votes
0answers
40 views

PostgreSQL: Having many instances of one application should I go with database per instance or schema per instance? [closed]

I am not asking for an opinion here. I need an answer supported by arguments. We have an web-application that will be installed multiple times on one server (one instance per customer). How should I ...
1
vote
0answers
21 views

Why are postgres binary files stored in multiple locations in RHEL? [closed]

OS vendor, version: RHEL 7.5 Postgres version : 11.2 In RHEL distributions, postgres binaries seem to be located in at least 2 locations. Postgres binaries in /bin directories seem to be softlinks ...
0
votes
0answers
10 views

Importing Access Database file to postgres by pgAdmin3

I have many tables in a MS access database file. I want those tables to directly import in postgres (pgAdmin3). Thanks in advance.
0
votes
1answer
14 views

Duplicate results from query trying to find cross-indexed multiple tables

I working with a database of several hundred schemas with 250 tables and thousands of records. PGAdmin III 1.22 as my only tool. A GUI interface to the database is not robustly written and ...
1
vote
1answer
26 views

Convert an int column to bigint with the 4 bytes of padding available

If there's 4 bytes of padding that would fit the bigint perfectly, is it possible to efficiently convert it on-the-spot by taking advantage of that gap? CREATE TABLE a ( b SERIAL PRIMARY KEY, ...
0
votes
1answer
86 views

Why is my Mac install of Postgres not tokenising Thai words?

On my Mac install of PG: =# select to_tsvector('english', 'abcd สวัสดี'); to_tsvector ------------- 'abcd':1 (1 row) =# select * from ts_debug('hello สวัสดี'); alias | description | token ...
0
votes
1answer
17 views

Postgres text search has unexpected query conversion

I have a dictionary built in this way: DROP TEXT SEARCH DICTIONARY IF EXISTS simple_english_with_stopwords CASCADE; CREATE TEXT SEARCH DICTIONARY simple_english_with_stopwords (TEMPLATE = pg_catalog....
1
vote
0answers
39 views

Validation within a transaction with all violations in response

I'm trying to solve a problem with some sort of offline integration where data occasionally (once in several days) comes in a form of csv files. It can be new rows or updates for existing ones. Total ...
0
votes
1answer
36 views

Barman not able to connect: fe_sendauth: no password supplied

I'm trying to setup streaming replication to a Barman server. Barman config is pretty much basic, I'm using backup_method = postgres. [barman] barman_home = /var/lib/barman barman_user = barman ...
0
votes
1answer
22 views

Get value from jsonb_array

How can I get values of array in my jsonb type column named 'body' in order to use the value to join another table. example:- SELECT U.* from masters."USERS" as U left join masters."ROLE" R on U....
2
votes
0answers
48 views

Huge speed difference between left join and inner join - Postgres

It is known that left join is generally slower than inner join, but this difference seems out of proportion. I have table A with 2,542,526 rows and table B with 30,444 rows. I ran this query, which ...
0
votes
0answers
22 views

max_stack_depth not changed even stack changed in limits.conf

The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log: DETAIL: "max_stack_depth" must not exceed ...
0
votes
1answer
14 views

What is the maximum length of a comment in PostgreSQL?

PostgreSQL has a special piece of a functionality called COMMENT which attaches metadata to an identifier or any type (schema, table, column, function, type, domain, etc). It looks like this, COMMENT ...