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
1answer
28 views

Return hours, minutes and seconds left if column wasn't updated

I currently have this query: UPDATE MyTable SET next_update = now() at time zone 'utc' WHERE next_update::timestamp < now() at time zone 'utc' - interval '1 day'; Which updates the column ...
0
votes
1answer
41 views

Postgres is returning the “column reference '<column_name>' is ambiguous” message inside a function even with aliases

I have a function like this: CREATE OR REPLACE FUNCTION test() RETURNS TABLE(my_date date, gates smallint, area_type character(1), area_sizes float[], area_canvases float[]) AS $$ BEGIN ...
1
vote
0answers
56 views

Postgres stuck on IO/LWLock

I have an instance running Postgres v PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit. We have several indexes, GIN ...
0
votes
1answer
47 views

Postgres support for UUID

My understanding is that Postgres supports UUID data type out-of-the-box. I'm running Postgres on Amazon RDS with Engine version 10.6 and am scratching my head as to why the following commands are not ...
0
votes
2answers
35 views

Materialized views Performance

Postgres’ docs note: While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; ...
0
votes
2answers
38 views

Creating tablespaces in Postgresql ; Best practices

PostgreSQL version : 11.2 OS : RHEL or Oracle Linux 7.6 (Yet to be decided) I am at the design stage of setting up a production database. In production, this DB size will be around 300GB to 400GB in ...
0
votes
0answers
25 views

PostgreSQL: Replace schema in db1 with schema from db2

Is there a way to replace data in a schema in db1 with data in a schema in db2, given that the schemas are identical (only the data differs)? An alternative would be to replace the whole schema? I ...
0
votes
1answer
21 views

Postgres still connecting via SSL - despite invalid certificates

I'm playing around with SSL connections to a test postgres server and I'm finding I can still initiate an SSL connection despite invalid certificates. (The valid date has expired) I have enforced SSL ...
0
votes
1answer
16 views

how to filter through HTML in PostgreSQL query

I have a table with a name snapmail_logs. which has a column body in which I have been saving Email Templates as an HTML. There are a few things included in this body, to determine which email was ...
0
votes
1answer
11 views

Postgres migration local to remote, issue with terminal command

I'm trying to use the first answer here to transfer a local postgre db to elephantSQL https://stackoverflow.com/questions/1237725/copying-postgresql-database-to-another-server The command given is: ...
1
vote
1answer
43 views

Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?

I went overboard on CITEXT columns on a particularly large table in my application. I would like to back some of these out as it's confusing how to trigger lookups on the desired indexes. My question ...
1
vote
2answers
39 views

Disable / bypass authentication in PostgreSQL

I need to run benchmarks (pgbench) in different environments on different versions of PostgreSQL. Authentication works inconsistently and is a generally huge headache / adds a lot of fragile ...
0
votes
0answers
9 views

Have anybody configured PostgreSQL BDR replication? how useful it is?

We have a requirement to configure multimaster replication in postgreSQL. we have 2nd Quadrant BDR replication but I wanted to make sure is it error prone or any disadvantages are there configuring ...
2
votes
0answers
36 views

Postgresql slow remote execution when using alternative clients

I noticed a strange perfomance issue when running sql queries on a remote Postgresql Server. Everything good when using psql: Connect via ssh and run things locally $ ssh user@example.org user@...
1
vote
1answer
42 views

How to insert very big XML data file to PostgreSQL

I'm using PostgreSQL 9.5 and trying to import XML data file to database. To load the XML I use this function from here CREATE OR REPLACE FUNCTION bytea_import(p_path text, p_result out bytea) as $$ ...
1
vote
1answer
32 views

Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade

I (successfully) followed the upgrade procedure in §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old ...
0
votes
5answers
43 views

Create Role or Create user

PostgreSQL version : 11.2 OS platform : RHEL 7.6 I am new to PostgreSQL. I have a CRM application whose data I want to store in my 11.2 PostgreSQL Database. The Database name will be : crm_db The ...
0
votes
0answers
18 views

Postgresql function equivalent to Excel's NORMSINV function?

