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
votes
1answer
158 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
156 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
49 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
138 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
563 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
596 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
378 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
425 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
96 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
39 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
39 views

ERROR: check constraint “dates_check” is violated by some row [on hold]

ALTER table myTable ADD CONSTRAINT dates_check CHECK(start_date::date < end_date::date); Gives.. ERROR: check constraint "dates_check" is violated by some row But.. select start_date, ...
-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
79 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
48 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
662 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
1answer
36 views

Database block size and migration [closed]

My client is planning to move their database from Oracle to PostgreSQL. Current blocksize is 32 KB for Oracle. As per Postgresql documentation and research on internet, 8k is default page size but can ...
-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
59 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
41 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
89 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
305 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) ...
-1
votes
1answer
23 views

How to get users by specified tag?

I have 3 tables users, tags, user_tags. I try to fetch users with theirs tags with next query: SELECT u.*, t.id AS tag_id, t.value AS tag_value FROM (SELECT * FROM ...
-1
votes
2answers
57 views

POSTGRESQL - How to sum (date row 0 - date row 1) + (date row 2 - date row 3)

I would like to know how I could make a sum of (date row 0 - date row 1) + (date row 2 - date row 3) and so on... group by userId. Please refer to the screenshot below, I want to sum the time between ...
-1
votes
2answers
183 views

How to quickly remove a FK constraint?

I have a PostgreSQL 9.3 database with two tables, table B with 100 million records linked via a FK constraint to another table A with around 10 million records. Occasionally I need to delete a record ...
-1
votes
1answer
142 views

Generating Record Ids in Postgres [closed]

Using the UUID data type with its 256 chars seems like overkill, while assigning an auto-increment int4 seems less robust. Is there a more robust way to assign a record id without using uuid or an ...
-1
votes
1answer
3k views

Restore postgres in command line on ubuntu failed

This question may be asked many times. We are trying to move a postgres db from windows (psql 9.3) to ubuntu runing postgres 9.3. Here is what we did: In windows, create backup for db cis with ...
-1
votes
1answer
25 views

append the text to postgres crosstab query [closed]

Am using below crosstab query: SELECT * from crosstab('SELECT id,task_id, status_id FROM table ORDER BY 1,2') AS final_result(id character varying(50), task1 int,task2 int,task3 int,task4 ...
-1
votes
1answer
54 views

Postgres vs SQL Server [closed]

Can anyone share proper advantages-disadvantages? If possible, I am trying to search for really great benchmarks between both, so please share it with me. Also, which will be best between them for ...
-1
votes
2answers
47 views

EAV in postgresql

I'm building an application that needs to be deployed several times, with a relatively well organized data model. A table needs to be configured for each deployment with a small number of additional ...
-1
votes
1answer
73 views

Importing CSV file into a PostgreSQL table

I am using Python with pandas to import a CSV file into a table in Postgres import pandas as pd import psycopg2 from sqlalchemy import create_engine df = pd.read_csv('products.csv', sep=';', ...
-1
votes
1answer
28 views

Advantage of 'with' queries over plain 'select' and joins?

Are there any advantages using WITH queries instead of simple SELECT queries using JOINs performance wise? I am asking solely about reading data (from catalogues specifically).
-1
votes
1answer
32 views

How to update a numeric type column to avoid many decimal points

lets assume I have a table with this entries: thing: position: ball 1001.25 pensil 1001.0625 car 1002.5 watch ...
-1
votes
2answers
67 views

In Postgres txid_current() should return biginteger but instead an integer is returned

In Postgres 11, according to documentation, txid_current() should return biginteger but I'm getting small integer that doesn't look like is extended by epoch counter. My txid looks like this: 1123 "...
-1
votes
1answer
31 views

Design schema to handle different data types (avoiding EVA anti-pattern)

So I have a table that stores ML model metadata. I have a table that holds my model. I need to also include hyper parameter information (epochs, batch_size, weight_decay, classes etc.). I don't want ...
-1
votes
1answer
23 views

Postgres jsonb column or extra table for tightly coupled data? Which will likely have better performance [closed]

I am working on some sort of a bug tracking product. We're using Postgres as our only database. So far we are only using normal tables and no jsonb. Our basic data hierarchy looks like this: ...
-1
votes
1answer
116 views

ER diagram to Relational Schema (Many to Many relationship)

In this ER diagram, can anyone explain how we should convert the many to many relationships ('view', 'update' and 'diagnose') to Relational Schema and then SQL? What I think is that there shouldn't be ...
-1
votes
1answer
41 views

Is it possible to “lose” inserts if throughput is too high?

I'm running a large ETL operation on > 100,000 jsonl files. I'm using RabbitMQ to insert filepaths into a queue and then using 25 workers to transform the files and perform batch inserts into a number ...
-1
votes
1answer
36 views

How to store data [closed]

I have an app which records Insurances in a company where they work. each insurance has a time frame. Example : Employee John decides to buy a medical insurance from their employer. But the next ...
-1
votes
1answer
25 views

Postgresql 9.6 db connection our Oracle 11.2.0.4. db

I am trying to do a connection between tables/fields between our Postgresql database and an Oracle database. When updates are made in one it needs to be as closely as possible updated in the other, ...