Gtjkyu

Is there a way to use pg_trgm like operator with btree indexes on PostgreSQL? I have two tables: ref_id_t1 is filled with id_t1 values , however they are not linked by a foreign key as table_2 doesn't know about table_1. I need to do a request on both table like the following: SELECT * FROM table_1 t1 WHERE t1.c1_t1= 'A' AND t1.id_t1 IN (SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE '%abc%'); Without any change or with basic indexes the request takes about a minute to complete as a sequencial scan is peformed on table_2. To prevent this I created a GIN idex with gin_trgm_ops option: CREATE EXTENSION pg_trgm; CREATE INDEX c1_t2_gin_index ON table_2 USING gin (c1_t2, gin_trgm_ops); However this does not solve the problem as the inner request still takes a very long time. EXPLAIN ANALYSE SELECT t2.ref_id_t1 FROM table_2 t2 WHERE t2.c1_t2 LIKE '%abc%' Gives the following Bitmap Heap Scan on table_2 t2 (cost=664.20..189671.00 rows=65058 width=4) (actual...

Avoid repeated columns from a select statement My database contains a table person with these fields username, name, online, visible, ... person username, name, online, visible, ... I have the following query to fetch the person SELECT *, CASE WHEN online = 0 THEN 0 WHEN visible = 0 THEN 0 ELSE online END AS online FROM person where id = SOMEID I am trying to move the online/offline with respect to user-set visibility logic right into the query. Basically show online status depending on visibility status (If the user has chosen to be invisible, show offline even if the user is online). This kind of works but with an issue. I get two repeated columns in the result of this query. username | name | online | visible | ... | online x | x | x | x | ... | x For reasons not in my control I am stuck with using SELECT * instead of manually typing in the columns as SELECT username, name, visible, ... . Is there a way to exclude online field ...

PostgreSQL: Which Datatype should be used for Currency? Seems like Money type is discouraged as described here Money My application needs to store currency, which datatype shall I be using? Numeric, Money or FLOAT? If you have read the whole thread, Numeric is the way to go. – razpeitia Mar 31 '13 at 5:10 5 Answers 5 Numeric with forced 2 units precision. Never use float or float like datatype to represent currency because if you do, people are going to be unhappy when the financial report's bottom line figure is incorrect by + or - a few dollars. The money type is just left in for historical reasons as far as I can tell. That's not why you avoid floating point. Even Numeric will have rounding errors if you divi...

FATAL ERROR lock file “postmaster.pid” already exists I have recently installed PostGIS on my Mac (El Capitan 10.11.4, Postgres is version 9.5.1) using Homebrew, and I am following these instructions - http://morphocode.com/how-to-install-postgis-on-mac-os-x/ When I try to start Postgres using pg_ctl -D /usr/local/var/postgres start I get the following error: $ FATAL: lock file "postmaster.pid" already exists HINT: Is another postmaster (PID 280) running in data directory "/usr/local/var/postgres"? So I spent a few hours researching how to address this, but to no avail. Notably, I tried to kill the PID as recommended in an answer on Superuser - https://superuser.com/questions/553045/fatal-lock-file-postmaster-pid-already-exists- (in the case above, I ran kill 208 ), but as soon as I tried to start Postgres again, kill 208 I got the same error, albeit with a different PID number. I saw a few people recommended deleting the postmaster.pid file, but I feel like may...

