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
2
votes
2answers
1k views

Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]

I have a pretty large query in a view (let's call it a_sql), that is really fast unless I use ORDER BY in an outer SELECT with a small LIMIT: SELECT customs.id AS custom_id, customs.custom_name AS ...
1
vote
2answers
300 views

What administration utilities does Ubuntu and Debian package with PostgreSQL?

I see that there are a lot of tools authored by Ubuntu and Debian for PostgreSQL? Does any one have a list of them, or the build-differences?
84
votes
4answers
156k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
52
votes
1answer
71k views

How to get a working and complete PostgreSQL DB backup and test

I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users. From what I've read, and it ...
37
votes
7answers
37k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
43
votes
7answers
86k views

EXISTS (SELECT 1 …) vs EXISTS (SELECT * …) One or the other?

Whenever I need to check for the existence of some row in a table, I tend to write always a condition like: SELECT a, b, c FROM a_table WHERE EXISTS (SELECT * -- This is what I normally ...
40
votes
6answers
57k views

Export Postgres table as json

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like: {'id':1,'name':'David'} {'id':2,'name':'James'} ... EDIT: postgres version: 9.3.4
14
votes
1answer
13k views

How to determine the sweet spot between pool size and database connections in PostgreSQL

We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on ...
43
votes
2answers
57k views

How to list all constraints of a table in PostgreSQL?

How to list all constraints (Primary key, check, unique mutual exclusive, ..) of a table in PostgreSQL?
16
votes
3answers
7k views

How to implement business logic permissions in PostgreSQL (or SQL in general)?

Let's assume I have a table of items: CREATE TABLE items ( item serial PRIMARY KEY, ... ); Now, I want to introduce the concept of "permissions" for each item (please note, I'm not talking ...
26
votes
6answers
50k views

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; ...
18
votes
2answers
39k views

Export remote Postgres table to CSV file on local machine

I have read-only access to a database on a remote server. So, I can execute: COPY products TO '/tmp/products.csv' DELIMITER ','; But on that server I don't have permissions to create/save a file, so ...
13
votes
2answers
9k views

How to speed up ORDER BY sorting when using GIN index in PostgreSQL?

I have a table like this: CREATE TABLE products ( id serial PRIMARY KEY, category_ids integer[], published boolean NOT NULL, score integer NOT NULL, title varchar NOT NULL); A product can ...
23
votes
2answers
15k views

How to create an index to speed up an aggregate LIKE query on an expression?

I may be asking the wrong question in the title. Here are the facts: My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
17
votes
2answers
33k views

PostgreSQL joining using JSONB

I have this SQL: CREATE TABLE test(id SERIAL PRIMARY KEY, data JSONB); INSERT INTO test(data) VALUES ('{"parent":null,"children":[2,3]}'), ('{"parent":1, "children":[4,5]}'), ('{"parent":...
25
votes
3answers
6k views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address ...
21
votes
1answer
28k views

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?
3
votes
1answer
8k views

Search for nested values in jsonb array with greater operator

Here is the table definition (simplified): CREATE TABLE documents ( document_id int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY, data_block jsonb NULL ); Sample values: INSERT INTO documents ...
6
votes
1answer
3k views

How do I parse addresses in PostgreSQL?

Let's say for instance that I want to parse these addresses for the Chicken Ranch Chicken Ranch 10511 Homestead Rd Pahrump, NV 89061 Chicken Ranch 1600 Pennsylvania Avenue NW Washington, D.C. 20500 ...
5
votes
1answer
6k views

PostgreSQL not using index during count(*)

I have a COUNT(*) query in PostgreSQL that runs often, and looks like: SELECT COUNT(*) FROM customer WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); This query takes ...
5
votes
3answers
2k views

Full Text Search With PostgreSQL

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

Postgresql Database on external HD

My laptop has a small hard drive. I want to load a big data set. I have moved the entire /usr/local/var/postgres to the external hd, and symlinked it back. Now I'm getting this error, psql: could ...
20
votes
7answers
9k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
18
votes
2answers
13k views

Should I use the PostgreSQL bit string?

I've been learning about the bit string data type lately, and I am quite curious about: At the bottom of this doc page there is the sentence: ... plus 5 or 8 bytes overhead depending on the ...
17
votes
1answer
5k views

Naming conflict between function parameter and result of JOIN with USING clause

Given this setup in current Postgres 9.4 (from this related question): CREATE TABLE foo (ts, foo) AS VALUES (1, 'A') -- int, text , (7, 'B'); CREATE TABLE bar (ts, bar) AS VALUES (3, 'C') ...
16
votes
1answer
95k views

How do I reset the postgres password for PostgreSQL on Windows? [duplicate]

I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I ...
10
votes
2answers
7k views

Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation? In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default ...
9
votes
2answers
31k views

Quoting columns with spaces in PostgreSQL?

I migrated a database from SQL Server to PostgreSQL. Most column names contain double words, for example: SELECT [Column Name] FROM table; ...which does not work in PostgreSQL. What is the correct ...
8
votes
2answers
3k views

How to apply ORDER BY and LIMIT in combination with an aggregate function?

A fiddle for my question can be found on https://dbfiddle.uk/?rdbms=postgres_10&fiddle=3cd9335fa07565960c1837aa65143685. I have a simple table layout: class person: belongs to a class I want to ...
8
votes
2answers
5k views

Optimizing queries on a range of timestamps (one column)

I am using Postgres 9.3 through Heroku. I have a table, "traffic", with 1M+ records that has many inserts and updates every day. I need to perform SUM operations across this table over different time ...
7
votes
2answers
13k views

PostgreSQL query with max and min date plus associated id per row

I have the following table: CREATE TABLE trans ( id SERIAL PRIMARY KEY, trans_date date, trans_time time ); I want to have the following view CREATE OR REPLACE VIEW daily_trans ...
6
votes
3answers
21k views

Change column type from varchar to text in all tables at once

I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text. If anyone knows that I would be very glad for your help.
6
votes
1answer
2k views

SELECT very slow when no results and a LIMIT is specified

I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified. If the number of results is ...
5
votes
3answers
998 views

How to force order of WHERE clauses in PostgreSQL?

I have 6 columns in Postgres table: A1 character varying(5)[] A2 character varying(5)[] A3 int REFERENCES ... -- FK B1 character varying(5)[] B2 character varying(5)[] B3 int REFERENCES ... -- FK ...
16
votes
4answers
14k views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (https://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
15
votes
1answer
8k views

Can the 'returning' clause return source columns that are not inserted?

Here's a minimal example of my real-world problem: create table t(id serial primary key, rnd double precision); of course you can return the inserted columns with a returning clause: with w as (...
9
votes
3answers
12k views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
6
votes
3answers
11k 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....
4
votes
1answer
1k views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...
3
votes
3answers
2k views

Create unique sequence per set of rows sharing the same foreign key value

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...
3
votes
2answers
5k views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
3answers
3k views

Get count estimates from pg_class.reltuples for given conditions

Is it possible to query the reltuples column for a given table with additional conditions like table.name LIKE 'hello%'? Currently on my bigger tables the SELECT count(*) query takes long and I ...
12
votes
2answers
16k views

Slow index scans in large table

Using PostgreSQL 9.2, I have troubles with slow queries on a relatively large table (200+ million rows). I'm not trying anything crazy, just adding historic values. Below is the query and the query ...
12
votes
1answer
5k views

How to compare xmin and txid_current() after transactions ID wraparound?

Besides its regular columns, Postgres tables also have various system columns available. One of them, xmin, stores the transaction ID used to create a row. Its data type is xid, a four byte integer ...
8
votes
1answer
3k views

How to search hyphenated words in PostgreSQL full text search?

I have to search for hyphenated words like 'good-morning', 'good-evening', etc. My query is: select id, ts_headline(content, to_tsquery('english','good-morning'), ...
5
votes
1answer
15k views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
5
votes
1answer
879 views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
4
votes
3answers
28k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO bought_in_control_panel(ID,PARENT_ID,...
3
votes
1answer
2k views

How will the order of operations in this query affect my results?

I've written a query, and I'm kinda sure that it does what it want. I'm thinking, "huh, I wonder if I know how Postgres works?". I'm going to list a specific assumption about this query below, and ...
3
votes
1answer
4k views

Creating a case-insensitive and accent/diacritics insensitive search on a field

I would like to know if it makes sense to create an index combining two functions while using Full Text Search: lower(name) and f_unaccent(name) Where f_unaccent is just my wrapper to make unaccent ...

1 2 3
4
5
24