Questions tagged [postgresql]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
103 views

PostgreSQL Group by to create a new group whenever column value changes

I have a table for reservations as follows: ------------------------- | dt | reserved | ------------------------- | 2017-01-01 | 10 | ------------------------- | 2017-01-02 | 10 | ...
0
votes
1answer
490 views

Postgres won't use btree index in left-anchored LIKE query [duplicate]

Using Postgres 9.5. The Postgres documentation says that the btree index can be used for left-anchored "like" queries (i.e. "%" only at end). But my left-anchored "like" queries on a 1 billion row ...
0
votes
2answers
108 views

Modelling users and groups - but with multiple types of groups

I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them). However, its seems I'm dealing ...
0
votes
1answer
62 views

Optimize query with multiple IN statements

So I have a large query which I have been trying to optimize as over time it has gotten fairly slow (~116k records taking a few seconds to get a result). The context is a social network feed where ...
0
votes
1answer
356 views

Deleting old files in /var/lib/postgresql/9.x/main after changing postgresql data_directory

Very recently, I changed the data_directory of posgresql (9.3.10) to point to a new data disk I recently added to my VM (Ubuntu 14.04 OS). All the old data has been transferred to this new disk as ...
0
votes
1answer
540 views

change server date postgresql [closed]

I have a postgresql db (v9.4) and its default year is 2026. Is there any way I can modify it? I have read about modifying the postgresql.conf file, but I have just found the timezone property.
0
votes
2answers
192 views

Postgres 40001 Exception Conditional Insert

I have 3 tables, A,B and C. A function actOnABC(i) which is a read only operation on the tables: IF actOnABC(i) = 0 THEN RAISE EXCEPTION 'Message'; ELSE INSERT INTO TABLE B VALUES(...); END ...
0
votes
1answer
176 views

postgres PITR from WAL without changes

I'm trying to do Pont In Time Recovery from wal archive using postgres 9.6 and i got question: when i'm doing recovery procedure first time everything went ok, but when i'm doing some changes in ...
0
votes
1answer
28 views

Selecting union of results of dataset-producing function when applied element-wise to an array

This problem is somewhat an academic curiosity. I'm attempting to implement a SQL (i.e. not plpgSQL) function which takes an array of input data, transforms each entry to a set of zero or more rows ...
0
votes
0answers
296 views

Comparing explain plains and execution time

Please consider the following 2 queries (which yield the same result), and their corresponding explain plan output: Query A: select max(registro) from telemetria_data.historico_353871042915279 ...
0
votes
0answers
38 views

why slave is recoveryng old wal?

