All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
1
vote
1answer
16 views
Assignment of a column with dynamic column name
I get the name of a column to set in a (BEFORE UPDATE) trigger, I want to set it to the OLD value and ignore anything coming in. I've tried the following:
CREATE OR REPLACE FUNCTION ...
0
votes
0answers
20 views
Pause postgres process already in progress
A colleague and I are both working on the same postgres database. We both started memory intensive queries which have now been running for several hours. The machine that hosts the database has ...
0
votes
1answer
15 views
PSQL - Selecting all parents and children [on hold]
I have a one to many and I am wanting to grab all of the records from table1 and all of the relationships for the record.
The closest query I have gotten to this is
SELECT t1.*, table2.* from table1 ...
0
votes
0answers
31 views
Which Database Platform to Pick? [on hold]
I am working on some best database solution to pick. It can be either SQL, NoSQL or hybrid based on below information.
Requirement
- Over the period 2-5 years the team will collect massive amounts ...
1
vote
0answers
17 views
How to design efficient queries and structure for data logging table (numerical stats)
I'd like to create a logging table that logs numeric values (.NET application). Being a programmer and not really a database person, I'd like to ensure myself with the advice of more experience ...
0
votes
0answers
16 views
query cost in postgresql using explain
I read the following link and try to understand the output.
https://www.postgresql.org/docs/9.1/static/sql-explain.html
EXPLAIN SELECT sum(i) FROM foo WHERE i < 10;
...
1
vote
0answers
14 views
How to give select privileges to database in postgreSQL [duplicate]
How can we give select access at database level in postgreSQL.
At present I had created new user with select access on all tables in a schema using below syntax.
grant select on all tables in ...
11
votes
1answer
2k views
What is the special database “postgres” for?
I have a PostgreSQL server with several databases running on it. One of the databases is called postgres, and it was there right from the beginning. I could not see any tables inside and I've never ...
0
votes
1answer
32 views
Select rows based on a minimum in one column
Working with PostgreSQL 9.4, I want a result that takes into consideration only min() (all aggregate functions) values of distance on JOIN or WHERE. But its seems those things are not allowed. So, I ...
0
votes
0answers
14 views
Super polymorphic event schema
I want to be able to track every action a user takes on my site.
An action can originate from a visitor or a user (both of which are human).
An action can affect a subject (visitor or a user)
An ...
0
votes
3answers
57 views
Is there a symbol I can use to have a where clause match all columns?
I'm using sql parameter's and I have a query that I'd like to use for two similar queries. The first uses two where clauses the other uses only one of the clauses is there a symbol I can use for the ...
0
votes
1answer
15 views
Using varchar_pattern_ops in a multi-column index in Postgres
I'm using postgres 9.5
If I have a table with 2 columns like so:
CREATE TABLE mystuff
(
somestring character varying(256),
timestamp_ timestamp without time zone NOT NULL
)
Will this multi-...
0
votes
0answers
15 views
Postgresql promoting slave server to master
I am trying to setup Postgresql(9.4) master-slave cluster. I am using the following tutorial: http://linux.xvx.cz/2014/10/loadbalancing-of-postgresql-databases.html
The issue I am facing at the ...
1
vote
1answer
28 views
Speed and size with hidden index in postgresql
I have a table with 1M rows without index, and i don't understand why when i go for : SELECT * from table WHERE id = 954000 i get the result
instantly ! I have no index how its possible to get me ...
1
vote
0answers
14 views
#deleted in all fields using ODBC driver between PostgreSQL and Access 2013
I planning to use MS Access (2013) as a front end to a PostgreSQL database server (version 9.5.2). However, when updating a table in Access the new row appears with “#deleted” in every column until I ...
0
votes
1answer
28 views
PostgreSQL use NEW in query for INSTEAD OF trigger
I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario:
CREATE TABLE Product (
product_id ...
1
vote
1answer
19 views
Trying to upgrade PostgreSQL 9.1 to 9.4 on Debian
After upgrading my Debian development box I have both PostgreSQL 9.1 and 9.4 on my system. I want to have 9.4 serve my data and get rid of the outdated version.
AFAICS, it is important to use a ...
-4
votes
0answers
35 views
Retrieving the data based on id from table contains 2 million rows [on hold]
I have one table/collection contains 2 million rows/document (id(20 alphanumerical digits), name, ... etc).
From that table I want to retrieve data based on the id within 1 sec
Which database ...
3
votes
1answer
36 views
Slow query on primary server runs fast on hot standby
I have a primary PostgreSQL 9.3.4 server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 32GB of RAM. My problem is as follows: I've detected a query that ...
3
votes
0answers
28 views
Postgres 9.1 query fast second time
Postgres version: PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
Open psql and run query. Takes 80ms.
Run same query again. Takes 11ms
...
0
votes
0answers
26 views
Subquery or Inner Join?
I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions.
SELECT t.id, t.date, t.amount, t.description FROM ...
0
votes
1answer
17 views
how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement? [duplicate]
I follow this URL to make some SOP for my colleague.
How to update the parent/child of all rows in the tree (ltree)?
However, my colleague hope to use some simple statement about "UPDATE" for adding ...
1
vote
4answers
60 views
Build JSON object from one-to-many relationship data in a single query?
I have a PostgreSQL 9.5.3 DB with tables like this:
container
id: uuid (pk)
... other data
thing
id: uuid (pk)
... other data
container_thing
container_id: uuid (fk)
...
0
votes
2answers
41 views
Slow UPDATE FROM Query in Large Table
I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight.
My basic query is:
UPDATE us_demand
SET ...
FROM ...
1
vote
0answers
9 views
Raspberry as data aggregator node, transfer to database if Raspberry is online
I have never used SQL and currently I do read and watch various turorials and the manual of PostgreSQL. I would appreciate if someone could give me a brief overview how an experienced user would set ...
0
votes
1answer
14 views
Convert timestamp type during INSERT
I have a table of raw data as timestamp with timezone, as well as other extraneous columns. I want to convert each timestamp to the interval difference between it and the earliest (smallest) timestamp ...
0
votes
0answers
9 views
Security of SQL Trigger Using dynamically stored table name
I'm working with PostgreSQL 9.0 and am learning how to use Triggers to enable some useful functionality.
I have an insert trigger on a table that looks like this:
feature_id | layer_name| latitude | ...
0
votes
0answers
12 views
How does postgresql handle tablespace locations in multiple clusters?
I have a server running a single postgresql cluster with a tablespace defined like:
CREATE TABLESPACE secondary LOCATION '/data_secondary/main';
I have a second server where I have two postgresql ...
0
votes
3answers
42 views
What data type for a column which can have three states: yes, no, unknown
I have an application where the user can decide between:
yes
no
unknown
I started to use a boolean column which is nullable.
But things get quite complicated since my framework (django) does not ...
7
votes
1answer
398 views
Delete from table rows where any of the column field is null
Is there a way to delete a row from a table where any of the column field is null without specifying explicitly which column is null?
I am using postgreSQL.
Here's my relation schema:
Column |...
0
votes
1answer
35 views
Possible to use first CTE as filter for second CTE?
I'm trying to optimize a query on a large database that essentially fetches 50 images for a site and the URL's for the pages they appear on. The current query works okay for smaller sites but larger ...
0
votes
1answer
24 views
How can I DRY up a series of upserts of the same fields from multiple tables?
I have a bunch of tables that all have a storenumber, extracteddate and extractedtime column.
I'm doing ETL on these tables and I want to put the last extracted date and time for each store in a ...
3
votes
1answer
118 views
How to get max value of a column from one table with its matching record in second table in PostgreSQL?
I have two tables as follows:
create table two(grade_id int, edu varchar(20),sortby int);
alter table two add constraint pk_one primary key(grade_id);
create table one(id int, name varchar(20),...
3
votes
2answers
50 views
Does it make sense to store a couple of Boolean values as array?
I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.)
Instead of having Boolean columns, I could have a single array ...
3
votes
2answers
33 views
How do I get postgres to log the stored procedures it is running?
I'm not asking how to log what the stored procedure does, I simply want a list of all the stored procedures that are being triggered and run dumped into the statement log.
I've got it logging all the ...
1
vote
1answer
19 views
Persisting Prepared Statements
I know that the documentation states:
Prepared statements only last for the duration of the current database session.
Is there any way way to either
save a prepared statement
create code which ...
0
votes
2answers
43 views
Add database-level collation to PostgreSQL
So I have added a en_US collation to my system:
# locale -a
C
en_US.utf8
POSIX
sk_SK.utf8
Based on Postgres' documentation I have added it to my Postgres instance:
postgres=# select * from ...
1
vote
2answers
32 views
Postgresql Log message without its statement
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
2answers
29 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 ...
1
vote
1answer
20 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
19 views
How to delete/disable Extended SQL functions or turn off 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 ...
1
vote
0answers
45 views
SQL Server: How to select a snapshot for a transaction? [closed]
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
18 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) ...
-1
votes
0answers
55 views
In postgresql 9.1 comparing timestamp with date giving no result at all? [closed]
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
5 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
27 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
32 views
Postgres: Understanding Hash Left Join?
Using Postgres 9.4, I'm trying to a query by conditions ==> no hash left join.
EXPLAIN ANALYSE
SELECT
CEAR.RESULT_ENUM_TYPE_ID,
CEAR.CE_CE_COMMUNICATION_EVENT_ID,
MC.CAMPAIGN_NAME,
MC....
1
vote
1answer
30 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
44 views
How to update value in inserted row in PostgreSQL?
I have a 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 tried this:
WITH ...
0
votes
2answers
24 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.
...
