1,124 questions
0
votes
1
answer
80
views
Select only distinct values from two tables on pgadmin without getting all combinations
I have two tables.
table 1
Column A
Column B
rgz
1
dry
1
vgy
2
chy
3
pom
2
table 2
Column C
Column D
tuv
10
tuv
11
chx
22
lmn
34
I want the following result shown on data output of postgres PgAdmin
...
- 3
-2
votes
2
answers
53
views
Merge two rows with both possibly being null
In PostgreSQL, I have a table with some settings the user can make. In that table there is also a "global" user that takes place if the user doesn't have any settings for them.
This query ...
- 1,208
1
vote
1
answer
2k
views
Upgrading PostgreSQL 9.2.15 to 16.1 - Incremental Version Updates?
I'm dealing with an older PostgreSQL database running on version 9.2.15. I aim to upgrade it to the latest version 16.1 without losing data if possible. Should I perform incremental updates to major ...
- 11
0
votes
0
answers
52
views
User's Online Time and Period with no Users Online
Hello good people of stackoverflow!
I need help with writing a query, to identify online time grouped by users, and also to identify which are the periods where there were 0 users online and for how ...
- 1
1
vote
1
answer
356
views
insert if update subquery returns 0 rows
Specs:
PostgreSQL 9.2.18 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
I'm on MacOS 13.2.1, using DBeaver IDE (version 23.0.4.202305071722) to connect to ...
7
votes
0
answers
8k
views
Upgraded pgAdmin shows warning, not displaying Tables list, queries working without problem
I upgraded my local Postgresql test server to PostgreSQL15 today morning.
Opening pgAdmin gave me a warning that some of the features may not work correctly because I have a server with version 9 ...
- 183
0
votes
1
answer
238
views
Unable to query a table in hive metadata
I am not able to query the contents of the Notification_log table.
The table is in a Postgres 9.2 instance that stores my hive metadata.
hive=# SELECT table_catalog, table_schema, table_name
FROM ...
- 57
0
votes
1
answer
360
views
How to compare a value against a column value containing csv in Postgres?
I have a table called device_info that looks like below (only a sample provided)
device_ip
cpu
memory
100.33.1.0
10.0
29.33
110.35.58.2
3.0, 2.0
20.47
220.17.58.3
4.0, 3.0
23.17
30.13.18.8
-1
26.47
70....
- 3,056
1
vote
2
answers
183
views
How to apply different conditions for same column and output as new columns in Postgresql?
I have a Postgres table that looks like below
ip | up_score
-----------------+-------------------
223.110.181.122 | 1
242.123.249.85 | 0
10....
- 3,056
1
vote
0
answers
138
views
(Golang)How to call function postgres output REFCURSOR in golang ("github.com/lib/pq")
i don't know how to call function with output REFCURSOR in golang
My function
create function find_all_by_user_id(OUT rc_out refcursor, p_user_id bigint) returns refcursor
language plpgsql
as
$$
...
- 79
0
votes
0
answers
23
views
I want to extract the last substring "TEXT" but the INSTR syntax doesn't do in postgresql. Please help me
Select text,
Instr(text,’.’,1,4) as pos_4dot,
Length(text) as len_string,
Substr(text,Instr(text,’.’,1,4) as pos_4dot)+1,length(text)-Instr(text,’.’,1,4) as pos_4dot))
From ...
0
votes
1
answer
222
views
Postgres insert into selct on conflict
I would like to insert into a table values which come from another table without having duplicates.
I tried this way but i doesn't work for me..
could you please assist me to find the right query for ...
- 711
1
vote
1
answer
247
views
How to Create a New Dataframe From Old Dataframe Based on Conditions
I have a situation at work where I need to create a new Dataframe from a previous one
The previous dataframe looks like:
|id|name|balance|deleted|minimum_balance|payment_provider_account_id|type|...
1
vote
1
answer
56
views
Can these queries be combined into one using a subquery?
One query finds the aggregates the subtotals by country:
SELECT
customer.country,
SUM(i.subtotal) AS total
FROM
invoices i
LEFT JOIN customer ON i.customer_id = customer.id
WHERE
...
- 353
1
vote
2
answers
3k
views
how to add a plus sign and milliseconds to a date in postgresql?
the date is like 2021-07-23
the expected output is like this 2021-07-24 00:00:00+08 notice the +08 ?
I tried like this below, but i am not getting the plus symbol
s_table.school_date + INTERVAL '1 day'...
- 5,511
0
votes
1
answer
98
views
Postgresql 9.2 error with group by which doesn't appear in postgresql 12
I have a postgresql function with 'GROUP BY' which works fine on my local server (postgresql version is 12.6) but gets the famous error 'column "f.fa_docnum" must appear in the GROUP BY ...
- 11
1
vote
1
answer
3k
views
Postgres flyway create concurrently index error
I want do something like:
CREATE OR REPLACE FUNCTION __column_exists(TEXT, TEXT, TEXT) RETURNS bool as $$
SELECT exists(SELECT 1 FROM information_schema.columns WHERE (table_schema, table_name, ...
- 115
0
votes
1
answer
64
views
Truncate letters from the end in postgres?
I have col A in the table and I want to truncate all the letters after last digit in the values of col A and create col B.
Col A
Col B
A1234ABC
A1234
B1234AB
B1234
How can I convert col A to col B in ...
0
votes
2
answers
48
views
How to get Distinct Value in Postgresql?
I have a table approval like below..
m_requisition_id
ad_wf_activity_id
bpname
designation
1014546
1014546
Abul Kalam Azad
Asst. Manager
1014546
1225097
Md. Hasan Zahir
Plant Manager
1014546
1229239
...
- 442
1
vote
1
answer
1k
views
Postgresql multi insert from array of composite type and one additional column
What I have:
CREATE TYPE Item AS (
a bigint,
b bigint
);
CREATE TABLE items (
id bigint NOT NULL,
a bigint NOT NULL,
b bigint NOT NULL
);
CREATE OR REPLACE FUNCTION items_insert(
...
0
votes
0
answers
2k
views
Cannot upgrade postgresql in gitlab from 9.2 to 9.6
I have upgraded gitlab version from 9 to latest with failures and reverted back the changes in order to run. Solved most of the issues but stuck with this one
the logs for sudo gitlab-ctl tail is ...
2
votes
0
answers
529
views
Hikari CP - Closed conection by Hikari is still alive in database server
I am using Spring Boot application to connect to remote Postgresql server (9.2) running behind Azure Network Security Group.
When I set the hikari properties to default (means maxLifetime as 30 mts),I ...
- 463
1
vote
1
answer
1k
views
How do I convert character varying to timestamp in postgresql which is in the format [ "2020-09-21T13:56:58Z" ]
I tried doing this but got an error as:
ERROR: column "2020-09-21t13:56:58z" does not exits
Select to_timestamp("2020-09-21T13:56:58Z", "YYYY-MM-DDTHH24:MI:SSZ");
-1
votes
1
answer
282
views
How to use multiple columns but only one column in GROUP BY and ORDER BY
I want to display multiple columns from the join table, but only use one column to group by and order by
My PostgreSQL query is like this:
SELECT DISTINCT ON (r.id_rekrutmen)
r.id_rekrutmen,
...
0
votes
1
answer
71
views
Count Joins from Multiple Tables
For reference, I am using Postgres 9.2.23.
I have several tables where one table (user_group) is related to some other tables (eg: posts, group_invites, and some more other ones). There is, also a ...
- 564