Everyone probably seen 100% CPU while slave recovers a WAL with lots of deletes on instance with big shared_buffers. To avoid it I usually chase sync with small shared_buffers (it reads a day of WALs ...
0
votes
1answer
49 views

Concurrent usage of rdbms, write 4K rows without lockdown [closed]

I am working on a django project using Postgresql. The use case is as follows: A user needs to upload an excel (containing 2.5-4K rows) 2-3 times daily. There will be 100s of users (somewhere between ...
0
votes
1answer
151 views

postgres 9.3. resets log_min_duration_statement after receiving SIGHUP

I'm using PostgreSQL-server 9.3.5. After setting log_min_duration_statement to 1000ms I'm sending SIGHUP and see that server resets value to '-1': -bash-4.1$ psql psql (9.3.5) Type "help" for help. ...
0
votes
2answers
602 views

Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL)

Loan table associated to due table. also Invoice table associated to due table. In due table either one of them foreign key should present and other one should be null. What kind of constraint ...
0
votes
3answers
54 views

Help to improve a function

I have this function working fine: CREATE or replace FUNCTION pointscanner( IN source integer, IN target integer, IN k integer, IN directed boolean, IN quant integer ) RETURNS ...
0
votes
1answer
640 views

What is the key to pg_restore?

We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore. We are ...
0
votes
0answers
697 views

streaming replication same wal missing

Several days ago I saw this in master log: replica 2017-02-06 16:10:55 UTC ERROR: requested WAL segment 000000030000096D00000052 has already been removed This message was repeating untill I ...
0
votes
0answers
93 views

Performance of table locks vs exclude constraints

Suppose I have a system for reserving vacation properties. One of the tables is reservations. Each reservation has a property_id, a start_date, and an end_date. I want to prevent conflicting ...
0
votes
0answers
525 views

COPY TO file in Postgres fails because of write permissions

I'm using Postgres 9.3 in a CentOS 7 and another in CentOS 6 server and the copy table to /path/file command fails. I receive an error message that tells that I don't have write permissions. The ...
0
votes
1answer
277 views

postgresql query hangs indefinitely over VPN - new firewall

We have a product where a local linux machine "SCANNER" does some polling on a local network and then stuffs information into a database in our cloud server over an OpenVPN connection, using pg_query (...
0
votes
1answer
522 views

Find first-time purchases with certain category

I'm trying to find the first purchase of a customer who buys a computer category table only within all time purchase to segment them using SQL. My trial is. The JOIN part worked by someone else, so I ...
0
votes
1answer
45 views

How make my architecture scalable to handle linear process

I have a system to calculate Real Time Traffic (RTT), using Automatic Vehicle Location (AVL). I can know where the car was, where is now, and calculating distance and time can estimate the traffic ...
0
votes
1answer
37 views

PostGIS: how to retrieve a report of costs by decades from a table

I have a table with a list of 'films' and, for each film, information about the 'company' that produced, the 'cost' of the production and the 'year' of production. I want to consult the 'total_cost' ...
0
votes
1answer
187 views

Efficient sensor data storage

So, I have a task to produce design to store data retrieved from sensors, like temperature, pressure, roll etc. First prototype was quite simple, I created TimeSync table consisting of 2 columns: ID ...
0
votes
0answers
33 views

Tool to administer Postgres user rights?

I think it is incredibly tedious to administer user rights in Postgres. If I for example want to give editing access to an updatable view, then I need to ensure that the user has the right accesses to,...
0
votes
1answer
84 views

Can Postgres inherited tables overlap

Is it possible to have single row in a parent table be visible in more than one inherited (child) table? Perhaps by inserting a row directly into the master table and then "making it visible" in one ...
0
votes
0answers
59 views

What index type to use for a log

I have a table in which I will store messages (as in chat messages). The table will be appended to a LOT, rows being inserted as messages fly by, much more often than entries will be updated. Older ...
0
votes
1answer
48 views

re-write active record query to PostgreSQL

I want to rewrite the active record query to Actual PostgreSQL query Camera.joins("left JOIN users on cameras.owner_id = users.id") .joins("left JOIN vendor_models vm on cameras....
0
votes
2answers
785 views

fetching Remote table data and storing into local dump file in pgSQL? [duplicate]

I want to copy one table's content from a remote machine to local. I have tried with psql Command \copy. It's working fine as "copy" is not working due to copying from remote. But is there any way I ...
0
votes
1answer
171 views

Migrating PostgreSQL 9.1 database to Ubuntu 14.04

I want to migrate a database from PostgreSQL 9.1 installed on an old Debian system to Ubuntu 14.04. The database is making use of triggers and it is a requirement that the triggers be migrated as well,...
0
votes
0answers
45 views

Splitting database based on country with shares users

99.9% of queries will be filtered for a specific country (either USA or Canada for now), so I would like to split the database country. This way, a query would not have have to step over all the rows ...
0
votes
2answers
42 views

How to write the following trigger

I am writing a trigger that needs to raise an exception if a condition doesn't hold. That condition that should result in a raise is that the current row being modified has a field enrolled that is ...
0
votes
0answers
106 views

Jdbc3SimpleDataSource vs. PGSimpleDataSource. What's the difference?

PostgreSQL's JDBC 4.2 driver includes classes PGSimpleDataSource and Jdbc3SimpleDataSource. The latter extends the former, but there's no difference in their APIs. And I find that both provide the ...
0
votes
1answer
218 views

Slony-I replication stopped working

I inherited a 5 node postgres cluster running postgres 8.4 and slony 1.2.21. We are in the process of migrating the application to all new code and have not wanted to do very little maintenance on the ...
0
votes
1answer
1k views

Error Converting In OpenQuery

I keep getting an error of Conversion failed when converting date and/or time from character string. when I run my query. How should this be altered in order for the query to execute succesfully? ...
0
votes
1answer
152 views

How do I easily create the DDL statements for a schema represented by the contents of an XML file?

I have been given an xml file and it's pretty obvious in looking at the contents that it contains an inherent schema. What I'd like to do is run a program against it that outputs the schema ...
0
votes
0answers
41 views

How to get the maximum amount of memory (RAM) consumed by a single query?

I know this topic is pretty complex and involves a lot of different factors. Let's say I have several similar queries running at the same time. These queries involves only read operation and several ...
0
votes
0answers
16 views

Table update in a lost past no log

I am trying to figure out how to find some information about a table that received a update. The database has logs, but it is a old operation (more than 5 months, and we store only 3 months of log). ...
0
votes
0answers
331 views

Is there any way to set the alias for command permanently in PostgreSQL?

I'm trying to set the alias for command permanently in PostgreSQL. I've set the alias for a command like given below. Test=> \set x '\\! reset' So, the alias has set for reset the terminal. While ...
0
votes
1answer
24 views

get result of search in database postgresql and work with it every time

I have a query who spend alot of time because there is thousand of ligne in the database, so I need if it's exist a method to put the result of the query some where and work with them the time i want. ...
0
votes
1answer
118 views

psqlODBC access encoding problem

My local machine: Windows 7, 32-bit. I have installed on it the psqlODBC for 32-bit. MS Access is 32-bit as well. I have tried both Unicode and Ansi connections to... The server: a 64-bit Ubuntu, and ...
0
votes
0answers
39 views

postgresql streaming replication

My environment consists of two RHEL7 nodes such as following: # cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.2 (Maipo) # uname -a Linux X 3.10.0-327.36.2.el7.x86_64 #1 SMP Tue ...
0
votes
0answers
699 views

How to prevent UPDATE on any record in a table, but allow INSERT and DELETE (PostgreSQL)?

How do I prevent UPDATEs on any record in a give table, but allow everything else? Must I use a trigger? I am using PostgreSQL.
0
votes
1answer
219 views

Using `nextval` and `currval` inside one CREATE TABLE

Let's say that we would like to model a team of people that can have subteams like so: CREATE SEQUENCE seq_teams; CREATE TABLE teams ( tid INTEGER PRIMARY KEY DEFAULT nextval('seq_teams'), ...
0
votes
1answer
673 views

In postgresql binary replication, indexes are replicated or not? And if i dont like it?

I want to know if indexes are replicated in a binary replication in postgresql. And if it is, how can i disable this behavior? So if slave becomes master i can start to index it using reindex?
0
votes
1answer
715 views

How can I redirect database requests in my postgres server

So I have a list of(lot many) scrapers which push lot many data into one of my database location. Unfortunately I have a requirement that my database location have to be changed for some performance ...
0
votes
1answer
175 views

Exclude certain table data for all non public schemas (tenants)

I have tried running the pg_dump command with the following flag: --exclude-table-data foo_bar However it seems this flag is ignored completely if the public schema does not contain a foo_bar table. ...
0
votes
1answer
36 views

Get rows that are referenced in another table

I have two entities. product and bom. CREATE TABLE product ( id integer NOT NULL, CONSTRAINT product_pkey PRIMARY KEY (id) ) /* Bill of materials*/ CREATE TABLE bom ( id serial NOT NULL, ...
0
votes
1answer
200 views

PostgreSQL exits the shell when importing large data

I'm having this totally weird scenario: We have an archiving server based on PostgreSQL inside a Scientific Linux virtual server. I want to migrate the data (20+ GB) to a physical Scientific Linux ...
0
votes
1answer
80 views

Connect to postgres with different privileges [closed]

From python program, I want to connect to postgres with various privileges. lower_privileges = connect(db, username, password) higher_privileges = connect(db, username, password) How do I setup ...