All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
1
vote
0answers
9 views
Amount by which to increase checkpoint_segments (postgresql)
In my postgresql log, I see statements such as checkpoints are occurring too frequently (26 seconds apart). Currently in the conf, checkpoint_segments are 32, checkpoint_timeout is 15 mins and ...
0
votes
0answers
8 views
Monitoring Streaming Replication Lag - Postgres 9.2
I have some Slaves running with Streaming Replication, and would like to monitor the streaming replication.
I'm currently using these queries:
On master:
SELECT client_hostname
, client_addr
...
0
votes
0answers
7 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 ...
1
vote
1answer
11 views
Command not found: pg_ctl on Ubuntu
PostgreSQL 9.4.8 Ubuntu 16.04 64 bit
I just want an explanation of what is happening here. Why is this command not found? Nothing else.
malikarumi@Tetuoan2:/usr/lib/postgresql/9.4/bin$ pg_ctl stop -...
4
votes
3answers
104 views
Reverse Byte-Order of a postgres bytea field
I'm currently working on a table that contains hashes, stored in bytea format. Converting the hashes to hex-strings however yields the wrong order of bytes. Example:
SELECT encode(hash, 'hex') FROM ...
2
votes
1answer
35 views
Why would a Greenplum procedure not recognize a view change?
I have a procedure that loops through a query that returns a set of dates, recreates a set of views using the date in the WHERE clause, and the calls a series of other procedures that use these views. ...
2
votes
0answers
13 views
Unexpected failure in transactions with isolation level serializable
Unexpected failure in transactions with isolation level serializable
Hi,
I have a challenge with transactions in Postgresql when they have the isolation level SERIALIZABLE.
This is the transaction ...
0
votes
0answers
28 views
how to convert xml column into float datatype
I parse xml document with use xPath. But all columns are datatype - xml. When I want convert any column into float data type I get error: column "x" is of type double precision but expression is of ...
3
votes
1answer
28 views
list of PostgreSQL trusted languages?
I am trying to find a list of PostgreSQL trusted procedural languages (PL) as I often need to use a PL in a non-superuser environment.
But I couldn't find a definitive answer. For example, the ...
-4
votes
0answers
33 views
Why do my queries take longer together than when I run them separately? [on hold]
I have a series of 46 update queries. Each query takes 15-20 seconds to run on its own, but when I run them all together as a single statement via PgAdmin it takes over an hour instead of the ~15 mins ...
0
votes
0answers
20 views
How to effectively monitor PostgreSQL 9.5 Replication?
Background: I've got quite some experience running and automating MySQL Clusters with several independent replication streams. However I rarely had to run an PostgreSQL replication myself. They were ...
3
votes
1answer
44 views
How to display queries of separate tables in adjacent columns?
Question:
I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval.
Here ...
0
votes
1answer
35 views
How to deploy PostgreSQL database on shared directory?
I have created a Java Application, deployed with a .jar file, which accesses a postgreSQL database. Currently, everything is stored on my computer.
I am trying now to share the application with other ...
0
votes
1answer
11 views
Measuring how long a query is IDLE IN TRANSACTION (postgresql + Django app)
When looking at pg_stat_activity, how can I tell whether the state IDLE IN TRANSACTION is transient?
Can the table's columns give that information to me (e.g. xact_start or query_start)? Btw, ...
0
votes
0answers
18 views
Odoo Postgres Databases synchronization
I have to deploy odoo with postgres database on amazon cloud. That i can do by simply setting up EC2 server and setting uo odoo on it. In case if internet in down, I want to be able to access same ...
-2
votes
1answer
28 views
Is CASE WHEN appropriate for optional parameters in a SQL query? [duplicate]
I'm wondering if I'm handling CASE WHEN appropriately below. I have optional parameters and want to make sure this is the best way to handle them.
Postgres version 9.3.14.
CREATE FUNCTION ...
3
votes
2answers
48 views
What is the overhead for an array in Postgres?
What is the additional overhead of an array compared to a normal column of that same datatype? In other words, if an array will almost always have one value in it, how much space would I be "wasting" ...
0
votes
0answers
23 views
PostgreSQL Single Mode Command
I've been working for a while with PostgreSQL and I managed to find out a way to use pg_rewind function in order to resynchronize an old master after a new promoted master (old standby). (using repmgr ...
0
votes
1answer
21 views
Import in postgres json data in a csv file
I'm trying to import into postgres a csv file containing the data for a table. One of the column of the table has jsonb type.
One line of my csv file contains something like
1,{"a":"b"}
Suppose the ...
0
votes
1answer
26 views
For PostgreSQL database, restrict remote connections to password
I have recently set-up a PostgreSQL database on client's CentOs machine. Until now the remote access was restricted.
Now I have tried to open the remote connections for us, with following entry in ...
0
votes
0answers
25 views
I don't know what the Title should be, but here's an example [on hold]
Dear All, I have an example of a given value from the table above, the expected output is the Allocation field.
Where "qtyrs" is the request value that has to be done
"plot" is the actual value that ...
0
votes
1answer
24 views
How connect to DB PosgreSQL in pgAdmin4 with port 84?
Install DB Server PosgreSQL
postgresql-9.6.1-1-windows-x64.
After Install I change number standard port to 84.
In postgresql.conf
port = 84 # 5433 (change requires restart)
I ...
1
vote
0answers
41 views
Postgresql improve select performance/parallelism in huge table
I have a huge table (> 3000 million rows, totaling 3 TB) using Postgres 9.5 defined as follows:
CREATE TABLE user_events
(
user_id VARCHAR NOT NULL,
datetime BIGINT NOT NULL,
field1 ...
0
votes
2answers
34 views
Is it possible to COPY tables into PostgreSQL without setting structure first?
I'm just getting to grips with PostgreSQL 9.6.
I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin?
Or...I've not ...
0
votes
0answers
29 views
Postgres function how to best return a complex object [closed]
I'm looking for tips on the best way to return a complex object. I have a user object with multiple email addresses, phone numbers, addresses, and other dynamic fields.
What is the best practice of ...
0
votes
1answer
49 views
UPDATE and CASE problem [closed]
It is my first question in this forum.
I wrote this code, but it is incorrect and I can't find a problem.
I have 2 tables: sciana (gid,id,geom) and sciana1(gid,id,geom).
If table sciana is empty I ...
0
votes
0answers
19 views
PostgreSQL error “server closed the connection unexpectedly” when connecting using psql [closed]
I'm trying to connect to my PostgreSQL database from my local machine. My local IP address is 192.168.0.5. The following things work as expected:
psql postgresql://localhost
psql postgresql://127.0.0....
1
vote
1answer
24 views
Why isn't my Postgres index being used?
I’m using Postgres 9.5. I want to create indexes that will make queries like
select * FROM my_object_times where name like ‘Dave %’;
and
select * FROM my_object_times where name like '% LastName';
...
0
votes
0answers
13 views
Is there a way to add disk space to a Postgres instance hosted on an EC2 server?
I have a machine that has 512 GB disk space on which I am running a Postgres instance. The problem is that the disk is almost full and I need to increase the size of the available disk space.
I know ...
0
votes
1answer
29 views
Update Local Table With Data From Linked Server Using Open Query
This is the syntax I am trying to use, but I get an error of
Incorrect syntax near #
I have also tried it with an actual table and not a temp table and still not succesful. Here is my syntax, ...
1
vote
2answers
21 views
DB Design betwwen 2 DBs VS 2 Schemas [closed]
We're preparing a database design for an ERP. The ERP are composed from several modules and every module has his proper db, which can deployed at least on one personnel schema.
When we have the ...
0
votes
1answer
23 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
0answers
17 views
Open Query Syntax [duplicate]
This query returns my data that I am after, but since I am needing to select between date criteria I must add an additional field into my query which in essence returns row by row data as opposed to ...
3
votes
1answer
29 views
Why earthdistance <@> operator returns different value than earth_distance function?
Seeing a different value with <@> and earth_distance
with points as (
select
'(1,1)'::point as p1
,'(2,2)'::point as p2
)
select
((p1 <@> p2) * 1609.34::double precision) as ...
1
vote
0answers
15 views
Create PostgreSQL fulltext Parser
I want to make a FTS on my text column on Postgres, the problem is that I want to search words with "_" between letters, like "the_event" but the default parser split those words and identify "_" like ...
0
votes
0answers
13 views
pgAdmin4 Hide Alerts
pgAdmin4 has introduced 'alerts' or 'toasts' in the UI that display the status of certain tasks.
There seems to be no way to dismiss these alerts, which obscure other parts of the UI, without first ...
4
votes
1answer
72 views
Calculate inventory out row unit price
My current query SELECT * FROM foo ORDER BY date; returns result:
id | qty | unit_price | date | action
----------------------------------------------
1 | 2000 | 4.01235 | 2015-10-10 | ...
0
votes
1answer
237 views
`CREATE TABLE AS` vs `SELECT INTO`
PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both?
CREATE TABLE AS -- define a new table from the results of a query
CREATE TABLE AS creates a table and fills it with data ...
3
votes
1answer
30 views
Shorthand for epoch
Is there a shorthand for the epoch instead of having to type '1970-1-1 00:00:00' all the time?
0
votes
2answers
36 views
Postgres Database too large to start service
Due to some craziness on a server I am looking after, I have a huge database that needs to be truncated (44GB despite being a simple site). However, I can't start the postgres service as there is not ...
3
votes
2answers
44 views
Using window function to carry forward first non-null value in a partition
Consider a table that records visits
create table visits (
person varchar(10),
ts timestamp,
somevalue varchar(10)
)
Consider this example data (timestamp simplified as counter)
ts| person ...
0
votes
0answers
58 views
How to tune Postgres to take advantage of 256GB RAM hardware
I am trying to configure postgres (version 9.5) to take advantage of very large memory environment. Server configuration has 256GB RAM, 12 cores and 2 SSDs on RAID0 and runs Ubuntu. Swap is set at 4GB....
1
vote
3answers
71 views
Column does not exist in UNION ALL query
I am doing some code for my badminton club.
Tables
CREATE TABLE School (
Name text PRIMARY KEY
);
CREATE TABLE Tournament (
Number integer PRIMARY KEY
);
CREATE TABLE Player (
Name ...
0
votes
1answer
15 views
How to set 'timing on' permanently in PostgreSQL?
I work with PostgreSQL in the command line interface. I would like to activate a \timing command permanently for every database in the cluster so every time I connect to a database - this parameter is ...
1
vote
0answers
53 views
Access-PostgreSQL: Link tables as readonly, edit registers in form
I have a PostgreSQL database working as backend and I have to develop an Access frontend solution for regular users of this database.
I decided it best to create some forms to manage the input, ...
0
votes
1answer
17 views
Variables in DB data to avoid redundancy?
"Avoid redundancy" is important to me.
I want to store the input data for configuration management in a relational database.
My input data:
project_name
linux_user
db_user
db_name
In 95% of all ...
3
votes
2answers
44 views
Postgres JOIN conditions vs WHERE conditions
Postgres newbie here.
I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See ...
1
vote
1answer
32 views
Postgres function return table with extra column
Newbie to Postgres here..
I have a Postgres / plpgsql function that will return a table. I want to return everything from a query plus a logical value that I create and return along with it.
...
-1
votes
1answer
20 views
in Postgres 9.5, can you select a set of users and have a subset be ordered first?
We have a list of users but would like the subset of users who are friends to be first if they exist and sorted in a specific order. Let's say your friend_user_ids are [6,4,2] (such that if user 2 and ...
3
votes
1answer
33 views
Multi-expression indexes in PostgreSQL not using being used as expected
I have a PostgreSQL 9.3 table with multiple expression indexes defined. When I use explain to see which indexes are being used for my query, I am surprised to see that PostgreSQL is not using the ...
