Tagged Questions
1
vote
0answers
23 views
Join two tables using id and descendants from tree like table
I have the following tables:
CREATE TABLE element
(
element_id serial NOT NULL,
local_id integer,
name varchar,
CONSTRAINT pk_element_id PRIMARY KEY (element_id),
CONSTRAINT fk_element_local_id ...
0
votes
1answer
24 views
Creating a table with PSQL as clone of another table but excluding a column
Short Version:
What psql command should I use to create temporary table, which is a clone of a given table, excluding a given column?
create temp table bar_temp as <columns from foo.bar excluding ...
0
votes
1answer
41 views
UPDATE with ORDER BY
Need to "tie" UPDATE with ORDER BY. I'm trying to use cursors, but get the error:
cursor "cursupd" doesn't specify a line,
SQL state: 24000
Code:
BEGIN;
DECLARE cursUpd CURSOR FOR SELECT * ...
1
vote
3answers
49 views
PostgreSQL Select from 5 million rows table
I have table with about 5 million rows
CREATE TABLE audit_log
(
event_time timestamp with time zone NOT NULL DEFAULT now(),
action smallint, -- 1:modify, 2:create, 3:delete, 4:security, 9:other
...
1
vote
0answers
14 views
PosgreSQL full text search change dictionary
I have the following statement:
UPDATE customer SET customer_text = to_tsvector(company_name || ' ' || email || ' ' || city);
The company name field in some cases ends with the letter 'Y' which ...
0
votes
2answers
32 views
plpgsql how to just execute query in a function or procedure
I am beginning to learn stored procedures and functions in sql in a postgres database.
I need an example to get me going for what I am trying to accomplish.
I need to run a procedure and have it ...
1
vote
2answers
43 views
Compare strings by matching words
I'd like to know if such an algorithm exists and is implemented in any database (ideally Postgres).
Levenstein matches strings, but I'd like to compare strings based on the number of matching words. ...
0
votes
1answer
41 views
That is possible to add '' in execute statement (PSQL)
I want to run in postgresql that statement
CREATE OR REPLACE FUNCTION dynamic_update(nazwa_t text,index int,nazwa_k text, war text )
RETURNS void LANGUAGE PLPGSQL AS $$
BEGIN
EXECUTE 'UPDATE ...
1
vote
1answer
31 views
Combine rows into one result and add results as different values in SQL
I have a table (people) that include the following information:
id cert_id type name
1 123 owner Paul
2 123 seller George
3 123 buyer steve
4 456 ...
5
votes
1answer
129 views
Query for matching multiple rows?
We have next tables in our system
Table object
object_id object_description
1 "Car"
2 "Person"
Table attribute
attribute_id attribute_name
1 ...
0
votes
1answer
31 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
54 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
47 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
23 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
55 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
35 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
16 views
postgresql.CIDR and sqlalchemy [closed]
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
42 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
91 views
calculating number of bounces 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
57 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
42 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
31 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
29 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 ...
0
votes
3answers
50 views
Returning the results of hundreds of columns into an array
I have a table with hundreds of columns. I need to take the result of every column (except one) and put them into an array and bring back the rest of the results. Here it was the table looks like:
ID ...
0
votes
1answer
16 views
Postgres avoid Trigger on delete cascade
Hello i have a problem with a Trigger
i have 2 tables:
t_mandant
t_user_has_mandant
when i delete a row in t_user_has_mandant i call a trigger beforeDeleteUserMandant()
but i need a possibility ...
1
vote
2answers
45 views
How to generate calendar of movable feasts?
There is a table: feasts with dates stored in smallint fields (not elegant way, I know..).
It looks like that:
id - serial, PK
day - smallint NOT NULL,
month - smallint NOT NULL,
year - smallint
It ...
0
votes
0answers
36 views
Update related table (Postgres)
I am Creating two table in Postgres.
1st table have one primary column and other columns & in 2nd table have one primary column "MLeaseId" those column same in 1st table (not primary of 1st ...
1
vote
2answers
53 views
time zone confusion in relational databases
I have downloaded a postgres database in .sql format. The type of temporal column in the database is: timestamp with time zone. And dates are mentioned in the database in the form given below:
...
