Tagged Questions
0
votes
0answers
6 views
Joining two table by mask
I have 2 tables PAGE and SITE. PAGE table has field URL. And SITE table has field DOMEN_URL. I want to join two tables on these fields. But I want to condition of joining is that url of the page ...
0
votes
1answer
42 views
SQL query: how do results get retrieved via “any” in own columns?
Another SQL question. I have the following query:
SELECT EXTRACT(epoch from dt) as diff
from (
SELECT time_col - lag(time_col) OVER dt
FROM myTable where elementID=1234
) as dt
This ...
1
vote
2answers
41 views
Rollback Transaction on Trigger ERROR
I'm trying to check if the room that is going to be inserted in the system is already rented at that date or not. I've though about counting the rows that match both the room number and the date, and ...
1
vote
1answer
20 views
Postgresql: Calculate time difference between elements of a column for an element
Assuming I have the following table:
id | elementID | date | time
----------------------------
What I search is the following:
select all "time"-Values for a given elementID order by date,time ...
2
votes
2answers
52 views
String seems to end query
I am storing user's liked pages from facebook in my postgres database, one of them being Sinead O'Connor's page. It seems like when it gets to the apostrophe, it terminates the query beacuse the ...
2
votes
2answers
41 views
Select max value among rows where another value is the same
Sorry if the topic name seems confusing -- I couldn't think of a better way to phrase it.
I'm stuck with a SELECT statement. I have a DB with 3 tables:
Customer (PK cid, name, city, gender);
Goods ...
0
votes
1answer
33 views
Join two tables using common column in postgres
I've got three database tables that are connected trough each other but not directly. Here are the tables:
Table one
-----------
id
rank
table_two_id
Table two
-----------
id
amount
table_three_id
...
-2
votes
0answers
12 views
postgresql.CIDR and sqlalchemy
SELECT net FROM nets WHERE net >> '10.0.0.0/8'
How to convert this sql-code to sqlalchemy? 'net' has type CIDR in postgres. Is it possible?
0
votes
1answer
41 views
Table to matrix in PostgreSQL
I have a table storing matrix information in the format:
CREATE TABLE "column" (
id serial primary key,
name varchar
);
CREATE TABLE matrix (
id serial primary key,
...
3
votes
2answers
79 views
calculating bounce rate with postgresql
I'm trying to calculate the number of organic bounces with postgresql. I want to count all the instances where a user came to site.com and them leaves after viewing the first page (e.g. row 4,5 and ...
1
vote
1answer
53 views
What's an elegant way to find the minimum value in each row of a table?
I've got a table which has a row per product, and the price that product has on ten different merchants. What I'd like to see is the minimum price each product has among those different merchants.
...
1
vote
2answers
38 views
Returning results from a function in 'select statement' format
I have a function that looks like this:
CREATE OR REPLACE FUNCTION mffcu.test_ty_hey()
RETURNS setof record
LANGUAGE plpgsql
AS $function$
Declare
cname1 text;
sql2 text;
...
0
votes
2answers
28 views
SQL or filesystem for FAST storing files/BLOBs?
I have an app that stores quite a lot of publications as files on filesystem, using nested dirs like "6/0/3/6/....". Files are not huge (.jpg, .pdf, similar documents), there's "just" a lot of them, ...
0
votes
1answer
25 views
Dynamic Sql: Create array from records using array of column names
I am pulling all of the column_names (cname1) from a crosstab table that I made. There are thousands of these column names so I combined them into an array. I then want to use dynamic sql (or whatever ...
1
vote
1answer
37 views
PostgreSQL insert into from select but ignore existing rows
I want to insert data into a table from a select. This works fine so far...
INSERT INTO table_2
SELECT t.id, 1
FROM table_1 t
WHERE t.title LIKE '%search%';
But when I run this ...
