Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
1answer
26 views

Ge the value of id in master table and supply it to other tables

I have 5 tables, of which one is master table and other 4 are the tables with have the foreign key of the id of the master table I have to create a procedure which INSERT's data in all the tables ...
2
votes
1answer
23 views

How to delete global variables set in PostgreSQL

I had Set a global variable using SET bc.xyz=value but now I am not able to delete it. Please help me in it.
1
vote
1answer
66 views

How can I lock rows FOR UPDATE without returning data over the connection?

I want to do something like this: begin; select * from foos where owner_id=123 and unread=true order by id for update; update foos set unread=false where owner_id=123 and unread=true; commit; The ...
-4
votes
1answer
45 views

test if the solution & SQL understanding is correct [closed]

Employee table- has several attributes such as EMP_ID, TITLE, DEPARTMENT, etc. a date column enabling us to know when was it updated. what does this query do? SELECT emp_id ,COUNT(*) as ...
0
votes
0answers
11 views

How to create index for XML query in PostgreSQL

I am using PostgreSQL 12. below is my table schema create table soa_rawdata_lp ( date_created timestamp with time zone default now(), filename varchar(128), xmldata xml ); Each ...
-1
votes
1answer
24 views

When creating a FOREIGN KEY constraint, get “ERROR: column ”xyz“ referenced in foreign key constraint does not exist” [closed]

I keep getting this error in my psql database; bikefacility=# ERROR: syntax error at or near "c" bikefacility-# LINE 1: c bikefacility-# ^ bikefacility-# bikefacility=# ALTER TABLE bicycle ...
0
votes
3answers
16 views

Does PostgreSQL and other databases have a feature to reset serial/identity values?

I recently started a new personal project. The first thing I do after thinking about it is to construct an install.php script which consists of a series of database calls where it does things like ...
0
votes
2answers
33 views

Postgres query slow, shows long running active queries, statement_timeout does not work

I have a Postgres database that's accessed through a node.js api using a mobile react native app. Don't thing the code stack specifics matter, but essentially there are queries in the psql db that ...
0
votes
1answer
24 views

Running the .sql script in Linux to get the postgresql database table and functions

I am using Red hat Centos 7. When I use this command to run the .sql script to get postgres database table and fucntion: $sudo psql -U postgres -d tsdb -W -f /.sqlscript/location It asks for postgres ...
0
votes
1answer
21 views

Can I change the wal_level from logical to replica? If so are any impact on the replication process?

Is there a chance to change the wal_level from logical to replica without any impact. I know this involves a restart of services but is this possible? For example if there is a already a logical ...
0
votes
0answers
15 views

Why can't I access some tables in postgresql [duplicate]

I am running into some strange problem in PostgreSQL $ sudo -u postgres psql Blogging psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1)) Type "help" for help. Blogging=# \dt List of ...
2
votes
1answer
38 views

Strange performance of a top-N query with Postgres

I have a table transfer_event from which I need to find the latest n entries for a set of tokens; both n and the tokens are supplied by the user. I am using Postgres 9.6. The question is that if I ...
0
votes
1answer
34 views

Most efficient way to query a date range in Postgresql

I have a table with a timestamp with tz column. I want to perform a count / group by query for rows within a certain date range: select count(1), url from app_pageview where viewed_at between '2019-...
0
votes
0answers
22 views

Create event/lambda from materialized view refresh in PostgreSQL

