New answers tagged

1

You can export data to a client file using psql's \copy command. To write query output to a file, you can redirect output with the \o directive psql does not offer ways to align and format output, but psql aligns the output pretty well to begin with, so you won't have as much trouble as with SQL*Plus to get readable output. For advanced needs, you can use \...


2

I think you've answered your own question, but here are some observations. From the question, I suspect that the abc database had all of its tables created in the default public schema, while the xyz database has its tables created in the xyz schema. Since public is part of the default search path, psql could find abcs tables successfully while in xyz you ...


1

I saw this answer and it partially solved the problem I was having ("The relations appear to be missing because Postgres does not copy the search_path to the new database"). That is, I can see the tables like this: xyz= \dt xyz.* but not like this: xyz= \dt I tried setting the search path, currently: "$user", public to: public, xyz ...


0

If you created the dump with -c and don’t want to drop and recreate the database, then you will have to doctor the dump file to remove the DROP and CREATE statements (and anything else you don’t want changed). The edited file can be fed to psql using the -f option.


0

Since the function is declared as RETURNS SETOF bigint[] You need RETURN NEXT: RETURN NEXT stuff_list; Alternatively, to return a single array: RETURNS bigint[] ... RETURN stuff_list; Or to return a set of bigint: RETURNS SETOF bigint ... RETURN QUERY SELECT unnest(stuff_list); (Or return IDs directly, instead of aggregating and unnesting back.)


1

Use \set to set a variable inside psql. Read about SQL interpolation in the manual here: https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-INTERPOLATION Ideally, your script would read: SELECT * FROM :"my_schema".:"my_table" LIMIT 1; With schema and table double-quoted separately, for identifier-interpolation. Then set ...


Top 50 recent answers are included