天道酬勤,学无止境

postgresql

Retrieve username inside a trigger function

In our database we have a login table featuring coloumns username and password. How can this username be retrieved inside a trigger function so that it can also be audited. user keyword inside the trigger function retrieves the postgresql username, what I need is the application username. Any suggestions?

2022-01-19 02:31:03    分类:问答    postgresql   triggers

Which SQL pattern is faster to avoid inserting duplicate rows?

I know of two ways to insert without duplication. The first is using a WHERE NOT EXISTS clause: INSERT INTO table_name (col1, col2, col3) SELECT %s, %s, %s WHERE NOT EXISTS ( SELECT * FROM table_name AS T WHERE T.col1 = %s AND T.col2 = %s) the other is doing a LEFT JOIN: INSERT INTO table_name (col1, col2, col3) SELECT %s, %s, %s FROM ( SELECT %s, %s, %s ) A LEFT JOIN table_name B ON B.COL1 = %s AND B.COL2 = %s WHERE B.id IS NULL LIMIT 1 Is there a general rule as to one being faster than the other, or does it depend on the tables? Is there a different way which is better than both?

2022-01-19 02:20:27    分类:问答    sql   database   postgresql   query-optimization

Best way to store images in PostgreSQL [duplicate]

This question already has answers here: Storing Images in PostgreSQL (7 answers) Closed 8 years ago. I am working on a web site that will start off with hundreds of thousands of images in it to hopefully hundreds of millions. Most of the images won't exceed 300K in physical size. What is the best way to store these in PostgreSQL? One thing I know for sure is that the system will be using schemas, partitions, and tablespaces to manage storage.

2022-01-19 01:54:54    分类:问答    postgresql   blob

PostgreSQL - JOIN on string_agg

The title may be misleading because I just can't seem to find the right for it. But here it goes. I have three tables. Students student_id | name 1 Rhon Subjects subject_id | subject_name | student_id 1 Physics 1 2 Math 1 Grades grade_id | student_id | subject_id | grade 1 1 1 90 2 1 2 89 3 1 2 88 I would like to result to be like this: student_id | student_name | subject_name | grades 1 Rhon Physics 90 1 Rhon Math 88,89 My current query is: SELECT students.student_id, subjects.subject_id, string_agg(grades.grade, ',') FROM students JOIN subjects ON students.student_id = subjects.student_id

2022-01-19 00:43:04    分类:问答    postgresql   string-aggregation

Run a postgresql command with ansible playbook. Postgresql requires password

I would like to write an Ansible playbook which tries to handle some postgresql command. I did earlier a similar playbook, but in that case Postgresql didn't need password. I thought I could use my old playbook, which works fine, but I was wrong. This database requires password. I know that Ansible has postgresql support which is fine (postgresql_db, postgresql_ext, postgresql_lang, postgresql_privs, postgresql_user) and with them I can do half of the work and it handles password just fine, BUT I can't figure out how to send a simple sql commands to my database. In my old playbook I used

2022-01-18 23:58:44    分类:问答    database   postgresql   ansible-playbook

NLog to PostgreSQL connection

I have a problem in connecting with the postgres database through NLog. I made the database with query: CREATE TABLE system_logging ( ID serial primary key, entered_date date default Now(), log_application varchar(200) NULL, log_date character varying(100) NULL, log_level character varying(100) NULL, log_logger character varying(8000) NULL, log_message character varying(8000) NULL, log_machine_name character varying(8000) NULL, log_user_name character varying(8000) NULL, log_call_site character varying(8000) NULL, log_thread character varying(100) NULL, log_exception character varying(8000)

2022-01-18 23:13:57    分类:问答    postgresql   nlog

Is it possible to use a stable function in an index in Postgres?

I've been working on a project at work and have come to the realization that I must invoke a function in several of the queries' WHERE clauses. The performance isn't terrible exactly, but I would love to improve it. So I looked at the docs for indexes which mentioned that: An index field can be an expression computed from the values of one or more columns of the table row. Awesome. So I tried creating an index: CREATE INDEX idx_foo ON foo_table (stable_function(foo_column)); And received an error: ERROR: functions in index expression must be marked IMMUTABLE So then I read about Function

2022-01-18 22:43:20    分类:问答    postgresql   postgresql-9.0

How to do ORM using reverse engineering in Hibernate in Intellij IDEA?

I have 100 tables and most of them have average 40-50 fields. Some table have 5-6 fields, so I can make it's object relational mapping easily with DB. But I have so many numbers of tables as well as fields so Is it possible to create ORM using reverse engineering in Intellij IDEA ? If yes then how to do so ? I have done mapping simple like : @Entity @SequenceGenerator(name = "sequence", sequenceName = "pat_id_seq") @Table(name = "pat") public class Patron extends BaseEntity { @Column(name = "p_id") private String pID; @Column(name = "user_password") private String userPassword; } I am using

2022-01-18 21:56:07    分类:问答    hibernate   postgresql   orm   intellij-idea   reverse-engineering

Using 'end' as column name in Ruby on Rails (MySQL)

I had an model with an "end" column (datetime format), only to discover that Heroku crashes and burns with illogical Active Record errors whenever I attempted to reference the column in a query. I spent two hours trying to debug the extremely simple query, after which point I renamed the column to "end_at" and all of my problems disappeared. Has anybody else experienced this issue? I'm curious of the reasoning behind this and hope that we can help others avoid the same mistake. A similar question has been asked before, but a clear answer was not presented.

2022-01-18 21:29:43    分类:问答    mysql   ruby-on-rails   postgresql   activerecord   heroku

Why can't I successfully use rake db:drop or rake db:purge?

Commands that work: rake db:migrate rake db:rollback rake db:seed Commands that don't: rake db:drop rake db:drop:all rake db:migrate:reset rake db:purge If I use db:rollback enough times (or with STEP), my databases drop successfully, but not when I use db:drop / db:drop:all. db:migrate:reset and db:purge leave all the data untouched. Running with trace, watching development.log, system logs and watching DB queries via the DB monitor (no SQL queries appear to run for those commands.) No errors, either. Running: Rails 4.2 postgres 9.4 CentOS 7

2022-01-18 21:03:59    分类:问答    ruby-on-rails   database   postgresql