Tagged Questions
All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
3
votes
1answer
17 views
Indexes: integer vs string performance if the number of nodes in the index is the same
I hope this is the right place to ask!
I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the ...
0
votes
1answer
11 views
INDEX size as bytes - PostgreSQL 9.2
Is there any way to get this query returns the index size as BYTES?
SELECT
relid::regclass AS table,
indexrelid::regclass AS index,
...
1
vote
1answer
16 views
Postgresql: compare record against sibling, but limit search only to active records
I'm trying to find a way to compare records against it's sibling record while limiting the comparison only to the most recent entry for each set.
I know it does not make to much sense so I'll go ...
1
vote
1answer
12 views
PostgreSQL ntile() partition
i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is.
now i want to calculate the percentile ntile(100) ...
0
votes
0answers
17 views
Postgresql relation from array column to multiple ids [duplicate]
I`m trying to create a simple postgresql database for payments accounting.
Here are sql codes for several tables in my db.
CREATE TABLE "buh"."campaign" (
"id" SERIAL NOT NULL,
...
4
votes
1answer
29 views
PostgreSQL join subquery can't restrict query
I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances.
I'm trying to run a query ...
-1
votes
0answers
9 views
PostgreSQL Crash Could not write to file “pg_subtrans/3009” at offset 172032
database crash after restore backup space is full . I'm trying to drop that database which i restore but have an error Could not write to file "pg_subtrans/3009" at offset 172032: No space left on ...
0
votes
1answer
13 views
postgres full text search modifies ts_query
I have following query:
select
vec.vec,
vec @@ to_tsquery($$'fabr':*A$$), -- true
to_tsquery($$'fabr':*A$$), -- fabr
vec @@ to_tsquery($$'fabry':*A$$), -- false
...
1
vote
0answers
28 views
Function for matching email address returns full table of emails
I am using Postgres 9.4.6.
I have created a Function for finding email address that match an email variable that is passed. However, when I search for '[email protected]' I get a full table of email ...
0
votes
0answers
36 views
Knowing the most juicy client!
Good evening!
I'm studying business informatics in Paris and I just created an amazing database that will help the supermarket down my street to win the loyalty of its clients. We want to know, ...
0
votes
1answer
26 views
Set values conditionally in Postgres UPSERT
We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like
if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1
...
0
votes
0answers
19 views
Is Mongodb a smart decision for a social network? [on hold]
So this question is more than a bit broad , I would appreciate input from those with experience working on social networking. I have been questioned many times why I have chosen MongoDB for a social ...
1
vote
0answers
34 views
Replicate Postgres 9.2 to 9.4 with no downtime
My current set up has a database running on Postgres 9.2.4 and would like to move from AWS EC2 to RDS. However, AWS requires Postgres be running at least Postgres 9.4 in order to perform this ...
0
votes
1answer
19 views
How to duplicate the database in postgresql?
I need to duplicate the existing database including its schema and structure to another new database. I need this in shell command environment and not in pgadmin. Please kindly help me.
nohup pg_dump ...
0
votes
0answers
10 views
How to upgrade PostgreSQL 7.3 to 9.3?
I want to upgrade the PostgreSQL database from 7.3 to 9.3. Please help me how to perform the upgrade.
1
vote
0answers
22 views
Materialized Views and Indexes
Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
3
votes
1answer
15 views
Sort table rows and save the row numbers using UPDATE
I have Postgres 9.5 with a table movimientos that has the following data:
| id | concepto | movimiento | numero | orden |
| 1 | AJUSTE 1 | 2542 | 0 | 2 |
| 2 | APERTURA | 12541 ...
1
vote
1answer
16 views
Add variable value in single quoted string. Expand variable within single quotes
I have tried different variations of the below commands but non work.
I simply want to run the following command using a variable within the location string:
CREATE TABLESPACE data OWNER appuser
...
0
votes
1answer
32 views
Who deleted a record?
Is it possible to audit a Postgresql 9.4.5 database in such way that I could find when a specific record was deleted? I don't know what do I need to have enabled / available for such
0
votes
0answers
12 views
Track updated columns postgresql trigger?
I am using postgreSQL and I have tables on which I use triggers to get notified of changes on tables.
Now, I have a usecase where when an update on a table is done, I want to notify only the updated ...
0
votes
2answers
26 views
Index for column with two possible value
I use PostgreSQL 9.4.
I have a database where a table have index in column which can contains two possible value only(gender: male/female). When I execute a query with condition on this column index ...
0
votes
1answer
15 views
Postgres Error: could not read block 167593 in file… SQL state: XX001
When creating a new column on a table, I'm getting the following error:
ERROR: could not read block 167593 in file "pg_tblspc/16384/PG_9.4_201409291/16386/157223695.1": read only 0 of 8192 bytes
SQL ...
0
votes
0answers
15 views
pgpool: buffer overflow detected
My pgpool(use docker create) met this buffer overflow detected :
And It generate many core file in server which is very big (about 6MB)
Why would this happened?
I try google it ,but there is seldom ...
0
votes
0answers
40 views
How to schedule the daily backup of a specific database in PostgreSQL?
I have a database 'ahrims', I want to schedule PostgreSQL under linux to backup this specific database (not all databases) at 4:00 PM daily.
How can I achieve this?
Please help!
5
votes
2answers
47 views
Postgres Update Limit
I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID.
For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
1
vote
1answer
33 views
Can we export data from PostgreSQL to xlsx file?
I want to export the result of a query to xlsx file format.
I know we can export it to csv file, but I want to export it to xlsx file.
Like the following:
copy(select name_related from ...
2
votes
1answer
25 views
How to append datetime in filename in postgresql
How to append datetime in filename in below command:
COPY (select query) To '/tmp/test.csv' DELIMITER ',' CSV HEADER;
I want my filename like test_date_tim.csv.
1
vote
1answer
26 views
How to select redundant rows
Suppose a table data recording values of some foo and bar over time:
time | foo | bar
-----+-----+----
1 | a | a
2 | a | a
3 | a | a
4 | B | a
5 | B | a
6 | a | a
7 | ...
1
vote
1answer
13 views
I have a query which does not return result with the desired alias names of columns in PostgreSQL
The following is my query which does not return result with the desired alias column names:
with d as (select distinct emp.ahrims_id, rank.name as rank
from hr_employee as emp, ana_rank as rank,
...
0
votes
2answers
29 views
Slow query / Indexes creation (PostgreSQL 9.2)
I have the following Query:
explain analyze
SELECT split_part(full_path, '/', 4)::INT AS account_id,
split_part(full_path, '/', 6)::INT AS note_id,
split_part(full_path, '/', 9)::TEXT ...
5
votes
1answer
34 views
+50
Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?
I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ...
0
votes
0answers
15 views
Time Series on Postgresql
I am developing a software that have a dataset time series like this:
id_equip, Timestamp, Double v1, ..., Double v30
The size estimate is 5.000.000 rows by year and the time of response is very ...
1
vote
1answer
19 views
How to set up Amazon RDS parameter group for Postgres?
I'm trying to create a parameter group in Amazon Web Services for an Postgres 9.5 RDS instance. I'm a little confused by the units being used for things such as shared_buffers and min_wal_size. ...
0
votes
2answers
15 views
postgresql - how to find and extract text from a description column
I'm trying to create a new table using data from an existing.
I have the following structure:
Table "public.def"
Column | Type ...
0
votes
1answer
20 views
Find unused indexes
I'm using the following query to find unused indexes:
SELECT
PSUI.indexrelid::regclass AS IndexName
,PSUI.relid::regclass AS TableName
FROM pg_stat_user_indexes AS PSUI
JOIN pg_index AS PI
ON ...
0
votes
0answers
8 views
outdated stats or missing records?
I'm using the following query go gather index usage statistics:
SELECT
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS ...
0
votes
0answers
19 views
Cannot drop constraint due to FK references
I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the ...
0
votes
1answer
8 views
Missing / Orphan Indexes?
In the process of finding duplicate indexes, I've been using the following query to identify them:
SELECT
indrelid::regclass AS TableName
,array_agg(indexrelid::regclass) AS Indexes
FROM pg_index
...
2
votes
1answer
26 views
PostgreSQL won't take new timezone
In Venezuela a timezone change have been undertaken on May 1st 2016. The old offset for the Venezuela timezone was -04:30 and the new one is -04:00.
All database servers have been updated with the ...
0
votes
2answers
17 views
Is there a way in PostgreSQL to set an default owner for all future create table statements in a session
We have a program that transfers cobol data (index files) to a database.
Until now we transferred the data to an oracle database.
In the beginning of the program I logged in with user sys and ...
1
vote
2answers
43 views
How to ensure that only unique row combinations are present?
I have three tables which can be called set, item and assignment. The assignment table assigns an item to a set, thereby my goal is to ensure that each combination of items is unique. Hence, if item A ...
0
votes
2answers
12 views
How can I sum values of one columns based on the distinctness of another column in PostgreSQL?
I have a table me
id | name | type
----+--------+------
1 | Ali | a
1 | Sami | a
3 | Kamil | c
3 | Imran | c
2 | Wali | b
4 | Yousuf | d
5 | Kamran | e
...
2
votes
0answers
6 views
In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?
I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...
0
votes
0answers
20 views
Creating mini postgresql dump
My production postgresql database is about 70GB now .
so i want to create a mini database dump from it so i could restore it on the development environment for testing purposes .
any ideas ?
1
vote
2answers
24 views
Optimizing PostgreSQL with Index
I'm having a hard time trying to get PostgreSQL to use an index that I created. I understand that PostgreSQL is smart enough to determine when an index should be used because in most cases a seq scan ...
0
votes
1answer
10 views
What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?
I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the ...
1
vote
1answer
22 views
index creation - Slow Query - PostgreSQL 9.2
I've got the following query:
SELECT
* FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id,
split_part(full_path, '/', 6)::INT AS note_id,
split_part(full_path, '/', 9)::TEXT ...
1
vote
1answer
28 views
Duplicate key value violates a unique constraint
I've been looking at other posts. It seems many people have had this problem, but I'm somewhat new to Postgres and tried other solutions with no luck.
I have ny_stations table:
CREATE TABLE ...
1
vote
1answer
36 views
How can I dump all tables to CSV for a PostgreSQL schema?
I have a database with a lot of schemas in it and I want to dump the each of the table contents to CSV. I'm aware of the COPY command but I'm not sure how to script something that will read all of ...
1
vote
0answers
13 views
Profiling postgres with gprof: no data/gprof folder and 'no time accumulated'
I'm attempting to profile Postgres with gprof. A gmon.out file successfully gets created in both the data directory and in the working directory where I launched the client (pgbench) process from.
...