I have a materialized view in PostgreSQL that I refresh say every fifteen minutes. I want to create an event triggered if that materialized view is not empty upon refresh (so that I can send AWS ...
0
votes
0answers
22 views

Postgres. INSERT INTO table in stored procedure with array of composite type objects as procedure arguments

I have two composite types: CREATE TYPE truck.truck_event_type AS ( event_global_key VARCHAR(100), event_type VARCHAR(50), datetime_utc TIMESTAMP(4), ...
1
vote
2answers
38 views

Are there side effects to Postgres 12's NOT MATERIALIZED directive?

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the ...
5
votes
3answers
40 views

Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?

I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...
0
votes
1answer
45 views

How to improve the performance of query xmldata in Postgresql 12

I use PostgreSQL 12 for storing XML data. This is my first time to save XML to RDBMS. Each XML data is about 4.2MB. Below is my table schema: create table soa_rawdata_lp ( date_created timestamp ...
0
votes
0answers
24 views

pg_restore idling during second half of restore

We're migrating from PostgreSQL hosted on our VM to Azure Database for PostgreSQL managed database. One of the first problems we hit has been time to restore database dump. It took 20-30 min on our VM ...
1
vote
2answers
20 views

function checking type of input and reacting

I have a function that can check if each element in an array is present in a given column of a table, and it works fine. I would now like to extend the function so that it can handle json/jsonb arrays ...
0
votes
1answer
14 views

Modelling a user-defined filter set in SQL

In my database I have a table containing data that I need to filter. The exact contents of this table aren't important so I'll just call it data. Now, my users also need to be able to create filter ...
0
votes
0answers
10 views

Does replica config parameter affect primary/master RDS Postgres?

I want to add hot_standby_feedback = on to a RDS readonly replica. The problem is that the replica currently has a parameter group already attached, which is inherited from the master. My question is:...
0
votes
1answer
35 views

PostgreSQL not using varchar index when ordering by primary key

My company has a pretty large PostgreSQL 9.4.5 database (we'd rather not try updating it atm as it's stable), one of the tables used to hold some sort of statistics wasn't designed well and when it ...
2
votes
1answer
37 views

Cannot see two tables with the same name

I have two non-public schemas in one database. A table named "foo" exists in both schemas and I can execute all necessary DDL & DML commands against both. However, when I execute \d, I cannot see ...
0
votes
1answer
19 views

PostgreSQL 9.6, Disable archive log or minimal archive log size when do a pg_restore

I have a production DB PostgreSQL 9.6 that is on master/standby structure. Every at 1 AM I restore a few huge tables(from the other DB server) to the master node during restore till finished the ...
0
votes
1answer
73 views

PostgreSQL 9.6 pg_rewind - takes a long time to replicate

I use Streaming Replication,and Replication works normally. Two servers are HA and PostgreSQL DB is replicated. The data in the DB is about 40G. When a failover occurs, the slave is successfully ...
0
votes
0answers
32 views

AWS RDS Postgres database connection - password

I have an AWS RDS Postgres instance. I'm connecting to it using Powershell and the ODBC connection classes on Windows. I found an issue that when my password had either a plus or minus character in ...
0
votes
1answer
34 views

Can I modify a column's data type against a live table in Postgres?

I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields. I want to change these columns back to varchar. Can I do this against a live ...
0
votes
1answer
31 views

bridge table joined table have both the keys equal to both the keys

SELECT DISTINCT i0.id, i0.name FROM main_table AS i0 RIGHT OUTER JOIN "bridge_table" AS i3 ON i3."main_table_id" = i0."id" RIGHT OUTER JOIN "other_table" AS r4 ON i3."other_table_id" ...
0
votes
0answers
13 views

Bidirectional grant between rols for Postgres database

If I have 2 roles, role_a and role_b. If I grant all from a to b with: grant role_a to role_b; Then any table, schema, etc created by role_a can be queried by role_b. But if I create a table/...
1
vote
1answer
50 views

UUID or BIGSERIAL Foreign Keys Postgres

I have 8 tables which use UUID Primary Keys and 7 of them have UUID Foreign Keys. Would it be better for performance to use BIGSERIAL instead of UUID?
1
vote
1answer
53 views

invalid record length at 1A2/8F240298: wanted 24, got 0

Due to power failure, my server running Postgres database got crashed. Now when I am trying to start the database, I am getting the below error. < 2020-01-04 11:40:39.409 CET > LOG: database ...
1
vote
1answer
35 views

Postgres: how to limit the number of query results by aggregate type

Consider the following table definition CREATE TABLE associations ( id SERIAL PRIMARY KEY, resource_id integer, target_id integer, relation_id integer ); which is used for specifying ...
1
vote
2answers
109 views

Database design issue, with multiple variables and constraints

I'm using PostgreSQL. I have the following entities Categories, Items, Attributes. Relationships: M2M between categories and items (an item can be in multiple categories, a category can have ...
1
vote
2answers
21 views

Add data directory on PostgreSQL 10

I have a postgresql database on / root partition (61GB used out of 63GB), so I asked the VPS provider to add more disk (50GB to /mnt/vdb1) How to add another data directory to that new partition (...
1
vote
1answer
20 views

DB Design - Assigning users to multiple sites with different roles

I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules: A user can manage multiple sites A user can only have one role per site A ...
1
vote
2answers
37 views

Postgres: update single boolean column on 12 million rows

I have a table with 12 million rows. There are 16 columns on the table, one of which is a boolean. After a certain business process, a large chunk of these rows will have this boolean set to false (...
0
votes
2answers
25 views

PostgreSQL dump distinct column without memory consumption

I'm trying to dump csv of distinct varchar(43) column to file, table has around billion rows, select distinct command either runs out of memory and command is aborted or server just closes all ...
0
votes
3answers
55 views

Indexing only for most recent entries in PostgreSQL

Consider the following table: |-----------------------------------------------------| | raffle | |----|---------|----------|-----|---------------------| | ...
-1
votes
3answers
56 views

ID, UUID and “deleted_at” columns, How to avoid performance degradation?

Consider this table, but with thousands of records, on PostgreSQL: | id | uuid | color | ... | deleted_at | |----|---------|----------|-----|---------------------| | 1 | 4fc1... | red ...
0
votes
0answers
34 views

I can't successfully install postgreSQL. 'Problem running post-install step' error message pops up

I have successfully installed postgreSQL previously, but I am struggling to install it in one particular machine (Windows 8.1 Pro). Everytime I try to install it I get the following message: Warning ...
0
votes
1answer
22 views

Would refreshing a materialized view in a transaction allow me to have test-only specific data on it?

I have created a postgresql database used in integration tests for my code. Its schema is consisted with some materialized views and the code I want to test uses them, therefore I need once I place ...
3
votes
1answer
73 views

How to store chess positions in a relational database (in particular, Postgres)

I'm exploring the idea of building a website that involves analysis of chess positions. Chess positions themselves are described using a format called FEN, e.g. the starting position for a chess board ...
1
vote
1answer
35 views

Does “alter table … set data type” functionally differ from “alter table … type”?

The docs show [SET DATA] TYPE which implies the SET DATA part is optional, but the 9.1 release notes also imply that SET DATA TYPE may be different: Allow ALTER TABLE ... SET DATA TYPE to avoid ...
1
vote
0answers
26 views

Difference between LC_COLLATE='C' and LC_COLLATE='C.UTF-8' in PostgreSQL [duplicate]

I need guidance on the exact distinction, if any, between LC_COLLATE='C' and LC_COLLATE='C.UTF-8' in PostgreSQL. It seems to me that they behave exactly the same and that the codeset (with bearing on ...
1
vote
1answer
26 views

postgresql: make objects owned by parent role

I have a set of apps that produce data. Each app has its own credentials to access the DB, and they all inherit from a common role (producer). I'd like to set default privileges to grant access to ...
2
votes
1answer
18 views

why do I need \connect hostdb

In the answer to How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA? I see the line \connect hostdb why and when do I need this line? I'm executing role creations SQL line from a ....
0
votes
2answers
39 views

Update rows in table with set-returning function

I am trying to update a table of 100 already existing rows with 100 random values from a Gaussian distribution, for which I am using the normal_rand function from the tablefunc extension. Since this ...
0
votes
1answer
21 views

Postgresql comment on specific role

I am trying to store a specific user's description in the Postgres by doing this: comment on role blog is 'blog user long name'; However, when I look for it in the pg_description table it is not ...
0
votes
0answers
5 views

Adding Business Identifier to Multi-tenant Rate Limiting Table

My table keeps tracks of attempted actions by IP: postgres=# \d rate_limit Table "public.rate_limit" Column | Type | Collation | Nullable | Default ...