Tagged Questions
All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
1answer
15 views
How archive command works in postgres
Just wondering how does this postgres wal archive command works in replication:
rsync -z -a --contimeout=120 --partial-dir=.rsync-partial %p db-slave.sr1.net::primary-wal-archive-share/%f
what does ...
1
vote
0answers
16 views
PostgreSQL PostGIS geopoint within angle range
I'm new to PostGIS and I see there are so many options, it's a bit overwhelming.
I want to simply find all geo points (stored in ISO 6709 format) within a certain angle of a given geopoint.
For ...
0
votes
0answers
27 views
Postgres 9.6.1 Full Text Search dictionaries for most spoken languages
Using Mac OSX Yosemite 10.10.5, I am trying to run full text search operations, such as to_tsvector, to_tsquery, etc and have a need for dictionaries in about 80+ languages.
Postgres seems to only ...
3
votes
1answer
23 views
Should I disable autovacuum on a table while I do a bulk update?
I need to perform a simple update on all rows in a table. The table has 40-50 million rows. Dropping indexes and constraints during the UPDATE results in a massive performance improvement.
But what ...
0
votes
0answers
26 views
Running PostgreSQL on the main Windows network server
I am working with an IT guy for a small business (about 15 employees) who I am not seeing eye-to-eye with, but I could be wrong, so trying to gain some perspective.
Long story short, I need to ...
-1
votes
1answer
14 views
What is the syntax to add a constraint on an already-created table?
PostgreSQL exclusion constraints are documented briefly under
DDL-Constraints
CREATE TABLE .. CONSTRAINT .. EXCLUDE As linked in ddl-constraint
But, the syntax for ALTER TABLE does not mention /...
0
votes
2answers
34 views
Compare an hour between two hour columns
Please help. I am kinda new to this database world. I am using PostgreSQL.
I have a table with defined shifts like this:
CREATE TEMPORARY TABLE shifts AS
SELECT id, start_hour::time, end_hour::time
...
1
vote
0answers
16 views
Vacuum freeze and transaction wrap around functioning
Our server is hosted on AWS and the database is RDS (Postgres). All of a sudden, the database stopped accepting commands. All the queries were taking forever and never completed. I tried killing some ...
0
votes
1answer
19 views
How does trigger file in recovery.conf work?
Can someone explain how the trigger_file in recovery.conf works?
As far as I know the standby server will become the primary server when it detects the trigger_file. But how will the trigger_file be ...
4
votes
3answers
71 views
SQL query to find all parent records where child records are a specific set
An Item has many ItemDetails. An ItemDetail has fields of "type", "value" and "item_id".
I need to find all Items if and only if item has exact ItemDetails which are restricted by some changeable ...
0
votes
2answers
38 views
Query with two LEFT JOIN's is not working as expected
I am using PostgreSQL as my database and I need to write the query that returns:
a user_id from 1st table
sum of every row's amount from 2nd table (where user_id is the same as in the 1st table)
sum ...
3
votes
1answer
24 views
Designing an extensible authentication system
I need to design an authentication system, where different type of authentication schemes may exist. At the end, I should assign a passphrase (access token) to the user.
I have several authentication ...
1
vote
0answers
22 views
Use session variable as reference to session user on PostgreSQL session
I would like to build an authorization system to handle role-based permission within the database itself.
Basically, I want my application user to have different access. The problem of basing this on ...
0
votes
3answers
27 views
Best way to move functions from postgresql to another
I'm not familiar with postgres but I'm sure there has to be a way to source control its functions.
Currently my team adds functions on a postgresql database directly, and when it's time to deploy to ...
4
votes
2answers
28 views
How to create a constraint in Postgresql to prevent unique combination rows?
Imagine you have a simple table:
name | is_active
----------------
A | 0
A | 0
B | 0
C | 1
... | ...
I need to create a special unique constraint which fails on following situation:
...
1
vote
1answer
15 views
pg_upgrade from 9.3.5 to 9.6 with PostGIS (Windows)
Versions
current:
PostgreSQL: 9.3.5 64-bit
PostGIS: 2.2.3
notes: 2.2.3 is the latest available on StackBuilder for 9.3.5;
upgrade
PostgreSQL: 9.6 64-bit
PostGIS: 2.3.1
notes: 2.3.1 is the only ...
1
vote
2answers
27 views
Creating an array from multiple columns without NULL elements
I'm trying to build a query to aggregate together multiple columns in a legacy table stored in a similar structure as below:
CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
(1,'Router','...
0
votes
1answer
20 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
0answers
16 views
How to grant a role that is member of another role through this one?
I am trying to grant user1 the select permission through granting this permission to another role which user1 is member.
Then I did this:
CREATE ROLE testrole NOCREATEDB NOCREATEROLE NOCREATEUSER ...
0
votes
0answers
5 views
Role 16420 was deleted for a concurrent transaction
I am creating a table with a user called admin. It works fine.
This user is superuser, and I have two roles, reader and executor that are member of it. Then, two other users, user1 and user2 are ...
0
votes
1answer
43 views
Creating a UNIQUE constraint from a JSON object
Lets take some example table peoples , that got only 2 fields: id and data(json).
SELECT data FROM peoples ;
{"name": "Adam","pos":"DBA","age":22 }
{"name": "Alice","pos":"Security","age":33 }
{"name"...
3
votes
1answer
25 views
Renaming table and putting a view in its place on Postgres
I have a very central table in our database that is used by a range of applications, it has rules attached to it, triggers and all the dependencies that you can imagine. Now I would like to modify the ...
1
vote
1answer
16 views
Array stringifies to using `{}` in Postgres output
I have an array column but when I output it using SQL SELECT, it looks like
{{"a":"b","c":"d"},{"e":"f","g":"h"}}
But why does it start and end with {{ and }}? Shouldn't it be [{ and }] for arrays? ...
1
vote
1answer
38 views
Is it safe to rely on auto increments even after restoring data?
I plan of using auto increments as a primary key on one of my table.
I need to store data related to each row in an other storage system and I plan to use each primary key id as the key for the data.
...
0
votes
1answer
36 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 ...
3
votes
1answer
38 views
Transfer microsecond timestamp into table using COPY
I need to transfer a microsecond timestamp into a table by using the COPY command.
My approach using a normal INSERT, where X is a timestamp (since 1.1.1970) in microseconds, looked like this:
...
3
votes
1answer
35 views
Misleading ERROR: relation [“schema_name.table_name”] does not exist [on hold]
Should not it be ERROR: relation [“schema name”].["table_name"] does not exist instead? Lots of people use case sensitive relation names with dots and so forth. Eg:
t=# select * from public.t1;
ERROR:...
0
votes
0answers
9 views
How add pgAgent to a second postgres server in same PC
I have installed 9.5 and now install 9.4 to test some compatibility issues. But the pgAgent isnt installed.
My guess is the pgAgent service cant handle both server.
When I try to install the pgAgent ...
0
votes
2answers
18 views
How to add a new column with auto increment ,increment factor, min-value and max value in psql?
I have table t1 and I would like to add a new column app_id with the properties given below
auto increment
increment factor(increment by 4)
min-value/initial value(100)
max-value(2000)
I have tried
...
1
vote
0answers
13 views
Permission denied errors during Nominatim import
I'm trying to import the OSM database into postgres using the default importer that Nominatim uses. After a couple of hours, i keep running into a snag. The error message is as follows:
index_placex: ...
3
votes
0answers
25 views
+150
FTS non-latin: text-search query contains only stop words or doesn't contain lexemes, ignored
I'm trying to use Full Text Search with Chinese characters using a postgres (9.6.1) FTS extension ZHParser.
Following some of the examples here, I wanted to select my title column and match the title ...
3
votes
1answer
113 views
> 30 sec slow query
I've got a very slow query, running over 30 seconds:
SELECT DISTINCT id10
FROM
(SELECT j.id AS id10,
j.modified,
j.n_type AS n_type5
FROM note j
WHERE j.modified_date >=...
1
vote
1answer
40 views
Group by with showing full rows in Postgresql [on hold]
I have a table like below with two columns A, B:
CREATE TABLE foo AS
SELECT * FROM ( VALUES
(1,1),
(1,1),
(1,2),
(1,2)
) AS t(a,b);
I want the result like below:
A B
1 1
1 1
sum B = 2
1 2
...
3
votes
0answers
50 views
How to upgrade PostgreSQL from 9.5 to 9.6.1 without losing data?
When I tried to run psql, I got
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?...
1
vote
1answer
25 views
'Column reference is ambigious' when upserting element into table
I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set.
...
3
votes
1answer
19 views
pgadmin / postgresql - no pg_hba.conf entry for host
I am trying to use pgAdmin to connect to a Postgresql server hosted by Elephantsql. However, I keep getting the error no pg_hba.conf entry for host:
no pg_hba.conf entry for host "173.18.54.553", ...
1
vote
2answers
67 views
Best practices for generating unique multi-column keys for weak entities?
How should one generate non-unique, non-natural identifiers for weak entities?
For example, if order_id is the primary key for an order table, and (order_id, item_number) is the primary key for an ...
1
vote
3answers
52 views
How to store short stories for access to individual sentences?
I am building a database for the first time ever (using PostgreSQL), and am very conflicted over the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the ...
0
votes
0answers
14 views
posgres WAL file database system identifier wrong
I'm having a problem restoring a copy of a postgres database to another server.
I have taken a backup using the pg_basebackup tool and can restore that but when I restore WAL logs from the archive I ...
0
votes
0answers
36 views
How to achieve Master-Master replication in Postgres
I have a application that performs read and write on a Postgres DB. Now, for fault tolerance, I am planning to have a failover mechanism by placing a second database in a different region. Best ...
0
votes
0answers
70 views
MVCC semantics for ACCESS SHARE and VACUUM with backend_xmin?
I just had a ton of fun writing this exhaustive answer which may be relevant to an understanding of this question..
PostgreSQL has an action that is crucial for routine maintenance VACUUM. It's ...
0
votes
0answers
19 views
Postgresql - What's the best way to handle privileges when the application account needs to maintain the database objects?
I have a user who needs a PostgreSQL database in which the application will create all the database objects & periodically drop & recreate objects.
In the past we usually create two users - ...
1
vote
1answer
35 views
Rewrite trigger from PostgreSQL to MySQL
I have this trigger in PostgreSQL database:
CREATE FUNCTION connections_insert_update_connection_root_fnc() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE ...
4
votes
2answers
159 views
VACUUM VERBOSE outputs, nonremovable “dead row versions cannot be removed yet”?
I have a Postgres 9.2 DB where a certain table has lots of nonremovable dead rows:
# SELECT * FROM public.pgstattuple('mytable');
table_len | tuple_count | tuple_len | tuple_percent | ...
0
votes
0answers
10 views
Materialized views (postGIS) as datasources in Mapserver
I am looking for a way to have materialized view as a datasource in Mapserver. Is there a way it can be possible? For some reason it only accepts views as datasources from a postGIS. I need the views ...
3
votes
3answers
48 views
Postgres ERROR: tuple concurrently updated
I have a large table test in which in user_id 2 have 500000 records. So I want to delete this record in chunks of 100 records but it is given error.
Here is my query:
delete from test where test_id ...
-1
votes
0answers
23 views
function in Posrgres db [closed]
I 'm getting error @ declare statement
1) CREATE OR REPLACE FUNCTION Test
DECLARE
v_address_id igce.igce_mapping_address%ROWTYPE;
v_address1 igce.igce_mapping_address%...
1
vote
1answer
27 views
How do I create a psql alias for “\q”?
I'm using Postgres 9.5 on Ubuntu 14.04. Even after using Postgres for a year, I can't get used to typing \q to exit my command line session. My fingers just want to type "exit" or "quit". How can I ...
1
vote
0answers
21 views
PostgreSQL what happens if TimeZone is not set?
From the docs on 9.1, I see
If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default ...
1
vote
0answers
17 views
How can I find the source of postgresql per-connection memory leaks?
I'm using postgresql 9.5.4 on amazon RDS with ~1300 persistent connections from rails 4.2 with "prepared_statements: false". Over the course of hours and days, the "Freeable Memory" RDS stat ...
