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
96 views

The opposite of ordering by a column in SQL

If you have a table and you'd like to sort by a column you can use order by foo to order the results by foo. Is it possible to do the opposite, such that rows which have the same value for foo would ...
-1
votes
1answer
25 views

How are database views executed?

Lets assume there is a design where every table is represented by a view, that is simply selecting all of the columns of the table and all the rows. --vw Select * From sys_table And I then run a ...
0
votes
2answers
252 views

Add foreign key constraint to an existing table column, referencing a column in a newly created table

Let's say I have one table defined as this CREATE TABLE sales ( agent_id integer references agents(agent_id), sale_date date, amout numeric(10,2) ); Then an ETL process fills ...
0
votes
1answer
36 views

Postgres sometimes uses sequential scan instead of an index only scan for data that doesn't exist

We are using Postgres 12 and We have a simple table like this: > \d live_event_segmentation Table "public.live_event_segmentation" Column | Type ...
5
votes
2answers
4k 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
1answer
2k views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
0
votes
1answer
31 views

How to efficiently combine user authentication with token authentication?

What is the most efficient approach of authentication, considering an user based authentication system combined with a token based authentication system? For this question please consider the ...
0
votes
2answers
15 views

Postgres normalize human name string

Is there some function to normalize name string: select normalize('R.C. Bray') 'R. C. Bray' Or the opposite direction? The simple replace('. ', '.') or replace('.', '. ') is wrong and may be ...
13
votes
4answers
26k views

Display user-defined types and their details

I've created a few new UDTs in PostgreSQL. However, now I have two problems: how to see which UDTs have been defined? how to see the columns defined within these UDTs? Unfortunately, I couldn't ...
0
votes
0answers
19 views

Upgrade Postgres 9.6 to 12

If I up to date my postgres from 9.6 to 12, can I get some issues with my queries? I'm afraid to have some problems with my actual code if I upgrade my version.
0
votes
0answers
13 views

PostgreSQL pg_restore: How do I save script report to a txt file?

I need to run pg_restore on my PC with Windows CMD to restore a dump to a remote DB. I need to save the running report of the pg_restore into a file, to make sure the restoration ran as expected. ...
0
votes
1answer
116 views

Stored procedure for adding cascade

I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
2
votes
2answers
27 views

How to order some rows one way, and the rest another way?

