Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

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
1
vote
1answer
2k views

Returning case-sensitive input of case-insensitive array match

Taking a JSON table with some simple data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}'); INSERT INTO jsonthings(d) VALUES('{"id":2,"...
1
vote
2answers
2k views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...
1
vote
0answers
71 views

Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer ...
1
vote
2answers
5k views

Switch user to 'postgres' user on macOS results in “su: Sorry” error

The Postgres installers kindly provided to the community by EnterpriseDB.com creates a postgres user account on macOS as is common. In a console session in Terminal.app, I need to switch to that user....
1
vote
1answer
604 views

How do I ALTER all columns of one type to another type?

Let's say I want to change the data type of all columns of one type to another. How would I go about doing this, for example converting all bigints to ints.
1
vote
1answer
4k views

SQL query to find all parent records where all child records have a given value (not just some)

An Event has many Participants. A Participant has a field of "status". I need to find all Events except the following ones: Events where every one of its participants has a status of 'present'. I ...
1
vote
1answer
5k views

Do I need to execute “create extension pgcrypto” everytime?

I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart....
1
vote
1answer
422 views

Occasional/intermittent, slow (10+-second) UPDATE queries on PostgreSQL table with GIN index

The Setup I am running PostgreSQL 9.4.15 on an SSD-based, quad-core Virtual Private Server (VPS) with Debian Linux (8). The relevant table has approximately 2-million records. Records are frequently ...
1
vote
4answers
842 views

Check if two tables have the same structure (are compatible)

I was wondering if there is an established way to test if two PostgreSQL tables/relations have the same structure. In other words, how to test whether they are compatible with each other in the sense ...
1
vote
1answer
585 views

How I create backup with backup by dump and WAL files in PostgreSQL?

I have a backup and WAL files, I like update my backup with WAL files. Anyone know how to do it? Thanks
1
vote
2answers
743 views

Is there a way I can use currval() without selecting it more than once?

I want to be able to use the current value of an auto ID by giving it some kind of name and just referring to it (instead of calling SELECT for each record): Requests table has a serial column named ...
1
vote
1answer
1k views

Have aggregate function group results in one row

I have a table that has the following columns: debit credit payment_method class Now I will perform this query: select sum(debit - credit), payment_method, class from my_table group by ...
0
votes
1answer
2k views

postgresql.conf file found in both /usr/share/postgresql and /etc/postgresql/

While troubleshooting another issue I noticed that in my Ubuntu 14.04 system there are two postgresql directories containing a postgresql.conf file: /usr/share/postgresql /etc/postgresql/9.3/main/ ...
0
votes
1answer
2k views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...
0
votes
1answer
2k views

Select rows from table where each row refers to a different schema

I have customer table in public schema. And I have a schema named after each customer. Each customer schema contains different tables like portals, plugin, ... When I want to select portals used by ...
0
votes
1answer
268 views

Queries using GIN index on f_unaccent() seem slow?

I have almost 20M rows in one table that contains product, including their names. I wish to search fulltext and fast by the name so I created this index: CREATE INDEX ...
0
votes
4answers
3k views

Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?

Example you have the following table CREATE TABLE emp (id,name,salary,manager) AS VALUES ( 1 , 'james' , 10000 , null ), ( 2 , 'alex' , 5000 , 1 ), ( 3 , 'Alice' , 4500 , 1 ), ...
0
votes
3answers
171 views

Setting up a simple “permissions architecture” in PostgreSQL

I host the databases of several clients. For each database dbname, I currently have a dbnameusers role that I grant to the users of that client. I would now like to have read only "sub roles". I ...
0
votes
1answer
500 views

What is the best approach to process huge amount of data insertion efficiently

I'm dealing with a HUGE amount of data entry. I need to know the best practice to insert/update table records after new entry inserted in another table. By huge i mean we get more than 2 million ...
0
votes
2answers
645 views

Constraint for multiple foreign key yet it's mutually exclusive (PostgreSQL)

Loan table associated to due table. also Invoice table associated to due table. In due table either one of them foreign key should present and other one should be null. What kind of constraint ...
0
votes
1answer
98 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 ...
14
votes
1answer
12k views

Is there a way to insert multiple rows into a table with default values for all columns?

I can insert multiple rows into a table with default values for all columns the RBAR way: create table course(course_id serial primary key); do $$ begin for i in 1..100000 loop insert into ...
13
votes
3answers
39k views

Using PostgreSQL 8.4, how to convert bytea to text value in postgres?

In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly ...
12
votes
3answers
471 views

How-to implement an entity with an unknown maximum number of attributes?

I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
9
votes
2answers
6k views

Customize jsonb key sort order involving arrays

I have a table in PostgreSQL with some data: create table t2 ( key jsonb, value jsonb ); INSERT INTO t2(key, value) VALUES ('1', '"test 1"') ,('2', '"test 2"') ,('3', '"test 3"') ,('[]', '"...
9
votes
3answers
5k views

PostgreSQL procedural languages overhead (plpython / plsql / pllua…)

I'm trying to find information about PostgreSQL user defined functions in procedural languages performance for real time tasks. How does they compare to builtin functions? Is there any difference (...
9
votes
2answers
8k views

Multi-row insert vs multiple single row inserts

In my app I do multi-row inserts when I can just because it reduces the number of round trips between the db and the app. However, I was curious, is there any other advantages? For example, if ...
9
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 ...
8
votes
4answers
2k views

What can go wrong using the same sequence across multiple tables in postgres?

We are considering using a shared sequence to assign ids to primary keys for all of the tables in our database. There are about 100 of them. Only a couple are inserted to frequently and regularly. We ...
8
votes
3answers
6k views

How to concatenate psql variables?

How can I concatenate two psql (PostgreSQL client) variables? I want to generate an absolute path by concatenating a directory path variable and a filename variable. I've tried this: \set path '/tmp/...
8
votes
1answer
546 views

How to dive into the PostgreSQL's optimizer and trace each step it takes to optimize the query?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Does PostgreSQL have the similar structure which records steps the ...
8
votes
2answers
3k views

How to wait for PostgreSQL to be startable / restorable?

I'm testing a PostgreSQL 8.2.1 to 9.2 upgrade on a virtual machine running a custom Linux distro. The upgrade procedure is as follows: Start the pg service Vacuum all DBs (not sure if this is needed) ...
7
votes
1answer
5k views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ----------------+-----------------------------+-----------------------...
7
votes
1answer
28k views

Installing pgcrypto Extension [closed]

I have this password hash function that hashes a password to sha256. I've already got it running in pgadmin3 in a local database. I've done it by importing the extension, a feature that has been added ...
7
votes
3answers
2k views

How to understand the PostgreSQL discrepancy between connection pool recommendation ((2 * n_cores) + n_disks) and support for 100s of connections?

From PostgreSQL docs: Number of Database Connections -> How to Find the Optimal Database Connection Pool Size for optimal throughput the number of active connections should be somewhere near ((...
7
votes
2answers
1k views

Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

forgive me if this has been asked and answered before. I'm roughing out a schema for an inventory management system, to be implemented in PostgreSQL. All of our products and services have a sku. ...
7
votes
2answers
2k views

Why does pg_hba.conf sometimes have random rules added to it? (postgresql)

I have postgres 10.4 set up on a Linux machine, and sometimes there will randomly be rules at the top of pg_hba.conf that only allows an MD5 connection by a user "pgdbadm". I'm not sure if the ...
7
votes
2answers
16k views

Unnest multiple arrays into rows

I have been shown a great way to bulkinsert with this example: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, ...
6
votes
6answers
6k views

Backup a sample of a PostgreSQL database

Is it possible to back up a PostgreSQL database with only a fraction of the data (1000 Rows from each table)? I am able to back up that database using pg_dump. In some case I need db with minimum ...
6
votes
1answer
1k views

Optimal hash technique to index large text

Erwin Brandstetter thankfully saved me from myself when I tried to create unique indexes on textual columns with large lengths. The upper limit for the insertion rate is tens of billions of rows per ...
6
votes
1answer
1k views

PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly

We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
6
votes
1answer
5k views

Transpose two-dimensional array from n rows to 2 columns

Background Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available. Problem Looking to simplify the code that pivots a two-dimensional array. Code A working, overly-verbose example ...
6
votes
1answer
2k views

Is it possible to wrap aggregate functions in Postgres?

Postgres' string_agg(expr, delimiter) function is great. But I would like to have a version that takes a single argument -- the field to aggregate -- and assumes a delimiter of ', ' since that is ...
6
votes
1answer
2k views

Row by row subtraction with single input number

I want to subrtact dynamically from previous row subtraction result and as input i want to give a single number. I have table a CREATE TABLE a (id int, code text, qty numeric); And data in it ...
6
votes
2answers
225 views

Create smaller row from alias and preserve column names

Using Postgres: SELECT users."name" AS "name" , array_to_json(array_agg(sites)) as sites FROM remodel.users AS users JOIN remodel.user_sites AS user_sites ON users.id=user_sites.user JOIN ...
6
votes
2answers
4k views

PostgreSQL: SET-ing from psql arguments

Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution: SET statement_timeout TO '1min'; This is great, but ...
6
votes
1answer
10k views

Very slow simple PostgreSQL query on RDS

I seem to be getting very slow queries on a medium sized RDS box (db.m3.medium, 3.7gb ram). This is across a table of 4,152,928 rows.. select sum(some_field) c from pages where pages.some_id=123 and ...
6
votes
2answers
2k views

Why does Postgres ORDER BY seem to halfway ignore leading underscores?

I have an animal table with a name varchar(255), and I've added rows with the following values: Piranha __Starts With 2 Rhino Starts With 1 0_Zebra _Starts With 1 Antelope _Starts With 1 When I run ...
6
votes
1answer
2k views

Debug query on big table that is sometimes slow

I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries (...
6
votes
1answer
596 views

How to add a PostgreSQL 10 identity column to an existing table with rows?

I have a table with some rows and I try adding an identity column using the command: ALTER TABLE sourceTable ADD COLUMN ogc_fid int GENERATED BY DEFAULT AS IDENTITY; The ...