All Questions
Tagged with postgresql sql
49,899
questions
0
votes
1
answer
18
views
How to add permission to custom role in postgres to create index
I am currently creating a role for a particular database using the below sql script executed as master user (postgres).
CREATE USER customrole WITH PASSWORD 'mypassword';
-- removed CREATE ...
- 884
0
votes
0
answers
13
views
Navicat 16 Backup Error: 'column d.datlastsysoid does not exist' for Postgres 15.4 Database [duplicate]
I am using Navicat 16 to back up my remote PostgreSQL database, which is running version 15.4. However, during the backup process, I encounter an error that reads:
[ERR] ERROR: column d.datlastsysoid ...
- 1
1
vote
1
answer
59
views
INSERT rows that were missing for the UPDATE
I want to write a query that, if I was using variables, would look something like (pseudocode)
records = SELECT * FROM table_1 WHERE id = x
numberOfRowsUpdated = UPDATE table_2 SET column_1 = y WHERE ...
- 95
-2
votes
0
answers
22
views
insert in to target table and avoid the rows with same set of column value from source to target [duplicate]
-- Create a temporary table with sample data
CREATE TEMPORARY TABLE temp_student (
roll_no INT,
name VARCHAR(50)
);
-- Insert some sample records into the temporary table
INSERT INTO ...
0
votes
1
answer
47
views
Selecting Rows By Specific Column Data in PostgreSQL
I'm trying to build a recipe finder app (PERN stack) like SuperCook but I ran into some problems with my postgres table.
Short description of the desired result. I'd like the user to select some ...
- 13
0
votes
1
answer
47
views
SQL using ROWS BETWEEN to aggregate 12 months, nulls included?
I'm currently working with a table with the following columns:
Supplier
itemnumber
invoice_year_numerical
invoice_month_numerical
invoice date
invoice amount
invoice qty
I'm using SUM(invoice amount)...
- 3
0
votes
1
answer
51
views
Postgres Join base table with other tables and show all records without duplicate
I have three tables:
Base table:
parent_id
parent_name
location
P1
Mr. Smith
New York
Child table:
s_id
child_name
parent_id
C1
Jane
P1
C2
John
P1
School table:
school_id
school_name
parent_id
...
0
votes
1
answer
38
views
SQL full outer join, default value to column in other table
I'm attempting to do a full outer join in Postgres to see how data changes between the past 24 hours and the 24 hours before that ("today" and "yesterday" euphemistically). Some ...
- 1,280
0
votes
0
answers
50
views
Wrong index is chosen when data are not random
I created a simple table:
CREATE TABLE logs (
user_id int4 NOT NULL,
create_time timestamptz NOT NULL DEFAULT now()
);
Inserted some random data:
INSERT INTO logs (user_id)
(SELECT RANDOM() *...
- 1,033
2
votes
3
answers
29
views
Order by the oldest relation Postgres
So I have 2 tables - Videos and Watches (with datetime). I want to order the videos by the last time watched by a given user, with the never before watched videos coming first and the rest being ...
-1
votes
2
answers
32
views
Postgres Query to join multiple tables properly
I want to join two tables to get the output as shown in Table4. Below are the details:
Table1: SalesData
Date
PlatformId
UnitsSold
Revenue
ChannelId
2024-07-01
ABCD1
12
1200
1
2024-07-02
ABCD1
11
...
- 1
-1
votes
1
answer
32
views
PostgreSQL Window Function with equality predicate
I have the following query in PostgreSQL. Everything works as expected. It returns a single row for each grp. The row returned has the greatest id within the grp.
with tbl1(id, grp, name) as (
...
- 1,092
0
votes
2
answers
73
views
How To Import a Big .csv(~2GB) Into a PostgreSQL Table
I am new into databases and PostgreSQL and today I wanted to import this dataset: https://www.kaggle.com/datasets/wilmerarltstrmberg/recipe-dataset-over-2m/data into a PostgreSQL so I can make a ...
- 13
0
votes
1
answer
43
views
Postgres - conditional queries for report
Let's say we have two tables - plan and foreign_exchange
Plan schema is
CREATE TABLE plan
(
plan_id UUID NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
...
- 363
1
vote
1
answer
38
views
SQL/Postgre combining all tables from a schema
I have multiple tables - all with the same structure/columns but with different data. The structure is following:
"timestamp" timestamp,
company text,
division text,
team int,
request text,
...
- 11