I need Excel's NORMSINV equivalent function for PostgreSQL, preferably plpgsql function. Can somebody help? For reference, here is Excel's normsinv function explained: https://support.microsoft.com/en-...
4
votes
1answer
63 views

Slow left join lateral in subquery

I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
0
votes
0answers
9 views

Unable to access Odoo intermittently

I have Odoo with PostgreSQL installed in different server. So I created my Odoo config pointing to PostgreSQL server. Intermittently, I can not access Odoo UI. Restarting Odoo service will fix the ...
0
votes
0answers
16 views

2 postgresql identical rds instances and one is significantly slower

I have 2 instances of free tier rds postgresql db.t2.micro on same zone us-west2 region on 2 different accounts, using defaults at creation, Original and Copy. Original is significantly slower than ...
0
votes
1answer
26 views

Can I compress two rows with a difference in two columns into a single row with both columns?

I have a query that returns customer product information that shows which region a product is sourced from. Query: SELECT prod.prod_id, prod.is_source, link.prod_link_disabled, ...
0
votes
1answer
27 views

Efficient way to compute unique values using a PostgreSQL GIN index

Say I have a JSONB column, with a GIN index on it. I can use jsonb_object_keys() to fetch the keys for each row. But is there an efficient way of getting the unique set of keys, across all rows? ...
0
votes
0answers
13 views

PostgreSQL Function not returning message in Python 2.7 Script using EXCEPT NOTICE

I was not able to make the PostgreSQL function return the raised message in the python script it was being called from. The scenario I wanted to make was:- 1) If the first table was created, a ...
0
votes
0answers
33 views

PostgreSQL Function: Returning Boolean Values depending on whether a table exists

This is my rookie attempt to create a function where it would return True or False depending on whether a table exists in the db. Unfortunately it outputs an empty table, and no raised messages; ...
0
votes
2answers
26 views

Ideal way to start 'Database Server' in PostgreSQL 11

PostgreSQL version : 11.2 OS : RHEL 7.5 I am new to PostgreSQL. I would like to know which is the ideal way to start 'Database server' . In Oracle world, we call this starting the instance. Should ...
0
votes
1answer
19 views

PostgreSQL compare a timestamp with timezone with a timestamp

How do you compare a timestamp with a timezone with a timestamp in postgresql? I am trying to cast one of the 2 to the type of the other but it does not work for me. Eg. Having TS1: 2019-01-07 21:...
0
votes
1answer
28 views

How to store LOB-image or PDF in Postgresql and what is the impact of having in Postgresql?

Please guide me to store LOB-image or PDF in Postgresql and let me know what is the impact of storing these types of objects on Postgresql database. Any help will be appreciated.
-1
votes
2answers
73 views

Which has better career prospects : PostgreSQL or MySQL? [closed]

I am a Senior Oracle DBA. At the moment , my DBA career is not affected by the rise of open source databases. But, It might not stay like this for long. To make my career future proof, which open ...
0
votes
1answer
60 views

Postgresql: How to store high-dimensional ( N > 100) vectors and index for fast lookup by cosine similarity?

I am trying to store vectors for word/doc embeddings in a postgresql table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm ...
1
vote
0answers
25 views

Dynamic JSON path filtering in PostgreSQL 9.6

I have a somewhat odd JSONB structure in my database to accomodate different possible overrides to common fields. The tricky thing is that I want to query based on those overrides. Here's a sample of ...
0
votes
1answer
21 views

Time oriented design in Postgresql

I've been reading Snodgrass's "Developing Time-Oriented Database Applications in SQL" and I'm consulting other documents as well, but I'm willing to find more modern stuff about time-oriented design ...
0
votes
2answers
46 views

Where does Postgres store all statistics?

I recently ran into a weird problem while migrating from PG 9.4 => 11 I replicated the production server from PG 9.4 to PG 11 using pglogical. Everything worked out well, except we discovered that a ...
1
vote
1answer
27 views

Group By with inner join

