All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
1answer
17 views
How to select one column by two different names in the same query?
I have a table named Main that contains two fields id,name.
I want to select the name column by two different names.
For ex:
Table:
Main-> id:int, name:varchar(20)
I have inserted the following ...
0
votes
0answers
16 views
postgresql wildcard searches using parameters in function that uses dynamic sql
What is the proper way to implement a wildcard search in PostgreSQL when using a parameter in a function that uses dynamic sql?
As a starting point, here is an example from Erwin Brandstetter ...
0
votes
1answer
19 views
postgresql function index not being used by query
I am trying to match a text field so I created an md5 function index as it suggested since a full index was not allowed because the text data was too large. But I can't understand why postgres is not ...
2
votes
0answers
26 views
Order by index unexpectedly slowing query down
I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query:
SELECT "houses".* FROM ...
1
vote
1answer
13 views
Postgres restore from WAL-E backup never finishes
Doing a restore on a WAL-E backup to S3, postgres never seems to complete the restore process. The log repeats the following:
< 2016-04-08 13:41:55.801 PDT >LOG: connection received: ...
0
votes
0answers
15 views
Store enum in database
I have a database for a job advertisement web application for an assignment at university.
I have a lot of enumeration values in database such as names of the cities, categories of the advertisement ...
0
votes
1answer
15 views
Shipping big WAL archives to hot standby
I'm looking to extend my PostgreSQL 9.4 master with a few slaves in hot standby read-only for load balancing.
The idea would be to update the slaves only at defined times (once every 24/48 hours) to ...
0
votes
0answers
10 views
Find missing associations in many to many relationship [duplicate]
I have client and category tables with a many to many relationship via client_category_association.
table: client
id name
1 ek
2 do
table: category
id name
1 un
2 dos
3 tres
table: ...
1
vote
1answer
21 views
Finding similar rows by comparing similarity of tsvector
I initially posted this in the main SO page but it hasn't gathered much interest so I thought it may be better suited in here.
So! I'm needing some help getting the SQL to work here in PostgreSQL ...
5
votes
1answer
45 views
Looking for a simpler alternative to a recursive query
The actual query is more involved, but the problem I'm facing can be distilled to this:
A query to filter a rowset of monotonically increasing integers so that - in the final result set, ...
-1
votes
0answers
24 views
PostgreSQL vs UNIX regular expressions [on hold]
What is the difference between * and + and the difference
between Postgres regular expression and UNIX regular expression?
I have also tried the * and + in the UNIX using sed and grep command, but I ...
0
votes
0answers
10 views
postgres vacuuming query: value of datfrozenxid increased after vacuuming
I checked the datfrozenxid values, and performed "vacuumdb --analyze -a", Normally postgres, template1 values are very high and they return to around 1,000,000,000 after vacuum. But I found something ...
1
vote
1answer
24 views
Storing blobs outside the database
I had a PostgreSQL database with loads of scanned documents, as a document bytea column in the table scans, with hundreds of thousands of documents that was large and inconvenient to backup. Also, ...
0
votes
0answers
34 views
Tomcat not reconnecting JDBC pools on DB restart [on hold]
Recently we had to restart our database server (yes, a production one), and one of the Tomcats didn't reconnect to the JNDI resources.
This is our JNDI Resource definition:
<Resource ...
1
vote
0answers
15 views
postgres wal sender replication timeout during pg_basebackup
Let me start with the caveat that I am still green with Postgres.
I am working on a postgres 9.2 Active/Standby cluster on Debian wheezy for an application, based off of the ClusterLabs pgsql cluster ...
1
vote
2answers
36 views
UPDATE from subquery updates rows that shouldn't be updated
Using PostgreSQL, I want to update a column. The information how to update is in another table.
My try was this, but it also updates rows I don't want to update:
update table_1
set column_x = (
...
0
votes
0answers
41 views
Basic PostgreSQL 9.x Performance Optimization Strategies for Reads/Analysis [on hold]
Postgres comes, out of the box, configured as a general purpose database for both OLTP and OLAP types of loads. My need is to do use Postgres, in this case, purely as an OLAP running aggregate queries ...
1
vote
1answer
26 views
Get total size for all Postgres or Greenplum tables including partitions
Is there an easy way to get the total size of all tables including partitions? I've written this query for Greenplum, but it's a bit round-a-bout:
with part_sz as (
select ts.sotdschemaname sch
...
1
vote
0answers
33 views
pg_restore slow after adding a check function
I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records.
The way I did this was to remove the foreign key on some tables in ...
3
votes
2answers
62 views
UPSERT with ON CONFLICT using values from source table in the UPDATE part
Given:
CREATE TABLE A (
PK_A INT8 NOT NULL,
A INT8,
PRIMARY KEY (PK_A)
);
CREATE TABLE B (
PK_B INT8 NOT NULL,
B INT8,
PRIMARY KEY (PK_B)
);
This query:
insert into table_b (pk_b, b)
select ...
1
vote
1answer
29 views
How to copy a Postgres database within the same DB cluster?
I want to replicate a Postgres database without dumping data using pg_dump. Both source and target database are on the same machine (Test and Production server).
I would like to create a ...
0
votes
1answer
32 views
BIG Binary column - PostgreSQL 9.2
I've got the following table:
CREATE TABLE gorfs.inode_segments
(
st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key ...
1
vote
0answers
14 views
pypl store function to update a record is not working
I am using PostgreSQL 9.4.7 and Python 2.7.6. I am writing a plpython function to update a row in user table and my code is as below:
CREATE FUNCTION update_user(myid int, mymail text, myname text) ...
0
votes
1answer
24 views
How to insert values into a table with default values from a select query in PostgreSQL?
Is it possible to INSERT values into a PostgreSQL table from a SELECT statement and to use DEFAULT values for the columns that are null?
In my case, the SELECT statement is selecting from JSON.
In ...
2
votes
1answer
45 views
Grouping data by year based on two timestamp columns
My data table has the following columns:
id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP
I want to product a report of the count of each name (which can appear several times in the ...
1
vote
1answer
35 views
Convert Postgres TIMESTAMP to TIMESTAMPTZ
I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller.
Stupidly, when I initially designed ...
1
vote
1answer
75 views
Postgres sharding installation with citusdb
We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end.
We are using a 16*2.4 GHz processors with ...
0
votes
0answers
28 views
template1 database being accessed - PostgreSQL 9.2
I'm currently using postgres 9.2 and as you can see below, the "template1" database was being accessed:
The server is a 4-day-old backup DB - does a gzip of pg_dump, excluding some tables; also ...
0
votes
1answer
33 views
“Unrecognized configuration parameter: qreplace_function” trying to backup a database
I am new to PostgreSQL and I am trying to backup a database with the pg_dump utility.
I am using this command:
/opt/PostgresPlus/9.4AS/bin/pg_dump -U (username) -f ...
0
votes
1answer
33 views
How to check if a postgresql backup is finished or not? [closed]
I started my backup yesterday, but now I don't know if it is finished or not.
Can you please help me?
3
votes
1answer
60 views
DELETE rows which are not referenced in other table
I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group.
I want to delete all rows from link_group where no related row in ...
-1
votes
0answers
23 views
How can i export a file from the database? [closed]
I'm using PHP with PostgreSQL, and I need to export the file's content of a register in database.
The image show the registers and the column that save the file reference
The name of table is ...
0
votes
0answers
39 views
When to reindex postgres index?
I want to know if I need to reindex a PostgreSql index, ith SQL Server, I check for index fragmentation and then decides whether a Rebuild, a Reorganize or no action is needed.
When should I reindex ...
-1
votes
0answers
25 views
How to drop postgresql database owned by other user [closed]
Logging in as posgres i created a database for a user using:
CREATE ROLE tester LOGIN password 'apassword';
CREATE DATABASE new_db ENCODING 'UTF8' OWNER tester;
to drop I tried:
DROP OWNED BY ...
1
vote
1answer
29 views
regexp_replace() vs. replace() to replace all matches in a string
By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior ...
2
votes
2answers
91 views
Eliminate duplicates and optimize performance for big count and small LIMIT
I have a big query in my Postgres 9.1 database:
SELECT *,
count(*) OVER () AS full_count
FROM (
SELECT DISTINCT ON(message.messagetime,message.messageid) message.messagetime,
...
0
votes
0answers
25 views
Why does Postgresql not write the username to the log file
I am trying to understand why, my database is logging without username in the queries.
I discovered today that I can't see by looking at the logs who did a query.
I think it is a configuration ...
1
vote
1answer
39 views
Structure for storing table data, lists, text, pictures within PostgreSQL JSON field
We are extracting content sections from word documents and have been going back-and-forth on how to store this arbitrary data. We are using PostgreSQL and our plan was to use JSON fields to save ...
1
vote
1answer
43 views
Moving data from PostgreSQL 32bit to 64bit
I use Synology's DS1515+ as a PostgreSQL (PG) DB server. Last week Synology upgraded their DSM (Linus based OS) to version 6.0. Doing so, they've upgraded their PG to 64bit and potentially to a newer ...
0
votes
1answer
37 views
Postgresql sequence always 1 [closed]
I have an application that often does insertion into PostgreSQL database, for some tables I have id column with serial type with auto-generated sequences. Running
SELECT ...
0
votes
1answer
32 views
Need help designing a database (beginner) [closed]
Thanks in advance for taking the time to read this somewhat long post.
I am planning to create a website for math education. It will present concepts in a pedagogic and easy-to-follow manner. I am ...
-2
votes
0answers
21 views
I don't have delete option under my question in stackexchange [migrated]
How to have delete option under my question in stackexchange?
2
votes
0answers
36 views
insert/update from web server or database
I have a data-model on the front-end that I want to insert/update in the database which of the following approaches (or neither!) is considered best practice:
# in webserver
some_model = {json}
...
0
votes
0answers
22 views
Database design for efficient read queries given lots of writes
I have a PostgreSQL database, and an application which receives around 200 requests per second. Each of those requests contains a message with an identifier representing a journey id, a timestamp, ...
0
votes
0answers
33 views
Pooling at the app layer or in an intermediate layer?
I need to use a connection pool with PostgreSQL. I do not need load balancing or replication, just pooling.
What would be the best option? Pooling at the app layer (using a JDBC connection pool) or ...
1
vote
1answer
76 views
How to give title to the result of a select query in postgresql?
For example I want to do like this:
select id,name from first;
It should look like this:
The Query Result // this should be the title of the query
id name
1 Ali
2 Sami
1
vote
1answer
30 views
Read only postgres table, except by trigger
Suppose the following situation:
I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock ...
1
vote
1answer
57 views
Select column values linked to exact set of values in another column
Given the below chats_users table in Postgres 9.5, with integer columns chat_id and user_id, I want to find the chat_id associated with an exact set of user_ids, for example:
user_id IN (1, 3) => ...
1
vote
1answer
29 views
Started using UUID's as primary key, would like to execute union of entities with BigSerial Primary Key
Some of my newer tables are now using UUID type fields as PKey and older tables are still using bigserial. I have several tables (let's call them entities) I would like to query returning the primary ...
3
votes
1answer
29 views
Does updating a jsonb column trigger updates on all expression indexes on that column?
I am using the jsonb type in PostgreSQL. I frequently make updates of the following kind (where jdoc is a jsonb column):
UPDATE my_table SET jdoc = jdoc || '{a: "Hello World!"}'::jsonb WHERE id = 123
...
