All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
0
votes
0answers
3 views
Postgresql binary data read C++
///outfilename=/opt/PostgreSQL/test.jpg,sql = select info from bytea_test where id = 1;
int PGOpApp::PGExportFile(PGconn *conn, const char *outfilename,const char *sql)
{
const ...
1
vote
1answer
6 views
Subtracting sets within a grouped column, should I be pivoting?
I'm not the best at explaining, but I have a table that has the format
CREATE TABLE foo
AS
SELECT type,date::date,tp,price
FROM ( VALUES
( 'A', '2010-10-01', 1, 0.05 ),
( 'A', '2010-10-01'...
0
votes
2answers
30 views
CASE WHEN with GROUP BY
I have a table that looks like this (oversimplified case of the actual case to make my point)
CREATE TABLE sales (
id int,
currency VARCHAR(5),
price int
shop_id int
);
Now I need to ...
-1
votes
1answer
28 views
Handling millions of rows
In our project, there is a table that has two foreign keys columns, with an enum type column.
How to make sure that we are not exceeding the limit when inserting millions of rows? how many M row is ...
3
votes
1answer
32 views
pg_restore: [custom archiver] could not read from input file: end of file
Not going into details how that happened, all started with a corrupt DB. It was OK on the file-system level, but trying to:
select count(*) from mytable
resulted in the following:
ERROR: could ...
1
vote
0answers
19 views
I have a field which stores json data. I want to write a query to check if the required value is in the json key [on hold]
table name appliedsla with fields ticket as foreign key and sla_details in which the sla_details store json like
{L1:{},L2:{'applied:True}}
and now when i query for "L2" i want all the tickets ...
1
vote
0answers
21 views
Table sync without admin rights
I have a client that really (really, REALLY!) wants to use a standard database server through a specific hosting company. That company offers vanilla installs of PostgreSQl and MySQL only.
The client ...
0
votes
0answers
25 views
How to Alter Postgres Tablespace
I cant seem to create a table in a particular tablespace. For instance, when I do:
CREATE TABLE mytable (rc_id integer NOT NULL) TABLESPACE my_space;
and then I do:
select * from pg_tables where ...
2
votes
2answers
29 views
Postgres Proper Index for Sorting and Join
I have a simple schema and query, but am experiencing consistent awful performance with certain parameters.
Schema:
CREATE TABLE barcodes (
id integer PRIMARY KEY,
value citext NOT NULL
);
...
2
votes
0answers
24 views
Postgresql 9.6 best settings for large admin work (min_wal and max_wal)
We have a server that's disconnected from the world. It's a high end system with 48GB memory and 500GB SSD hard disk, 16 core CPUs. We're trying to do a pg_restore of a simple database dump of less ...
3
votes
6answers
40 views
Postgres join where foreign table has ALL records
I have this people and tags table, like this,
CREATE TABLE people
AS
SELECT *
FROM ( VALUES
(1,'Joe'),
(2,'Jane')
) AS t(id,name);
CREATE TABLE tags
AS
SELECT * FROM ( VALUES
(1, ...
1
vote
0answers
21 views
How to set connection timeout for pgAdmin 4 (postgres 9.6)
I have read the article listed here:
How to set connection timeout value for pgAdmin?
many times, but I still have no idea where one sets the config parameter for connection_timeout. I am connecting ...
1
vote
0answers
12 views
Selective error logging (based on error code) in PostgreSQL
I want to log all PostgreSQL errors except unique_violation (error code 23505), which I would like suppressed.
How do I achieve that?
3
votes
2answers
44 views
How to select specific value from a table that has multiple values for a record in primary key table in PostgreSQL?
I have three tables as follows (with sample data):
CREATE TABLE users (
id int PRIMARY KEY,
login varchar(20),
organization varchar(20)
);
CREATE TABLE rights (
id int PRIMARY KEY,
name ...
1
vote
1answer
22 views
How to count the amount of values grouped by some column? [on hold]
I need to create a view based on data from another table and grouped by some column value. Here is a source table:
CREATE TABLE source
AS
SELECT *
FROM ( VALUES
( 6, 1000 ),
( 6, 2000 ),
...
1
vote
0answers
55 views
Complicated unique constraints in Postgres
I have a table transactions that belongs to an account (through two foreign keys recipient_id and sender_id). Each account can have many sources through a joint table accounts_sources (a user can link ...
1
vote
2answers
30 views
problem query postgresql
I have problems when I want to generate a query in posgretsql, I was trying and I do not know how to calculate the following
My table has the following data:
CREATE TABLE "abc-ventas"
AS
SELECT *
...
0
votes
0answers
30 views
PostgreSQL 9.6 (ish): moving/running/rolling/trailing sum over date range look back
How do I calculate a moving/running/rolling/trailing sum over a date range look back when:
A date/record is allowed to be missing entirely and periodicity is unknown (hence the date range look back ...
3
votes
0answers
27 views
PostgreSQL could not connect to server on AWS
I am currently using AWS's free tier. I am receiving the error message:
could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "xxxxxxxxx" (-) and ...
0
votes
0answers
17 views
Upgrade Postgres from 9.5 to 9.6
Our postgres servers are hosted on AWS. We tried an upgrade from 9.5 to 9.6 a few days back. After the upgrade, the database stopped taking new commands. Even a pg_cancel_backend would not run. This ...
1
vote
3answers
34 views
Transfer datas from Access to PostgreSQL
I'm trying to insert some data from Access to PostgreSQL database, but it's not a simple query, because it's a nested query.
For example :
rst.Open "INSERT INTO car(id, model, color, type) SELECT (...
0
votes
0answers
16 views
Joining recursive table in PostgreSQL
Situation
I have two simple tables, one called "node_relations" and one called "node_information". The table "node_relations" represents a simple parent-child hierarchy, having only two columns "id" ...
0
votes
0answers
25 views
PostgreSQL performance tuning and max connections (practically)
For the past few weeks I've been tuning and messing with PostgreSQL which I'm going to use with my next project.
My Specs are:
DigitalOcean 8 Cores 16GB SSD x2 (One for DB, another for Web)
Centos 7
...
3
votes
4answers
88 views
Query slows with additional FROM item (even with no extra actions)
I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms:
SELECT COUNT(body)
FROM comment
WHERE body LIKE '%[deleted]%';
...
3
votes
1answer
27 views
How do I get HeidiSQL to connect to Postgres databases for the first time?
With two different Linux servers I have installed Postgres on them using no standard configurations. They are in different networks off-site.
The Postgres service is running (as I checked by running ...
4
votes
2answers
45 views
Can I let PostgreSQL report the offending rows when a multi-row INSERT fails because of mismatched geometry types?
Inserting multiple rows into a table with a single INSERT INTO statement fails (as expected) when some of these rows have values of a PostGIS geometry type incompatible to the respective row's PostGIS ...
1
vote
0answers
22 views
Tracking different users accessing database
I am using postgresql for my HR login web based application.There are many users or Roles (actually HR's) who are accessing the table like customer_data.I as a admin user (role with superuser access) ...
2
votes
3answers
147 views
Find rows in which the column doesn't contain a “space”
I'm using Postgres 9.5. I want to search for rows in which my name column does not contain a space. I'm a little murky on how to define a space to you, though. I thought it would just be the space ...
1
vote
1answer
20 views
Ensure N:ALL relation ship for some rows
I have a simple N:M relation ship:
Table User:
Column ID
Column username
Column is_superuser (Boolean)
Table Permission:
Column ID
Column Name
Table UserPermission:
Column ID
ForeignKey User
...
1
vote
1answer
35 views
Can I let PostgreSQL report the offending rows when a multi-row INSERT fails?
When inserting multiple rows into a table by a single INSERT INTO invocation, and the INSERT statement fails due to reasons specific to some individual rows (e.g. a value has the wrong type for the ...
0
votes
1answer
64 views
Have both distinct and count
I have a table users(id and name) and order(cost and user: FK to users).
users.name users.id
bob 1
alice 1
and
orders.user orders.cost
1 15
1 ...
11
votes
1answer
705 views
Does the SQL Spec require a GROUP BY in EXISTS ()
Microsoft currently permits this syntax.
SELECT *
FROM ( VALUES (1) ) AS g(x)
WHERE EXISTS (
SELECT *
FROM ( VALUES (1),(1) )
AS t(x)
WHERE g.x = t.x
HAVING count(*) > 1
);
Notice ...
3
votes
1answer
384 views
Why does a JSON element return text when it's not text?
When I try to access a JSON element using ->>'elementName' I get a texttype.
SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2)
FROM ( VALUES
('{"a":5}'::jsonb, '5'::jsonb)
) AS t(x1,x2);
...
0
votes
0answers
27 views
How to anonymize pg_dump output before it leaves server?
For development purposes, we dump the production database to local. It's fine because the DB is small enough. The company's growing and we want to reduce risks. To that end, we'd like to anonymize the ...
1
vote
3answers
71 views
Performance of count(*) in subquery
Suppose we have the following queries:
1.
SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'
2.
SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
1
vote
1answer
55 views
GROUP BY with subqueries for a Group?
Here's a simplified recreation of what I have:
CREATE TABLE "DocumentStore" (
"ID" int NOT NULL,
"Content" jsonb NOT NULL,
CONSTRAINT documentstore_pk PRIMARY KEY ("ID")
);
INSERT INTO "...
3
votes
1answer
27 views
What's happening if one of concurent lock on a table rename the locked table?
I've hundred of insert as following each minutes:
BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT INTO table1 VALUES (...);
COMMIT;
And I've the following transaction which will occured once:
BEGIN ...
-1
votes
0answers
41 views
Hack Proof Production Setup for Postgres Database [closed]
Inviting all the gurus here to review our production setup for Postgres to make our database server (almost) hack-proof. Kindly suggest any thing that we can improve upon to better guarantee the ...
1
vote
1answer
34 views
Postgresql: How to index jsonb array of objects column
I have a table that has a jsonb column which has an array of objects.
Looks like this for every row.
[{grade: 'A', subject: 'MATH'}, {grade: 'B', subject: 'PHY'}...]
Now querying over it thanks to ...
0
votes
0answers
8 views
Cannot GRANT usage privilege to public schema to a user in Postgres
I have a user "admin" that can create roles.
I'm trying to do...
grant usage on schema public to another_user;
returns...
WARNING: no privileges were granted for "public"
admin user already has......
-2
votes
1answer
17 views
PostgreSQL bug with JSON type?
This query not working, but the error not make sense
SELECT count(*) FROM t
WHERE json_typeof(info->'vatids')='array' AND json_array_length(info->'vatids')>1;
ERROR: cannot get array ...
1
vote
1answer
44 views
Need urgent help with recovering data from dropped database
I just made a huge mistake by doing a rails command on my production web server which reset the postgres database - I believe the database is dropped and reset with just the schema
I have a backup ...
-1
votes
0answers
23 views
How can i append new data from external .csv file data to postgres table [closed]
I am having same number of columns in .csv and postgres table. To my existing postgres table and I want to add new data from external .csv or excel data.
I have tried with this command:
copy test(...
1
vote
1answer
23 views
ALTER TABLE online by keeping data as BLOBs
Could you please explain me how extreme de-normalization (store the value as a serialized blob will help to ALTER TABLE online? source
Additionally, it's crucial to be able to change the schema: ...
2
votes
0answers
28 views
How to get the sequence name for a serial column
As far as I can tell, this query should show the expression for the new value of a serial column:
select d.adsrc
from (
SELECT a.attrelid, a.attnum, n.nspname, c.relname, a.attname
FROM pg_catalog....
1
vote
1answer
17 views
Some help with a crosstab query in postgresql
I have a data set that looks like this:
CREATE TABLE game
AS
SELECT team_name, match_id, sections_won
FROM ( VALUES
( 'Team A', 6 , 4 ),
( 'Team B', 6 , 1 ),
( 'Team A', 19 , 4 ),
...
0
votes
0answers
29 views
Database diffing tool for Postgres - available in Java [closed]
I have a local, testing and production database.
The issue is, that updates that I made on the local database should be merged into testing and production.
Drop and Insert is not an option since ...
0
votes
0answers
20 views
How to rename/recreate a table without disrupting service? [closed]
I've a table I need to purge without disrupting the service. About 99.99% of data should be deleted, so I'm trying to recreate the table and moving the 0.01% usefull data into the new table as ...
0
votes
0answers
17 views
PostgreSQL replication not working after base backup
Last week we had an incident with our master PostgreSQL server instance which got totally thrashed so, we had to switch to our slave as our one and only DB instance. After the initial chaos was ...
0
votes
1answer
37 views
Postgresql: How to optimize performance of a query which uses CTEs and has a jsonb column?
I'm querying through 3 joined tables and I'm using CTEs and flattering (converting jsonb to a tabbular form) one of the table's jsonb column then querying through that dynamically made table so I can ...