I am new to SQL world. I am practising on postgresql pgguide sample database . I wanted to count all the purchases of users who have bought a product 'Williams Selden'. So i write something like ...
0
votes
1answer
16 views

Query Quill-Delta JSON array in Postgres

I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g. ----------------------------------------------------------------- | id | title | content ...
0
votes
1answer
23 views

Conditional insert vs UNIQUE constraint

https://stackoverflow.com/questions/4069718/postgres-insert-if-does-not-exist-already I would like to check if something exists before inserting it, what should I rely on? UNIQUE constraint or ...
0
votes
0answers
9 views

What is the optimal pipeline for updating table on FK value in Postgresql?

Hi I have the following schema in my app. -- Table: Investment_round CREATE TABLE Investment_round ( id serial NOT NULL, round_type varchar(50) NOT NULL, money bigint NOT NULL, ...
1
vote
2answers
55 views

Postgresql Deadlock - what does “while rechecking updated tuple” mean?

Question: Can someone please decipher while rechecking updated tuple for me in the following: 2019-02-20 10:34:26 UTC:1.2.3.4(17866):user@database:[31391]:ERROR: deadlock detected 2019-02-20 10:34:...
0
votes
0answers
6 views

Uninstall pgaudit?

I have installed pgaudit for my RDS instance in AWS following the instructions here. Now Im not satisfied and move the audit to the application layer and want to uninstall pgaudit. How can I do this? ...
1
vote
1answer
23 views

Optimizing query performance with aggregation tables and partitioning

Our setup is a bit complex, so I will try to simplify what our tables look like but still try to describe the real problem: We have a large table (items) with about 500 million rows (grows with ~1....
2
votes
1answer
16 views

Is it possible to add a spatial index to a partition with Postgres 10?

I am creating declarative partitions like: CREATE TABLE mydata.my_features ( id bigint, my_feature1 character(1), my_feature2 integer, ... my_geom geometry ) PARTITION BY ...
0
votes
1answer
26 views

query for each row from another arbitrary query, and union results

I'm trying to build a query that will take another query. Then, perform another query based on those results. Union the results so that there are no duplicates. All on the same table. I've seen some ...
4
votes
1answer
50 views

Can a non-owner of a table be allowed to disable the table's triggers?

I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?
2
votes
1answer
27 views

Join tables but keep matches in a single row

I have the following tables structure in a PostgreSQL DB: These tables have this content: So if I want to retrieve the SB associated to each server I'll need to join these 3 tables: Server with ...
0
votes
2answers
26 views

Most efficient way of updating table from another - Postgres

I need to populate a foreign key column based on the contents of another table, and both methods I've tried are prohibitively slow. I have the below tables (some columns omitted): ...
2
votes
1answer
41 views

Make a DBMS-enforced multi-column “UNIQUE” constraint, with a “match all” wildcard

Well I wonder if I can do the following on database level (instead of server level). We are using PostgreSQL as the SQL flavour. I have multiple columns. Now I wish to make sure each entry into the ...
1
vote
1answer
13 views

Wraparound Transaction ID

Once the txid has passed the the xmin/xmax 32 bit limit, and assuming you've not suffered any issues with wraparound because you've been correctly maintaining your tables (plenty of vacuums, vacuum ...
1
vote
1answer
37 views

\COPY not returning count psql

I am trying to copy CSV data into tables. Also I want to print the count. The command I'm using is: echo "begin; delete from a; \copy a from a.csv CSV HEADER; end;" | psql -hlocalhost -dpostgres -...
2
votes
1answer
28 views

Is it possible to have unique together constraint on a SET of columns in PostgreSQL?

What I want to achieve is the following: CREATE TABLE foo ( bar1 int, bar2 int, type varchar(16), FOREIGN KEY bar1 REFERENCES bar(id), FOREIGN KEY bar2 REFERENCES bar(id) ); ...
1
vote
0answers
24 views

Force psqlODBC Postgres drivers to use Windows Trusted Root Certificate Store

I need to configure my client software to use SSL when connecting to a postgres database (sslmode=verify-full). The configuration requires me to specify a root certificate file in the following ...