Tagged Questions
0
votes
0answers
29 views
PostgreSQL deadlock in single update statement affecting multiple rows
I have a live web app where I sometimes see a deadlock in a statement like this, which updates more than one row.
update table1 set a = 123 where b = 'foo';
I can't reproduce it in my test database....
0
votes
0answers
13 views
SQL Query slow during batch update of table
I have a postgresql table with about 250K records. It gets updated a few times an hour. However, the entire table gets deleted and new records added. (Batch job).
I don't have much control over ...
0
votes
1answer
22 views
Postgressql - error - could not open file “C:\Insert_postgres.csv” for reading: No such file or directory
I have copied function(from one of the webportal and modified accordingly) to copy data from csv file to table.
create or replace function public.load_csv_file
(
target_table text,
csv_path text,
...
-1
votes
0answers
16 views
How to get a preview for an image using postgresql?
I'm using image tables in my database. I want to create a preview for the fetched image using PostgreSQL query.
What is the syntax for creating a preview?
3
votes
0answers
28 views
how to restrict the table rights of certain tables for a certain user eventhough that user inherits from admin
I have a user called 'user1' inherited from admin(who have all access to all tables).Is there any way in postgres such that if logged in as
admin - all tables will be visible.But when logged in as '...
0
votes
1answer
24 views
Periodic RDS Postgresql Replication Delays
I have been observing that my PostgreSQL read replica shows periodic delay for replication lags. The lag seems to build to up to 30-40 minutes and then automatically goes down to 0. There is a ...
3
votes
2answers
55 views
How to lock certain columns from being edited for a user in postgresql
How to lock certain columns from being edited even though user have access to editing rights for the table in postgresql.
0
votes
1answer
20 views
real-time sync between local Postgres instance and Azure Cloud Postgres instance
I need to set up real time sync process between a on premise postgresql instance with cloud postgresql instance. Please let me know what are all the options available through which i can achieve it.
...
0
votes
1answer
39 views
postgresql - data divided by months
I have following tables
and
I receive the Count of user_id per month where the registration date and login date should be different. For example :
However, I need to receive the count of unique ...
1
vote
2answers
46 views
How to get the row count of update,insert,delete query in postgresql
I have a postgresql tables. user may update records or delete records manually using interface or using query. I need to track how many records have been updated ,deleted into a log table.I had tried ...
-1
votes
1answer
15 views
Select only group of string which ends with number series 1,2,3, instead of 01,2,3.
I have a requirement where I need to search for the number in the later part of the string.
Now I can have 3 kind of instances.
1. String ending with number starting from 0 , 1 ,2 ,3... so on
2. ...
0
votes
1answer
36 views
date at time zone related syntax and semantic differences
Question: How is query 1 "semantically" different than the query 2?
Background:
To extract data from the table in a db which is at my localtime zone (AT TIME ZONE 'America/New_York').
The table has ...
1
vote
1answer
23 views
lower() in where clause does not match pattern
select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL ...
-6
votes
1answer
57 views
Postgres equivalent for Oracle's DBA_FREE_SPACE and DBA_DATA_FILES
This is the sql query. I need to convert into postgres query
SELECT a.tablespace_name,
a.maxbytes,
a.mbytes,
(a.maxbytes - a.mbytes),
ROUND(((a.maxbytes - a.mbytes) / a....
1
vote
0answers
44 views
Subtracting amounts from wallets via SQL
For the following set up (simplified):
transaction:
| transaction_id | account_id | transaction_amount |
| 1 | 1 | 80 |
| 2 | 2 | 30 ...
0
votes
1answer
39 views
How to retrieve top 3 results for each column in postgresql?
I have given a question. The table looks like this..
STATE | year1 | ... | year 10
AP | 100 | ... | 120
assam | 13 | .. | 42
madhya pradesh | 214 | ... | 421
...
1
vote
1answer
62 views
pg_xlog files not recycling on slave
I've setup streaming replication with postgres 9.3
My problem is that on the Slave server the pg_xlog folder just gets fuller and fuller and WAL files are not getting recycled.
The slave server has ...
0
votes
1answer
19 views
how to get postgresql server log details to two different logs in shell script?
I am running postgresql copy command in shell script.
psql -v ON_ERROR_STOP=1 "host=$HOST port=$DBPORT dbname=$DBNAME user=$DBUSER password=$DBPASS" \
-F -a --no-align <<EOF 2>> file1....
0
votes
1answer
23 views
How to avoid the idle connections in postgres 9.3
We are using postgres 9.3 version. And we are getting connections like "set extra_float_digits =3" as idle minimum 40 connections . I'm surprised even though I disabled that parameter in conf file ...
0
votes
0answers
24 views
postgresql tables deleted misteriously
It is the second day that I am encountering a strange thing: my tables in a postresql DB have all been deleted.
I am the only one who has access to that system, so it can pretty much rule out that ...
0
votes
0answers
38 views
Query Optimization for the below one:
I am running this query which fetches close to 100 records. But it is taking nearly 10 mins to accomplish this. Can someone help me in optimizing it if possible.
select Client_Details.*,org_key_table....
0
votes
1answer
24 views
How to return rows of query result in PostgreSQL's function?
I've tried following tutorials for many times but failed.
Could someone give me some examples please?
Here is my code, it prompts that "ERROR:invalid type name 'SETOF RECORD'"
create or replace ...
0
votes
2answers
53 views
Insert Data in specified column if column value id matches the description of the ID in another table (normalized form)
In order to do this (in Title Description) it's obvious that it's already a normalized database so an inner join kind of check will be used for sure.
Let's say that we have a customer table and a ...
1
vote
1answer
41 views
PosgreSQL aggregate function for each row across multiple unknown number of columns
I looked through similar questions like this one, but they seem to have a definite number of columns. I would like to input a table that I do not know the number of columns.
Question:
How to ...
0
votes
2answers
44 views
Generate randomised columns with parametric names in table postgres9.3
UPDATE: I was looking for an intersection of sets that do not intersect, thus updated the original table file. Like in my big dataset each lot has all the timestamps dt available. My version still ...
0
votes
2answers
24 views
Editing a Materialized View in PostgreSQL 9.3 if other Views depend on it
I want to change the value-construction in one field of a materialized view. (e. g. use UPPER() instead of LOWER() for a text field) Neither field-types nor any properties of the view are affected.
...
1
vote
0answers
13 views
php 5.3 compilation failed using postgresq93-devel into Docker image
I am using a custom compilation based on PHP 5.3 and I need to upgrade the postgresql version from 9.2 to 9.3.
I am using docker for my infrastructure and the new lines I added was
yum remove ...
0
votes
1answer
22 views
Query for column key when foreign key is absent in another table postgresql
We have a PostgreSQL database which has a table with a foreign key reference to the primary key of another table like below
Table A
a_key
b_key
when_
Table B
b_key
There was a bug in our code ...
0
votes
1answer
81 views
How to create tables with millions of rows with fast performance in PostgreSQL?
I have data that correspond to 400 millions of rows in a table and it will certainly keep increasing, I would like to know what can I do to have such a table in PostgreSQL in a way that it would still ...
3
votes
2answers
115 views
Update query too slow on big PostgreSQL table
I am trying to improve speed of simple UPDATE query, but it is taking between 0.7-1.5s for one row, which is too slow.
UPDATE users SET firstname = 'test' WHERE userid=2120;
Here is explain:
Update ...
0
votes
0answers
32 views
DBLink in PostgreSQL drops DNS name of target (Redshift), but recovers after service restart
I've a (random) problem within PG 9.3.
I have stored procedures, that create a dblink connection to a redshift instance.
It used to work fine, but recently it stopped.
Setup:
AWS EC2 instance (...
0
votes
1answer
45 views
Why postgresql writes huge temporary files and fills my disk within a loop?
Problem: The function (below, in PostgreSQL 9.3) runs fine with few iterations, but with many iterations it writes a file of ~1 GB on the disk each iteration until the disk is full and then the code ...
-2
votes
1answer
61 views
Centos postgres connect
When you try to connect to postgres:
# psql -h localhost dgrt postgres
I get an error:
#User "postgres" has not passed authentication (Ident)
Configuration my files
postgresql.conf:
...
0
votes
1answer
57 views
file_fdw Access csv file on a remote server
Creating a foreign table like this:
CREATE FOREIGN TABLE xxxx
(... )
SERVER file
OPTIONS (filename '/data/data.csv', format 'csv', delimiter ';');
In the filename Option, Is it possible to specify ...
0
votes
1answer
118 views
Cross-tab in postgres
I am trying to create a query in Postgres to obtain a result in form of table as follows. The idea is to create a table including the value of the variable whose name is variableN at dateM. Instead of ...
-1
votes
1answer
47 views
Extract value of unnamed record from json in postgres
I have the data in json format in my postgres db as shown:
data={"id": "1:2:3", "[{\"info\": \"No\", \"links\": [\"<link rel": "''parent'' href=''http://example.com/1''/>\"], \"uid\": 1}]", "...
0
votes
1answer
45 views
Sequence incremented randomly postgres
We have a primary key of serial type that is incremented automatically based on Postgres sequence.
The problem is this field at midnight start having a big gaps between it values. example:
Id
...
1
vote
1answer
53 views
Update from regexp matches in same table without using subquery
I want to fill two columns from the results of a regular expression matching on a column of the same table.
Extracting the matches in an array is easy enough:
select regexp_matches(description, '(?i)...
1
vote
0answers
43 views
where is postgresql-docs pdf?
So I installed postgresql-docs:
[root@me ~]# yum info postgresql93-docs.x86_64
Loaded plugins: priorities, update-motd, upgrade-helper
Installed Packages
Name : postgresql93-docs
Arch : ...
-3
votes
1answer
54 views
I have error loading data into PostgreSQL database
follow up to the question, this is the command line i used but it could not recognizecommand line my password
The Image
I have a PostGreSQL script that contains table definition and sample data but ...
0
votes
0answers
80 views
EXPLAIN (BUFFER,ANALYZE) understading
I am using PostgreSQL 9.3 Can some one help me what i can do to improve this. The max number of records in db table is approx 5000 to 7000 varies daily basis. shared_buffers setting in PGsql DB is =...
0
votes
0answers
34 views
How wal logs work in postgres?
I have to do a point in time recovery in postgres should i have all the wal logs that are generated or is it fine to have few recent wal log files ? I tried recovery but I'm not sure if all the files ...
0
votes
0answers
16 views
Read disk .sql file names with PostgreSQL [duplicate]
does anyone know how to read file name from the client machine disk from the postgresql manager?
In sql used the following:
EXECUTE master.dbo.xp_cmdshell 'dir /b "C:\Folder_Scripts\*.sql"
0
votes
1answer
55 views
Read .sql files to disk with PostgreSQL
is there any way to read file names stored in a folder on the disk and filtering through the extension?
This could do with sql server, but now I need to do it with postgresql.
I await your suggestions....
3
votes
2answers
50 views
How to pass a parameter and cast to a certain data type inside a function?
I'm attempting to write a plpgsql function to insert a record into a table that expects some timestamps in certain columns. Here is the function:
create or replace function insert_slot(created_by ...
1
vote
0answers
26 views
how shared_buffers works when we update a table in postgres
Say i have to update a table in postgres that is of size 10gb and my shared_buffers is 1 gb, can you explain the working if the update is committed and update fails after a certain period.
0
votes
1answer
29 views
Flatten one column keeping others in POSTGRESQL
I created a view which is presently giving the data like this:
practice_name message_type message_count
CHC ALOG_SYNC 1
CHC BULKNT 0
CHC ...
0
votes
0answers
33 views
Postgresql Streaming Repication failed
I'm using Postgresql 9.3 in two servers in master/standby mode. I made use of streaming replication to replicate data in the standby server. Suddenly one day, Streaming replication failed between the ...
1
vote
1answer
82 views
Select * From table As a table dblink
To select from another database I try to use dblink or fdw extension of Postgres, like this:
CREATE EXTENSION dblink;
SELECT * FROM
dblink ('dbname = bd_name port = 5432 host = 10.6.6.6 user = ...
1
vote
2answers
124 views
How to count days except Sundays between two dates in Postgres?
To find the number of days between two dates we can use something like this:
SELECT date_part('day',age('2017-01-31','2017-01-01')) as total_days;
In the above query we got 30 as output instead of ...
