All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
0answers
4 views
Logging a message to the postgres log and not the statement that generated to it
If I want to write a message to the Postgres log I can do this:
DO $$
BEGIN
RAISE LOG 'Justin Is Awesome';
END $$;
However, it logs the statement as well so I get 5 lines of output instead of one:...
0
votes
1answer
8 views
Are there any Database functions which allow access to other network services in PostgreSQL
I have set up a new postgres installation and have to meet the security requirements from my company.
There is one requirement were i'm not quite sure:
Database functions which allow access to ...
0
votes
0answers
7 views
How to insert a record returned by a function into a table
I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record.
CREATE FUNCTION my_func(
IN id bigint,
OUT foo bigint,
OUT bar double precision
...
0
votes
0answers
7 views
How to delete/disable Extended SQL functions or turn of extended stored procedures in postgres
I have set up a new postgres installation and have to meet the security requirements from my company.
There is one requirement were i'm not quite sure how to
implement:
Extended SQL functions ...
0
votes
0answers
24 views
SQL Server: How to select a snapshot for a transaction? [on hold]
PostgreSQL has a feature to select a snapshot for a transaction using:
SET TRANSACTION SNAPSHOT snapshot_id
Does SQL Server have a similar feature?
Edit: Answer to: What is the use case?
In an ...
0
votes
0answers
12 views
Postgres dump fails on higher version
I'm trying to replace the current production database with staging. The production database version is 9.3.9 and staging is 9.3.10.
Importing the dump on my local machine (which has version 9.3.4) ...
0
votes
0answers
9 views
In postgresql 9.1 comparing timestamp with date giving no result at all?
I am having two tables a, b. Table a is a FDW table from a mysql database. Table b is a normal pgsql table. b.entry_time is of type timestamp without time zone.
table a is having some data that I ...
0
votes
0answers
4 views
Postgresql: replica and HA
I've postgresql 9.4.
I'm aware that there are many answer on DBA and SO, I'm aware of the wiki and the documentation.
But anyway, I can't understand in a simple way what's the right way to have a ...
0
votes
1answer
21 views
Have DB users share tables
I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying ...
0
votes
0answers
17 views
Postgres datetime with null value slow and not using index
Using Postgres 9.4, I'm trying to a query by conditions with PRF.THRU_DATE column.
EXPLAIN ANALYSE
SELECT
CEAR.RESULT_ENUM_TYPE_ID,
CEAR.CE_CE_COMMUNICATION_EVENT_ID,
MC.CAMPAIGN_NAME,
...
1
vote
1answer
16 views
Postgres - MultiXactId error
I have a table which has just started giving me the following error when trying to set a primary key, create an index or take a backup:
ERROR: MultiXactId 1092514672 has not been created yet -- ...
0
votes
1answer
29 views
How to update value in inserted row in PostgreSQL? [on hold]
I have table t1
create table t1 (
id int primary key not null,
name varchar(64),
str2 varchar(64)
);
I want to get the row ID and update a field str2 in this row. I do it so:
WITH inserted ...
0
votes
2answers
20 views
Postgresql pg_hba.conf changes are ignored or not loaded
Using Postgresql 9.3 on Ubuntu 14.04.4 LTS
I've been changing the /etc/postgresql/9.3/main/pg_hba.conf config file madly trying to make a change that will allow me to log in with any user remotely.
...
0
votes
0answers
23 views
Can I write a constraint that requires two rows joined via a junction table to both have the same foreign key in Postgres? [duplicate]
I have a database of data collection instruments (e.g. questionnaire, test, etc.) based on the DDI standard. I am allowing categories to be reused within an instrument for multiple code lists, but ...
1
vote
1answer
17 views
Postgres permission for EXPLAIN
I would like to give user a permission to perform EXPLAIN but not to execute the SELECT itself. Is this doable in PostgreSQL?
0
votes
1answer
14 views
Disable trigger effects in PostgreSQL
I have a function where there is:
alter table a disable trigger trig1;
some code
alter table a enable trigger trig1;
It basicaly disables the trigger for the purpose of specific operations.
My ...
1
vote
0answers
17 views
Postgresql/AWS RDS Problem with high write IOPS
I am currently hosting a ~10GB database in amazon RDS running postgresql. I have 100GB of space allocated using General Purpose SSDs which means that I get 300IOPS to be split between read and writes.
...
2
votes
2answers
66 views
Fastest query for selecting arrays that contain duplicates
I'm using Postgres 9.5 and I have a column, named phones, of type text[].
I need to find all rows where this column contains duplicates.
I've found this extremely useful set of functions https://...
0
votes
1answer
9 views
postgres index for “?|” JSONB operator
I have a jsonb row with objects/dictionairies : {"a":1, "b":2, "c",3}
I want to query rows that contain a set of keys, ex:
SELECT * from my_table v
WHERE v.my_jsonb_column::jsonb ?| array['b','a']
...
1
vote
1answer
21 views
is shared storage safe in Postgres if all servers are read-only?
Description of shared storage in Postgres: https://wiki.postgresql.org/wiki/Shared_Storage
Related question: http://serverfault.com/a/620315/137464
Both of these make it clear that it's not safe to ...
1
vote
1answer
54 views
How do I insert a record and put the ID into an existing record in another table?
I want to insert a record in one table and save the resuting ID in a record I already have in another table. Which sounds easy.
Simplistic example:
create table aaa (id serial primary key, foo int);...
2
votes
1answer
132 views
How to constraint a foreign key to a subset
I have a table employee, and each of them can fulfill different roles, like inspector and constructor. I also have another table with a column where I can store what inspector is on the job.
People ...
0
votes
1answer
24 views
Use system timestamp comparison in SELECT query
I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in ...
1
vote
1answer
29 views
Database design. And get last modified time with each column in PostgreSQL
My database/table each endpoint/scope have multiple table,
e.g user: user, user_information, user_role ...
I was wondering should I separate table like below, or not?
This is my first time to ...
1
vote
2answers
43 views
How to find out if rows are refenced from other tables?
I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an ...
0
votes
0answers
11 views
Backup and Restore Hive Metastore in Postgres
How to backup and restore hive metatstore in embeded postgresql for CDH5.7.0 cluster?
If the table is drop, then how to restore the metadata for that particular table in embeded postgresql.
0
votes
1answer
21 views
how to check list of users present in all postgres database
I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like,
List of roles
...
0
votes
1answer
25 views
PostgreSQL - cannot create RULE from stored procedure
I'm trying to automate boring database work. I would like to have one function which creates views, rules, etc so I don't need to run corresponding SQL queries from file. Currently I faced an issue ...
-1
votes
0answers
15 views
PostgreSQL Table Function: return all table fields
I have already created a view which has my derived columns with appropriate aliases. I would like to create a table function which uses this view and returns all the columns from that view.
I have ...
0
votes
0answers
13 views
Is Schema Level Replication possible in PostgreSQL?
I am very new to the world of databases and wanted to know how one would/could accomplish Schema level replication in PostgreSQL. I've read about Slony, but it does not appear to state anything about ...
0
votes
1answer
13 views
Postgres: SELECTing bytea data partially with offset and length
I have a table containing a bytea column, with an average entry size of 5MB. Is it possible to query from random points in the bytea data (given an offset and length of data required) instead of ...
0
votes
0answers
16 views
pg_controldata 'Time of latest checkpoint' on standby doesn't change
About 2 weeks ago, pg 9.3 master space filled up, didn't die, continued operation after space was freed up, and it's standby has kept up with it ever since.
However the standby pg_xlog keeps filling ...
0
votes
2answers
45 views
Are two logically equal indices physically the same index?
I have a table with this definition:
CREATE TABLE public.delete_operation
(
id bigint NOT NULL,
parent_id bigint,
pdu character varying,
progress smallint NOT NULL DEFAULT 0,
description ...
0
votes
1answer
19 views
plpgsql function to replace ' ' with null after insert using a trigger
I have a table in PostgreSQL 9.5 on which insert statements are run frequently. After an insert statement was ran on the table there are ' ' (single quote, space, single quote) values when no data was ...
0
votes
0answers
8 views
how to enable shp2psql-gui in pgadmin3 with postgresql-9.5 on centos
I have installed postgresql-9.5 on centos with postgis2_95 extension. Now I want the shp2psql-gui plugin tobe enable in my pgadmin3. Please help on this as I am unable to enable it though I have ...
0
votes
1answer
24 views
postgresql reindex frequency and monitoring
What's up guys. I have a couple of tables where i inserting much of data (and almost never deleting). Periodically i see that insert gains ExclusiveLock on index of table. So i guess that sometimes ...
0
votes
2answers
19 views
PITR restore in postgresql
I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows.
wal_level = archive
archive_mode = on
archive_command = ...
1
vote
2answers
50 views
Full Join Query
I am doing a full join query from two tables.
SELECT
i.idinasistencia, i.fecha, i.valor, i.justificado,
h.id, p.nombres, p.apellido_paterno, p.apellido_materno,
p.rut_persona
FROM historialalumno ...
3
votes
1answer
55 views
How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?
I am stuck on implementing a left join on dates for each individual customer.
I have a table which stores customer purchases:
SELECT purchase_date, customer
FROM purchases
I would like to create a ...
3
votes
1answer
69 views
Optimise a LATERAL JOIN query on a big table
I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016.
CREATE TABLE event_pg (
...
0
votes
0answers
13 views
Restore Postgres Database with Old Data Directory
I have not made a backup from my old data directory, but I still have the directory in tact. In my current database I have the same Schema, etc... When I try to restore the database (using pgadmin III)...
0
votes
0answers
18 views
Traditional SELECT with Multiple Joins or SELECT JSONB then multiple SELECT
I am writing a simple recipe book app based on node.js with sequilize as ORM and Postgresql 9.4 as DB.
Recipe has multiple ingredients that can be measured with different units.
I store recipe steps ...
0
votes
0answers
8 views
Postgresql - Get Distinct Records on the basis of Json Values [closed]
Scenario: I have table having the value stored in Json format on its
one of the column.I'm expecting the result to get the Distinct or
Unique Records on the basis of value (one of the value of ...
0
votes
0answers
39 views
Deadlock during delete in same transaction?
pid basename oid user tx locktype acquired date time query
7592 base 1595014 postgres 363/197025 RowExclusiveLock Yes 2016-06-27 18:19:54+...
1
vote
1answer
33 views
Impact of OS timezone change on Postgres Database
I want change OS time zone to UTC. This will shift back time by 2h. Can this possibly damage data in any way (for example overwrite data)? What should I do to safely change the OS time zone?
0
votes
1answer
72 views
CTE Query copy value if exists into an existing table [closed]
I got the following query:
WITH table_data AS (
SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description,
UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
0
votes
1answer
27 views
Incremental backup in postgresql in windows
I am developing an application with postgresql as backend. So my requirement is to take the incremental backup either hourly or daily basis.
I have gone through many related articles (like https://...
0
votes
1answer
20 views
Can I pass a TABLELIKE parameter to a PostgreSQL FUNCTION/PROCEDURE?
I have a function which returns:
TABLE (id TEXT, collected TIMESTAMPTZ)
In another function, I would like to be able to pass the result of that first function as a parameter. For example:
CREATE ...
0
votes
1answer
20 views
Postgrsql After automatic failover how can I automatically make old master as slave? [closed]
I researched lot of websites and blogs for PostgreSQL automatic failover in Ubuntu, Finally I configured it almost perfectly between two AWS EC2 instances.
Now my question if the master goes down ...
0
votes
0answers
26 views
Open source library for scheduling and storing query results? [closed]
What open source libraries/tools are available for scheduling and storing the results of various sql queries?
Basically, I have a bunch of sql queries that I'd like to be run daily (e.g., SELECT ...
