Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [postgresql]

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

0
votes
0answers
10 views

Variable length Work Histories in a RDB

Am building a database for CVs/Job Applicants in Postgres (via Django), which would naturally include work histories for every person. Have to consider the variable length of people's careers, which ...
3
votes
1answer
697 views

postgresql: deleting from a view

In Postgresql 9.4, we're doing a view as a UNION of to tables A and B, and we'd like to have a single DELETED N ROWS message when we 'delete' the records in the underlying tables (via a a trigger on ...
1084
votes
7answers
2.3m views

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...
0
votes
0answers
10 views

Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database

We have one master table and multiple child tables (dependent tables) associated with it. While taking the backup of the master table , I would like to take the backup of all its child (dependent) ...
0
votes
0answers
15 views

Sync two postgres databases from two sperate locations with no network

I have 2 postgres databases running the same schemes on two different computers, that are not guaranteed to have any network connection while having to handle read/write requests. Because they ...
0
votes
0answers
11 views

Postgresql - Usage of index on an inherit table but not for the original table (with the index)

My Postgresql database (postgres version 11.1 and postgis version 2.3) consists of hundreds of tables and each table contains 10 of millions of rows. Data is divided by month and then each month table ...
0
votes
2answers
38 views

Slow Sum/Group By over large amount of rows

Please can you help with a slow query that we have with a large amount of data (~500K rows). We're on Postgres 10. SELECT mike_romeo, SUM(hotel) as counts FROM whiskey_lima WHERE yankee = 'kilo' ...
10
votes
5answers
34k views

PostgreSQL user can not connect to server after changing password

I've met this with 4 roles I created: After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more. pgAdmin III show error message: An error has occurred: ...
0
votes
2answers
29 views

Logical Replication to different schema name

We have PostgreSQL 11 database with structure one database one schema (public). dbname : production_one schema : public dbname : production_two schema : public I would like to set up a logical ...
0
votes
1answer
217 views

Postgres row_to_json precision for numeric type

I am using the row_to_json function to convert a table row to a json object. There are a few columns in the table that are of type numeric which contain float values. When row_to_json returns the ...
3
votes
2answers
539 views

Postgres 9.0 vs Postgres 10 & 11 Performance

We want to move our databases from webhosting provider (postgres 9.0) to our local network server (tried both postgres 10 and latest 11) Our machine is windows server, fast XEON machine with 16gb ram, ...
1
vote
1answer
43 views

Use NEW in FROM clause in Postgres trigger?

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g. SELECT unnest(something) FROM NEW However, this seems to lead to an error: relation '...
0
votes
0answers
12 views

How to Set Up a Shadow Production Database for PostgreSQL?

I'm working on a Django project and I'd like to avoid the common pitfall of using SQLite for development and PostgreSQL for production referenced in Two Scoops of Django. I'd like to take this a ...
4
votes
2answers
157 views

Different syntax to add/substract interval

Till now I used the following syntax to add an interval to a timestamp: select now() + '5 year'; This worked fine till I tried to subtract the interval which results in a syntax error. invalid ...
3
votes
2answers
2k views

PostgreSQL use NEW in query for INSTEAD OF trigger

I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario: CREATE TABLE Product ( product_id ...
0
votes
1answer
221 views

Postgresql - archive_command Windows, not working

I'm attempting to set up archiving on a windows server machine but when I check the logs nothing is happening. The postgresql.conf file this is my input: #wal_level = replica #archive_mode = on #...
2
votes
1answer
22 views

How to “group” based on consecutive occurrences of an element in a column in postgres

I have a table that, when ordered, describes a path by point descriptions in each row. A column tells whether a row is a special (stop) point within that path and is empty otherwise. The path starts ...
0
votes
0answers
13 views

Fixing problems with postgreSQL wal archives

i just took over working postgre sql server, and i noticed in the logs, that are in: /var/log/postgresql/postgresql-9.4-main.log spitting out this warnings: cp: cannot stat 'pg_xlog/...
0
votes
1answer
15 views

PostgreSQL how to return data from anonymous code block?

I have a table mytable (id, "someLongURI", status, "userId") and a working query: UPDATE mytable SET status = 'IN_WORK' WHERE "someLongURI" IN ( SELECT "...
2
votes
1answer
280 views

Marking duplicate records

I have a list of things, some of which are duplicates. create table things ( id int primary key, title text not null, author text not null, duplicate_of int null references things(id) ); I'...
0
votes
0answers
10 views

Postgresql streaming replication sticky read queries

I have setup 1 master node and 1 slave node with postgresql streaming replication, is it possible to configure at database level to allow certain read queries to be executed in slave node only instead ...
-1
votes
1answer
28 views

pls give the solution . this query will not work [on hold]

select * from masters."RCM" where id in (select rcm_id from masters."TASK" where id in (select audit_details->>'task_id' from masters."...
0
votes
0answers
8 views

Partitioning Postgres table that have references to and queried without date range filters

As I understand partitioning is useful only for fact tables that isn't referenced by other tables and when you issue range select queries in case if you also have retention policy. Spring Batch ...
0
votes
2answers
22 views

How to update column with ascending value column

I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this? ...
-2
votes
1answer
32 views

Postgresql - LEFT JOIN error

Apologies ahead of time for the novice question as I am working through building my first database. I've come to joining my tables (I have multiple), but have decided to test one first. I am trying to ...
8
votes
3answers
2k views

Is the keyword “ALIAS” actually used?

According to PostgreSQL 7.1 through 9.1 (now unsupported), ALIAS is listed as a reserved word, at least for SQL-99. Later versions do not show it - suggesting that it has been dropped as a reserved ...
0
votes
1answer
265 views

When exactly does pg_stat_all_tables.last_autovacuum get updated?

I'm looking into issues with our autovacuum settings and trying to understand last_autoanalyze and last_autovacuum from the pg_stat_all_tables. I understand autovacuum to work in an incremental way, ...
4
votes
1answer
709 views

pg_dump with ssl certificates

I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
0
votes
1answer
233 views

Can any one Explain me What is the Use of Cascading Replication in Postgresql?

What are the advantages of cascading replication in PostgreSQL. I have done Testing On this With three nodes. M1---->S1----->S2 What is the Use if My master(M1) goes Down(failover). Where the ...
1
vote
0answers
21 views

pgbench - 20-30% variation in benchmark results (non-repeatable benchmarks)

Problem I'm trying to use pgbench to help me tune Postgres for my hardware, but I'm seeing a very strange behaviour. I cannot seem to get a stable number for TPS in consecutive runs of pgbench. Since ...
3
votes
2answers
82 views

GRANT SELECT ON SEQUENCE succeeds, but to no effect?

I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the ...
7
votes
2answers
6k views

Amazon RDS Postgresql adding new extensions

I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature ...
0
votes
1answer
17 views

Joining three tables with intermediate nullable option and filter condition

Sorry if this seems like a dumb question, but I've read through tens of answers and tried many different options, yet my query results were either overly inclusive or exclusive. The basic scenario is,...
5
votes
3answers
4k views

How to perform conditional insert based on row count?

I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table: Table "public....
1
vote
1answer
42 views

Query to obtain weighted percentile

Trying generate SQL to compute a weighted continuous value at a given set of percentile values (the 25%,50%,and 75% levels used below, but solution should allow for an arbitrary parameter level). In ...
2
votes
0answers
29 views

Getting unfamiliar error when using terminal command to update database

I'm a bit in over my head when it comes to using and managing large databases. I run a small startup that uses a PostGreSQL and pgAdmin to administer and query the database, which is housed on Herkou. ...
32
votes
5answers
21k views

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in this SO post. What I would like to know is: are there ...
0
votes
0answers
33 views

Slow PostgreSQL query - how to improve it?

[Beginners question :-) hopefully on-topic here.] In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to ...
1
vote
1answer
719 views

The server's DateStyle parameter was changed to ISO. The JDBC driver requires DateStyle to begin with ISO for correct operation

I use PostgreSQL 9.6.6 on CentOS Linux 7.4 and after upgrading the postgresql-jdbc package - # sudo yum update Failed to set locale, defaulting to C Loaded plugins: fastestmirror Loading mirror ...
5
votes
2answers
10k views

How can I store latitude and longitude without PostGIS?

I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a ...
0
votes
0answers
33 views

Postgres pg_dumpall failing

Recently on my debian server with postgres 10.6, pg_dumpall has started to fail with the following error: su - postgres -c pg_dumpall | gzip > /temp/directory/postgres_backup.gz pg_dumpall: query ...
1
vote
1answer
23 views

Replication to subscriber with different name

Is it possible in PostgreSQL to set up the following replication scenarios? Replication between 2 servers with different database name. For example, replicate FROM server_A (primary) database name ...
0
votes
2answers
30 views

Get value increment during an hour of a day

I query YouTube Data Api for a list of most popular videos on a channel and then get their statistics, 4 times per hour (each 15 minutes, by cron). The data is stored in Postgres, but dumping it and ...
0
votes
1answer
39 views
+100

Postgres 9.6 - Taking differenctial backup

I have a few CentOS boxes running Postgres 9.6. I am planning a central server which can make local copies of all DBs on the other servers. These servers are accessible via SSH on Internet. What is ...
3
votes
2answers
5k views

WAL archive: FAILED (please make sure WAL shipping is setup)

I am trying to configure Barman to backup. When I do a barman check replica I keep getting: Server replica: WAL archive: FAILED (please make sure WAL shipping is setup) PostgreSQL: OK superuser: OK ...
25
votes
4answers
3k views

Can spatial index help a “range - order by - limit” query

Asking this question, specifically for Postgres, as it has good supoort for R-tree/spatial indexes. We have the following table with a tree structure (Nested Set model) of words and their frequencies:...
1
vote
2answers
34 views

Count items on condition

In an application where a user can have copies of a mobile application installed on multiple devices I'd like to know how many iphone and how many android apps each user has. I tried a query such as ...
5
votes
3answers
1k views

Full Text Search With PostgreSQL

i have a table with this rows: Stickers ------------------------------------------------------ ID | Title |Keywords (ts_vector) ------------------------------------------------------ ...
2
votes
2answers
649 views

Trigram index for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
1
vote
1answer
30 views

How to get the size of an index created in a plpgsql block

I'm trying to benchmark something. I want to create an index and get it's size in a plpgsql block. This works: haki=# create index ix on t(n); CREATE INDEX haki=# select pg_relation_size('ix'); ...