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

Sorting based on multiple-table columns in Postgres

I have to query a table in my database in order to generate a feed of relevant items. We're talking in this case about listings and the most relevant are considered those with most votes and most ...
0
votes
1answer
41 views

What type of database can I use to store mutual scores between every combination of users?

Let's say I have an application that calculates and stores a mutual score between every combination of users. I'm currently using Postgres to store this information. So if my system had users with ...
0
votes
1answer
212 views

What is the best approach to index varchar and date range?

There is an order table which stores customer information and location. I need to filter the customer's last five locations within the last six months based on order status. Query as follows, ...
0
votes
2answers
118 views

Update table where table name in select query itself

I am having following select query (select article.articleId ,'MUSIC' as type ,createddate FROM MUSICarticle where article.status=2 and article.createdBy=101 order by ...
0
votes
2answers
62 views

Is it possible to COPY tables into PostgreSQL without setting structure first?

I'm just getting to grips with PostgreSQL 9.6. I'm wondering if there is a simple way to add a .csv table to a database without first creating the table structure in psql or pgAdmin? Or...I've not ...
0
votes
2answers
126 views

Have DB users share tables

I want to have 2 users of the DB (user_app and user_migration), one for executing migrations (which will have more privileges, like creating/dropping tables + basic usages) and one that the overlaying ...
0
votes
1answer
737 views

Incremental backup in postgresql in windows

I am developing an application with postgresql as backend. So my requirement is to take the incremental backup either hourly or daily basis. I have gone through many related articles (like https://...
0
votes
1answer
42 views

How can I improve this nested postgresql query?

I have a mildly complex query that is having rather poor performance: UPDATE web_pages SET state = 'fetching' WHERE web_pages.id = ( SELECT web_pages.id FROM ...
0
votes
1answer
647 views

Reset postgres password v9.1 [closed]

I'm trying to reset my lost password for Postgres v9.1 (windows 7) like written at https://wiki.postgresql.org/wiki/FAQ#I_lost_the_database_password._What_can_I_do_to_recover_it.3F : 1. edit pg_hba....
0
votes
1answer
55 views

How to model parent-child relationship correctly in multi-tenant, multi-schema Postgres database

I have a multi-tenant SaaS app where each tenant gets their own schema. When a user creates an account, I create a new schema and run a bunch of migrations to create tables under that schema. Then, ...
0
votes
1answer
26 views

Caching results of query for part of table that almost never change

In our database we have a table with a very large number of rows. Let's call it Table1. The greatest difficulty for us is a COUNT query to this table with some conditions (see example below): Query1 ...
0
votes
2answers
43 views

Oracle to PostgreSQL conversion

In oracle: employees table contain more than 10 million data. For update I'm using rownum for faster update. CREATE OR REPLACE PROCEDURE emp_up AS begin loop update employees set ...
0
votes
1answer
50 views

Postgres: How to optimize time constrained wildcard search?

I'd like to optimize Postgres response times for a query which looks for select orders.id, orders.created from orders left join order_items on order_items.order_id = orders.id where ...
0
votes
1answer
2k views

What is the pgAgent service account?

Im installing pgAgent using Stack Builder. In one step ask me for service account. Should I use PC, Domain or DB user? Or create a new one? I try using my DOMAIN\my.user.name also using the ...
0
votes
2answers
655 views

Refresh postgres database on nightly basis

I've got a Postgres 9.2 database (production) which is almost 90 GB, I need to restore production database to development postgres server every day, what I think is to schedule a backup an restore ...
0
votes
2answers
2k views

How do I select distinct json columns? [closed]

I have over 10,000 geojson rows in a table. How do I go about selecting only distinct coordinates? The data was imported from a csv file into PostgreSQL 9.5. The columns in the table are: Trip ...
0
votes
1answer
1k views

PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

I am working on a financial software where we have huge table of ticks with more than 800M rows. A single row: symbol_id, broker_id, date, time, bid, ask The problem is the table has many ticks ...
0
votes
1answer
189 views

intel p3600 SSD no increase in read speed - postgres rdbms vs hdd

We run a custom application that produces analyzable data-sets for medical research. the amount of data we have is about 600GB in form 3Billion rows distributed in 600+ tables it is hosted in ...
-1
votes
3answers
1k views

Select everything after the last space using PostgreSQL [closed]

I'm working with an address database using PostgreSQL 9.5 and trying to create a select statement where only the names of the streets and not the types are returned. So if I have a record that says: '...
-1
votes
3answers
2k views

Why does connecting to '127.0.0.1' work whilst 'localhost' fails?

I have noticed that although the listening host in postgresql.conf is localhost connecting to a database via psql fails unless the host is changed 127.0.0.1. I have also noticed MySQL display the ...
-1
votes
1answer
118 views

How to use postgresql variable in where clause

# It works! select * from device where device.user_id in (select user_id from user); # It doesn't work! select user_id into test from user; select * from device where device.user_id in test; Why ...
-1
votes
2answers
142 views

Select multiple rows with different criteria for each [closed]

I am trying to put together a query will return sales information across multiple date ranges. I was planning to do something like this: SELECT count(*) AS thirty_day_sales FROM "MySchema"."MyTable" ...
-1
votes
2answers
44 views

Create an unique integer id column for an existing string column

I have an PostgreSQL server with an existing table which has an fixed-width-non-unique-string column such as this: | ID_STRING | | 'ABCDEFG' | | 'HIJKLMN' | Now I want to compute integer ids for ...
-1
votes
1answer
133 views

Indexing the condition lower(f) LIKE '%foo%'

How do I index the following statement with an un-anchored search pattern SELECT somefield FROM sometable WHERE lower(somefield2) like '%foo%'; Some rows have more than 2k bytes.
-1
votes
3answers
559 views

Should I spread many connections over a period of time, or hammer the database at same time?

I currently have a fairly small (micro, but can upgrade if necessary) Postgres db instance on Amazon RDS. I think I have a max connections of about 85 (checked through max_connections value in console)...
-1
votes
1answer
281 views

Anybody know story behind pgFoundry (PostgreSQL project site) downtime? [closed]

pgFoundry site's been unreachable 2 days and counting.... I have googled up some ominous messages from the Spring about it eventually closing, but I had never seen any notice on the site to say that ...
-1
votes
2answers
533 views

Can I create a faster version of unnest() over int[]?

I have a column of int[] type. now I want to convert int[] value into rows without using unnest() function. I want to create my own function which takes int[] as input and convert it into rows. How ...
-1
votes
1answer
360 views

UPSERT in postgres stored procedure

Postgres already has a way to do "upsert" but I don't like sending these long queries, I wonder if it could be implemented as a stored procedure which would accept json and perform INSERT INTO ... (...
-1
votes
1answer
400 views

When using SQLite for offline client, better use MySQL or PostgreSQL for server? [closed]

I'm developing an application which uses embedded SQLite for local storage. My application will replicate changes to internet server (via REST/HTTP). There I can choose between MySQL/MariaDB or ...
-1
votes
2answers
95 views

Huge tables with PostgreSQL and MySQL

I have written an application that parses imdb (from their public ftp files) data and stores it into data base of my choice. 2 of my database tables have over 1 million records (2.316.952 and 1.392....
-1
votes
1answer
68 views

What is the optimal data type for columns that retain dates, DATE or INTEGER? [closed]

In my experience, old school thinking tells that, for optimal performance, date data points should be stored as an INTEGERs (e.g., as in 20160101), since they are manipulated faster, accelerate ...
-1
votes
1answer
38 views

Proper use of array in table? Multiple elements of the same field and record

I have a set of processing priorities that change every day given some user inputs in another program. Each priority has an item and a set of names assigned to it. The names can duplicate across ...
-1
votes
1answer
63 views

What are the numbers in the output of an INSERT statement, and why is the first one zero? [closed]

When you INSERT, you'll get back something like # CREATE TABLE foo ( a int ); # INSERT INTO foo VALUES (1),(2); INSERT 0 2 What is the first and second number after the INSERT (the 0 and the 2)?
-1
votes
1answer
18 views

How to psql to a specific PG server

There are PG 9.5 and 11 installed on one server and I would like psql command line to be connected to 11 db server. How can I do that? I can psql to command line but there is only tables from 9.5 db ...
-1
votes
2answers
78 views

Is there a non-syntactical guide for naming database columns? [closed]

Is there a guide or best case practise to help people on choosing the best database column names. I am not looking at syntactical naming conventions such as snake case, camel case or even singular or ...
-1
votes
1answer
47 views

How to view the data size of a database in HybridDB for PostgreSQL

I am using HybridDB for PostgreSQL in Alibaba Cloud ECS. My Question is: How can I view the data size of a table and a database in HybridDB for PostgreSQL I appreciate any assistance with this
-1
votes
1answer
80 views

Is there any ways to get the row count of joined tables?

Is there any methods to get the row count of the table which is related to the specified condition.? here is my query select * from table2 t2 inner join t1 on t2.table1_id = t1._id where t1....
-1
votes
1answer
634 views

Postgresql cant declare cursor

I am trying to declare select statement to cursor and then declare fields from Cursor to variables and then loop over them (the loop is fine , I have problem with cursor declaration ) having syntax ...
-1
votes
1answer
66 views

in postgres how to select items that have a dupe across two columns

My data looks like this: queryable_type queryable_id User 1 User 2 User 2 I'd like to return dupes so that my result would look like this: queryable_type ...
-1
votes
1answer
381 views

Database for an application similar to Chatroulette

I am making an app that is similar to Chatroulette. When a user starts the app it finds someone that is not currently talking to anyone and have them talk to each other. At any point a user could ...
-1
votes
1answer
208 views

What database do most mobile start ups use? [closed]

mongodb? MySQL? PostgreSQL? If someone would add pluses and minus, let me know. Why I ask: I am a mobile startup. I need a database that supports spatial indexes. The spatial index must be able to ...
-1
votes
2answers
32 views

index for ILIKE on postgresql, inconsistent performance

I have a table with about 64 Million rows, I have created the following index on the address_url column CREATE INDEX trgm_idx_address_link ON public.address_link USING gin (address_url ...
-1
votes
3answers
57 views

Selecting Only one value in duplicate record

I have a result like the bwlow image. now, i want to select only wireline instead of all. So, how can i select the 1st row if the all columns are same except produtc_line No, where conditions as we ...
-1
votes
1answer
24 views

How can I get the max value for each column?

If data of table like this, [co1:varchar][col2:varchar][col3:varchar][col4:timestamp] And, Follow is data example. 224 / 755 / 542 / 2019-05-23 10:12:23 230 / 741 / 530 / 2019-05-23 10:12:23 221 / ...
-1
votes
1answer
39 views

One Product, Multiple Categories (one to many) too many rows on table

So I'm developing an e-commerce website, and I have a product with many categories. So my concern is, what if I have 1000 products and each of them has 5 categories, so is it unavoidable to have ...
-1
votes
1answer
62 views

Performance: storing list of thousands of items in postgresql's JSON field vs having separate table for items?

Let's say I have some table A which have json-field items: A: foo: 'foo' items: [ {a: 1, b:2}, {a: 3, b:4}, ... ] There could be couple of thousands of items in one A object. Is there any ...
-1
votes
1answer
2k views

structure of query does not match function result type

I want to create a basic function which takes an input and throws a table with multiple columns and rows as an output . I have written the following query for the same : create or replace function ...
-1
votes
2answers
36 views

Postgres, copy to a csv file [closed]

On Postgres, I want to load a select query into a csv file. But in the CSV file I want to put some values, that doesn't appear in the query, how do I do that? It's like: select id, name, email And ...
-1
votes
3answers
57 views

How to count last records from table where cell null before same cell is not null by order

I have simple table with conversation. id | room_id | member_id | message | readtime -------+---------+-----------+----------------------+--------------------------...
-1
votes
1answer
281 views

ERROR: column “minute” does not exist

I am migration mysql to Postgres Runing this bellow query on both Mysql and Postgres SELECT cb_sessions.*,cb_users.username, cb_users.userid,cb_users.email FROM cb_sessions LEFT JOIN (cb_users) ...