Update Postgres Using Flink My dataset named dbData contains set of data. I want to update postgres with that data regularly where the dbData changes regularly on everyday purpose. dbData.map(new Write()) .output(JDBCOutputFormat.buildJDBCOutputFormat() .setDrivername(Utils.properties_fetch("drivername")) .setDBUrl(Utils.properties_fetch("dbURL")) .setUsername(Utils.properties_fetch("username")) .setPassword(Utils.properties_fetch("password")) .setQuery( Write.updatequery) .finish()); My "Write" class looks like the following: public class Write implements MapFunction<Tuple7<String, String, String, String, String, String, String>, Row> { static String updatequery ; private static final long serialVersionUID = 1L; public Row map( Tuple7<String, String, String, String, String, String, String> value)throws Exception { Row obj = new Row(7); ...

Search first character in a column PostgreSQL I want to search the first character from a column by charlist (bracket expression) but it brings all the column characters although there are customers their names starting with non-letters. I use PostgreSQL. SELECT name FROM customs WHERE name ~* '[a-z]' 2 Answers 2 You could also dispense with regular expression matching and simply do: where name < 'a' or name >= '{' { is the mysterious character that follows z in the ASCII chart. Note: For this or any solution, you may need to check whether or not the collation is case-sensitive. { z https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP: Unlike LIKE patterns, a regular expression is allowed to match anywhere within a string, unless the regular expression is explicitly anchored to the beginning or end of the string. LIKE Some e...

Datetime fields in json How do we store postgres datetime objects in java pojo classes for json objects? I am trying to sort them and want to check if I should be comparing datetime or strings? Date compareTo doesn't work but strings comparTo works fine for datetime objects private Date fieldA; private Date fieldB; fieldA.compareTo(fieldB); What do you mean by Date compareTo doesn't work? It always seems to work for me. Have you got an example of two dates for which it gives an incorrect result? – Dawood ibn Kareem Jul 1 at 19:34 Date compareTo @JEE_program I find that extremely difficult to believe. Is there any possibility you have made a mistake of some kind? – Dawood ibn Kareem Jul 1 at 19:43 ...

SQLAlchemy - work with connections to DB and Sessions (not clear behavior and part in documentation) I use SQLAlchemy (really good ORM but documentation is not clear enough) for communicating with PostgreSQL Everything was great till one case when postgres "crashed" cause of maximum connection limits was reached: no more connections allowed (max_client_conn) . That case makes me think that I do smth wrong. After few experiments I figure out how not to face that issue again, but some questions left Below you'll see code examples (in Python 3+, PostgreSQL settings are default) without and with mentioned issue, and what I'd like to hear eventually is answers on following questions: making connection to DB : from sqlalchemy.pool import NullPool # does not work without NullPool, why? def connect(user, password, db, host='localhost', port=5432): """Returns a connection and a metadata object""" url = 'postgresql://{}:{}@{}:{...

Entity Framework and PostgreSQL: quotation marks issue The problem I am facing is around SQL queries in pgAdmin 4. Entity Framework (including its Core edition) capitalizes the names of tables and columns. Which means your SQL will then look something like select e."Id", e."Text" from "Entries" e where e."Text" like '%implicated%' I was googling the way to prevent Entity Framework from capitalizing names but didn't found out much. Is there a workaround to avoid wrapping table and column names in quotes? Thanks in advance! Can you try [Column(“lowerCaseolumnName”)]? – vivek nuna Jun 14 at 18:22 1 Answer 1 Easy done! Go to OnModelCreating method. OnModelCreating You need an extension method (the code is ...

Unable To Plot Graph From PostgreSQL Query Results In Dash App I am attempting to write a simple code to simply plot a bar graph of some fruit names in the x-axis vs corresponding sales units. The aim of this code is just to understand how to query postgres results from heroku hosted database through a dash app. Below is the code, from dash import dash import dash_core_components as dcc import dash_html_components as html import plotly.graph_objs as go import psycopg2 import os DATABASE_URL = os.environ['DATABASE_URL'] conn = psycopg2.connect(DATABASE_URL, sslmode='require') cur = conn.cursor() cur.execute("SELECT fruits FROM pgrt_table") fruits1=cur.fetchall() #print(fruits1) cur.execute("SELECT sales FROM pgrt_table") sales1=cur.fetchall() app = dash.Dash() app.layout = html.Div(children=[ html.H1( children='Hello Dash' ), html.Div( children='''Dash: A web application framework for Python.''...

Ambiguity in the SQL query There are two tables: table customer consists of information about customers and table payment consists of information about payments. Primary key customer_id in the customer table is a foreign key in the table payment_id . The following two queries return identical results: customer payment customer_id customer payment_id SELECT payment.customer_id, last name, amount FROM customer INNER JOIN payment ON customer.customer_id = payment.customer_id SELECT customer.customer_id, last_name, amount FROM customer INNER JOIN payment ON customer.customer_id = payment.customer_id The only difference between the queries is in the first argument in the SELECT clause: payment.customer_id vs customer.customer_id . As the customer_id is the column on which the tables are joining on, the distinction between payment.customer_id and customer.customer_id seems meaningless. However, if I try to omit the table in the query: SELECT payment.customer_id customer....