Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql]

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

2
votes
1answer
35 views

One vs. Many Tables for Frequently but Asynchronously Updated Columns?

As part of my Postgres 10 schema, I have two tables; current and historic -- both have the same columns. current has about 20 (+/-) columns, and holds the most up-to-date information for accounts. I ...
0
votes
4answers
121 views

How to tell if a project needs a NoSQL database solution? [closed]

How to tell if a project needs a NoSQL database solution? I think that SQL limits define it. If the project needs bigger database or bigger fields then it needs NoSQL. Am I right? I looked into ...
1
vote
1answer
26 views

Explore postgres recovery log records

I simulated a crash in Postgres in middle of a transaction by force closing the Postgres process, after running again the Postgres the data that was changed was recovered to last data before ...
1
vote
1answer
28 views

Using column alias in a WHERE clause doesn't work

Given a table users with two fields: id and email. select id, email as electronic_mail from ( select id, email from users ) t where electronic_mail = '' Postgres complains that: ...
0
votes
2answers
28 views

How to verify SSL always being used on Postgresql 9.6

I have a sensitive application with app server and db on separate machines, and in the case of the slave db, in separate data-centers. Although I believe my postgresqls are configured to always use ...
0
votes
2answers
34 views

Is there a way to automatically update a view based on daily created tables?

Let's say I have a table that is created daily: 20181227 20181226 20181225 ... There is view that is like: CREATE OR REPLACE VIEW someview AS SELECT * FROM 20181227 UNION SELECT * FROM 20181226 ...
1
vote
2answers
43 views

Joining two ltree based tree tables recursively

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
3
votes
3answers
865 views

Database Connection vs Raw TCP Connection [closed]

I have some fundamental questions about how database clients & database interact Do databases support multiple transactions simultaneously on a single database connection from client? If not, why ...
0
votes
0answers
19 views

How to add a domain user to windows version postgresql?

I have a windows server 2012R2 (domain member) and a postgresql 9.3 which is installed on it. Now, I want to add a domain user to the postgresql, but I can't find any solution. Any help would be ...
0
votes
1answer
20 views

slow autovacuum on table with binary data

I have a table with about 4M rows, but each row has some binary data that may consume up to 1Mb. The autovacuum process on this table is taking over 1.5 hours to complete, which seems way to long to ...
0
votes
0answers
32 views

Insert max numeric value in Postgres column

Suppose I have a table like CREATE TABLE public.products ( id INTEGER NOT NULL article CHARACTER VARYING(255) name CHARACTER VARYING(255) quantity NUMERIC(10, 3) ) As you ...
0
votes
0answers
25 views

Create new table with unique foreign key or Nullable columns for one to zero/one relation? Better design?

I am newer to database design, and was hoping I could get some input on a concept I have not seen that much discussion on when I look it up. I am using Postgres 10. Basically, I have a User table ...
-2
votes
1answer
29 views

Fetching Records from to table with many to many relationship

Dear folks, I need a query the scenario is on the attached screen shot. thank in advance
0
votes
1answer
23 views

Getting started with query tuning

I tried explain analyze in postgres to find below plan. I have index in all of column with join but still the query is slow. How can I optimize the query? ---------------------------------------------...
0
votes
0answers
39 views

Postgresql Replica is in sync but repmgr says node unattached

I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't ...
0
votes
1answer
40 views

Query to get all schemas in a database in PostgreSQL?

I'm not looking for the command \dn of psql, but instead of a query in SQL language that selects all schemas in a given database.
0
votes
0answers
21 views

no performance improvement with pgpool query result caching

I'm trying to add query result caching to a PostGIS database that's frequently hit with the same (expensive) spatial queries. Looks like pgpool-II is currently the only well-developed solution. ...
2
votes
3answers
54 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 ...
0
votes
1answer
35 views

How do I modify a JSON object?

I have a table "raw_data" in which I have a field "data" containing a json array like: {'a' : 1, 'b' : 2, 'c' : 3} I would like two different things: Append a new item like {'d' : 4} at the end of ...
-1
votes
1answer
26 views

Database volume completely full

/dev/mapper/pgdatavg-base 138304552 131262788 16384 100% /var/lib/pgsql/data/base pg_xlog is on a separate volume, and it's got plenty of space. [postgres@fpslbxmbstgl05 ...
2
votes
0answers
16 views

Does VACUUM FULL ANALYZE a partition locks the main table in postgresql?

I'm using postgresql 9.5 and partman for partitioning tables. there is this table that has 30 partitions and volume of one of them are too higher than others. so I need to full vacuum this partition ...
1
vote
0answers
20 views

Which client authentication methods make sense when calling `initdb`?

Postgres offers many client authentication methods: Trust Authentication Password Authentication GSSAPI Authentication SSPI Authentication Ident Authentication Peer Authentication LDAP Authentication ...
3
votes
1answer
30 views

Postgresql: delete all comments from database

I have a database for which the comments on objects (PSQL command COMMENT) are just pollution for us: they are obsolete and in a language that no one reads in our organisation. Hence, I'd like to ...
0
votes
1answer
26 views

setting a default collation for a postgresql database

