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
4 views

Postgresql to log errors caught within BEGIN/EXCEPTION/END blocks

I'd like to have errors caught within a BEGIN/EXCEPTION/END block logged. Here's what I have, compare 1. and 2. -- 1. Nothing will be logged CREATE OR REPLACE FUNCTION func() RETURNS VOID AS $$ ...
0
votes
0answers
2 views

Date pattern regex doesn't match the records in PostgreSQL query?

I am working with a dataset in PostgreSQL which is quick messy. I have written a few regex operations to find date patterns in the records of a table. Since I don't have a generalized REGEX defined ...
0
votes
0answers
16 views

Design critique on a table to keep track of which person flipped a switch at what time

I created a table to keep track of which person flipped a switch at what time. It consists of: create table records ( name varchar not null , changed_at timestamp with time zone not null , ...
12
votes
1answer
7k views

Querying non-ASCII rows from Postgres

Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it. I have a UTF-8 database, where collation and c_type are en_US.UTF-8, ...
172
votes
4answers
376k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
0
votes
0answers
5 views

how to get data from multi table with column json in postgresql?

I have a lot of tables like the following tables. main_table id |customer_id | tabl2_id | tabl1_subJson_id |... ---|------------|-----------|------------------|------ 1 | 1 | 1 | ...
0
votes
1answer
10 views

Restructure create and delete events on rows into single table

Similar to this problem: Using start and end event logs to create a table/view containing spans between the times of each log. I have table that logs 'create' and 'delete' events for a resource that ...
1
vote
1answer
8 views

Postgres 10 Query waits on IO: BufFileWrite causing failure to get new database connections

I have a query (attached with query plan) that is run every 5-7 seconds from every device that is running our dashboard (typically, 500+ devices at peak time). This query, at the outset looks like it ...
1
vote
2answers
16 views

PostgreSQL: difference between collations 'C' and 'C.UTF-8'

In PostgreSQL, what is the difference between collations C and C.UTF-8? Both show up in rows of pg_collation. Is it perhaps the case that C.UTF-8 is the same as C with encoding UTF-8 regardless or ...
8
votes
3answers
2k views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
0
votes
0answers
11 views

Select / remove nested JSON attributes postgresql

I have the following JSON structure in postgres. drop table if exists data cascade; create table json_test (data json); insert into json_test values ('{ "a": "1", "b": "2", "c": "3", "level2"...
0
votes
0answers
12 views

Use Thesaurus as Synonym Dictionary for FTS?

As I understood, the function of this dictionaries are the same, except that Synonyms Dict do not support operation with phrases. Is It OK to use one dictionary for thesaurus and phrases in ...
8
votes
1answer
4k views

How do you drop an unnamed check constraint in a PostgresQL table?

I created a PostgresQL table but I added an unnamed check constraint on one of the columns: CREATE TABLE FOO ( id serial primary key, price_range smallint CHECK (price_range > 0), url varchar(255) ...
3
votes
1answer
29 views

Get base table from materialized view in postgresql

I use materialized view in postgresql. when retrieving the list of materialized view (select * from pg_matviews), in the "definition" column, there is a query building the materialized view. how do i ...
0
votes
1answer
9 views

PostgreSQL Correlation Between “max_connections” and “numbackends”

I'm trying to figure out why the field numbackends in pg_stat_database never goes above 16 (and often stays the same even after a restart). I have max_connections set to 100 and ...
1
vote
1answer
190 views

script breaks my ability to run psql

I've successfully installed postgres on my Ubuntu machine. I am running a script for a website I am working on, here is the relevant code: apt-get -y install postgresql libpq-dev su postgres <<...
-4
votes
2answers
62 views

Why is this query not returning the right result? [on hold]

I want to select/filter by 2 columns status and state from a table of 6 columns . I have written the query below to select cars that are unsold and either new or used but the filter returns both new ...
4
votes
1answer
242 views

Use case for hstore

This is the first time (I think) I have the chance to use the hstore data type, but I would like to hear from more experienced folks if what I have in mind is actually a good idea. Now, there's this ...
8
votes
2answers
62 views

Will Postgres perform the computation of calculated columns that aren't selected in a view?

I'm trying to understand the performance impact of selecting data from a view, where one of the columns in a view is function of other data in the original table. Does the computation get performed ...
0
votes
1answer
107 views

Create or refresh materialized view

How would you create new or otherwise refresh a materialized view in Postgres? I've tried something like this: CREATE OR REFRESH MATERIALIZED VIEW institution_mv AS SELECT entity_id, ...
2
votes
1answer
14 views

Postgres database not using indexes

I have a SQL optimization question. Table Name: charts Total Records: +250 million Columns: timestamp(string), imported(boolean), .... Indexes: CREATE INDEX index_charts_on_timestamp ON public....
0
votes
0answers
11 views

on insert rows in a table how to update that inserted data in another table?

I'm trying to update large amount of rows (20,000) rows, so i created a temp table similar to this one. and i want to create a trigger on insertion or update and a function to take the inserted or ...
0
votes
0answers
13 views

Restoring Postgresql database on Windows if only “data” folder available?

Is is possible to restore a PostgreSQL database if all I have available is the "data" (C:\PostgreSQL\data in my case) folder of the server whose databases I want to restore.I know this folder contains ...
0
votes
1answer
98 views

How to remote connect with PostgreSQL 11 CentOS server?

My Server: CentOS 7 x64, PostgreSQL 11.2 My Client: Navicat, pgAdmin 4.5 . Error at client: FATAL: no pg_hba.conf entry for host "14.248.254.146", user "postgres", database "postgres", SSL off I ...
-5
votes
1answer
40 views

Create table of sequence first days of the calendar month in Postgresql [on hold]

hei, I am trying to create temp table in Postgresql sql script is: I know how to write in SQL server but in Postgresql while running below code i am getting error as ERROR: syntax error at or near "@" ...
2
votes
2answers
339 views

Can I cluster PostgreSQL by sharing a data directory over NFS?

I created a PostgreSQL cluster in one host and shared it over NFS share to another host. Even though i can access the cluster using second host but can not use or log in to databases. Is it possible ...
1
vote
1answer
62 views

How to find the count of words (all and unique) in a column row-wise?

I have a column in my Postgresql database named Description which contains text. For some analytics purpose, I want to find total number of words (seperated by spaces) - all words and unique words - ...
5
votes
1answer
348 views

CASE, VIEW, CTE or JOIN LATERAL: which is the best approach?

As a follow-up to this question, I have one of my own. The original question concerns the use of a CASE statement with > than 100 options and the statement has to be used in 4 places - so obviously ...
1
vote
1answer
13 views

Using plpgsql to remove all functions

I intended to drop all functions in all schemas within a certain database, however, after I executed the bash file containing the following code block, the functions are still there # use pg_dump and ...
0
votes
0answers
12 views

Postgres count the same join by different values

I've been trying to create a query that counts the number of different types of events associated with each entry in another table city job job_events id | name | state ...
0
votes
0answers
12 views

Postgresql to have date of 12 last months but it should 1st day of every month [duplicate]

hei, i am getting ERROR: syntax error at or near "int" in declare. declare v_n int; v_i int = 0 v_n := month(now()) - 1 select v_n if object_id('tempdb..#t12345') is not null then drop table #t12345;...
0
votes
1answer
11 views

'utf-8' codec can't decode byte ERROR when querying data

I have created this foreign SQL Server using tds_fdw foreign data wrapper: CREATE SERVER servprod_sqlprod_mydb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'XX.XX.XX.XX', port '1501',msg_handler ...
1
vote
1answer
34 views

Error: FATAL: no pg_hba.conf entry for host - PostgreSQL - pg_hba.conf is empty

I upgraded the PostgreSQL database from version 10 to 11. As I tried to access the database from a client pgadmin4 console, I got the below error: I checked the two file postgresql.conf and pg_hba....
0
votes
1answer
33 views

How to query results from a huge postgres database?

I am trying to query a few tables in a 125GB Postgres database. I find a hard time getting the results back quickly! Even to find unique rows of a column it takes more than 10 minutes. Either I use a ...
10
votes
1answer
251 views

Optimizing a 'latest' query in Postgres on 20M rows

My table looks as follows: Column | Type | -----------------------+-------------------+ id | integer | source_id | integer ...
0
votes
1answer
18 views

Merge 2 columns from joined table

lets say I have 2 tables : group { id, export boolean not null default false } and channel { id, group_id null, export boolean null } Is it possible, with 1 query, to ...
1
vote
0answers
70 views

Filter sorted overlapping ranges with window functions

I have the following data set: layer period 1 ["2019-06-17 10:00:00","2019-06-17 11:00:00") -- visible 1 ["2019-06-17 12:00:00","2019-06-17 14:00:00") -- visible 1 ["2019-06-17 15:00:...
0
votes
0answers
17 views

Bidirectional Publish/Subscribe with PostgreSQL

With PostgreSQL, I can set up a publication and subscription between two databases and the subscription will duplicate the contents for the published table. What I found interesting is that the ...
0
votes
2answers
94 views

Slow Sum/Group By over large amount of rows

Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10. SELECT mike_romeo, SUM(hotel) as counts FROM whiskey_lima WHERE yankee = 'kilo' ...
0
votes
0answers
20 views

Delete duplicate Data joining different tables

I've this problem, and I'm using PostgreSQL 9.4.12 on x86_64 ; I have these tables Entity ------------ idEntity_pkey value ... ... Points --------------- idPoints_pkey idEntity_fkey x y ...
0
votes
0answers
18 views

Postgresql - Can we create streaming and logical replication replication from same server

I have a postgresql setup with 1 master and 3 slaves. Now I want to setup a replication for only one database to reporting server. My problem statements: I can't use bucardo (no approval for ...
1
vote
0answers
24 views

Querying foreign table with SP-GiST index results in “ERROR: cache lookup failed for type 0”

I'm using PostgreSQL 11.3 (compiled by Visual C++ build 1914, 64-bit) and I want to access a table in another Postgres database using postgres_fdw but since the original table has an SP-GiST index on ...
0
votes
2answers
26 views

Speeding up Postgresql manual index reset?

I'm merging databases with each other. One problem I have is that both of the database tables have the same primary keys (starting from 1, but otherwise they are different records). To solve this, I ...
0
votes
1answer
181 views

Index not used when casting computed value to integer [on hold]

I found a solution using PL/pgSQL (see below), but the question remains: Why can't postgres use a computed and cast value in an index? My history table has an index on (itemid, clock) ...
0
votes
0answers
13 views

WAL buffer checkpoint/flush time during serialization isolation

I have a doubt regarding the flush/checkpoint time of WAL buffers during serialization isolation in Postgres. Is it possible to lose acknowledged writes even if transaction runs in serialization ...
6
votes
1answer
167 views

Efficiently computing aggregate functions over subqueries with incremental data

I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a ...
2
votes
1answer
205 views

improve postgres query perfromance on windows with billions of records, TB data size

using Postgres 9.6, pgAdmin 4 (for running the queries) on Windows 10 64-bit with Intel Core i7-6700 CPU @ 3.40GHz, 16GB RAM, 1 TB C drive HDD , 2 TB D drive HDD. Postgres data is stored in D drive I'...
1
vote
1answer
23 views

How do I select the count of a specific value without grouping in postgresql?

I have a view that's a result of a UNION of two selects. It has contents similar to this: id foreignkey 1 a 2 a 3 b 4 c I'm looking to add a column in the view's definition that will show a ...
5
votes
2answers
1k views

Estimate end time for a long update

I have currently running a long update (~million rows based on a postgis st_within condition) that was expected to finish in an hour or so based on similar previous queries. However, it's been three ...
0
votes
0answers
9 views

Script to alert me that a droplet resize is necessary for PostgreSQL DB to fit into memory?

I am trying to answer the question, "How big is my PostgreSQL DB relative to memory on my Ubuntu server?" The goal of asking this question is to be able to alert me to when the size of my database is ...