All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
0answers
18 views

Count number of indexes usage for select

I have a lot of indexes. Some of them are not used (because they created once). But I want to know why it is not used. I have the following tables and indexes: create table statuses ( id ...
0
votes
0answers
16 views

PostgreSQL 9.5, getting “cached plan must not change result type” error

I had a really odd and unexpected behaviour in PostgreSQL. Debugging I found that the problem only occurred while including CREATE TEMP TABLE ... inside a function, and by using an alternative (in ...
0
votes
1answer
26 views

Why does restoring postgresql database from dump produce errors

I did the following commands: pg_dumpall --clean | gzip > db.gz gunzip db.gz psql < db I expected the third command (loading the database back) to just run with zero errors, but I got some ...
1
vote
0answers
17 views

Why can read only users create objects inside postgres database? [duplicate]

I've just started working with Postgres and I noticed the following. If I create a user they automatically have the ability to do whatever they want inside the database. User was created with: ...
0
votes
1answer
37 views

PostgreSQL indexing on bit string

What is the best way to create an index on a bit string column? Let's say I had the column of type bit(4) and I wanted to search for all entries that had had a specific bit set. So if I had the ...
1
vote
0answers
44 views

GUI tools for querying PostgreSQL [closed]

I have tried Psequel, pgAdmin and plain old psql on OSX for querying against Postgres. In my experience, all these free third-party tools are incredibly buggy and pale in comparison with SQL Server ...
3
votes
2answers
136 views

Why some rows are missing in my recursive CTE?

These are my 2 tables id_hierarchy and hierarchy_link: CREATE TABLE id_hierarchy ( id integer NOT NULL, hierarchy integer ); insert into id_hierarchy values (11,2),(22,3),(44,5),(77,8),(170,...
0
votes
1answer
26 views

Join query result of 2 different tables based on common column

I want to join the result of two different queries from two different tables based on a common column (which is named differently in the two tables) but I cant seem to figure out the easy way to do it....
1
vote
1answer
40 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
1answer
48 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 ...
1
vote
0answers
29 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
21 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
17 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 ...
15
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
39 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
18 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
63 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
16 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
31 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
32 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
15 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
34 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
22 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 ...
-5
votes
0answers
44 views

Retrieving the data based on id from table contains 2 million rows [closed]

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
44 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
38 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
30 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
71 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
44 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
19 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
11 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
13 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
46 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
410 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
37 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
26 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
120 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
57 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
40 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
49 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
33 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
31 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
23 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
21 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) ...
0
votes
1answer
11 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 ...