Questions tagged [postgresql]

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

Filter by
Sorted by
Tagged with
0
votes
0answers
2 views

Inserting Millions Records Into A Table With Identity Column In Sql Server

I was changing the primary key column to different column on a table which contains about 300 million records in itself. I used the table design screen to change it but it got time-out. And, i decided ...
0
votes
2answers
20 views

Counting duplicate instances within column in SQL when grouping by another column

My DB looks something like the following: id | Name | JOBID ----+--------+------ 1 | Sam | a 2 | Sam | b 3 | Ali | b 4 | Tim | c 5 | Tim | d 6 | Fin | a 7 | Mona | ...
1
vote
0answers
5 views

PostgreSQL Serializable unique key violation on different ids

Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2: begin ...
-2
votes
3answers
59 views

Can a `Select` Subquery be conditionally returned?

Is it possible in Postgres to return a column(Resulting from a subquery) only when a condition matches? Query: SELECT (SELECT NOW()) AS FIRST, (SELECT NOW()) AS SECOND Outputs: | first | ...
0
votes
2answers
27 views

Select/print data from subquery

In my application I have a table "user", a table "role", and a table "userroles". Userroles is simple table consisting of user, role, where user corresponds to the id column of the user table. As user ...
1
vote
2answers
32 views

Ensure total order in table via constraint

