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
368 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
94 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
67 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
76 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
606 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
377 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
26 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
56 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
54 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
1k 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
259 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
22 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
156 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
139 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
2answers
43 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
27 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
29 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
51 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
26 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
20 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
73 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
40 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
35 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
2answers
85 views

Which has better career prospects : PostgreSQL or MySQL? [closed]

I am a Senior Oracle DBA. At the moment , my DBA career is not affected by the rise of open source databases. But, It might not stay like this for long. To make my career future proof, which open ...
-1
votes
1answer
31 views

How to make your SQL data available for people without database access? [closed]

I have several report views that are relevant for a few people that have no SQL knowledge so I guess the best solution would be to put them on a website. Surely data sharing must be a common thing to ...
-1
votes
1answer
58 views

Database volume completely full

/dev/mapper/pgdatavg-base 138304552 131262788 16384 100% /var/lib/pgsql/data/base pg_xlog is on a separate volume, and it's got plenty of space. [postgres@fpslbxmbstgl05 ...
-1
votes
1answer
22 views

Postgresql working with geographic coordinates? [closed]

How to work with geographic coordinates in postgres, is there a standard alternative Postgis out of the box?
-1
votes
1answer
245 views

Copy in SQL with index vs without index

Recently I have received a data dump in csv format that I am trying to import in my PSQL database. The dump consists of 8 csv file each of size 7GB. I have found that using the Copy command on one ...
-1
votes
1answer
327 views

LIKE search in bytea datatype (PostgreSQL)

Table: \d blob Column | Type | Collation | Default ---------+--------------+-----------+------------------------------------------ id | integer ...
-1
votes
1answer
17 views

Which type to pick for column that will hold a variety of data types

I'm setting up a Postgres database that will function as a storage place for data records. There's a column in the records that I'm going to be inserting that contains different kinds of data types; ...
-1
votes
1answer
516 views

Is there an open source PostgreSQL IDE that allows for model generation? [closed]

I have done quite a lot of SQL Server work where I could use SSMS. Now I'm working with Postgres and I miss the ability to generate models (diagrams) from existing relations (taking into account ...
-1
votes
1answer
710 views

how to insert multiple columns in a single column of another table in sql

I have the following table: Then I want to get the following: I want to achieve this in postgres. What should I do?
-1
votes
1answer
54 views

Insert with Primary key column in the middle of the table

I decided to add a primary key to a table after I created the table. This was fine until i wanted to add additional columns to the table. Now I can't figure out how to insert a row. Insert into ...
-1
votes
1answer
3k views

How to update JSONB array in postgres

I have a table with following definition: create table json_test ( my_data jsonb); Inserted to it values like this: {"details": [ { "f1": "", "f2": "free text" }, { ...
-1
votes
1answer
2k views

postgresql function CAST json ARRAY to A TYPE

I try to cast a JSON to a TYPE VOID_EVENT_CREATE, but I didn't work because of the array. I'm going to use the query with NodeJS that why I would like to cast the JSON to a special type. It's easier ...
-1
votes
1answer
231 views

sum up the results in window function:postgres

When running my query I receive a result set which I would like to aggregate and show in two different columns. This result set itself is derived from different aggregate functions and window ...
-1
votes
1answer
126 views

Implementing a database schema that supports a multi-choice answer quiz

I am making a database with 3 tables. tables(columns):- people(pid, name, contact), questions(qid, question), answers(pid REFERENCE pid(people), qid REFERENCE qid(questions), a, b, c, d) I just ...