Questions tagged [postgresql-9.4]

PostgreSQL version 9.4

Filter by
Sorted by
Tagged with
1 vote
1 answer
22 views

Merging wifi session data if time between them is less than 15 minutes

I am trying to process network logs and join sessions together if the time between them is less than 15 minutes. The relevant fields are start time, end time, mac address, and wifi access point. I am ...
lpscott's user avatar
  • 13
0 votes
0 answers
19 views

Generate the next records for a specific condition

I am using postgresql. The table details Create table weather_forecast ( date date, temperature decimal, avg_humidity decimal, avg_dewpoint decimal, avg_barometer decimal, avg_windspeed decimal, ...
Rishabh Mahajan's user avatar
0 votes
0 answers
78 views

Check Existence Before Creating - Select, Insert, on conflict return

I've the following code that creates mapbox vector tiles and inserts them for storage. In the final code each xyz tile will be unique. Before the below code I would like to check the storage table to ...
Tom Shelley's user avatar
1 vote
0 answers
111 views

User Permissions for WAL files

I'm setting up replication on Postgres. (expect more questions later :-) I'm putting the WAL files on the NAS so that both servers have access to the files. The Postgres service is running as "...
BWhite's user avatar
  • 111
0 votes
1 answer
276 views

In Postgresql how do I get localtime as the TimeZone to work on FreeBSD?

PostgreSQL: 9.4 FreeBSD: 11.2 When I set my TimeZone parameter in postgresql.conf to localtime I receive the following error when trying to start PostgreSQL. service postgresql restart <2022-06-02 ...
quickblueblur's user avatar
0 votes
1 answer
269 views

DELETEd Lots of Rows from pg_largeobject, pg_freespacemap Curiosity

postgres noobie, PG 9.4 (please don't say I need to update PG, thx) Database has huge pg_largeobject (PGLOB) table (~450GB, 169.5M rows, lots of DML leading to ~95GB/~22% free space - all per ...
TheSQLGuru's user avatar
2 votes
0 answers
90 views

Cannot Create Extension vacuumlo postgresql 9.4

I tried to CREATE EXTENSION vacuumlo, but it fails with the following message: "/usr/pgsql-9.4/share/extension/vacuumlo.control": no such file or directory. I can see other .sql and ....
TheSQLGuru's user avatar
0 votes
1 answer
3k views

Invalid input syntax for timestamp error with Postgres Copy command

I am trying to load data in Postgres through COPY command in a SQL file COPY <table name> (<column1>, <column2>, <column3> etc etc) FROM stdin; 1 test1 test2 ...
user2405589's user avatar
1 vote
1 answer
1k views

Count of Saturdays and Sundays in a given month in PostgreSQL

How can I get a count of the Saturdays and the Sundays in a given month in PostgreSQL? I am getting total days count, or total Saturday and Sunday days in a month. For month days code: SELECT ...
bharathi m's user avatar
1 vote
2 answers
4k views

Postgresql update slow on a relative small/medium table

I'm trying to run an update on a table with 10 million rows (I just need to update a few), but the query is taking more time than I would like. This is the table deinifition: CREATE TABLE ...
dssof's user avatar
  • 11
1 vote
1 answer
866 views

pgAdmin - "cannot open logfile!" (WINDOWS 10; Postgres 9.4)

Our Windows 10 clients have both PostgreSQL 9.4 and pgAdmin3 (v1.22) installed. I keep receiving the FATAL: "Cannot open the logfile!" error by invoking pgAdmin3 in either of the following ...
user9491577's user avatar
1 vote
1 answer
618 views

PostgreSQL - 1 billion rows table cannot get vacuumed despite no errors

I have a statistics table that should be processed in a scheduled task to remove rows and count them into a separate, smaller table. Unfortunately the cron task failed for some period of time and the ...
hazelnutek's user avatar
-1 votes
1 answer
969 views

Single query to replace a value in multiple tables with same column

I am using Postgres SQL. I am having multiple tables say table1, table2, table3, table4, ... with a common column say name in each of the tables. I want to replace a the value 'Jhon' with 'Jimmy' in ...
Vishal's user avatar
  • 3
1 vote
2 answers
252 views

Unexpected behaviour for GROUP BY query

CREATE TABLE public.temp ( id integer, a integer, b integer, x integer ); select x, a, b from temp group by id, a, b; ends up with expected error: ERROR: column "temp.x&...
mpapec's user avatar
  • 133
3 votes
3 answers
4k views

Why is the database size larger than sum of table sizes

I wanted to know why database size is a way greater than thesum of table size in a database? How does it change?
Raksha's user avatar
  • 53
2 votes
1 answer
256 views

Caveats of OUTER JOIN on nested JSON value

I'm writing a query which is supposed to find the elements from a list which DO NOT exist in the DB. My first attempt at this was to use a nested query where the first query fetches the ids, then I ...
Archmede's user avatar
  • 165
0 votes
0 answers
432 views

How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?

We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place ...
AKBAR ALI's user avatar
0 votes
0 answers
183 views

WAL Archival not working in PostgreSQL V9.4.9 DB server

Our DB servers are on VM, during planned server restart for system patch deployment our Master DB server crashed. We have taken the clone of Primary Slave VM and restore the Master DB using last ...
AKBAR ALI's user avatar
1 vote
0 answers
59 views

Postgres Major version upgrade 9.4 -> 9.6. PITR compatibility across versions?

Just dug into postgres documentation and couldn't get a clear answer on this. With Debian Jessie hitting EOL for Debian LTS in June, we are upgrading our database servers along with our other servers ...
jrkinnard's user avatar
3 votes
0 answers
3k views

How do I fix multixact wraparound after it has already occurred?

I have a database which has a single table for which MultiXact wraparound has already occurred. Neither manual vacuums nor automatic vacuums are able to fix this condition. Manual vacuums fail almost ...
Freiheit's user avatar
  • 249
1 vote
1 answer
760 views

Does the Postgres 9.4.9 update fix MultiXactId "does no longer exist" in addition to "has not been created yet" errors?

Postgres 9.4.9 has a fix in it's release notes which reads (emphasis mine): Prevent possible failure when vacuuming multixact IDs in an installation that has been pg_upgrade'd from pre-9.3 (Andrew ...
Freiheit's user avatar
  • 249
2 votes
1 answer
1k views

Does postgres single-user mode use postgresql.conf settings?

Does running postgres in single-user mode (--single argument) cause postgres to start and use the settings present in the postgresql.conf file in the data directory defined by the -D option? I am ...
Freiheit's user avatar
  • 249
0 votes
1 answer
302 views

Postgres 9.4.5 small table grows into huge size - periodic VACUUM FULL needed

We are running a PostgreSQL database on Windows Server. The database version is 9.4.5 (it's been running stable for a long time now so we aren't updating it). Some of our tables have millions of rows,...
AJ Cole's user avatar
0 votes
1 answer
823 views

Postgres ignoring GIN index for LIKE query?

I am working in Postgres 9.4. I have a table and index like this: Table "public.title" Column | Type | ...
Richard's user avatar
  • 245
0 votes
1 answer
169 views

slow count queries - what are we doing wrong?

Using Postgresql 9.6.14, running the following query: SELECT COUNT(*) AS "__count" FROM "crm_client" INNER JOIN "crm_role" ON ("crm_client"."role_ptr_id" = "crm_role"."id") INNER ...
SColvin's user avatar
  • 101
2 votes
1 answer
5k views

Postgres - Vacuum doesn't complete on large/busy table

We have a decently active PG database hosted on AWS. We recently started getting notifications like the following: Transaction ID age reached 750 million. Autovacuum parameter values for [...
brandonhilkert's user avatar
2 votes
1 answer
2k 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 ...
user183592's user avatar
1 vote
1 answer
358 views

move postgresql 9.4 cluster to new server

I have a redmine postgresql folder backup (no dump) from postgresql 9.4 and want to move it to a new server with debian 10 (buster) with postgres 11. On my debian buster server I tried to get this ...
rubo77's user avatar
  • 816
0 votes
1 answer
568 views

Comparing nullable column values using "IS NULL" versus "= NULL"

I'm dealing with nullable values in a column of a table, i'm wondering why this: with cte as ( select 'c1'::text as c1,null::text as c2 union select 'c2'::text as c1,null::text as c2 ) ...
Victor's user avatar
  • 557
0 votes
3 answers
1k views

How to SELECT "closest" rows from another table?

I have two tables, map1 and map2, there are multiple possible combinations between the columns map1.id1 and map2.id2. I have tried the below query: SELECT map1.id1, map2.id2, MIN(...
Gautam Shahi's user avatar
0 votes
1 answer
73 views

Allow transparently altering View's underlying table

How to create a view that does not prevent altering its underlying table? There's a certain reason why I can't drop and re-create the view every time I need to change a column type or add a new column....
basin's user avatar
  • 263
0 votes
0 answers
431 views

Quering Postgresql digest function inside procedure works on pgadmin but not works by calling via Laravel

recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below. I have also installed extension ...
Azer's user avatar
  • 1
0 votes
1 answer
35 views

How to perform count(), count(distinct) in the same output using POSTGRESQL

I am trying to perform both count(distinct(keyword)) and count(keyword) from same tables. Here are the two queries. Query-1 SELECT t1.count(distinct(keyword)) from keyword t1, sent t2,tweets t3,...
bunny sunny's user avatar
2 votes
0 answers
207 views

Changing Postmaster File Name in PostgreSQL

I have two instances of PostgreSQL with master-slave replication between them and want to use GlusterFS. Everything works fine until the second instance wants to store the data in the GlusterFS. ...
Sayad Xiarkakh's user avatar
0 votes
1 answer
175 views

FUNCTION BEFORE TRIGGER INEXISTENT OPERATOR date > = double precision

In the 'pubblicazione' table I want to insert some fields of the tuple contained in the 'legge_approvata' table, but to do this the promulgation date must be in the same month and year as the date of ...
Utente000's user avatar
0 votes
1 answer
575 views

How to make use of WITH in PostgreSQL to reduce query cost

I am trying to get twitter data of narendramodi using below command. SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(...
bunny sunny's user avatar
1 vote
0 answers
6k views

How to run multiple sql statements in Postgres using the tool PGADMIN?

I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint. All of these should be run in one in one script as batch script and I'm using PGADMIN IV ...
Ganesh Saragadam's user avatar
4 votes
1 answer
5k views

Use NEW in FROM clause in Postgres trigger?

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g. SELECT unnest(something) FROM NEW However, this seems to lead to an error: relation '...
Brylie Christopher Oxley's user avatar
3 votes
1 answer
2k views

Streaming replication archive folder taking all space- Postgresql- 9.4

We had recently setup postgres streaming replication between master (physical, CentOS release 6.6) and slave(virtual, CentOS 7.6). We did the test and streaming replication is working fine. We ...
SinghVK's user avatar
  • 163
2 votes
1 answer
239 views

Regarding GIN Index in Postgresql

I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time. select email from customer where lower(email) like '%gmail'; CREATE INDEX idx1 ON customer USING gin (...
Shaju M.K's user avatar
2 votes
3 answers
311 views

Find all items where the 2 latest statuses meet condition

I have a table in a PostgreSQL database like this: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | EXPIRED 2 | 123 | ... | PENDING ... I want to query all ...
Barcelona's user avatar
  • 123
2 votes
1 answer
2k views

postgres :: FATAL: could not access file "pglogical": No such file or directory

We installed pglogical from source code, we are running PostgreSQL 9.4 make USE_PGXS=1 clean all make USE_PGXS=1 install Installation was successful after updating the parameters, we tried ...
user166404's user avatar
3 votes
1 answer
3k views

Streaming replication version between different minor versions

I want to know few details about streaming replication. I want to setup streaming replication between version 9.4.4 and 9.4.20 ? I have the master server in centos6.6 with postgres version 9.4.4 ...
SinghVK's user avatar
  • 163
0 votes
1 answer
3k views

ERROR: relation "codepool_codepoolseq_seq" does not exist SQL state: 42P01

Below is my script but I keep getting an error. -- Table: public.codepool -- DROP TABLE public.codepool; CREATE TABLE public.codepool ( codepoolseq bigint NOT NULL DEFAULT nextval('...
Craig Gordon's user avatar
1 vote
3 answers
13k views

Downgrading PostgreSQL 10 to 9.4

Are there any particular precautions I should take when downgrading PG 10 to 9.4? I have a database on Heroku (PG 9.4) which I'm going to upgrade to PG 10.6. In case something goes wrong, I want to ...
pmichna's user avatar
  • 293
3 votes
2 answers
7k views

How to dump multiple databases of the same cluster in single execution?

I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t. Is there any way to do ...
Spike's user avatar
  • 755
0 votes
1 answer
140 views

Streaming replication in postgres question

We have recently setup streaming replication in our Postgres server (t01, t02). t01 is master and t02 is the slave. I want to understand the below two issues: Recently our /var directory of t01 ...
SinghVK's user avatar
  • 163
1 vote
1 answer
123 views

Merge 2 columns and replace with specific output

The setup is a Debian 8 with Postgres 9.4 (64bit if that matters). Given following table I want to "search" for a combination of columns first and column sub and replace their output with another ...
Dennis Nolte's user avatar
1 vote
0 answers
21 views

Calculate percentages by using JOINS result in postgresql

I am trying to calculate percentages by joining 3 tables data to get the percentages of positive_count, negative_count, neutral_count of each user's tweets. I have succeeded in getting positive, ...
bunny sunny's user avatar
0 votes
2 answers
79 views

Different results for logically same, but textually different queries

Is there any reason why the following queries would return different results? select count(id) from mytable where status='active' and ((textfield1 ilike '%needle%') or (textfield2 ilike '%...
Saurabh Nanda's user avatar

1
2 3 4 5
10