We’re rewarding the question askers & reputations are being recalculated! Read more.

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
2answers
41 views

Create integer id columns from existing string columns (integer coding?)

I have an PostgreSQL server with an existing table which has two fixed-width-non-unique-string (variable size) columns such as this: | ID_STRING_A | ID_STRING_B | | 'AAAA' | 'BBBB' | | ...
0
votes
1answer
136 views

Postgres to_tsvector is case sensitive on mac os

On PostgreSQL 10.4 on Ubuntu 10.4-2.pgdg16.04+1 select to_tsvector('simple', 'БОЛЬШИЕ БУКВЫ'); returns 'большие':1 'буквы':2 But on postgres 10.3 (installed by brew) on Mac Os High Sierra version ...
0
votes
1answer
451 views

How to change a web application that now needs to become OLTP and OLAP application?

Previously, I did a simple web application for an enterprise client using PHP and MySQL. After some time, it has grown to a point where I realized after talking with people here that I need to treat ...
0
votes
1answer
54 views

Give particular privileges for a table to existing role

My database have some tables such as: Table A Table B Table C I'm using user monitoring with read-only permission. SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE ...
0
votes
3answers
725 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
54 views

Creating a composite type with “char”?

PostgreSQL has a type called char that's stored as an signed 1 byte int The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used ...
0
votes
2answers
4k views

How to create a table dynamically based on json data?

I have a json that I get as a file, I have loaded that in the Postgres database. Here is the json data: { "tablename": "test", "columns": [ { "name": "field1", "datatype": "BigInt"...
0
votes
1answer
113 views

Migrating a database from one PostgreSQL server to another cleanly

I have a Rails app server that uses Postgres for the DB backend. I'm spinning up a new server and I want to get a clean and up to date copy of the database. I've tried it once so far while the ...
0
votes
1answer
908 views

Postgres datetime sort slow and not using index

Using Postgres 9.4, I'm trying to order a query by an indexed datetime column. explain SELECT g.*, array_agg(gp.platform_id) as platform_list, array_agg(gm.metacritic_id) as ...
0
votes
1answer
35 views

Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

I am getting "ERROR: out of shared memory" presumably because my plpgsql function drops/creates a lot of temporary tables. Searching for a solution to the issue, I found this answer that says: ...
0
votes
2answers
157 views

What configurations affect the speed of DDL statements in PostgreSQL

I would like to setup PostgreSQL in such a way that dropping and recreating the entire database (~100 tables/views + a few rows per table) is as fast as possible. This is for a development environment ...
0
votes
2answers
5k views

SQL command to clean job queue in pgAdmin 4

I have a Postgres 9.6 installation on a developer pc and a restore seems to have failed. Even after reinstalling (remove Postgres and PgAdmin completely, incl. database and installed new version) the ...
0
votes
1answer
219 views

Constraint exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted rows, so at the beginning I just queried for: select id, ...
0
votes
1answer
29 views

PostgreSQL: What is the best way to cast from from text to point[]?

I'm looking for a way to cast from a text value: '(180,90),(-180,-90)' into a point[] value: {"(180,90)","(-180,-90)"} What is the best way to do this? One possibility that I can think of is to ...
-1
votes
2answers
79 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
141 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
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
2answers
84 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
826 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?
-2
votes
2answers
279 views

Database engine with counting b-trees for efficient paging support [closed]

Today I realized another fascinating thing about sql databases: They don't do paging well at all. By paging I mean selecting a slice of data defined by a starting record number and a record count ...
-2
votes
1answer
246 views

Why can't a function that accepts an int, be overloaded to accept an interval?

Currently getting an error about type casts, HINT: Could not choose a best candidate function. You might need to add explicit type casts. Let's say I create an overloaded function foo CREATE ...