I am trying to sort rows from a table in PostgreSQL 10.12: CREATE TABLE example ( id serial PRIMARY KEY, val text NOT NULL, x integer NOT NULL, y integer NOT NULL ); INSERT INTO example (...
2
votes
1answer
49 views

Trigram similarity (pg_trgm) with German umlauts

I try to figure out how to improve Postgres 10.6 pg_trgm queries with German umlauts (äöü). In german 'ö' can be written as 'oe'. But beware: not every 'oe' can be written as 'ö'. CREATE TABLE public....
0
votes
1answer
211 views

Problem to apply replacement with regexp that include look ahead in Postgres

I have this kind of string 12s2-3a abc def and I am trying to apply the regexp ^(?=.*\d) to remove the first part and having as left abc def. I have tried with: select substring ('12s2-3 abc def' ...
-1
votes
1answer
27 views

why does Postgres raise a not-null constraint error on a serial column?

something strange is happening to my database. my understanding is that the SERIAL datatype is the Postgres equivalent of MySQL's auto-increment and that omitting it from an INSERT clause will ...
1
vote
1answer
40 views

Daily Value of Stock Portfolio Postgres

I am working on making a database that queries the daily balance of a stock portfolio. The balance would be calculated as (sharesA x priceA)+(sharesB x priceB), etc. for each day. I have a pricesclose ...
5
votes
3answers
18k views

Error: Unable to write inside TEMP environment variable path

I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1. I get the following, rather famous it seems, error: There has been an error. Unable to write inside TEMP ...
1
vote
1answer
13 views

How do I Order By on multiple rows in Postgresql

I have a table with columns with severities like: name | critical | major | trivial ---------------------------------------- Apache |2 |3 |2 Eclipse|5 |0 |4 TestNg |0 ...
111
votes
8answers
150k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
5
votes
3answers
4k views

How to reinstall PostgreSQL over an existing installation

I am trying to upgrade an installer created with NSIS that installs postgresql. It used to install postgresql 8.3 but we want to upgrade the DB to 9.4.4. The old version on of the DB used to be an ...
1
vote
2answers
46 views

How postgresql full text search disable file parser?

select to_tsvector('english_nostop', 'u,s') @@ to_tsquery('english_nostop', 'u<->s'); return t, while select to_tsvector('english_nostop', 'u.s') @@ to_tsquery('english_nostop', 'u<->s'); ...
1
vote
0answers
1k 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 ...
1
vote
0answers
25 views

How to increase max_connections in Postgresql-9.2

I need to increase production Postgresql-9.2 max_connections parameter from current 1000 to 3000. Used PGTune service to get the values and updated accordingly to the postgresql.conf file and /etc/...
4
votes
1answer
231 views

Does the fact a table is big can impact the overall performance of a PostgreSQL server?

If I have a table that is getting bigger and bigger (i.e. it is taking more and more storage space - currently 65GB), can this affect the overall performance of the PostgreSQL server, e.g. impact the ...
1
vote
1answer
38 views

GROUPING SETS query with many-to-many relationships

I'm writing a query that is mostly based on a GROUPING SET. A very simplified version of the query is the following: SELECT GROUPING(a,b) as grouping, a, b , COUNT(*) FROM table GROUP BY GROUPING ...
0
votes
1answer
152 views

psqlODBC access encoding problem

My local machine: Windows 7, 32-bit. I have installed on it the psqlODBC for 32-bit. MS Access is 32-bit as well. I have tried both Unicode and Ansi connections to... The server: a 64-bit Ubuntu, and ...
1
vote
2answers
2k views

Returning values for Stored Procedures in PostgreSQL

I was reading this on PostgreSQL Tutorials: In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned to ...
0
votes
1answer
16 views

How do I send an array of strings to PG from PHP without the former becoming confused?

I want to do this, but for more than one string: SELECT * FROM table WHERE label <> $1 That selects all records from table where the label column is different from a single string. It works. ...
0
votes
0answers
21 views

PostgreSQL : change the type of a domain

I have a PostgreSQL DOMAIN "mydomain" based on NUMERIC(8,2). I need to change it to be based on NUMERIC (12, 2). That DOMAIN is used by about 50 tables. ALTER DOMAIN doesn't allow to modify ...
1
vote
4answers
31 views

Find timestamp for highest number of concurrent users by date

I have a table including these columns: Sample data: concurrent_users concurrent_timestamp concurrent_date 3 2020-06-24 07:20:00 UTC 2020-06-24 7 2020-06-24 ...
2
votes
1answer
28 views

In a Postgres function, how to skip expressions if input value is null?

I have a working function. But it only works if I literally supply both the city and country variables. CREATE OR REPLACE FUNCTION gridref_by_address(city text, country text) RETURNS SETOF ...
1
vote
1answer
480 views

Having a subquery somehow do the same group by as the outer query

i'm pretty decent with SQL but it's not my main experience of expertise. A colleague asked me a stumper of a question the other day. I was wondering if you guys could provide assistance. Basically we'...
0
votes
1answer
108 views

Postgres index not being used

Postgres 9.6.8, a table with 6GB including a jsonb field that is up to 15MB. The query planner is not using any indices btree or gin when querying that table. When I add the same indices to a local/...
0
votes
1answer
30 views

Postgres looped table (start sequence from 1 and rewrite old rows)

I want table to contain logs and be looped, so when it came to last key in sequence it will start over from one and will rewrite old logs. I can do it if i mark sequence as cycle and do requests ...
-2
votes
3answers
37 views

sql query for select first word of data from column

I have a table with this data: shyam banarjee rahul khanna priya kumari rohit srivastava I just want first names, like this: shyam rahul priya rohit
3
votes
1answer
22 views

How to call from a shell program a postgresql COPY FROM FILE command, passing the path of the FILE as an argument?

I am trying to run, via a linux shell script, a Postgresql command that reads a CSV and updates a table with it. The difficulty I am facing, is that this shell script has to pass an argument to the ...
1
vote
1answer
17 views

Why are my queries in idle state?

I'm new to postgres , I've aws rds instance running postgresql with engine version 11.5. All my queries are clientRead has the wait_event. Why do I have all my queries in idle status.Does this mean ...
28
votes
5answers
49k views

save/export PGAdmin server list configuration settings

I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings
6
votes
1answer
3k views

Unused index in range of dates query

I have a query that is not using existing indices and I do not understand why. The table: mustang=# \d+ bss.amplifier_saturation Table "bss....
1
vote
0answers
28 views

Correcting wrong timezones/timestamps in existing tables

I did a huge mistake because I didn't know how the TIMESTAMPTZ datatype works in postgresql. We need to store sensor data from a handful of different sources. We have a few tables with two timestamp ...
0
votes
1answer
30 views

is it possible to populate a primary key of a table based on a column of another table?

I have a table that contains a text-type column(for example graph path : 12 5 7 19 30 ..) and I want to split this text by space and put all of the output strings as a primary key in the other table. ...
1
vote
0answers
23 views

Migration from Postgres to MySQL using MySQL Workbench (Error in Reverse Engineering catalog. A task has failed while execution)

When I am trying to migrate database from Postgres to Mysql, I am facing an error in the Reverse engineering source step in which the log shows Starting... Connect to source DBMS... - Connecting... ...
1
vote
0answers
41 views

Query is failing

I am running a query from SQL Server 2019(64bit) SSMS to PostgreSQL on AWS through Linked Server. Other queries are working fine but this particular query is failing due to message column. My query is ...
0
votes
0answers
14 views

PostgreSQL resources requirements

We need to have db server (shared hosting, vps ..etc) mainly as storage (saving data) The database (PostgreSQL) is going to have <= 10 tables with data types: 1 small int, 2 bigint, 1 bool and 3 ...
0
votes
1answer
584 views

Query tables with information_schema in pgsql

I am trying to query the tables contained in a schema from an insert trigger on another table. My process is : Manually create the schema with DB Manager inside QGIS. Load tables foo, bar, baz. ...
0
votes
0answers
23 views

Poor performance in a SELECT using Inheritance

I will detail my problem in good detail. I have a marketplace application so I make a decision to make a multitenant per schema approach. I like this approach because of the isolation of data, but I'm ...
0
votes
0answers
11 views

How to design database with post sharing with public, specific users or hide from specific users

I am new to database design, so please ignore if i am asking very simple, but i have searched a lot about this but didn't found some helping material. So, the question is, I want to design a database ...
0
votes
0answers
17 views

Relational DB which can do inserts at billions of rows with multiple indexed columns?

I am looking for advice on a database to potentially replace SQLite for my application. To briefly describe my DB requirements: needs to support billions of rows and the most queried table has ...
-1
votes
0answers
19 views

How to make PostgreSQL's pg_dump exclude a given table in a given schema?

I'm trying to backup my database and exclude the table "Table Name" from the schema "Schema Name". (My object names contain spaces and non-ASCII characters.) Test 1: pg_dump --...