Re: any psql \copy tricks for default-value columns without source data?

On Tue, May 05/06/14, 2014 at 01:22:20PM -0700, David G Johnston wrote:
> So, I am trying to import a file into a table and want to assign a sequence
> value to each record as it is imported.
>
> I know that I can pre-process the input file and simply add the needed data
> but I am curious if maybe there is some trick to having defaults populate
> for missing columns WITHOUT explicitly specifying each and every column that
> is present?

There isn't really any way to get around specifying the columns. I wrote
a tool to help import files into the database, you could probably make
use of it:

https://github.com/f0rk/csv2table

Assuming the table already exists and your csv has columns with the same
names as the columns in your table:

csv2table --file /path/to/your/file.csv --no-create --copy --backslash | psql

If the table doesn't exist, you could do:

csv2table --file /path/to/your/file.csv --copy --backslash | vipe | psql

And edit the create statement to add a SERIAL column (the copy command
will specify all of the columns in the file for you).

It's a 95% solution I use to get delimited files into the database. It's
not perfect but it works most of the time. If you have any issues or
feature requests feel free to open an issue on github.

-Ryan Kelly

On Tue, May 6, 2014 at 4:22 PM, David G Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> So, I am trying to import a file into a table and want to assign a sequence
> value to each record as it is imported.
>
> I know that I can pre-process the input file and simply add the needed data
> but I am curious if maybe there is some trick to having defaults populate
> for missing columns WITHOUT explicitly specifying each and every column that
> is present?
>
> Thanks!
>
> David J.
>
>
>
>
> --
> View this message in context: http://postgresql.1045698.n5.nabble.com/any-psql-copy-tricks-for-default-value-columns-without-source-data-tp5802795.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general