I have created a database with Turkish collation. createdb -l tr_TR test_tr -E LATIN5 -T template0 Then a table with a row. \c test_tr create table turkish(one text); insert into turkish values(...
2
votes
1answer
34 views

PostgreSQL: Issue with a corrupted GiST index on ll_to_earth

I'm facing something that seems like an index corruption issue on a table that stores about 120k entries. Each entry has a latlon and this latlon is indexed using the ll_to_earth GiST implementation: ...
0
votes
1answer
28 views

Postgresql responses are slow without internet

I have a Postgres service in Centos 7 and it works normally. Each time when the internet service (by an IPS) is down, the responses are slow, other times the DNS is reset by an electrical trouble, ...
2
votes
1answer
47 views

How to store data that depends on dynamic number of parameters

I can't get my head around this problem. I'm trying to find a good way to store this in a PostgreSQL database. Each client of my company needs to visualize data that depends on a dynamic number of ...
0
votes
0answers
20 views

Restore single database (or table-space) using PITR with PostgreSQL?

Is there any possibility restore a single database with WAL files.
1
vote
0answers
20 views

PostgreSQL update and select in one query [duplicate]

I have a table (id, serialnumber, status) of numbers and their statuses, need to take one with status 'unused' and change it's state to something like 'in work'. Can do it this way: UPDATE serials ...
1
vote
1answer
57 views

Equivalent Join Conditions Yield Vastly Different Results

The following query aligns test data with the closest truth data equal to or earlier than the test data for all possible truth_id's. There are very few truth_ids and very few test ids, but each id has ...
2
votes
0answers
28 views

Postgres Replication out of sync

We have master server on one Datacenter, and the Replicate is in another Datacenter Intermittently, on the replicate server, we see this as below. “2018-12-19 07:24:25 UTC: : @: [25775]: [2-1] ...
-1
votes
1answer
25 views

How to use postgresql variable in where clause

# It works! select * from device where device.user_id in (select user_id from user); # It doesn't work! select user_id into test from user; select * from device where device.user_id in test; Why ...
4
votes
1answer
54 views

PostgreSQL How to DEFAULT Partitioned Identity Column?

PostgreSQL 11 What is the best way to generate default values for identity columns on partition tables. E.g CREATE TABLE data.log ( id BIGINT GENERATED ALWAYS AS IDENTITY ...
2
votes
1answer
65 views

“ERROR: timestamp out of range” converting epoch stored as bigint to a timestamp

I'm getting ERROR: timestamp out of range: "1.52701e+15" when trying to convert an epoch stored as a bigint into a timestamp (value is taken from a real database table): select to_timestamp(...
0
votes
1answer
31 views

PostgreSQL functions inside where clause

I have table like this: table a ( id -- p1 text, p2 text, p3 text ) When I write some function, which use this table, I need to write code like this create or replace function ... ...
2
votes
1answer
87 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
1
vote
1answer
38 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 ...
1
vote
1answer
74 views

Does it make sense to create an index on foreign key (uuid) column in Postgres?

Assuming 2 sample tables in Postgres 10.X: CREATE TABLE public.order ( id VARCHAR(36) NOT NULL, ... ) CREATE TABLE public.suborder ( id VARCHAR(36) NOT NULL, order_id VARCHAR(36) NOT ...
0
votes
0answers
15 views

Compare PostgreSQL Execution Plans

PostgreSQL newbie here. I'm trying to optimize a function that returns a table. Essentially it boils down to either creating a temp table and then selecting/joining on that table to return results, or ...
0
votes
1answer
22 views

How to calculate the final value of a row based on another table with costs in PostgreSQL

I'm new to SQL and I have two tables like those in my PostgreSQL database: table 1 - building_specs +-----+---------+---------+--------+ | key | wallsId | floorId | roofId | +-----+---------+--------...
0
votes
0answers
29 views

How to check memory consumption per PG SQL process on Linux

So I tried ps aux but the output seems to make no sense. When I calculate sum for "%MEM" column I get 800%. This doesn't make sense to me. Total on RSS column gives me more then 260 GB which is much ...
0
votes
1answer
18 views

Postgresql createrole permission for user

I created a role with createrole flag, but users in this role trying to create users fail with permissions denied. Do I need to assign the createrole to each user instead of just the role (group)? my ...
5
votes
1answer
49 views

Do BRIN indexes support ENUM types?

BRIN indexes seem useful, but I'm not sure how to use one on an ENUM type. I thought this code would work: CREATE TYPE test_enum AS ENUM ('a', 'b'); CREATE TEMPORARY TABLE my_table ( x test_enum ...
1
vote
1answer
32 views

postgresql - paginate correctly ordering by non-distinct values?

How can I properly page by ordering on a column that could possibly have repeated values? I have a table called posts, which has a column that holds the number of likes of a certain post, called ...
1
vote
1answer
33 views

How to pass an array to a plpgsql function with VARIADIC parameter

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array. I have already defined the function CREATE OR REPLACE FUNCTION mix_table_fields(...
0
votes
1answer
31 views

Copy Table from one MySQL server to another

Is there a way to copy or export a large table from MySQL server to postgres without affecting servers performance. I mean the table is very large and using mysqldump or any select query has bad ...
0
votes
0answers
14 views

Postgresql streaming replication slow on macOS

I am using PostgreSQL 10.1 on MAC on which I am trying to set up streaming replication. I configured both master and slave to be on the same machine. I find the streaming replication lag to be slower ...
2
votes
1answer
60 views

PostgreSQL JSONB update array elements query

Given a table in PostgreSQL 10: CREATE TABLE forms (id serial, form jsonb); INSERT INTO forms (id, form) VALUES ( 1, '"id":"a uuid_v1 here", "widgets": [ {"id":"1", "permissions": ["user1", "...
0
votes
1answer
20 views

Postgresql filtering by value over rolling time period

I am newbie to postgresql and still getting my head around. I have used windows function etc. till date and yes I can make some sense. I have got the following data set: I need to find those ...
1
vote
3answers
44 views

PSQL: create random data sets with the same statistic distrbution as another table

For test purposes, I want to create data sets (tables) where the statistic distribution of the values in the columns is the same as my actual data, but randomized. Example : consider the following ...