Tagged Questions
0
votes
2answers
20 views
assign selected columns to a variable
Select
sum(column_a) as a,
sum(column_b) as b
round(a / b)
From
table
Group by column
In the example above I am summing two column, and I would like to use those 2 results in calculating ...
1
vote
1answer
29 views
Does PostgreSQL allow multiple grantees in GRANT commands?
Looking at the PostgreSQL docs it seems that the GRANT command only grants to a single user or role. In fact it doesn't seem to be PostgreSQL alone.
Isn't possible to do something like GRANT SELECT ...
3
votes
1answer
6 views
Postgresql query plan differs with limit value making the query slower for lower limits
I have this query:
SELECT "table_1".* FROM "table_1"
WHERE "table_1"."deleted_at" IS NULL
AND "table_1"."table_2_id" = 15797104
ORDER BY "table_1"."id" ASC LIMIT 1
The explain for the query ...
1
vote
1answer
17 views
How to apply a newly-created Domain to an existing Column?
In Postgres 9.4 Beta 1, I successfully created a domain of type VARCHAR, fuel_domain_. The domain checks that a value is any of 5 possible strings, coal, gas, wind, hydro, other.
CREATE DOMAIN ...
3
votes
2answers
42 views
Pagination with PostgreSQL 9.3: counting number of pages
I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id.
-- index
create index category_score_id on products(category, score, ...
0
votes
1answer
34 views
How do I select the latest rows for all users?
I have a table similar to the following:
=> \d table
Table "public.table"
Column | Type | Modifiers
...
1
vote
1answer
44 views
Efficient way to insert/update/delete table records from complex query in Postgres 9.x
I have this function which returns a set a records and I need to persist those records into a table. I have to do it hundred times a day.
My initial approach was just clear data from my table and ...
1
vote
1answer
72 views
PostgreSQL 9.3 - Performance Issue: Counting table entries slower with jdbc postgres driver
I'm using PostgreSQL 9.3 and want to ask if it is possible to improve the performance of my queries based on the following database schema:
CREATE TABLE "entities" (
"id" BIGSERIAL,
"type" INT ...
0
votes
0answers
34 views
Select script for selecting two columns in a table [migrated]
I need help writing a Select script which selects data from two columns based on a condition.
Say I have a table
Table: X
Columns: ID, Type , and Old_type
Column "Old_type" has nulls or historic ...
4
votes
3answers
279 views
Checking whether two tables have identical content in PostgreSQL
This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table.
A PostgreSQL ...
0
votes
2answers
59 views
How to change schema so that account_id reference is unique among 3 tables
Original image:
Updated to have more-correct terminology and an 'is_debit' column:
I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting.
'jackpots', ...
0
votes
0answers
35 views
Wrapper function with SECURITY DEFINER is slow [closed]
I have a strange behavior of wrapper function with SECURITY DEFINER option.
Function 1 - Queries data what is not directly accessible by user.
Function 2 - Is a wrapper that calls function 1 with no ...
1
vote
2answers
63 views
How to construct a set-based query for subset problem?
I have a DB of the following structure (picture shows simplified version)
How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe ...
3
votes
2answers
106 views
How to avoid the SORT operation in SQL query that contains ORDER_BY condition?
I am optimising a PostgreSQL query which involves three tables and a ORDER_BY condition. It seems that PostgreSQL prefer to arrange a SORT operation to perform the ORDER_BY condition, rather than ...
3
votes
2answers
84 views
How to inner join some tables and outer join others?
I'm certain I'm going to be embarrassed by the eventual answer, but I've been googling around and banging my head against the wall and can't figure it out.
I have four tables described below. I'm ...
0
votes
1answer
32 views
Demo DB for book on PostgreSQL
The book PostgreSQL: Introduction and Concepts, printed in 2000, was highly recommended to me by the regulars in PostgreSQL IRC channel.
However - I was trying to figure out which demo database ...
1
vote
2answers
92 views
How to make a table name an attribute in PostgreSQL?
I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data.
I would like to Union ...
2
votes
1answer
202 views
How to pass a parameter into a function
At the moment I have a view created, see it here in the answer. How can I create a function on that cross tab, so I can pass a date, and get data for the specific date?
Also is it a good practice to ...
3
votes
2answers
116 views
Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries
I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this
SELECT *
FROM
( SELECT ...
4
votes
1answer
124 views
Pull data from multiple tables in a view or function
There are 3 tables from which I need to pull data, and print it in a specific way.
http://sqlfiddle.com/#!15/59481/8
You can refer to this question to see how the query was produced.
In the fiddle ...
1
vote
1answer
33 views
Calculate value differences of rows before and after bounded by multiple referenced rows
Supposed I have a user travelling table like this:
id | start_time | end_time | location |
----+--------------+------------+------------+
1 | 1 | 2 | Loc_A |
1 | ...
1
vote
2answers
80 views
Building a Trending query
Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every ...
2
votes
1answer
88 views
Select data based on records created_at column
I have two tables Keywords, ProjectReports:
http://sqlfiddle.com/#!15/06ae3/1
As you can see in that example everything works fine, but I want to get more data from those 2 tables and I don't know ...
0
votes
0answers
21 views
Starting up postgres on Ubuntu for the first time
I've installed postgres on ubuntu, created a node and now I'm trying to start up that node as per the instructions:
postgres --coordinator -D /usr/local/pgsql/data -p 1281
But I get the following ...
1
vote
1answer
99 views
Very slow simple PostgreSQL query on RDS
I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram).
This is across a table of 4,152,928 rows..
select sum(some_field) c
from pages
where pages.some_id=123
and ...
0
votes
1answer
31 views
PostgreSQL: how do I query over a column with a custom range?
I'm trying to write a query over a string column called grades_served that can have values such as
k-5
1-5
5-9
The query could be for a specific grade or it could be a range with a start and end ...
3
votes
1answer
59 views
Query with dynamic ranges in PostgreSQL based on status
We have a table with invoices classified per day, with normal invoices and void invoices (invoices that are voided, in our country we use the Spanish term "anuladas"). For example suppose that we have ...
2
votes
1answer
66 views
MS SQL - export tables to format compatible with PostgreSQL
We're using MS SQL Server 2005 at work and I want to export our data to PostgreSQL for testing.
MS SQL Server has an export function but the only one that looks like it would work would be to export ...
0
votes
2answers
55 views
PostgreSQL : select columns inside json_agg
If I have a query like
SELECT a.id, a.name, json_agg(b.*) as "item"
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
How can I select the columns in b so I don't have b.item_id in the ...
3
votes
1answer
43 views
PostgreSQL seems to ignore RAISE EXCEPTION in a CTE
Using PostgreSQL 9.3 I have been trying to define an assert helper function to check for empty query results and similar things as follows:
CREATE FUNCTION public.assert (
in_assertion boolean,
...
0
votes
1answer
30 views
Can PostgreSQL results include the query?
I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment.
psql -U <username> -h <servername> -d <databasename> -f
...
1
vote
1answer
39 views
Postgres Update, limit 1
I have a postgres database which contains details on clusters of servers, such as server status (active, standby, etc). Active servers at any time may need to fail over to a standby, and I don't care ...
2
votes
2answers
42 views
PostgreSQL using count() to determine percentages (cast issues)
I'm trying to run the following query to provide the % of records in my patients table that have a value entered in the refinst field. I keep getting a result of 0.
select (count (refinst) / (select ...
0
votes
0answers
12 views
Creation of a table with foreign keys in postgres is taking longer time [duplicate]
I am wondering if someone can help me understand this.
I tried to add an column to a table of 22000 rows which is referencing other tables(one of these tables have 0.5 million records). It didn't ...
0
votes
1answer
35 views
Postgresql Function Trying to Insert into 1 row instead of 2
Postgresql 9.3, Mac OS X 10.6.7
I created this function and I would like to insert both results into the same row. Obviously the way I have it inserts into 2 rows.
First, here is the Table T1:
...
3
votes
1answer
106 views
Optimizing concurrent updates in Postgres
I'm running concurrent Postgres queries like this:
UPDATE foo SET bar = bar + 1 WHERE baz = 1234
Each query affects the fixed K number of rows, and I can't find a way to enforce the order in which ...
0
votes
1answer
48 views
Database design Problem for the web-app
Hello I am in a problem of designing a web-app database schema.
Suppose I have 10 shops and every shop gives deals on every different day Sunday, Monday and so on and for limited time period like ...
1
vote
0answers
48 views
Join and aggregate result and insert blanks for missing appendants
Consider these two simplified tables:
CREATE TABLE "sources"
(
"id" serial NOT NULL,
"name" text NOT NULL,
CONSTRAINT "sources_pkey" PRIMARY KEY ("id")
);
CREATE TABLE "values"
(
...
1
vote
1answer
89 views
SQL hourly data aggregation in postgresql
I am a newbie with database so I'm seeking your help with this one.
I have a table containing time series data.
2012/01/01 00:10, 10
2012/01/01 00:30, 5
2012/01/01 01:00, 10
2012/01/01 01:40, 10
...
1
vote
1answer
30 views
Limit balancing results by a column
I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed.
I also have a function that returns me the ...
1
vote
2answers
55 views
Limiting read access in postgres to few rows
We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
1
vote
1answer
99 views
Have Postgresql query planner use nested loop w/ indices over hash join
I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
18
votes
6answers
654 views
Count where two or more columns in a row are over a certain value [basketball, double double, triple double]
I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
1
vote
1answer
28 views
capability and performance of tags implemented as array vs text vs full text
In terms of search capability and performance, what are the pros & cons of implementing tags as an array or text or full text field?
I am seeing limitations of capability in choosing to implement ...
2
votes
3answers
405 views
Change column type from varchar to text in all tables at once
I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text.
If anyone knows that I would be very glad for your help.
1
vote
2answers
68 views
In PostgreSQL is there a way to rank/weigh columns so as to order the results?
Let's say I am searching through three text columns. I don't just want results that match a given word or phrase, but I also want to weigh them (column A > column B > column C) - thereby ...
2
votes
2answers
108 views
Optimize a query on two big tables
I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have ...
1
vote
0answers
48 views
appending 2 tables based on common columns in Postgresql 9.3
I have 2 large tables that I need to combine into a single table--for statistical analysis. The two tables have some variations in column named, otherwise I would just use an INSERT statement. So I ...
1
vote
1answer
69 views
INSERT from a SELECT combined with a single value
I have this query that returns several rows of item_id:
select item_id from properties where name like 'body';
And I have the second one that returns 1 row of tag_id:
select id from tags ...
1
vote
1answer
628 views
SQL query to find all parent records where all child records have a given value (not just some)
An Event has many Participants. A Participant has a field of "status".
I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'.
I ...