Suppose I have a table create table log (id serial, logged_at timestamp, event_type integer) Can I efficiently (without making every row writable only once or by some special user) ensure that ∄ (r1,...
5
votes
1answer
537 views

Disallowing table creation in the public schema

Postgres version: 9.3.16 Let's say we have two users, luser and editor. I'd like to make it so that luser (or any other non-superusers) cannot create any tables under the public schema, except editor....
1
vote
0answers
14 views

By using SQL Window Functions, how can I get the lead of the last row in a partition?

Considering the following simple example: select lead(c) over (partition by a order by a) from (values (1,'1','date1'), (1,'2','date1'), (2,null,'date2') ) t(a,b,c) The first two ...
3
votes
1answer
902 views

speed up PostgreSQL calculations via OLAP warehouse?

So I have asked these three questions: Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5 https://quant.stackexchange.com/questions/29572/building-financial-data-...
0
votes
0answers
11 views

Frequency of table update in postgresql?

Given postgres 11. I'm looking for statistics on how often a particular table gets something inserted/updated/deleted. There's of course this: https://www.postgresql.org/docs/11/monitoring-stats....
1
vote
1answer
27 views

regex character class :print: in postgres

I am trying to replicate an Oracle check constraint that prevents non-printing characters in postgres, and I am stumped. I think postgres may be treating :print: and :ascii: as synonymys?!? select ...
18
votes
2answers
20k views

How to install the additional module pg_trgm

I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index. WHERE foo LIKE '%bar%';
176
votes
12answers
419k views

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

Hello I am trying to run a website sent to me but after doing so this error appeared connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "...
1
vote
0answers
9 views

AWS aurora (postgres) proper value for the shared_buffers parameter

I think it's fairly well known that a normal PostgreSQL database has a starting value of 25% of system memory for its shared_buffers parameter, but I haven't been able to find anything regarding a ...
0
votes
0answers
7 views

Query to fetch records using pivot in Postgres

I have the following table: CREATE TABLE public.flights ( flightid uuid NULL, keys varchar(255) NULL, value varchar(255) NULL ); INSERT INTO flights (flightid,keys,value,crdate) VALUES (...
0
votes
1answer
12 views

Handling misspellings when searching across tsvector columns

I have a projects table with a tsvector column. The column is populated and refreshed via a trigger function when some of the other columns on a given row change: CREATE OR REPLACE FUNCTION ...
0
votes
0answers
21 views

logical replication in postgresql - “server closed the connection unexpectedly”

I'm running two postgresql servers and I'm trying to setup logical replication. These are extra bits of configuration I added on master in order to make replication work: wal_level=logical ...
14
votes
2answers
7k views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
5
votes
3answers
215 views

A more intelligent ntile

When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value. For example with the following query: select id,...
0
votes
1answer
21 views

Create view with dynamically selected tables - is it possible?

I have subsection of tables in my database that share part of a schema and a naming scheme. I'd like to create a view that automatically gathers all of the common columns of these tables into a single ...
0
votes
1answer
29 views

How often do you run VACUUM FULL?

I'm running PostgreSQL 10 on AWS for a couple years in production and wondering if it's worth the downtime to perform a VACUUM FULL on the database.
1
vote
1answer
16 views

PostgreSQL not using the index on queries that return large number of records

I have a table called my_table which contains about 30M records. The table has an index set on column_a. However, I'm noticing that when number of returned queries are high, the index is not used. ...
0
votes
0answers
10 views

How to change the replication user in Pglogical without breaking the replication?

if_id | if_name | if_nodeid | if_dsn -----------+---------------------+-----------+-------------------------------------------------------------- 440218642 | ...
0
votes
4answers
54 views

Postgresql extremely slow count (with index, simple query)

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0
votes
1answer
521 views

Does 'max_worker_processes' include background process from postmaster?

I am wondering that max_worker_process include background process from postmaster? I'm going to upgrade Postgres from 9.3 to 9.6 and planning to use parallel query. -bash-4.2$ ps auxf | grep ...
1
vote
1answer
95 views

Query performance of index scans slower than parallel seq scan on Postgres

I've been trying to debug a performance problem on PostgreSQL 10 on AWS Aurora. Specifically I'm trying to understand performance problems on "cold" queries where the buffer cache does not contain ...
0
votes
0answers
15 views

PostgreSQL 10 logical Replication not working !!! slots blocking calls not sending wal events back to client

Postgres logical replication not working we have created logical replication slot on pg 10 using below command select * from pg_create_logical_replication_slot('slot', 'wal2json'); Above request ...
51
votes
4answers
164k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
1
vote
1answer
22 views

language detection column content

I have a Postgresql table that has contents of a column either in English or Spanish Like so: 202707 Redness or swelling on the cheek. 202575 Preguntas acerca de medicamentos antivirales de la ...
11
votes
4answers
8k views

Avoiding “Row by row” fetch method when dealing with source LOB columns

I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying: 'Row by Row' fetch method is enforced because the ...
1
vote
1answer
22 views

How to deal with `.partial` WAL segments generated by Postgres `pg_receivewal` command?

pg_receivewal creates a .partial file for incomplete WAL segment. My question is how to deal with this file during recovery. If I just leave it as it is, last transactions don't recover. If I rename ...
1
vote
1answer
424 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
36 views

DB design advice - shortcut foreignkeys vs long queries

I've designed quite a few databases but there's a problem I come across time and time again. The more information you want to pack into your database and the more fields and different properties you ...
5
votes
1answer
449 views

Azure Postgresql hitting 100% memory usage without easy way to restart the server

I have recently noticed an issue when using Azure Database for PostgreSQL where my memory usage keeps growing, and upon hitting 100%, the server will stop responding. This database server is used ...
0
votes
1answer
26 views

Free space of toasted content

I have just deleted a big part of the data stored in one jsonb inside my users table (Its not needed anymore) and just keep an id inside that column. I expected to free a lot of disk space with this ...
0
votes
1answer
18 views

When was the last time my table was vacuumed?

I am running Postgres 9.6. I have recently been reading of the importance of VACUUMing in postgres. I am looking for a SQL statement that could return a list of my tables and when was the last time ...
0
votes
1answer
44 views

Can we use CTE and Subquery together?

with sum as ( select ud.id,sum(data_volume) total from mediation.udr ud inner join mediation.meter_reading mr on ud.cdr_id = mr.id group by ud.id ) (select total from sum) select act....
0
votes
2answers
10k views

psql: FATAL: password authentication failed for user “postgres”

I am running postgresql-9.6 on Rhel7. I know that the default/root user doesnt have a password, however i will like to use phpPgAdmin to do my work. the problem is I have edited my pg_hba.conf file to ...
0
votes
1answer
9 views

updating associated records' tsvectors when the primary record is updated

I have the following trigger function that updates the object's tsvector column when its details change: CREATE OR REPLACE FUNCTION customer_search_trigger() RETURNS trigger AS $$ BEGIN ...
0
votes
1answer
93 views

How to unnest and display elements of a JSON array?

i have this table : And i want display all the names in the table like this : Also i want to list the number of gender each genre : Desired output:
2
votes
3answers
2k views

pg_restore terminates with “out of memory” error

Trying to restore a 45mb pg_dump file but it keeps failing with an "out of memory" error. Content of my postgresql-10-main.log: 2018-01-16 06:14:50.692 WAT [32405] postgres@app_development LOG: ...
4
votes
1answer
266 views

How to remove a table from a FOR ALL TABLES logical replication PUBLICATION in PostgreSQL

I created a logical replication PUBLICATION using FOR ALL TABLES. Now I've realized I don't want to replicate all of the tables after all. I tried dropping one of the tables from the publication, but: ...
0
votes
1answer
17 views

Select all columns from table but apply function to 1 column

What I want to do: Select all columns but apply a function to one of the selected columns in a shorthand manner such that I don't have to type out all of the column names Current approach: I am ...
0
votes
0answers
25 views

PostgreSQL 10 not allowing remote connection

I cant get a PostgreSQL 10 instance to allow remote connections. The database is hosted on Ubuntu 18.04 listen_addresses has been set to both '*' and 'myactualip' that needs to connect to the ...
1
vote
1answer
16 views

Change postgres default behavior for connection to database when dbname not supplied

According to postgres documentation: https://www.postgresql.org/docs/10/libpq-connect.html dbname The database name. Defaults to be the same as the user name. In certain contexts, the value is ...
1
vote
1answer
3k views

How to assign privileges on a postgresql schema to a user?

pg_hba.conf contains: ... host 10.10.10.10 service_db admin md5 ... Logged in as root, executed the following commands: ###CREATE DB ADMIN sudo -u postgres psql -p 5432 -d postgres -c "CREATE USER '...
0
votes
1answer
30 views

Improve speed of COPY COMMAND in postgres

I would like to understand if increasing work_mem does help improving the speed of the COPY command. Does COPY use work_mem or maint_work_mem extensively?
0
votes
1answer
25 views

Postgres PITR: Recovery does not seem to fetch from Archive Dir

Make and give permission to database dir cd /home/admin-12/Desktop/ mkdir test2 sudo chown postgres:postgres test2 mkdir test2_wal sudo chown postgres:postgres test2_wal touch test2.log sudo chown ...
0
votes
0answers
20 views

PostgreSQL - Update not working inside function

I am creating a web app that handles coupons. When a user claims a coupon the coupon batch count "should" be updated, and a new coupon is created. For some reason the UPDATE query does not work ...
3
votes
1answer
3k views

postgres backup for specific table with blob oid

I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 ...