Where communities thrive

  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
    ohh :/
    su - postgres psql
    /usr/bin/psql: line 19: use: command not found
    /usr/bin/psql: line 20: use: command not found
    /usr/bin/psql: line 21: use: command not found
    /usr/bin/psql: line 22: use: command not found
    /usr/bin/psql: psql: line 24: syntax error near unexpected token `$version,'
    /usr/bin/psql: psql: line 24: `my ($version, $cluster);'
    hello, is this normal ? i tried to reinstall postgresql entirely but it did nothing
    Ben Johnson

    @vroussea Not sure if it's "normal", though I'm sure there's some explanation for it.

    Just don't do it all in one command. su - postgres and then psql and it works fine.

    Yeah, your syntax is wrong. It's su - postgres -c psql if you do it in a single command.
    oh right thanks
    Shah Saraei
    this is maybe not the right channel for this question but I am gonna try anyway. I am doing this in doobie but it essentially uses the java library under the hood.
    Here is the issue: https://github.com/tpolecat/doobie/issues/1172#issuecomment-603270657 I am trying to write to a table that has a column which is an array of a custom type. and I manually write to the PGobject but it doesn't seem to like it.
    I have ARRAY[ ROW(...)]::typeName]::typeName[] which I have used in direct queries but it is telling to me start my arrays with {. :confused:
    @ah0y what kind of help you need ?
    hi.can anyone use the citus forpostgresql, a scale out plugin of postgresql
    Hi guys, I am new to postgresql community. Can somebody tell me what does
    ().* means. For ex. in below code SELECT (clusters).* FROM TABLE
    Sebastjan Hribar
    Hi all, I'm designing a DB table which will hold strings. I've never used INDEX before and I'd like to know if the string is parsed when INDEX is generated? Or do I have to provide a tokenized field of the said string and generate an INDEX on that? The search scenarios would be to find similar strings by certain percentage or strings that contain certain words. I've more details here.
    Hi. Is it possible to set locale per query?
    for background, I'm using postgrest. It'd be super convenient to be able to say hey this user is from france, give him french formatted money and dates, but this guy is US.. etc
    Hi everyone, I need to find all posts wich doesnt have any comment in last 30 days, anyone has any idea of the query??
    Most of these questions seem more suitable for StackOverflow

    Hi Channel , Currently I am using doobie Hikari connection to connect to postgres database and get the result of API request which internally has 3 methods which individually call the postgres for data pool. I have below 2 approaches to perform this task :

    1. Using Hikari Transactor with datasource :


      In this scenario for every internal postgres call, Transactor is creating a single connection to database but not able to shutdown the same connection pool after request completion.
      Advantage : It is creating a single connection pool and executed all internal calls in a single created connection pool.
      Disadvantage : A lot of ideal connection remains open.

    2. Using Resource :

      Resource[IO, HikariTransactor[IO]]

      In this scenario for every internal postgres call, Resource is creating a new connection pool to database and after completion of each request it shutdown the connection pool. example :

      INFO  c.z.h.HikariDataSource - HikariPool-6 - Starting...
      INFO  c.z.h.HikariDataSource - HikariPool-4 - Starting...
      INFO  c.z.h.HikariDataSource - HikariPool-5 - Starting...
      INFO  c.z.h.HikariDataSource - HikariPool-5 - Start completed.
      INFO  c.z.h.HikariDataSource - HikariPool-6 - Start completed.
      INFO  c.z.h.HikariDataSource - HikariPool-4 - Start completed.
      INFO  c.z.h.HikariDataSource - HikariPool-5 - Shutdown initiated...
      INFO  c.z.h.HikariDataSource - HikariPool-5 - Shutdown completed.
      INFO  c.z.h.HikariDataSource - HikariPool-6 - Shutdown initiated...
      INFO  c.z.h.HikariDataSource - HikariPool-6 - Shutdown completed.
      INFO  c.z.h.HikariDataSource - HikariPool-4 - Shutdown initiated...
      INFO  c.z.h.HikariDataSource - HikariPool-4 - Shutdown completed.

      Advantage : Resource is handling the starting and shutdown of created connection pools. Very few ideal connection remains open
      Disadvantage : Creating a connection pool for every single request which might lead to more overhead to memory.

    I am little bit confused on which approached is better suited for my scenario.

    Hi All I want to understand how to upgrade PostgreSQL from lower version to highest version in standalone machine
    Ben Johnson

    @narala558 More information would be helpful... which OS? And which version of PostgreSQL are you running now?

    I posted about my experience here, if you're interested:


    @cbj4074 we have centos 6.5 and version is very old 8.4. I am able to upgrade from 9.6 to some other version but here tricky is not able to test 8.4 version since the Postgres 8.4 version is removed from the source
    Is their any best method to database administration for standalone latest postgresql database
    Hello everyone I'm reading about having constraints (primary key and foreign key) between different schemas in the same database. Could you tell me what could go wrong or why would you not recommend it? Thanks
    Ben Johnson
    @gerardoRolong I can't really think of anything that "could go wrong" specifically because the constraints cross schema boundaries. Did you read something to the contrary?
    I do it all the time, personally.
    Adam Yorwerth
    Hi All. I'm trying to solve a problem that relies on BigSerial and txid values being issued consistently - does anyone know if it's possible for two transactions to interleave their issuance of these two variables? For example, two concurrent transactions both insert to a table with BigSerial and txid being written in the row, and one gets BigSerial: 10, txid: 1000, while the second transaction gets BigSerial 11 and txid: 999. Any help would be much appreciated.
    Sanjeev Sharma
    This is my table
    SELECT json_build_object(
            "id", "simpleRecipeProductOption".id,
            "type", "simpleRecipeProductOption"."type",
            "price", "simpleRecipeProductOption"."price"->0->>"value"
        ) FROM "onlineStore"."simpleRecipeProductOption" WHERE id = 192;
    This is my query
    postgres-error : column "value" does not exist
    This is my error
    What am I doing wrong here? Sorry, if this looks naive. I am new to PSQL.
    Hi, has anyone replicated ownership chaining feature in MSSql to PostgreSQL? I have a SP in PostgreSQL that have execution privileges but i don't want that user to have access to access to run ddl statements against the objects used in the SP.

    so i have a intresting problem , does anyone know a good way to solve this:
    given a table


    • prefix
    • project_id

    and given a table

    • issue_id
    • project_id (FK)

    i wnat the issue id to be of form project.prefix-autoincremented value ex P1-01 , P1-02.... P2-01... so on.
    I cant do this at application level by reading the no of issue count & adding +1 to it since it will have concurrency issues

    Florian Hoedt
    I have two databases a) and b). I want to write tables from a) to b). I thought via FDW it would be possible to connect a) >> with b) and use CREATE FOREIGN TABLE. But it seems like the table will be connected at a) instead of b). I can not access a) from b). I can access b) from a).
    Michal Lacko
    hi all, anyone recognizing this problem reading money type into scala bigDecimal ?
    org.postgresql.util.PSQLException: Bad value for type BigDecimal : 600,000.00
    Michal Lacko
    i guess the comma is a problem
    I created a few aggregate functions but lost the code. How can I view the aggregate functions in the database?
    Thiago Milczarek Sayao
    About Streaming replication: my postgresql "master" is keeping all wal log that were not streamed, how do I set it to use wal_keep_segments ?
    Thiago Milczarek Sayao
    Ah, I did replication slot
    Ankush Chadda
    Hi, I am learning PG wire protocol and currently trying to implement the paramterized query flow using python. I am able to send startup message. But when I send a Parse message, I do not get any response from the server. The protocol says that I should get ParseComplete message back. One reason can be that the server is waiting for some more data from client. Maybe I should try sending a bind query after it ?

    How can i store skills in skill_s text array?
    skill_s Text[];
    jsonObject json =
    "Name": "Kshitiz Kala",
    "Education": "B.Tech",
    "Skills": ["enim", "aliquip", "qui"]
    SELECT jsonObject::TEXT[]->'Skills' into skill_s;
    raise info 'JSON value Name is %', skill_s;

    I need to print like this enim, aliquip, qui


    Roughly what kind of performance difference can I probably expect between the following, assuming several million tags stored, and with each result being about 1000 tags

    # Assume a 1-to-1 relationship & indexed tag_id
    tag(tag_id, name)
    tag_weight(tag_id, weight)
    SELECT t.tag_id, t.name, tw.weight
     FROM tag t INNER JOIN tag_weight tw
     ON t.tag_id = tw.tag_id 
     WHERE t.name = 'abc' and tw.weight > 50;


    tag(tagId, name, weight)
    SELECT t.tag_id, t.name, t.weight
     FROM tag t 
     WHERE t.name = 'abc' and t.weight > 50;
    Revell Bell
    Any resources to best learn joins for a beginner?
    @revellbrice I personally felt like the SQL Tutorial at Mode is a very good balance between newbie-friendly and technical. It gives you some practical food to chew on: https://mode.com/sql-tutorial/
    Revell Bell
    @pha3z Thank you!!!
    Hello! I'm playing with Postgres, Logical Replication, pgoutput and pg_recvlogical and I have a small question. There is something that I didn't understand: if I restart pg_recvlogical, changes that I already received are emitted again. Is there some way to say to Postgres: "I acknowledge receiving this event, you can set your cursor to the next one" in order not to receive changes again?
    Hi, I'm getting an error when starting the postgres docker image, anyone know what I can do about it?
    find: unknown user postgres
    I'm using this image postgres:12.4-alpine which I believe this is the docker file for - https://github.com/docker-library/postgres/blob/master/Dockerfile-alpine.template
    Jotaniya Jeel
    Hi, I want to find the common text values between two columns of a table, columns are of type text[]. Is anyone has any idea on how can I do that?